我想在访问数据库表中导入Excel工作表数据 [英] I would like to import excel sheet data in my access database table
问题描述
我的设计代码如
< 正文 >
< form id = form1 runat = server < span class =code-keyword>>
< div >
< asp:FileUpload ID = FileUpload1 runat = server / >
< asp:按钮 ID = Button1 文字 = 上传 OnClick = 上传 runat = server / >
< / div >
< / form >
< / body >
我尝试过:
我的代码就像是
< pre lang =C#> 使用系统;
使用 System.Collections.Generic;
使用 System.Linq;
使用 System.Web;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.Configuration;
使用 System.Data;
使用 System.Data.OleDb;
使用 System.IO;
使用 System.Data.SqlClient;
使用 ACCESS = Microsoft.Office.Interop.Access;
命名空间 DevRural
{
public partial class UploadDoc:System.Web.UI.Page
{
protected void Page_Load( object sender,EventArgs e)
{
}
受保护 void 上传(对象发件人,EventArgs e)
{
// 上传并保存文件
string excelPath = Server.MapPath( 〜/ Excel /)+ Path.GetFileName(FileUpload1.PostedFile.FileName);
FileUpload1.SaveAs(excelPath);
string conString = string .Empty;
string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
switch (扩展名)
{
案例 。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,excelPath);
使用(OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null )。行[ 0 ] [ TABLE_NAME]。ToString( );
DataTable dtExcelData = new DataTable();
// [可选]:建议为否则,默认情况下数据将被视为字符串。
dtExcelData.Columns.AddRange( new DataColumn [ 24 ] { new DataColumn( Sno, typeof ( int )),
new DataColumn( BranchCode, typeof ( int )),
new DataColumn( TempRegistrationNo, typeof ( int )),
new DataColumn( RegistrationNo, typeof ( string ) ),
new DataColumn( EnrollmentNo , typeof ( string )),
new DataColumn( Salutation, typeof ( string )),
new DataColumn( FirstName, typeof ( string )),
new DataColumn( MiddleName, typeof ( string )),
new DataColumn( LastName, typeof ( string )),
new DataColumn( GuardianType, typeof ( string )),
new DataColumn( FatherOrSpouseFirstName, typeof ( string )),
new DataColumn( FatherOrSpouseLastName, typeof ( string )),
new DataColumn( DOB, typeof ( string )) ,
new DataColumn( Gender, typeof ( string )),
new DataColumn( MaritalStatus, typeof ( string )),
new DataColumn( 类别, typeof ( string )),
new DataColumn( 少数民族, typeof ( string )),
new DataColumn( Religion, typeof ( string )),
new DataColumn( PState, typeof ( string )),
new DataColumn( PDistrict, typeof ( string )),
new DataColumn( PBlockCity, typeof ( string )),
new DataColumn( PPinCode, typeof ( int )),
new DataColumn( PVillageLocation, typeof ( string )),
new DataColumn( PAddress1, typeof ( string ))
} );
使用(OleDbDataAdapter oda = new OleDbDataAdapter( SELECT * FROM [ + sheet1 + ],excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
// string consString = ConfigurationManager.ConnectionStrings [DDUGKY]。 ConnectionString;
OleDbConnection con = new OleDbConnection( < span class =code-string> Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\ accessdb\rural.mdb);
使用(OleDbConnection conn = new OleDbConnection( Provider = Microsoft.Jet.OLEDB.4.0; Data Source = E:\ accessdb \rural.mdb))
{
// 使用(SqlBulkCopy sqlBulkCopy = new OleDbDataAdapter(con))
使用(OleDbDataAdapter SqlBulkCo = new OleDbDataAdapter())
{
FileInfo fi = new FileInfo(FileUpload1.PostedFile.FileName);
string ext = fi.Extension;
if (ext == 。xls || ext == 。xlsx)
{
// 设置数据库表名
// SqlBulkCo.DestinationTableName =[dbo]。[stuReg];
// [可选]:将Excel列与数据库表的列相映射
SqlBulkCo.ColumnMappings.Add( Sno, 斯诺跨度>);
SqlBulkCo.ColumnMappings.Add( BranchCode, BranchCode);
SqlBulkCo.ColumnMappings.Add( TempRegistrationNo, TempRegistrationNo);
SqlBulkCo.ColumnMappings.Add( RegistrationNo, RegistrationNo);
SqlBulkCo.ColumnMappings.Add( EnrollmentNo, EnrollmentNo);
sqlBulkCopy.ColumnMappings.Add( Salutation, 称呼);
sqlBulkCopy.ColumnMappings.Add( FirstName, FirstName);
sqlBulkCopy.ColumnMappings.Add( MiddleName, MiddleName);
sqlBulkCopy.ColumnMappings.Add( LastName, LastName);
sqlBulkCopy.ColumnMappings.Add( GuardianType, GuardianType);
sqlBulkCopy.ColumnMappings.Add( FatherOrSpouseFirstName, FatherOrSpouseFirstName);
sqlBulkCopy.ColumnMappings.Add( FatherOrSpouseLastName, FatherOrSpouseLastName);
sqlBulkCopy.ColumnMappings.Add( DOB, DOB);
sqlBulkCopy.ColumnMappings.Add( Gender, 性别);
sqlBulkCopy.ColumnMappings.Add( MaritalStatus, MaritalStatus);
sqlBulkCopy.ColumnMappings.Add( Category, 类别);
sqlBulkCopy.ColumnMappings.Add( 少数民族, 少数民族);
sqlBulkCopy.ColumnMappings.Add( Religion, 宗教);
sqlBulkCopy.ColumnMappings.Add( PState, PState);
sqlBulkCopy.ColumnMappings.Add( PDistrict, PDistrict);
sqlBulkCopy.ColumnMappings.Add( PBlockCity, PBlockCity);
sqlBulkCopy.ColumnMappings.Add( PPinCode, PPinCode);
sqlBulkCopy.ColumnMappings.Add( PVillageLocation, PVillageLocation);
sqlBulkCopy.ColumnMappings.Add( PAddress1, PAddress1);
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
ScriptManager.RegisterStartupScript(Page,GetType(), script1, alert('Excel文件已成功导入DB');, true 跨度>);
}
其他
{
}
}
}
}
}
}
}
string Access = Server.MapPath( App_Data文件/ contacts.mdb跨度>);
string Excel = Server.MapPath( App_Data文件/ Book1.xls的跨度>);
string connect = Provider = Microsoft。 Jet.OLEDB.4.0;数据源= + Excel + ;扩展属性= Excel 8.0;< /跨度>;
使用(OleDbConnection conn = new OleDbConnection(connect))
{
使用(OleDbCommand cmd = new OleDbCommand())
{
cmd .Connection = conn;
cmd.CommandText = INSERT INTO [MS Access; Database = + Access + < span class =code-string> ]。[TableName_Person] SELECT * FROM [Sheet1
;
conn.Open();
cmd.ExecuteNonQuery();
}
}
需要进一步的帮助
C#Corner:错误显示 [ u7se this link ]
尝试这个我希望它能工作
不太清楚用户的情况,但如果你想让用户在线编辑excel表,一个很好的实现建议是这里:
spreadsheet.codeplex.com
现在我假设你的挑战不是简单的文件上传或下载,你至少没有写出来,所以如果你想要或需要我相信你可以。一般不是一个好主意IMO,但那不是我的电话。
如果您的问题是与Excel交互,以便您可以更新MSACCESS ISAAM DB我推荐.net互操作程序集,即使有点慢,它们也非常好:
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.aspx
最好也是最快的选择是使用open xml sdk,虽然不支持某些非常旧版本的excel:
欢迎使用Open XML SDK 2.5 for Office [ ^ ]
my design code like
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" Text="Upload" OnClick = "Upload" runat="server" />
</div>
</form>
</body>
What I have tried:
my code is like
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;
using ACCESS = Microsoft.Office.Interop.Access;
namespace DevRural
{
public partial class UploadDoc : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Excel/") + 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[24] { new DataColumn("Sno", typeof(int)),
new DataColumn("BranchCode", typeof(int)),
new DataColumn("TempRegistrationNo",typeof(int)),
new DataColumn("RegistrationNo",typeof(string)),
new DataColumn("EnrollmentNo",typeof(string)),
new DataColumn("Salutation",typeof(string)),
new DataColumn("FirstName",typeof(string)),
new DataColumn("MiddleName",typeof(string)),
new DataColumn("LastName",typeof(string)),
new DataColumn("GuardianType",typeof(string)),
new DataColumn("FatherOrSpouseFirstName",typeof(string)),
new DataColumn("FatherOrSpouseLastName",typeof(string)),
new DataColumn("DOB",typeof(string)),
new DataColumn("Gender",typeof(string)),
new DataColumn("MaritalStatus",typeof(string)),
new DataColumn("Category",typeof(string)),
new DataColumn("Minority",typeof(string)),
new DataColumn("Religion",typeof(string)),
new DataColumn("PState",typeof(string)),
new DataColumn("PDistrict",typeof(string)),
new DataColumn("PBlockCity",typeof(string)),
new DataColumn("PPinCode",typeof(int)),
new DataColumn("PVillageLocation",typeof(string)),
new DataColumn("PAddress1",typeof(string))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
//string consString = ConfigurationManager.ConnectionStrings["DDUGKY"].ConnectionString;
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb");
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\accessdb\rural.mdb"))
{
//using (SqlBulkCopy sqlBulkCopy = new OleDbDataAdapter(con))
using (OleDbDataAdapter SqlBulkCo =new OleDbDataAdapter() )
{
FileInfo fi = new FileInfo(FileUpload1.PostedFile.FileName);
string ext = fi.Extension;
if (ext == ".xls" || ext == ".xlsx")
{
//Set the database table name
//SqlBulkCo.DestinationTableName = "[dbo].[stuReg]";
//[OPTIONAL]: Map the Excel columns with that of the database table
SqlBulkCo.ColumnMappings.Add("Sno", "Sno");
SqlBulkCo.ColumnMappings.Add("BranchCode", "BranchCode");
SqlBulkCo.ColumnMappings.Add("TempRegistrationNo", "TempRegistrationNo");
SqlBulkCo.ColumnMappings.Add("RegistrationNo", "RegistrationNo");
SqlBulkCo.ColumnMappings.Add("EnrollmentNo", "EnrollmentNo");
sqlBulkCopy.ColumnMappings.Add("Salutation", "Salutation");
sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
sqlBulkCopy.ColumnMappings.Add("MiddleName", "MiddleName");
sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
sqlBulkCopy.ColumnMappings.Add("GuardianType", "GuardianType");
sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseFirstName", "FatherOrSpouseFirstName");
sqlBulkCopy.ColumnMappings.Add("FatherOrSpouseLastName", "FatherOrSpouseLastName");
sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
sqlBulkCopy.ColumnMappings.Add("MaritalStatus", "MaritalStatus");
sqlBulkCopy.ColumnMappings.Add("Category", "Category");
sqlBulkCopy.ColumnMappings.Add("Minority", "Minority");
sqlBulkCopy.ColumnMappings.Add("Religion", "Religion");
sqlBulkCopy.ColumnMappings.Add("PState", "PState");
sqlBulkCopy.ColumnMappings.Add("PDistrict", "PDistrict");
sqlBulkCopy.ColumnMappings.Add("PBlockCity", "PBlockCity");
sqlBulkCopy.ColumnMappings.Add("PPinCode", "PPinCode");
sqlBulkCopy.ColumnMappings.Add("PVillageLocation", "PVillageLocation");
sqlBulkCopy.ColumnMappings.Add("PAddress1", "PAddress1");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
ScriptManager.RegisterStartupScript(Page, GetType(), "script1", "alert('Excel file successfully imported into DB');", true);
}
else
{
}
}
}
}
}
}
}
string Access = Server.MapPath("App_Data/contacts.mdb"); string Excel = Server.MapPath("App_Data/Book1.xls"); string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Excel+";Extended Properties=Excel 8.0;"; using (OleDbConnection conn = new OleDbConnection(connect)) { using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = conn; cmd.CommandText = "INSERT INTO [MS Access;Database=" + Access + "].[TableName_Person] SELECT * FROM [Sheet1
"; conn.Open(); cmd.ExecuteNonQuery(); } }
for further help
C# Corner : Error Display[u7se this link]
try this one i hope it work
Not very clear of what the user case is, but if you want to allow users to edit an excel sheet online, a fine implementation suggestion is here:
spreadsheet.codeplex.com
Now i'm presuming your challanges is not the simple file upload or download, you didn't at least write that so in case you want or need that i trust you can. Generally not a good idea IMO, but that's not my call.
If your problem is to interact with Excel so that you can update MSACCESS ISAAM DB's i recommend the .net interop assemblies, they're pretty good even if somewhat slow:
https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.aspx
The best and by far fastest option is to use open xml sdk, though some very old versions of excel will not be supported:
Welcome to the Open XML SDK 2.5 for Office[^]
这篇关于我想在访问数据库表中导入Excel工作表数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!