1.获取DataTable
/// <summary> /// 查询Excel文件中的数据 /// </summary> /// <param name="strPath">文件路径</param> /// <param name="sheetName">工作表名</param> /// <returns>DataTable</returns> public DataTable getExcel(string strPath,string sheetName) { string mystring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ‘" + strPath + "‘;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘";
//连接串, HDR=yes 表示excel第一行是标题行，反之表示第一行不是标题行 OleDbConnection cnnxls = new OleDbConnection(mystring); cnnxls.Open(); OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetName + "]", cnnxls);//查询工作表中的数据 DataSet ds = new DataSet(); myDa.Fill(ds, "[" + sheetName + "]"); cnnxls.Close(); DataTable dt = ds.Tables[0]; return dt; }
2.点击按钮函数，将数据存入数据库
/// <summary> /// 把Excel中的数据保存到sql数据库中 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSave_Click(object sender, EventArgs e) {
if (LaValue.Text.Length >0) { Helper.ExcelHelper eh = new Helper.ExcelHelper();//公共方法类对象，封装Excel操作的方法 DataTable dt = eh.getExcel(LaValue.Text, DropDownList1.Text.ToString());//得到Datatable DataRow[] dr = dt.Select();//得到 int rowsnum = dt.Rows.Count;//表中数据行数 List<String> lstMsg = new List<string>();//保存失败信息 if (rowsnum == 0) { Page.ClientScript.RegisterStartupScript(this.GetType(), "a", "alert(‘Excel表为空表,无数据!‘);", true); } else { String error = ""; for (int i = 0; i < dr.Length; i++)//循环插入数据 { // excel列名不能变 string C_OraName = dr[i]["CompanyName"].ToString(); string C_Town = dr[i]["Town"].ToString(); string C_Trding = dr[i]["Trading"].ToString(); string C_PostCode = dr[i]["PostCode"].ToString(); string C_Phone = dr[i]["Phone"].ToString(); if (String.IsNullOrEmpty(C_OraName)) { error += "公司名不能为空 "; } if (error.Equals("")) // { Model.Model_Companey company_model = new Model.Model_Companey(); BLL.BLL_Companey company_bll = new BLL.BLL_Companey(); company_model.C_OraName = C_OraName; company_model.C_Town = C_Town; company_model.C_Trding = C_Trding; company_model.C_Postcode = C_PostCode; company_model.C_Phone = C_Phone; // 公司名之前未出现过 if (Helper.SQLHelper.SqlContains(C_OraName, "Companey", "C_OraName") == false) { company_bll.Add(company_model);//添加数据 } else { error = C_OraName + "已存在"; lstMsg.Add("未导入成功，" + "原因:" + error + "。");//保存错误信息 error = ""; } } else { } } this.txtMessageList.Items.Add("导入完成。"); if (null != lstMsg) { this.txtMessageList.Items.Add("共有" + lstMsg.Count() + "条记录未成功。<br /><br />"); foreach (string s in lstMsg) { this.txtMessageList.Items.Add(s); } } } } }
3.将工作表名放入DropDownList控件中以作选择
/// <summary> /// 把工作表名绑定到DropDownList控件中 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnup_Click(object sender, EventArgs e) { if (FileUpload1.HasFile) { string ext = System.IO.Path.GetExtension(FileUpload1.FileName);//获取文件后缀 if (ext.ToLower() != ".xls" && ext.ToLower() != ".xlsx") { Page.ClientScript.RegisterStartupScript(this.GetType(), "a", "alert(‘请选择一个Excel文件!‘);", true); return; } HttpFileCollection files = HttpContext.Current.Request.Files;//获取页面上所有的FileUpload控件 string tmp = files[0].FileName; string savePath = "e:/test/" + DateTime.Now.ToString("yymmdd") + ".xls";//保存路径 LaValue.Text = savePath; //保存路径到一个不可见的Label控件中 FileUpload1.SaveAs(savePath);//保存文件 DropdownListBind(savePath);//绑定DropDownList } }
4.绑定DropDownList函数
/// <summary> /// 绑定DropDownList /// </summary> /// <param name="savePath">路径</param> protected void DropdownListBind(string savePath) { string mystring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = ‘" + savePath + "‘;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘";//连接Excel串 OleDbConnection cnnxls = new OleDbConnection(mystring);//创建连接 cnnxls.Open(); DataSet ds = new DataSet(); DataTable dt = cnnxls.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获取工作表名 DropDownList1.DataSource = dt; foreach (DataRow dr in dt.Rows) { DropDownList1.Items.Add((String)dr["TABLE_NAME"]);//向控件中添加工作表名 } cnnxls.Close(); }