asp.net实现将Excel中多个sheet数据导入到SQLSERVER中的

public DataSet GetDataSet(string filePath) { string Connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"); OleDbConnection Conn = new OleDbConnection(Connstr); //创建ArrayList对象 存放所有sheetname ArrayList sheetNamelist = new ArrayList(); //获取配置Excel中sheet总数(这里是根据项目需求配置的) 如果需要导入Excel表格所有sheet数据则将此代码删除 int sheetCount = Convert.ToInt32(ConfigurationManager.AppSettings["sheetCount"].ToString()); DataSet dsExcel = new DataSet(); try { if (Conn.State == ConnectionState.Closed) { Conn.Open(); } DataTable dtExcelSchema = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); string sheetName = string.Empty; if (dtExcelSchema.Rows.Count > sheetCount) { Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!-- alert('很抱歉!你上传Excel文件sheet总数过多不能大于10个sheet..!! ') // --></mce:script>"); return; } else { for (int j = 0; j < dtExcelSchema.Rows.Count; j++) { sheetName = String.Format("Sheet{0}$", j + 1); sheetNamelist.Add(sheetName); } } } catch (Exception ex) { throw new Exception(ex.Message.ToString(), ex); } finally { Conn.Close(); } try { string strSQL = string.Empty; for (int i = 0; i < sheetNamelist.Count; i++) { strSQL = "select * from [" + sheetNamelist[i].ToString() + "]"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, Conn); DataTable dtExcel = new DataTable(sheetNamelist[i].ToString()); da.Fill(dtExcel); dsExcel.Tables.Add(dtExcel); } return dsExcel; } catch (Exception ex) { throw new Exception(ex.Message.ToString(), ex); } } //从Excel 表中取出数据 将取出来的数据插入到数据库中 public void InsertData(DataSet ds) { string strSQL=string.Empty; if (ds.Tables[0].Rows.Count > 0) { for (int j = 0; j < ds.Tables.Count; j++) { for(int i=0;i<ds.Tables[j].Rows.Count;i++) { DataRow dr=ds.Tables[j].Rows[i]; //组名 string groupname = dr["组名"].ToString().Trim(); //联系人 string contactName = dr["联系人"].ToString().Trim(); //手机号码 string mobile = dr["手机号码"].ToString().Trim(); //公司名称 string companyName = dr["公司名称"].ToString().Trim(); //公办号码 string officeNum = dr["办公号码"].ToString().Trim(); //家庭号码 string homeNum = dr["家庭号码"].ToString().Trim(); //邮箱 string Email = dr["邮 箱"].ToString().Trim(); //联系地址 string address = dr["联系地址"].ToString().Trim(); //创建时间 string createtime = dr["创建时间"].ToString().Trim(); //性别 string Sex = dr["性别"].ToString().Trim(); //手机套餐类型 string mobileType = dr["手机套餐类型"].ToString().Trim(); //是否开通通信助理 string isOpen = dr["是否开通通信助理"].ToString().Trim(); //SQL 语句 strSQL = "insert into msm_Excel(groupName,Mobile,Name,companyName,officeNum,homeNum,Emial,address,Createtime,Sex,mobileType,isOpen)values('" + groupname + "','" + mobile + "','" + contactName + "','" + companyName + "','" + officeNum + "','" + homeNum + "','" + Email + "','" + address + "','" + createtime + "','" + Sex + "','" + mobileType + "','" + isOpen + "')"; try { int n = SQLHelper.SqlDataExecute(strSQL); if (n > 0) { Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!-- alert('数据插入成功!') // --></mce:script>"); Label1.Text = "一共成功插入" + ds.Tables[j].Rows.Count.ToString() + "条数据"; } else { Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!-- alert('服务器繁忙！请稍候再试..!') // --></mce:script>"); } } catch (Exception ex) { throw ex; } } } } else { Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!-- alert('此Excel文件中无数据!!!') // --></mce:script>"); } } //调用 //获取上传文件名 string fileName = FileUpload1.FileName; //判断是否存在上传文件 if (FileUpload1.PostedFile.FileName.Length == 0) { Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!-- alert('请选择你要上传的Excel文件!!') // --></mce:script>"); } //判断上传的文件类型是否正确 else if (!Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xls") && !Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xlsx")) { Page.RegisterStartupScript("", "<script>alert('很抱歉!你上传的文件类型不正确!只能上传Excel类型的文件!')</script."); } else { //获取上传的文件路径 filePath = Server.MapPath("TxtFiles//") + DateTime.Now.ToString("yyyyMMddhhmmss") + fileName; this.FileUpload1.PostedFile.SaveAs(filePath); ds = GetDataSet(filePath); InsertData(ds); }