如何在ASP.NET中将数据从EXCEL导入SQL数据库 [英] How to import data from EXCEL to SQL database in ASP.NET
问题描述
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
我尝试了什么：
protected void Button1_Click （对象发送者，EventArgs e）
{
String strConnection = @数据源= PARAMERP4-PC\SPEEDPEQ;初始目录= XYZ;集成安全性=真;
//文件上传路径
string path = FileUpload12.PostedFile.FileName;
//创建Excel工作簿的连接字符串
string excelConnectionString = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +; Extended Properties = Excel 12.0; Persist Security Info = False;
//创建与Excel工作簿的连接
OleDbConnection excelConnection = new OleDbConnection（excelConnectionString）;
//创建OleDbCommand到fe来自Excel的数据
OleDbCommand cmd = new OleDbCommand（选择[客户]，[任务]，[描述]，[日期]，[采取时间]，[参加电话会议]，[问题已解决] BY]，[帮助]来自[Sheet1 $]，excelConnection）;
excelConnection.Open（）;
OleDbDataReader dReader;
dReader = cmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnection）;
//给你的目的地表名
sqlBulk。 DestinationTableName =Export_Excell_Sheet;
sqlBulk.WriteToServer（dReader）;
excelConnection.Close（）;
}
Excell柱子名称：----客户任务，描述，日期，时间，致电，出席，决议，决定，帮助由
数据库列名： - tbale name -Export_ Excell_Sheet
[Export_Excell_Sheet]
（
[客户端] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[TASK] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[DESCRIPTION] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[DATE] [datetime] NULL，
[TIME_TAKEN] [datetime] NULL，
[CALL_ATTENDED_BY] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[ISSUE_RESOLVED_BY] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[Help_By] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL
）
What I have tried:
protected void Button1_Click(object sender, EventArgs e)
{
String strConnection = @"Data Source=PARAMERP4-PC\SPEEDPEQ;Initial Catalog=XYZ;Integrated Security=True";
//file upload path
string path = FileUpload12.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [CLIENT],[TASK],[DESCRIPTION],[DATE],[TIME TAKEN],[CALL ATTENDED BY],[ISSUE RESOLVED BY],[Help By] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Export_Excell_Sheet";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
Excell collumn name :----CLIENT TASK, DESCRIPTION, DATE, TIME TAKEN, CALL, ATTENDED BY, ISSUE RESOLVED BY, Help By
Data Base Column name :--tbale name -Export_Excell_Sheet
[Export_Excell_Sheet]
(
[CLIENT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TASK] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE] [datetime] NULL,
[TIME_TAKEN] [datetime] NULL,
[CALL_ATTENDED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISSUE_RESOLVED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Help_By] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
推荐答案
'。确保对象存在，并且您正确拼写其名称和路径名。
'. Make sure the object exists and that you spell its name and the path name correctly.
我尝试了什么：
protected void Button1_Click （对象发送者，EventArgs e）
{
String strConnection = @数据源= PARAMERP4-PC\SPEEDPEQ;初始目录= XYZ;集成安全性=真;
//文件上传路径
string path = FileUpload12.PostedFile.FileName;
//创建Excel工作簿的连接字符串
string excelConnectionString = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +; Extended Properties = Excel 12.0; Persist Security Info = False;
//创建与Excel工作簿的连接
OleDbConnection excelConnection = new OleDbConnection（excelConnectionString）;
//创建OleDbCommand到fe来自Excel的数据
OleDbCommand cmd = new OleDbCommand（选择[客户]，[任务]，[描述]，[日期]，[采取时间]，[参加电话会议]，[问题已解决] BY]，[帮助]来自[Sheet1
What I have tried:
protected void Button1_Click(object sender, EventArgs e)
{
String strConnection = @"Data Source=PARAMERP4-PC\SPEEDPEQ;Initial Catalog=XYZ;Integrated Security=True";
//file upload path
string path = FileUpload12.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [CLIENT],[TASK],[DESCRIPTION],[DATE],[TIME TAKEN],[CALL ATTENDED BY],[ISSUE RESOLVED BY],[Help By] from [Sheet1
，excelConnection）;
excelConnection.Open（）;
OleDbDataReader dReader ;
dReader = cmd.ExecuteReader（）;
SqlBulkCopy sqlBulk = new SqlBulkCopy（strConnection）;
//给你的目的地表名
sqlBulk.DestinationTableName =Export_Excell_Sheet;
sqlBulk.WriteToServer（dReader）;
excelConnection.Close（）;
}
Excell列名：----客户任务，描述，日期，采取的时间，电话，参加，决议，解决，帮助由
数据库专栏名称： - tbale name -Export_Excell_Sheet
[Export_Excell_Sheet]
（
[客户]] [nvarchar] （50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[TASK] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[DESCRIPTION] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[日期] [datetime] NULL，
[TIME_TAKEN] [datetime] NULL，
[CALL_ATTENDED_BY] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[ISSUE_RESOLVED_BY] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL，
[Help_By] [nvarchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NULL
）
", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Export_Excell_Sheet";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
Excell collumn name :----CLIENT TASK, DESCRIPTION, DATE, TIME TAKEN, CALL, ATTENDED BY, ISSUE RESOLVED BY, Help By
Data Base Column name :--tbale name -Export_Excell_Sheet
[Export_Excell_Sheet]
(
[CLIENT] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TASK] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE] [datetime] NULL,
[TIME_TAKEN] [datetime] NULL,
[CALL_ATTENDED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISSUE_RESOLVED_BY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Help_By] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
使用以下参考：
使用SqlBulkCopy使用C＃和VB.Net将Excel SpreadSheet数据导入ASP.Net中的SQL Server [<啊ref =http://www.aspsnippets.com/Articles/Using-SqlBulkCopy-to-import-Excel-SpreadSheet-data-into-SQL-Server-in-ASPNet-using-C-and-VBNet.aspxtarget =_ blanktitle =新窗口> ^ ]
和
无法从c＃中的excel读取数据 - Stack Overflow [ ^ ]
Use following Ref :
Using SqlBulkCopy to import Excel SpreadSheet data into SQL Server in ASP.Net using C# and VB.Net[^]
and
Could not read data from excel in c# - Stack Overflow[^]
这篇关于如何在ASP.NET中将数据从EXCEL导入SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
