如何解决不匹配的错误列映射 [英] How Do I Solve The Error Column Mapping Not Matched
本文介绍了如何解决不匹配的错误列映射的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我收到的错误称为给定的ColumnMapping与源或目标中的任何列都不匹配。我尝试将excel数据复制到sql server2008 db表中，名为excel。
我的源代码如下。
------------- -------------------------------------------------- ------
I am getting error called "The given ColumnMapping does not match up with any column in the source or destination." I am tryng to copy excel data into sql server2008 db table called excel.
My source code is below.
---------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//Get path from web.config file to upload
//string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
string filename = string.Empty;
//To check whether file is selected or not to upload
if (FileUploadToServer.HasFile)
{
try
{
string[] allowdFile = { ".xls", ".xlsx" };
//Here we are allowing only excel file so verifying selected file pdf or not
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
//Check whether selected file is valid extension or not
bool isValidFile = allowdFile.Contains(FileExt);
if (!isValidFile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please upload only Excel";
}
else
{
System.Threading.Thread.Sleep(8000);
//Get file path
string filePath = Server.MapPath("Files/" + FileUploadToServer.FileName);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
//Get file name of selected file
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
//Save selected file into server location
FileUploadToServer.SaveAs(Server.MapPath("Files/") + filename);
//Open the connection with excel file based on excel version
OleDbConnection con = new OleDbConnection();
if (FileExt == ".xls")
{
HiddenField1.Value = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
con.ConnectionString = HiddenField1.Value;
}
else if (FileExt == ".xlsx")
{
HiddenField1.Value = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
con.ConnectionString = HiddenField1.Value;
}
con.Open();
//Get the list of sheet available in excel sheet
GetExcelSheets(filePath, FileExt);
//Get first sheet name
//string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
//Select rows from first sheet in excel sheet and fill into dataset
lblMsg.Text = "uploaded successfully";
Panel1.Enabled = false;
Panel2.Visible = true;
}
}
catch (Exception ex)
{
lblMsg.Text = "Error occurred while uploading a file: " + ex.Message;
}
}
else
{
lblMsg.Text = "Please select a file to upload.";
}
}
/*if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
//string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
//string FilePath = Server.MapPath(FolderPath + FileName);
string FilePath = Server.MapPath("Files/") + FileUpload1.FileName;
FileUpload1.SaveAs(FilePath);
}*/
private void GetExcelSheets(string FilePath, string Extension)
{
string filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
HiddenField1.Value = connection(Server.MapPath("Files/" + FileUploadToServer.FileName),Path.GetExtension(FileUploadToServer.FileName));
OleDbConnection conStr = new OleDbConnection(HiddenField1.Value);
//Get the Sheets in Excel WorkBook
//conStr = String.Format(conStr, FilePath);
//OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
cmdExcel.Connection = conStr;
conStr.Open();
//Bind the Sheets to DropDownList
ddlCategories.Items.Clear();
ddlCategories.Items.Add(new ListItem("--Select Sheet--", ""));
//ddlCategories.DataSource = conStr.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dtsheet = conStr.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
foreach (DataRow dr in dtsheet.Rows)
{
ddlCategories.Items.Add(new ListItem((Convert.ToString(dr["TABLE_NAME"]).Replace("$", "")), Convert.ToString(dr["TABLE_NAME"])));
}
//ddlCategories.DataTextField = "TABLE_NAME";
//ddlCategories.DataValueField = "TABLE_NAME";
ddlCategories.DataBind();
conStr.Close();
}
public string connection(string path,string extension)
{
string connstr = "";
if (extension == ".xls")
{
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
}
else if (extension == ".xlsx")
{
connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
}
return connstr;
}
protected string getExcelSheet()
{
string sheetname = null;
if (String.IsNullOrEmpty(ddlCategories.SelectedItem.Value))
{
Response.Write("<script>alert('Please select the Sheet to upload the Batch')</script>");
}
else
{
hdn_choosedsheetname.Value = ddlCategories.SelectedItem.Value;
//sheetname = ddlCategories.SelectedItem.Value;
}
return hdn_choosedsheetname.Value;
}
/*protected string getExcelSheet()
{
string sheetname = ddlCategories.SelectedItem.Value;
return sheetname;
}*/
protected void Button1_Click(object sender, EventArgs e)
{
// Create sql connection string
SqlConnection sqlCon = new SqlConnection (ConfigurationManager.ConnectionStrings["conString"].ConnectionString );
sqlCon.Open ();
SqlDataAdapter da = new SqlDataAdapter("select * from excel where errormessage IS NOT NULL", sqlCon);
System.Data. DataTable dtMainSQLData = new System.Data. DataTable ();
da.Fill ( dtMainSQLData );
DataColumnCollection dcCollection = dtMainSQLData.Columns ;
// Export Data into Excel Sheet
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass ();
ExcelApp.Application.Workbooks.Add (Type.Missing);
//ExcelApp.Cells.CopyFromRecordset (objRS);
for ( int i = 1 ; i < dtMainSQLData.Rows.Count + 1;i++)
{
for ( int j = 1 ; j < dtMainSQLData.Columns.Count + 1;j++)
{
if ( i == 1 )
ExcelApp.Cells [i,j] = dcCollection[j - 1].ToString();
else
ExcelApp.Cells [ i , j ] = dtMainSQLData.Rows [ i - 1 ] [ j - 1 ].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs ( "C:\test.xls" );
ExcelApp.ActiveWorkbook.Saved = true ;
ExcelApp.Quit ();
Response.Write( "Successfully Exported Data into Excel File" );
}
protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(HiddenField1.Value);
//con.ConnectionString = HiddenField1.Value;
string getExcelSheetName = getExcelSheet();
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
//ddlCategories.SelectedItem.Value
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
DataTable csvData = ExcelDataSet.Tables[0];
if (csvData.Columns.Count > 1)
{
String Createtablequery = "CREATE TABLE excel(RowId int Identity(1,1) not null,CName nvarchar(60) null,firstname nvarchar(30) null,lastname nvarchar(40) null,invadd nchar(10) null, Address1 nvarchar(40) null,Address2 nvarchar(40) null, Address3 nvarchar(40) null,Address4 nvarchar(40) null,isprimaryadd nchar(10) null,IsDeleted nchar(10) null,email nvarchar(255) null,DirectoryName nvarchar(40) null";
int j = 1;
for ( int i = 11; i < csvData.Columns.Count; i++)
{
Createtablequery += ",level" + j + " nvarchar(255) null";
j++;
}
Createtablequery += ",errormessage nvarchar(max) null,rowinserted int null)";
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);
SqlCommand cmd = new SqlCommand("IF EXISTS(SELECT * FROM sys.tables WHERE name = N'excel' AND type = 'U') BEGIN DROP TABLE excel END", connection);
SqlCommand cmd1 = new SqlCommand(Createtablequery, connection);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
SqlBulkCopy sbpy = new SqlBulkCopy(connection);
sbpy.DestinationTableName = "excel";
foreach (DataColumn dc in csvData.Columns)
{
if (dc.Ordinal < 12)
{
switch (dc.Ordinal)
{
case 0:
SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName");
sbpy.ColumnMappings.Add(map);
break;
case 1:
SqlBulkCopyColumnMapping map1 = new SqlBulkCopyColumnMapping(dc.Ordinal, "firstname");
sbpy.ColumnMappings.Add(map1);
break;
case 2:
SqlBulkCopyColumnMapping map2 = new SqlBulkCopyColumnMapping(dc.Ordinal, "lastname");
sbpy.ColumnMappings.Add(map2);
break;
case 3:
SqlBulkCopyColumnMapping map3 = new SqlBulkCopyColumnMapping(dc.Ordinal, "invadd");
sbpy.ColumnMappings.Add(map3);
break;
case 4:
SqlBulkCopyColumnMapping map4 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address1");
sbpy.ColumnMappings.Add(map4);
break;
case 5:
SqlBulkCopyColumnMapping map5 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address2");
sbpy.ColumnMappings.Add(map5);
break;
case 6:
SqlBulkCopyColumnMapping map6 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address3");
sbpy.ColumnMappings.Add(map6);
break;
case 7:
SqlBulkCopyColumnMapping map7 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address4");
sbpy.ColumnMappings.Add(map7);
break;
case 8:
SqlBulkCopyColumnMapping map8 = new SqlBulkCopyColumnMapping(dc.Ordinal, "isprimaryadd");
sbpy.ColumnMappings.Add(map8);
break;
case 9:
SqlBulkCopyColumnMapping map9 = new SqlBulkCopyColumnMapping(dc.Ordinal, "IsDeleted");
sbpy.ColumnMappings.Add(map9);
break;
case 10:
SqlBulkCopyColumnMapping map10 = new SqlBulkCopyColumnMapping(dc.Ordinal, "email");
sbpy.ColumnMappings.Add(map10);
break;
case 11:
SqlBulkCopyColumnMapping map11 = new SqlBulkCopyColumnMapping(dc.Ordinal, "DirectoryName");
sbpy.ColumnMappings.Add(map11);
break;
default:
string destcolumnname = "level" + dc.Ordinal;
SqlBulkCopyColumnMapping map12 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
sbpy.ColumnMappings.Add(map12);
break;
}
}
else
{
string destcolumnname = "level" + dc.Ordinal;
SqlBulkCopyColumnMapping map13 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
sbpy.ColumnMappings.Add(map13);
}
/*if (dc.Ordinal == 0)
{
SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName");
sbpy.ColumnMappings.Add(map);
}
else
{
}*/
}
try
{
sbpy.WriteToServer(csvData);
}
catch (Exception es)
{
Response.Write(es.Message);
//WriteTextLogFilestatic(es.Message, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
connection.Close();
}
//Bind the dataset into gridview to display excel contents
//GridView1.DataSource = ExcelDataSet;
//GridView1.DataBind();
Panel2.Enabled = false;
ddlCategories.Enabled = false;
System.Threading.Thread.Sleep(3000);
lblText.Text = "Data inserted sucessfully";
}
}
}
-------------------- -------------------------------------------------- -------
aspx的源代码是：
-----------------------------------------------------------------------------
source code for aspx is:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Import Excel Data into Database</title>
<script language="javascript" type="text/javascript">
var size = 2;
var id= 0;
function ProgressBar() {
if (document.getElementById('<%=FileUploadToServer.ClientID %>').value != "") {
document.getElementById("divProgress").style.display = "block";
document.getElementById("divUpload").style.display = "block";
id = setInterval("progress()", 20);
return true;
}
else {
alert("Select a file to upload");
return false;
}
}
function progress()
{
size = size + 1;
if(size > 299)
{
clearTimeout(id);
}
document.getElementById("divProgress").style.width = size + "pt";
document.getElementById("<%=lblPercentage.ClientID %>").firstChild.data = parseInt(size / 3) + "%";
}
</script>
</head>
<body>
<form id="form1" runat="server">
<%-- ajax progress bar --%>
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<script type="text/javascript">
// Get the instance of PageRequestManager.
var prm = Sys.WebForms.PageRequestManager.getInstance();
// Add initializeRequest and endRequest
prm.add_initializeRequest(prm_InitializeRequest);
prm.add_endRequest(prm_EndRequest);
// Called when async postback begins
function prm_InitializeRequest(sender, args) {
// get the divImage and set it to visible
var panelProg = $get('divImage');
panelProg.style.display = '';
// reset label text
var lbl = $get('<%= this.lblText.ClientID %>');
lbl.innerHTML = '';
// Disable button that caused a postback
$get(args._postBackElement.id).disabled = true;
}
// Called when async postback ends
function prm_EndRequest(sender, args) {
// get the divImage and hide it again
var panelProg = $get('divImage');
panelProg.style.display = 'none';
// Enable button that caused a postback
$get(sender._postBackSettings.sourceElement.id).disabled = false;
}
</script>
<div id="div1" runat="server">
<asp:Panel ID="Panel1" runat="server">
<asp:FileUpload ID="FileUploadToServer" Width="300px" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload File" OnClientClick="return ProgressBar()" OnClick="btnUpload_Click" style="width: 99px" />
<div id="divUpload" style="display:none">
<div style="width:300pt; text-align:center;">Uploading...</div>
<div style="width:300pt; height:20px; border:solid 1pt gray">
<div id="divProgress" runat="server" style="width: 1pt; height: 20px; background-color:Gray;display:none">
</div></div>
<div style="width:300pt; text-align:center;">
<asp:Label ID="lblPercentage" runat="server" Text="Label"></asp:Label></div>
</div>
<br />
<asp:Label ID="lblMsg" runat="server" ForeColor="Green" Text=""></asp:Label> </asp:Panel></div>
<br />
<asp:Panel ID="Panel2" runat="server" Visible = "false">
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<br />
<div id="dropdown" runat="server">
<asp:DropDownList ID="ddlCategories" runat="server" AutoPostBack="true" Height="24px" Width="162px" OnSelectedIndexChanged="ddlCategories_SelectedIndexChanged">
</asp:DropDownList></div>
<div id="divImage" style="display: none">
<asp:Image ID="img1" runat="server" ImageUrl="~/images/progress.gif" />
Processing...
</div>
<br />
<asp:Label ID="lblText" runat="server" Text="" ForeColor="Green"></asp:Label>
</ContentTemplate>
</asp:UpdatePanel>
</asp:Panel></div><br /><br />
<asp:Panel ID="Panel3" runat="server">
<div runat="server" id="divbtn">
<asp:Button ID="Button1" runat="server" Text="Download" Width="92px" />
</div>
</asp:Panel>
<br />
<asp:Panel ID="Panel4" runat="server" Visible="false">
<asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found!" Height="25px">
<RowStyle Width="175px" />
<EmptyDataRowStyle BackColor="Silver" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" ForeColor="#003300" />
<HeaderStyle BackColor="#6699FF" BorderColor="#333333" BorderStyle="Solid" BorderWidth="1px" VerticalAlign="Top" Width="200px" Wrap="True" /></asp:GridView>
</asp:Panel>
<asp:HiddenField ID="HiddenField1" runat="server" />
<asp:HiddenField ID="hdn_choosedsheetname" runat="server" />
</form>
</body>
</html>推荐答案
\", \"\")), Convert.ToString(dr[\"TABLE_NAME\"])));
}
//ddlCategories.DataTextField = \"TABLE_NAME\";
//ddlCategories.DataValueField = \"TABLE_NAME\";
ddlCategories.DataBind();
conStr.Close();
}
public string connection(string path,string extension)
{
string connstr = \"\";
if (extension == \".xls\")
{
connstr = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" + path + \";Extended Properties=Excel 8.0;\";
}
else if (extension == \".xlsx\")
{
connstr = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\" + path + \";Extended Properties=Excel 12.0;\";
}
return connstr;
}
protected string getExcelSheet()
{
string sheetname = null;
if (String.IsNullOrEmpty(ddlCategories.SelectedItem.Value))
{
Response.Write(\"<script>alert('Please select the Sheet to upload the Batch')</script>\");
}
else
{
hdn_choosedsheetname.Value = ddlCategories.SelectedItem.Value;
//sheetname = ddlCategories.SelectedItem.Value;
}
return hdn_choosedsheetname.Value;
}
/*protected string getExcelSheet()
{
string sheetname = ddlCategories.SelectedItem.Value;
return sheetname;
}*/
protected void Button1_Click(object sender, EventArgs e)
{
// Create sql connection string
SqlConnection sqlCon = new SqlConnection (ConfigurationManager.ConnectionStrings[\"conString\"].ConnectionString );
sqlCon.Open ();
SqlDataAdapter da = new SqlDataAdapter(\"select * from excel where errormessage IS NOT NULL\", sqlCon);
System.Data. DataTable dtMainSQLData = new System.Data. DataTable ();
da.Fill ( dtMainSQLData );
DataColumnCollection dcCollection = dtMainSQLData.Columns ;
// Export Data into Excel Sheet
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass ();
ExcelApp.Application.Workbooks.Add (Type.Missing);
//ExcelApp.Cells.CopyFromRecordset (objRS);
for ( int i = 1 ; i < dtMainSQLData.Rows.Count + 1;i++)
{
for ( int j = 1 ; j < dtMainSQLData.Columns.Count + 1;j++)
{
if ( i == 1 )
ExcelApp.Cells [i,j] = dcCollection[j - 1].ToString();
else
ExcelApp.Cells [ i , j ] = dtMainSQLData.Rows [ i - 1 ] [ j - 1 ].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs ( \"C:\test.xls\" );
ExcelApp.ActiveWorkbook.Saved = true ;
ExcelApp.Quit ();
Response.Write( \"Successfully Exported Data into Excel File\" );
}
protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(HiddenField1.Value);
//con.ConnectionString = HiddenField1.Value;
string getExcelSheetName = getExcelSheet();
OleDbCommand ExcelCommand = new OleDbCommand(@\"SELECT * FROM [\" + getExcelSheetName + @\"]\", con);
//ddlCategories.SelectedItem.Value
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
DataTable csvData = ExcelDataSet.Tables[0];
if (csvData.Columns.Count > 1)
{
String Createtablequery = \"CREATE TABLE excel(RowId int Identity(1,1) not null,CName nvarchar(60) null,firstname nvarchar(30) null,lastname nvarchar(40) null,invadd nchar(10) null, Address1 nvarchar(40) null,Address2 nvarchar(40) null, Address3 nvarchar(40) null,Address4 nvarchar(40) null,isprimaryadd nchar(10) null,IsDeleted nchar(10) null,email nvarchar(255) null,DirectoryName nvarchar(40) null\";
int j = 1;
for ( int i = 11; i < csvData.Columns.Count; i++)
{
Createtablequery += \",level\" + j + \" nvarchar(255) null\";
j++;
}
Createtablequery += \",errormessage nvarchar(max) null,rowinserted int null)\";
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[\"conString\"].ConnectionString);
SqlCommand cmd = new SqlCommand(\"IF EXISTS(SELECT * FROM sys.tables WHERE name = N'excel' AND type = 'U') BEGIN DROP TABLE excel END\", connection);
SqlCommand cmd1 = new SqlCommand(Createtablequery, connection);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
cmd.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
SqlBulkCopy sbpy = new SqlBulkCopy(connection);
sbpy.DestinationTableName = \"excel\";
foreach (DataColumn dc in csvData.Columns)
{
if (dc.Ordinal < 12)
{
switch (dc.Ordinal)
{
case 0:
SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, \"CName\");
sbpy.ColumnMappings.Add(map);
break;
case 1:
SqlBulkCopyColumnMapping map1 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"firstname\");
sbpy.ColumnMappings.Add(map1);
break;
case 2:
SqlBulkCopyColumnMapping map2 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"lastname\");
sbpy.ColumnMappings.Add(map2);
break;
case 3:
SqlBulkCopyColumnMapping map3 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"invadd\");
sbpy.ColumnMappings.Add(map3);
break;
case 4:
SqlBulkCopyColumnMapping map4 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"Address1\");
sbpy.ColumnMappings.Add(map4);
break;
case 5:
SqlBulkCopyColumnMapping map5 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"Address2\");
sbpy.ColumnMappings.Add(map5);
break;
case 6:
SqlBulkCopyColumnMapping map6 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"Address3\");
sbpy.ColumnMappings.Add(map6);
break;
case 7:
SqlBulkCopyColumnMapping map7 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"Address4\");
sbpy.ColumnMappings.Add(map7);
break;
case 8:
SqlBulkCopyColumnMapping map8 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"isprimaryadd\");
sbpy.ColumnMappings.Add(map8);
break;
case 9:
SqlBulkCopyColumnMapping map9 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"IsDeleted\");
sbpy.ColumnMappings.Add(map9);
break;
case 10:
SqlBulkCopyColumnMapping map10 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"email\");
sbpy.ColumnMappings.Add(map10);
break;
case 11:
SqlBulkCopyColumnMapping map11 = new SqlBulkCopyColumnMapping(dc.Ordinal, \"DirectoryName\");
sbpy.ColumnMappings.Add(map11);
break;
default:
string destcolumnname = \"level\" + dc.Ordinal;
SqlBulkCopyColumnMapping map12 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
sbpy.ColumnMappings.Add(map12);
break;
}
}
else
{
string destcolumnname = \"level\" + dc.Ordinal;
SqlBulkCopyColumnMapping map13 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname);
sbpy.ColumnMappings.Add(map13);
}
/*if (dc.Ordinal == 0)
{
SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, \"CName\");
sbpy.ColumnMappings.Add(map);
}
else
{
}*/
}
try
{
sbpy.WriteToServer(csvData);
}
catch (Exception es)
{
Response.Write(es.Message);
//WriteTextLogFilestatic(es.Message, System.Reflection.MethodBase.GetCurrentMethod().Name);
}
finally
{
connection.Close();
}
//Bind the dataset into gridview to display excel contents
//GridView1.DataSource = ExcelDataSet;
//GridView1.DataBind();
Panel2.Enabled = false;
ddlCategories.Enabled = false;
System.Threading.Thread.Sleep(3000);
lblText.Text = \"Data inserted sucessfully\";
}
}
}
", "")), Convert.ToString(dr["TABLE_NAME"]))); } //ddlCategories.DataTextField = "TABLE_NAME"; //ddlCategories.DataValueField = "TABLE_NAME"; ddlCategories.DataBind(); conStr.Close(); } public string connection(string path,string extension) { string connstr = ""; if (extension == ".xls") { connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;"; } else if (extension == ".xlsx") { connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; } return connstr; } protected string getExcelSheet() { string sheetname = null; if (String.IsNullOrEmpty(ddlCategories.SelectedItem.Value)) { Response.Write("<script>alert('Please select the Sheet to upload the Batch')</script>"); } else { hdn_choosedsheetname.Value = ddlCategories.SelectedItem.Value; //sheetname = ddlCategories.SelectedItem.Value; } return hdn_choosedsheetname.Value; } /*protected string getExcelSheet() { string sheetname = ddlCategories.SelectedItem.Value; return sheetname; }*/ protected void Button1_Click(object sender, EventArgs e) { // Create sql connection string SqlConnection sqlCon = new SqlConnection (ConfigurationManager.ConnectionStrings["conString"].ConnectionString ); sqlCon.Open (); SqlDataAdapter da = new SqlDataAdapter("select * from excel where errormessage IS NOT NULL", sqlCon); System.Data. DataTable dtMainSQLData = new System.Data. DataTable (); da.Fill ( dtMainSQLData ); DataColumnCollection dcCollection = dtMainSQLData.Columns ; // Export Data into Excel Sheet Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass (); ExcelApp.Application.Workbooks.Add (Type.Missing); //ExcelApp.Cells.CopyFromRecordset (objRS); for ( int i = 1 ; i < dtMainSQLData.Rows.Count + 1;i++) { for ( int j = 1 ; j < dtMainSQLData.Columns.Count + 1;j++) { if ( i == 1 ) ExcelApp.Cells [i,j] = dcCollection[j - 1].ToString(); else ExcelApp.Cells [ i , j ] = dtMainSQLData.Rows [ i - 1 ] [ j - 1 ].ToString(); } } ExcelApp.ActiveWorkbook.SaveCopyAs ( "C:\test.xls" ); ExcelApp.ActiveWorkbook.Saved = true ; ExcelApp.Quit (); Response.Write( "Successfully Exported Data into Excel File" ); } protected void ddlCategories_SelectedIndexChanged(object sender, EventArgs e) { OleDbConnection con = new OleDbConnection(HiddenField1.Value); //con.ConnectionString = HiddenField1.Value; string getExcelSheetName = getExcelSheet(); OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); //ddlCategories.SelectedItem.Value OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); DataSet ExcelDataSet = new DataSet(); ExcelAdapter.Fill(ExcelDataSet); DataTable csvData = ExcelDataSet.Tables[0]; if (csvData.Columns.Count > 1) { String Createtablequery = "CREATE TABLE excel(RowId int Identity(1,1) not null,CName nvarchar(60) null,firstname nvarchar(30) null,lastname nvarchar(40) null,invadd nchar(10) null, Address1 nvarchar(40) null,Address2 nvarchar(40) null, Address3 nvarchar(40) null,Address4 nvarchar(40) null,isprimaryadd nchar(10) null,IsDeleted nchar(10) null,email nvarchar(255) null,DirectoryName nvarchar(40) null"; int j = 1; for ( int i = 11; i < csvData.Columns.Count; i++) { Createtablequery += ",level" + j + " nvarchar(255) null"; j++; } Createtablequery += ",errormessage nvarchar(max) null,rowinserted int null)"; SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString); SqlCommand cmd = new SqlCommand("IF EXISTS(SELECT * FROM sys.tables WHERE name = N'excel' AND type = 'U') BEGIN DROP TABLE excel END", connection); SqlCommand cmd1 = new SqlCommand(Createtablequery, connection); if (connection.State == ConnectionState.Closed) { connection.Open(); } cmd.ExecuteNonQuery(); cmd1.ExecuteNonQuery(); SqlBulkCopy sbpy = new SqlBulkCopy(connection); sbpy.DestinationTableName = "excel"; foreach (DataColumn dc in csvData.Columns) { if (dc.Ordinal < 12) { switch (dc.Ordinal) { case 0: SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName"); sbpy.ColumnMappings.Add(map); break; case 1: SqlBulkCopyColumnMapping map1 = new SqlBulkCopyColumnMapping(dc.Ordinal, "firstname"); sbpy.ColumnMappings.Add(map1); break; case 2: SqlBulkCopyColumnMapping map2 = new SqlBulkCopyColumnMapping(dc.Ordinal, "lastname"); sbpy.ColumnMappings.Add(map2); break; case 3: SqlBulkCopyColumnMapping map3 = new SqlBulkCopyColumnMapping(dc.Ordinal, "invadd"); sbpy.ColumnMappings.Add(map3); break; case 4: SqlBulkCopyColumnMapping map4 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address1"); sbpy.ColumnMappings.Add(map4); break; case 5: SqlBulkCopyColumnMapping map5 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address2"); sbpy.ColumnMappings.Add(map5); break; case 6: SqlBulkCopyColumnMapping map6 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address3"); sbpy.ColumnMappings.Add(map6); break; case 7: SqlBulkCopyColumnMapping map7 = new SqlBulkCopyColumnMapping(dc.Ordinal, "Address4"); sbpy.ColumnMappings.Add(map7); break; case 8: SqlBulkCopyColumnMapping map8 = new SqlBulkCopyColumnMapping(dc.Ordinal, "isprimaryadd"); sbpy.ColumnMappings.Add(map8); break; case 9: SqlBulkCopyColumnMapping map9 = new SqlBulkCopyColumnMapping(dc.Ordinal, "IsDeleted"); sbpy.ColumnMappings.Add(map9); break; case 10: SqlBulkCopyColumnMapping map10 = new SqlBulkCopyColumnMapping(dc.Ordinal, "email"); sbpy.ColumnMappings.Add(map10); break; case 11: SqlBulkCopyColumnMapping map11 = new SqlBulkCopyColumnMapping(dc.Ordinal, "DirectoryName"); sbpy.ColumnMappings.Add(map11); break; default: string destcolumnname = "level" + dc.Ordinal; SqlBulkCopyColumnMapping map12 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname); sbpy.ColumnMappings.Add(map12); break; } } else { string destcolumnname = "level" + dc.Ordinal; SqlBulkCopyColumnMapping map13 = new SqlBulkCopyColumnMapping(dc.Ordinal, destcolumnname); sbpy.ColumnMappings.Add(map13); } /*if (dc.Ordinal == 0) { SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(dc.Ordinal, "CName"); sbpy.ColumnMappings.Add(map); } else { }*/ } try { sbpy.WriteToServer(csvData); } catch (Exception es) { Response.Write(es.Message); //WriteTextLogFilestatic(es.Message, System.Reflection.MethodBase.GetCurrentMethod().Name); } finally { connection.Close(); } //Bind the dataset into gridview to display excel contents //GridView1.DataSource = ExcelDataSet; //GridView1.DataBind(); Panel2.Enabled = false; ddlCategories.Enabled = false; System.Threading.Thread.Sleep(3000); lblText.Text = "Data inserted sucessfully"; } } }
-----------------------------------------------------------------------------
source code for aspx is:
-----------------------------------------------------------------------------
source code for aspx is:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Import Excel Data into Database</title>
<script language="javascript" type="text/javascript">
var size = 2;
var id= 0;
function ProgressBar() {
if (document.getElementById('<%=FileUploadToServer.ClientID %>').value != "") {
document.getElementById("divProgress").style.display = "block";
document.getElementById("divUpload").style.display = "block";
id = setInterval("progress()", 20);
return true;
}
else {
alert("Select a file to upload");
return false;
}
}
function progress()
{
size = size + 1;
if(size > 299)
{
clearTimeout(id);
}
document.getElementById("divProgress").style.width = size + "pt";
document.getElementById("<%=lblPercentage.ClientID %>").firstChild.data = parseInt(size / 3) + "%";
}
</script>
</head>
<body>
<form id="form1" runat="server">
<%-- ajax progress bar --%>
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<script type="text/javascript">
// Get the instance of PageRequestManager.
var prm = Sys.WebForms.PageRequestManager.getInstance();
// Add initializeRequest and endRequest
prm.add_initializeRequest(prm_InitializeRequest);
prm.add_endRequest(prm_EndRequest);
// Called when async postback begins
function prm_InitializeRequest(sender, args) {
// get the divImage and set it to visible
var panelProg =get('divImage');
panelProg.style.display = '';
// reset label text
var lbl =
get('divImage'); panelProg.style.display = ''; // reset label text var lbl =
get('<%= this.lblText.ClientID %>');
lbl.innerHTML = '';
// Disable button that caused a postback
这篇关于如何解决不匹配的错误列映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
