EXCEL(.xls和.xlsx)导入到数据库代码,CSharp代码库,德仔网

代码语言

代码分类

【CSharp】 EXCEL(.xls和.xlsx)导入到数据库

作者:玉云 / 发布于2014/10/22/ 2268

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;
	 }
	 #endregion

试试其它关键字

同语言下

可能有用的

玉云贡献的其它代码(22)