把Excel数据导入数据库public void Import(DataSet ds, string table){ SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction); conn.Open(); SqlBulkCopy sbc = new SqlBulkCopy(conn); sbc.DestinationTableName = table;// ds.Tables[0].TableName; //将数据集合和目标服务器的字段对应 for (int q = 0; q < ds.Tables[0].Columns.Count; q++) { sbc.ColumnMappings.Add(ds.Tables[0].Columns.ColumnName, ds.Tables[0].Columns.ColumnName); } try { sbc.WriteToServer(ds.Tables[0]);//把数据插入到数据库中 } catch (Exception e) { throw new Exception("Import Failed"); } finally { ds.Dispose(); conn.Close(); sbc.Close(); } } //把Excel文件导入到数据库中 protected void BtnImport_Click(object sender, EventArgs e) { if (this.FileUpload1.HasFile == true) { ArrayList sheetname = ExcelSheetName(this.FileUpload1.PostedFile.FileName); ds = ExcelDataSource(this.FileUpload1.PostedFile.FileName, sheetname[0].ToString()); int count = ds.Tables[0].Rows.Count; int ret = stuBll.Import(ds, "zxsjbxx"); if (ret == 1) { this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert(\"" + "数据导入成功!" + "\");</script>")); //Response.Write("<script language=javascript>alert('数据导入成功!');</script>"); } else { this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert(\"" + "数据导入失败!请重试!" + "\");</script>")); } } else { this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert(\"" + "没有选择文件!" + "\");</script>")); } } //获得Excel中的所有sheetname。 public ArrayList ExcelSheetName(string filepath) { ArrayList al = new ArrayList(); string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=yes'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); foreach (DataRow dr in sheetNames.Rows) { al.Add(dr[2]); } return al; } //该方法实现从Excel中导出数据到DataSet中,其中filepath为Excel文件的绝对路径,sheetname为表示那个Excel表; public DataSet ExcelDataSource(string filepath, string sheetname) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filepath + "';Extended Properties='Excel 8.0;HDR=yes'"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn); DataSet ds = new DataSet(); oada.Fill(ds); return ds; |
|