把Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫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; //將數(shù)據(jù)集合和目標(biāo)服務(wù)器的字段對(duì)應(yīng) 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]);//把數(shù)據(jù)插入到數(shù)據(jù)庫中 } catch (Exception e) { throw new Exception("Import Failed"); } finally { ds.Dispose(); conn.Close(); sbc.Close(); } } //把Excel文件導(dǎo)入到數(shù)據(jù)庫中 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(\"" + "數(shù)據(jù)導(dǎo)入成功!" + "\");</script>")); //Response.Write("<script language=javascript>alert('數(shù)據(jù)導(dǎo)入成功!');</script>"); } else { this.ClientScript.RegisterStartupScript(this.GetType(), "return1", string.Format("<script language=javascript>alert(\"" + "數(shù)據(jù)導(dǎo)入失敗!請(qǐng)重試!" + "\");</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; } //該方法實(shí)現(xiàn)從Excel中導(dǎo)出數(shù)據(jù)到DataSet中,，其中filepath為Excel文件的絕對(duì)路徑,，sheetname為表示那個(gè)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; |
|
|
