如何将Excel工作表数据上传到sqlserver数据库 [英] how to upload excel sheet data to sqlserver database

问题描述

请有人告诉我如何将excel工作表数据发送或上传到sql server，我创建了一个代码，它在本地系统上完美运行，但是当我在服务器端使用此代码时，它抛出了一个错误，那就是excel工作表路径也被具有oledb连接的另一个用户打开.

这是我的代码，请告诉我在服务器端使用代码应该怎么做.

Pls some one tell that how to send or upload excel sheet data to sql server , i have created a code and it running perfectly on local system, but when i used this code on server side it throwing an error that excel sheet path is also opened by another user with oledb connection.

this is my code Pls some one tell that what should i do with code to work on server side.

protected void cmdImport_Click(object sender, EventArgs e)
{
string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
string sDestConstr = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [MSISDN],[Name],[Company],[Status] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "tblAgent";
//You can mannualy set the column mapping by the following way.
//bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
bulkCopy.WriteToServer(dr);
}
}
}
}

我也用我的代码完成了列映射.请有人告诉......

i have done columnmapping also with my code.Pls some one tell......

推荐答案

"); OleDbCommand命令= 新 OleDbCommand(sql，sSourceConnection); sSourceConnection.Open(); 使用(OleDbDataReader dr = command.ExecuteReader()) { 使用(SqlBulkCopy bulkCopy = 新 SqlBulkCopy(sDestConstr)) { bulkCopy.DestinationTableName = " ; // 您可以通过以下方式手动设置列映射. // bulkCopy.ColumnMappings.Add("MSISDN"，"MSISDN"); bulkCopy.WriteToServer(dr); } } } }

"); OleDbCommand command = new OleDbCommand(sql, sSourceConnection); sSourceConnection.Open(); using (OleDbDataReader dr = command.ExecuteReader()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr)) { bulkCopy.DestinationTableName = "tblAgent"; //You can mannualy set the column mapping by the following way. //bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN"); bulkCopy.WriteToServer(dr); } } } }

我也用我的代码完成了列映射.请有人告诉……

i have done columnmapping also with my code.Pls some one tell......

看看:
http://code.msdn.microsoft.com/CSASPNETExcelImportExport-71cf1101/源代码? fileId = 21619& pathId = 1189055533
[ ^ ]
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx [ ^ ]
http://forums.asp.net/t/1194250.aspx/1 [ ^ ]

have a look at :
http://code.msdn.microsoft.com/CSASPNETExcelImportExport-71cf1101/sourcecode?fileId=21619&pathId=1189055533
[^]
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx[^]
http://forums.asp.net/t/1194250.aspx/1[^]

请参阅这些:

http://geekswithblogs.net/thanigai/archive/2009/04/27/asp.net-excel-file-import-and-transfer-to-sql-server.aspx [将数据从Excel导入到SQL Server [ ^ ]

http://weblogs.asp.net/rajbk/archive/2009/05/02/uploading-an-excel-file-to-sql-through-an-asp-net-webform.aspx [

Refer these :

http://geekswithblogs.net/thanigai/archive/2009/04/27/asp.net-excel-file-import-and-transfer-to-sql-server.aspx[^]

Import Data from Excel to SQL Server[^]

http://weblogs.asp.net/rajbk/archive/2009/05/02/uploading-an-excel-file-to-sql-through-an-asp-net-webform.aspx[^]

这篇关于如何将Excel工作表数据上传到sqlserver数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！