将Excel导入Sqlserver表

本文介绍了将Excel导入Sqlserver表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 限时删除！！ 嗨Dude， 我将excel表导入sqlserver表，excel有130个coulmns，我想只导入20列。 sqlserver表应该替换为新记录而旧的记录应该被删除，但旧的一个应该在导入错误或一些粉碎时出现。 i已经完成导入excel到sqlserver但是不能做sqlserver端。 我刚创建了表格并将数据从excel导入表格。 请找到我用过的代码： 1）我是在UI中使用文件上传和提交按钮。 受保护 void submit_Click（ object sender，EventArgs e）{ string excelConnectionString; string ExcelContentType = application / octet-流; string Excel2010ContentType = application / vnd。 openxmlformats-officedocument.spreadsheetml.sheet; if （fup1.HasFile）{ if （fup1.PostedFile.ContentType == ExcelContentType || fup1.PostedFile.ContentType == Excel2010ContentType） { try { string path = string .Concat（Server.MapPath（ 〜/ TempFiles /），fup1.FileName）; fup1.SaveAs（path）; if （Path.GetExtension（fup1.FileName）== .xls || Path.GetExtension（fup1.FileName）== 。 XLSX） {excelConnectionString = string .Format（ @ Provider = Microsoft.Jet.Oledb.4.0; Data Source = + path + ;扩展属性= Excel 8.0;）; } else { excelConnectionString = string .Format（ @ Provider = Microsoft.ACE.Oledb.12.0; Data Source = + path + ;扩展属性= Excel 12.0;）; } 使用（OleDbConnection connection = new OleDbConnection（excelConnectionString）） { DataSet DtSet = null ; DataTable dt = new DataTable（）; DataTable dt1 = new DataTable（）; OleDbDataAdapter MyCommand = null ; MyCommand = new System.Data.OleDb.OleDbDataAdapter（ select [from [MobilityOM $]，连接）; DtSet = new System.Data.DataSet（）; MyCommand.Fill（DtSet）; dt = DtSet.Tables [ 0 ]; string con1 = dataManager.ConnectionString.ToString（）; 使用（SqlBulkCopy bulkCopy = new SqlBulkCopy（con1）） {bulkCopy。 ColumnMappings.Add（ 3 ， Opportunity_ID）; bulkCopy.ColumnMappings.Add（ 4 ， Reporting_Status）; bulkCopy.ColumnMappings.Add（ 5 ， OG）; bulkCopy.ColumnMappings.Add（ 9 ， Geo_Area）; bulkCopy.ColumnMappings.Add（ 10 ， Geo_Unit）; bulkCopy.ColumnMappings.Add（ 11 ， Master_Client_Name）; bulkCopy.ColumnMappings.Add（ 12 ， Master_Client_Class）; bulkCopy.ColumnMappings.Add（ 16 ， CR_Name）; bulkCopy.ColumnMappings.Add（ 19 ， Opportunity_Class）; bulkCopy.ColumnMappings.Add（ 20 ， 舞台）; bulkCopy.ColumnMappings.Add（ 23 ， 受限）; bulkCopy.ColumnMappings.Add（ 24 ， CREATE_DATE）; bulkCopy.ColumnMappings.Add（ 27 ， Proposal_Submission_Date）; bulkCopy.ColumnMappings.Add（ 29 ， Expected_Contract_Signing_Date）; bulkCopy.ColumnMappings.Add（ 31 ， Status_Since_Date）; bulkCopy.ColumnMappings.Add（ 42 ， Total_Net_Revenue）; bulkCopy.ColumnMappings.Add（ 45 ， Win_Probability）; bulkCopy.ColumnMappings.Add（ 117 ， 级别1\" ）; bulkCopy.ColumnMappings.Add（ 118 ， 级别2\" ）; bulkCopy.ColumnMappings.Add（ 121 ， Net_Revenue）; bulkCopy.DestinationTableName = tblmobilityimport; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer（dt）; bulkCopy.Close（）; }} } catch （Exception ex）}}}} 解决方案 如果打算恢复导出失败，你可以使用交易。 参考以下链接 了解ADO.NET中的事务和TransactionScope的初学者教程 [ ^ ] 希望这有帮助... Hi Dude,I'm importing excel sheet into sqlserver table,excel is having 130 coulmns and i wanna import only 20 columns. sqlserver table should be replaced with new records and old one wanna deleted,but old one should be there when import failes or some crushes occur.i have completed import excel into sqlserver but not able to do the sqlserver side.Just i have created table and importing data's into table from excel.Please find the code i have used:1)i'm using file upload and submit button in UI. protected void submit_Click(object sender, EventArgs e) { string excelConnectionString; string ExcelContentType = "application/octet-stream"; string Excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; if (fup1.HasFile) { if (fup1.PostedFile.ContentType == ExcelContentType || fup1.PostedFile.ContentType == Excel2010ContentType) {try {string path = string.Concat(Server.MapPath("~/TempFiles/"), fup1.FileName); fup1.SaveAs(path);if (Path.GetExtension(fup1.FileName) == ".xls" || Path.GetExtension(fup1.FileName) == ".XLSX"){ excelConnectionString = string.Format(@"Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + path + "; Extended Properties=Excel 8.0;"); } else {excelConnectionString = string.Format(@"Provider=Microsoft.ACE.Oledb.12.0; Data Source=" + path + "; Extended Properties=Excel 12.0;"); } using (OleDbConnection connection = new OleDbConnection(excelConnectionString)) { DataSet DtSet = null; DataTable dt = new DataTable(); DataTable dt1=new DataTable(); OleDbDataAdapter MyCommand = null; MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [MobilityOM$]", connection); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dt = DtSet.Tables[0]; string con1 = dataManager.ConnectionString.ToString(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con1)) { bulkCopy.ColumnMappings.Add(3, "Opportunity_ID"); bulkCopy.ColumnMappings.Add(4, "Reporting_Status"); bulkCopy.ColumnMappings.Add(5, "OG"); bulkCopy.ColumnMappings.Add(9, "Geo_Area"); bulkCopy.ColumnMappings.Add(10, "Geo_Unit"); bulkCopy.ColumnMappings.Add(11, "Master_Client_Name"); bulkCopy.ColumnMappings.Add(12, "Master_Client_Class"); bulkCopy.ColumnMappings.Add(16, "CR_Name"); bulkCopy.ColumnMappings.Add(19, "Opportunity_Class"); bulkCopy.ColumnMappings.Add(20, "Stage"); bulkCopy.ColumnMappings.Add(23, "Restricted"); bulkCopy.ColumnMappings.Add(24, "Create_Date"); bulkCopy.ColumnMappings.Add(27, "Proposal_Submission_Date"); bulkCopy.ColumnMappings.Add(29, "Expected_Contract_Signing_Date"); bulkCopy.ColumnMappings.Add(31, "Status_Since_Date"); bulkCopy.ColumnMappings.Add(42, "Total_Net_Revenue"); bulkCopy.ColumnMappings.Add(45, "Win_Probability"); bulkCopy.ColumnMappings.Add(117, "Level1"); bulkCopy.ColumnMappings.Add(118, "Level2"); bulkCopy.ColumnMappings.Add(121, "Net_Revenue"); bulkCopy.DestinationTableName = "tblmobilityimport"; bulkCopy.BatchSize = dt.Rows.Count; bulkCopy.WriteToServer(dt); bulkCopy.Close(); } } }catch (Exception ex) } } } } 解决方案 这篇关于将Excel导入Sqlserver表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..