如何通过将行分配给表列来导入xls
本文关键字：导入 xls 何通过 分配 | 更新日期: 2023-09-27 18:06:57
我只想做一个xls列映射功能，以便用户可以直接将xls数据导入sql。下面是我的代码:
这里我必须分配用户输入的行号。从文本框分配每个目标列号的更好方法是什么?有人能帮帮我吗?
更新我刚刚做了新的代码，但这次运气不好，我发现了一些没有发现的列异常。
protected void lbut_import_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
if (FileUpload1.FileContent.Length > 0)
{
string Foldername;
string Extension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
string filename = DateTime.Now.ToString("ddMMyyyy_HHmmss");
if (Extension == ".XLS" || Extension == ".XLSX" || Extension == ".xls" || Extension == ".xlsx")
{
Foldername = Server.MapPath("~/Files/");
FileUpload1.PostedFile.SaveAs(Foldername + filename + Extension);
string conString = string.Empty;
switch (Extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, Foldername + filename + Extension);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[23]
{new DataColumn("Name", typeof(string)),
new DataColumn("type_id", typeof(int)),
new DataColumn("Ownername",typeof(string)),
new DataColumn("Ownermob",typeof(string)),
new DataColumn("Room",typeof(string)),
new DataColumn("Build",typeof(string)),
new DataColumn("Road",typeof(string)),
new DataColumn("Area",typeof(string)),
new DataColumn("City",typeof(string)),
new DataColumn("Phone",typeof(string)),
new DataColumn("Mobile",typeof(string)),
new DataColumn("Email",typeof(string)),
new DataColumn("ContactPerson",typeof(string)),
new DataColumn("ContactPersonmob",typeof(string)),
new DataColumn("UserOFC",typeof(bool)),
new DataColumn("UserVAT",typeof(bool)),
new DataColumn("UserINV",typeof(bool)),
new DataColumn("UserNone",typeof(bool)),
new DataColumn("state_id",typeof(int)),
new DataColumn("country_id",typeof(int)),
new DataColumn("Remark",typeof(string)),
new DataColumn("Register_Date",typeof(DateTime)),
new DataColumn("User_id",typeof(string)),
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["CRMConnectionString"].ToString();
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "tbl_Party_master";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Name", dtExcelData.Columns[int.Parse(txt_col.Text.Trim())].ToString());
sqlBulkCopy.ColumnMappings.Add("type_id",dtExcelData.Columns[int.Parse(txt_col1.Text.Trim())].ToString());
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col2.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Ownername",dtExcelData.Columns[int.Parse(txt_col2.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col3.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Ownermob", dtExcelData.Columns[int.Parse(txt_col3.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col4.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Room",dtExcelData.Columns[int.Parse(txt_col4.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col5.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Build", dtExcelData.Columns[int.Parse(txt_col5.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col6.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Road",dtExcelData.Columns[int.Parse(txt_col6.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col7.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Area",dtExcelData.Columns[int.Parse(txt_col7.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col8.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("City",dtExcelData.Columns[int.Parse(txt_col8.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col9.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Phone", dtExcelData.Columns[int.Parse(txt_col9.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col10.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Mobile",dtExcelData.Columns[int.Parse(txt_col10.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col11.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Email",dtExcelData.Columns[int.Parse(txt_col11.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col12.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("ContactPerson",dtExcelData.Columns[int.Parse(txt_col12.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col13.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("ContactPersonmob", dtExcelData.Columns[int.Parse(txt_col13.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col14.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("UserOFC", dtExcelData.Columns[int.Parse(txt_col14.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col15.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("UserVAT",dtExcelData.Columns[int.Parse(txt_col15.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col16.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("UserINV",dtExcelData.Columns[int.Parse(txt_col16.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col17.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("UserNone",dtExcelData.Columns[int.Parse(txt_col17.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col18.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("state_id",dtExcelData.Columns[int.Parse(txt_col18.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col19.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("country_id",dtExcelData.Columns[int.Parse(txt_col19.Text.Trim())].ToString());
}
if(!string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col20.Text.Trim())].ToString()))
{
sqlBulkCopy.ColumnMappings.Add("Remark",dtExcelData.Columns[int.Parse(txt_col20.Text.Trim())].ToString());
}
sqlBulkCopy.ColumnMappings.Add("Register_Date",dtExcelData.Columns[int.Parse(txt_col21.Text.Trim())].ToString());
sqlBulkCopy.ColumnMappings.Add("User_id",dtExcelData.Columns[int.Parse(txt_col22.Text.Trim())].ToString());
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
StringBuilder sb = new StringBuilder();
sb.Append("<script type = 'text/javascript'>");
sb.Append("alert('");
sb.Append(dtExcelData.Rows.Count.ToString());
sb.Append(" Rows(s) are Inserted.');");
sb.Append("</script>");
ClientScript.RegisterStartupScript(this.GetType(), "script", sb.ToString());
}
}
}
ibtnimexls_ModalPopupExtender.Show();
}
}
从列映射的最后一行，我发现错误如下:
Server Error in '/CRM' Application.
Cannot find column 23.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: Cannot find column 23.
Source Error:
Line 3403: }
Line 3404: sqlBulkCopy.ColumnMappings.Add("Register_Date",dtExcelData.Columns[int.Parse(txt_col21.Text.Trim())].ToString());
Line 3405: sqlBulkCopy.ColumnMappings.Add("User_id",dtExcelData.Columns[int.Parse(txt_col22.Text.Trim())].ToString());
Line 3406: con.Open();
Line 3407: sqlBulkCopy.WriteToServer(dtExcelData);
Source File: f:'CRM'Staff'Raise_Ticket.aspx.cs Line: 3405
Stack Trace:
[IndexOutOfRangeException: Cannot find column 23.]
System.Data.DataColumnCollection.get_Item(Int32 index) +95
Staff_Raise_Ticket.lbut_import_Click(Object sender, EventArgs e) in f:'CRM'Staff'Raise_Ticket.aspx.cs:3405
System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +113
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
Column 'Name' does not allow DBNull.Value.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: Column 'Name' does not allow DBNull.Value.
Source Error:
Line 3407: sqlBulkCopy.ColumnMappings.Add("Register_Date", dtExcelData.Columns[int.Parse(txt_col21.Text.Trim())].ToString());
Line 3408: sqlBulkCopy.ColumnMappings.Add("User_id", dtExcelData.Columns[int.Parse(txt_col22.Text.Trim())].ToString());
Line 3409: sqlBulkCopy.WriteToServer(dtExcelData);
Line 3410: con.Close();
Line 3411: }
Source File: f:'CRM'Staff'Raise_Ticket.aspx.cs Line: 3409
Stack Trace:
[InvalidOperationException: Column 'Name' does not allow DBNull.Value.]
System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) +953277
System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() +1393
System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) +762
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) +186
System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) +6
Staff_Raise_Ticket.lbut_import_Click(Object sender, EventArgs e) in f:'CRM'Staff'Raise_Ticket.aspx.cs:3409
System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +113
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1
你可以试试:
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Salary",typeof(decimal)) });
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.tblPersons";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "PersonId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Salary", "Salary");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
也可以点击此链接了解更多信息