C# EXCEL(.xls和.xlsx)导入到数据库 C# EXCEL(.xls和.xlsx)导入到数据库
原理：
1.判断是否是Excel文件（xls和xlsx两种文件格式，Excel2003和Excle2007）
2.上传Excel文件到服务器的文件夹（要在服务器设置用户ASPNET对此文件夹的读写权限）
3.对此Excel文件进行操作（可以作为OLEdb数据源、ODBC数据源，还可以通过创建Excel.ApplicationClass）
GridView1.DataSource = null;
GridView1.DataBind();//先清除GridView1之前的数据绑定
#region 显示Excel数据
string clientFilename = FileUpload1.PostedFile.FileName.ToLower();
string serverFilename = "";
if (clientFilename == "")
{
Label1.Text = "Path and filename can't null！";
return;
}
if (clientFilename.ToLower().IndexOf(".xlsx") > 0)
{
serverFilename = ".xlsx";
}
else
{
if (clientFilename.ToLower().IndexOf(".xls") > 0 && clientFilename.EndsWith("xls"))
{
serverFilename = ".xls";
}
else
{
Label1.Text = "Must be Excel file！";
return;
}
}
serverFilename = "~/upload/" + "Test" + DateTime.Now.Year.ToString()
+ (DateTime.Now.Month > 9 ? DateTime.Now.Month.ToString() : "0" + DateTime.Now.Month.ToString())
+ (DateTime.Now.Day > 9 ? DateTime.Now.Day.ToString() : "0" + DateTime.Now.Day.ToString())
+ (DateTime.Now.Hour > 9 ? DateTime.Now.Hour.ToString() : "0" + DateTime.Now.Hour.ToString())
+ (DateTime.Now.Minute > 9 ? DateTime.Now.Minute.ToString() : "0" + DateTime.Now.Minute.ToString())
+ (DateTime.Now.Second > 9 ? DateTime.Now.Second.ToString() : "0" + DateTime.Now.Second.ToString())
+ DateTime.Now.Millisecond.ToString()+serverFilename;
serverFilename = MapPath(serverFilename);
if (File.Exists(serverFilename))
{
File.Delete(serverFilename);
}
FileUpload1.SaveAs(serverFilename);//上传文件
string strResult = "";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + serverFilename + "';Extended Properties='Excel 8.0;HDR=YES;'";
DataTable dt = new DataTable();
if (serverFilename.ToLower().IndexOf(".xlsx") > 0)
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + serverFilename + "';Extended Properties='Excel 12.0;HDR=YES'";
}
if (serverFilename.ToLower().IndexOf(".xls") > 0 && serverFilename.EndsWith("xls"))
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + serverFilename + "';Extended Properties='Excel 8.0;HDR=YES;'";
}
OleDbConnection conn = new OleDbConnection(strConn);
try
{
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [sheet1$]", strConn);
adapter.Fill(dt);
this.GridView1.DataSource = dt;
GridView1.DataBind();
conn.Close();
}
catch (Exception ee)
{
Label1.Text = Label1.Text + " File's content can't matched,please redo it！" + ee.Message;
//Label1用来显示错误信息
return;
}