讀取Excel的記錄并導(dǎo)入SQL數(shù)據(jù)庫
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
準(zhǔn)備一下，近段時(shí)間，需要把Excel的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫中。 using System.Configuration; using System.Data; using System.Data.OleDb; using System.Data.SqlClient;
你可以寫一個(gè)方法，是去讀取Excel文檔的方法，返回DataSet數(shù)據(jù)集：
  private DataSet ImportExcelToDataSet(string virtualPath, string sqlQueryStatement) { string excelConnectionString = DB.ExcelConnectionString(Server.MapPath(virtualPath)); OleDbConnection dc = new OleDbConnection(excelConnectionString); OleDbDataAdapter da = new OleDbDataAdapter(sqlQueryStatement, dc); DataSet ds = new DataSet(); da.Fill(ds); return ds; }
try { string cs = ConfigurationManager.ConnectionStrings["InsusSqlConnectionString"].ConnectionString; using (SqlConnection sqlConn = new SqlConnection(cs)) { string sqlQueryStatement = "SELECT [Material],[Plnt],[Level],[Item],[Component],[Object description] FROM [Sheet1$]"; string virtualPath = "~/App_Data/Book1.xlsx"; DataSet ds = ImportExcelToDataSet(virtualPath, sqlQueryStatement); DataTable dt = ds.Tables[0]; sqlConn.Open(); using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn)) { sqlbc.DestinationTableName = "BOM"; sqlbc.ColumnMappings.Add("Material", "Material"); sqlbc.ColumnMappings.Add("Plnt", "Plnt"); sqlbc.ColumnMappings.Add("Level", "Level"); sqlbc.ColumnMappings.Add("Item", "Item"); sqlbc.ColumnMappings.Add("Component", "Component"); sqlbc.ColumnMappings.Add("Object description", "Object description"); sqlbc.WriteToServer(dt); Response.Write("數(shù)據(jù)導(dǎo)入成功！"); } } } catch (Exception ex) { throw ex; }
下面內(nèi)容于2017-10-17 14:38分添加： private OleDbDataReader ImportExcelToDataReader(string virtualPath, string sqlQueryStatement) { string excelConnectionString = DB.ExcelConnectionString(Server.MapPath(virtualPath)); OleDbConnection oleConn = new OleDbConnection(excelConnectionString); OleDbCommand oleComm = new OleDbCommand(sqlQueryStatement, oleConn); oleConn.Open(); return oleComm.ExecuteReader(); }
string sqlQueryStatement = "SELECT [Material],[Plnt],[Level],[Item],[Component],[Object description] FROM [Sheet1$]"; string virtualPath = "~/App_Data/Book1.xlsx"; string cs = ConfigurationManager.ConnectionStrings["InsusSqlConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(cs); OleDbDataReader reader = ImportExcelToDataReader(virtualPath, sqlQueryStatement); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn)) { bulkCopy.DestinationTableName = "BOM"; try { sqlConn.Open(); bulkCopy.WriteToServer(reader); Response.Write("數(shù)據(jù)導(dǎo)入成功。"); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { reader.Close(); sqlConn.Close(); } } 該文章在 2017/11/16 23:48:37 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
