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("", ""); 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 0) { Page.RegisterStartupScript("", ""); Label1.Text = "一共成功插入" + ds.Tables[j].Rows.Count.ToString() + "条数据"; } else { Page.RegisterStartupScript("", ""); } } catch (Exception ex) { throw ex; } } } } else { Page.RegisterStartupScript("", ""); } } //调用 //获取上传文件名 string fileName = FileUpload1.FileName; //判断是否存在上传文件 if (FileUpload1.PostedFile.FileName.Length == 0) { Page.RegisterStartupScript("", ""); } //判断上传的文件类型是否正确 else if (!Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xls") && !Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower().Equals(".xlsx")) { Page.RegisterStartupScript("", "