如何使用asp.net 4.0和c#将XLS导入数据库
本文关键字：XLS 导入 数据库 何使用 asp net | 更新日期: 2023-09-27 18:06:17
我只是想让xls映射工具与asp.net 4.0与C#。我只是想从Excel sheet1导入到数据库的所有细节。
这里我绑定了一个中继器，这里有文本框，用户可以输入特定的行到表列，并使其做xls mapping。
protected void lbut_import_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
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 conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Foldername + "//" + filename + Extension + ";" +
"Extended Properties=Excel 8.0;";
break;
case ".xlsx": //Excel 07
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + Foldername + "//" + filename + Extension + ";" +
"Extended Properties=Excel 8.0;";
break;
}
OleDbConnection excelConnection = new OleDbConnection(conStr);
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["CRMConnectionString"].ToString());
//Give your Destination table name
using (DataClassesDataContext db = new DataClassesDataContext())
{
sqlBulk.DestinationTableName = "tbl_Party_master";
sqlBulk.ColumnMappings.Add("Name",dReader[int.Parse(txt_col.Text.Trim())].ToString());
sqlBulk.ColumnMappings.Add("type_id", dReader[int.Parse(txt_col1.Text.Trim())].ToString());
if (!string.IsNullOrEmpty(txt_col2.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Ownername", dReader[int.Parse(txt_col2.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col3.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Ownermob", dReader[int.Parse(txt_col3.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col4.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Room", dReader[int.Parse(txt_col4.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col5.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Road", dReader[int.Parse(txt_col5.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col6.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Area", dReader[int.Parse(txt_col6.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col7.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("City", dReader[int.Parse(txt_col7.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col8.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Mobile", dReader[int.Parse(txt_col8.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col9.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Email", dReader[int.Parse(txt_col9.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col10.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("ContactPerson", dReader[int.Parse(txt_col10.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col11.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("ContactPersonmob", dReader[int.Parse(txt_col11.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col12.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("UserOFC", dReader[int.Parse(txt_col12.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col13.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("UserVAT", dReader[int.Parse(txt_col13.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col14.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("UserINV", dReader[int.Parse(txt_col14.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col15.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("UserNone", dReader[int.Parse(txt_col15.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col16.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("state_id", dReader[int.Parse(txt_col16.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col17.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("country_id", dReader[int.Parse(txt_col17.Text.Trim())].ToString());
}
if (!string.IsNullOrEmpty(txt_col18.Text.Trim()))
{
sqlBulk.ColumnMappings.Add("Remark",dReader[int.Parse(txt_col18.Text.Trim())].ToString());
}
sqlBulk.ColumnMappings.Add("Register_Date", System.DateTime.Now.ToString());
sqlBulk.ColumnMappings.Add("User_id", db.Users.Where(u => u.Username.Equals((String)Session["Username"])).Select(u => u.Ref_no).SingleOrDefault());
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
}
}
ibtnimexls_ModalPopupExtender.Show();
}
}
}
但是这段代码给了我一个错误:there is no specific row/column at drreader[] object.
这里我放了来自iis的完整错误信息:
Server Error in '/CRM' Application.
No data exists for the row/column.
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: No data exists for the row/column.
Source Error:
Line 3121: {
Line 3122: sqlBulk.DestinationTableName = "tbl_Party_master";
Line 3123: sqlBulk.ColumnMappings.Add("Name", dReader[int.Parse(((TextBox)Repeater_Column_List.Items[0].FindControl("txt_col")).Text.Trim())].ToString());
Line 3124: sqlBulk.ColumnMappings.Add("type_id", dReader[int.Parse(((TextBox)Repeater_Column_List.Items[1].FindControl("txt_col")).Text.Trim())].ToString());
Line 3125: if (!string.IsNullOrEmpty(((TextBox)Repeater_Column_List.Items[2].FindControl("txt_col")).Text.Trim()))
Source File: f:'CRM'Staff'Raise_Ticket.aspx.cs Line: 3123
Stack Trace:
[InvalidOperationException: No data exists for the row/column.]
System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal) +1106519
System.Data.OleDb.OleDbDataReader.GetValue(Int32 ordinal) +12
System.Data.OleDb.OleDbDataReader.get_Item(Int32 index) +10
Staff_Raise_Ticket.lbut_import_Click(Object sender, EventArgs e) in f:'CRM'Staff'Raise_Ticket.aspx.cs:3123
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
Please help me!
这是我的重复器HTML标记:
<table>
<thead>
<tr>
<td><strong>Table Data Column</strong></td>
<td> </td>
<td><strong>Excel Column Number</strong></td>
</tr>
</thead>
<tr>
<td><asp:CheckBox ID="CheckBox1" runat="server" Text="Name" Enabled="false" Checked="true"/></td>
<td> </td>
<td>
<ajaxToolkit:TextBoxWatermarkExtender ID="TextBoxWatermarkExtender16" runat="server" TargetControlID="txt_col" WatermarkText="Enter Row number">
</ajaxToolkit:TextBoxWatermarkExtender>
<ajaxToolkit:FilteredTextBoxExtender ID="FilteredTextBoxExtender7" runat="server" TargetControlID="txt_col" FilterType="Numbers"></ajaxToolkit:FilteredTextBoxExtender>
<asp:TextBox ID="txt_col" runat="server" CssClass="input" MaxLength="4"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ErrorMessage="Required" ControlToValidate="txt_col" ValidationGroup="ImportXLS" Enabled="true" Display="None" SetFocusOnError="True"></asp:RequiredFieldValidator>
<ajaxToolkit:ValidatorCalloutExtender ID="ValidatorCalloutExtender7" runat="server" TargetControlID="RequiredFieldValidator6" WarningIconImageUrl="~/images/Warning.gif" CloseImageUrl="~/images/close.gif" >
</ajaxToolkit:ValidatorCalloutExtender>
</td>
</tr>
..............
</table>
哇!!我有点语无伦次了。为什么不把每个文本框都写在html上，而不是在一个重复器中动态地创建一切呢?这就是你的问题所在。当你发回中继器失去它的数据绑定，然后当你尝试和引用行在中继器他们都是空的，因为你没有数据绑定到它后发回。
这是一种猜测，但一个消息灵通的猜测。除非你有一些疯狂的商业案例，否则我建议你不要继续使用重复器，手写每个文本框。
现在我必须去找一个垃圾桶生病!: -)