如何使用ASP.net c＃将Excel工作表导入Sql server 2008 r2数据库表 [英] How to import Excel sheet to Sql server 2008 r2 database table using ASP.net c#
本文介绍了如何使用ASP.net c＃将Excel工作表导入Sql server 2008 r2数据库表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在通过asp.net c＃web应用程序将excel表数据导入我的数据库。我的数据库结构已经定义。现在我需要编写相同的代码。
我的excel表格式就是那样
I'm working on importing excel sheet data to my database through asp.net c# web application. My database structure is already defined. Now I need to code for the same.
My excel sheet format is like that
<table border="1">
<tr>
<td>Name</td>
<td>Code</td>
<td>Basic</td>
<td>Alwnce</td>
<td>Conv</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td>7600</td>
<td>1140</td>
<td>800</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td>9225</td>
<td>923</td>
<td>800</td>
</tr>
</table>
我的数据库结构如下
And my database structure like this
<table border="1">
<tr>
<td>Name</td>
<td>Code</td>
<td>Amount</td>
<td>CompCode</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 7600</td>
<td> 10</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 1140</td>
<td> 26</td>
</tr>
<tr>
<td>ABC</td>
<td>1126</td>
<td> 800</td>
<td> 30</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 9225</td>
<td> 10</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 923</td>
<td> 26</td>
</tr>
<tr>
<td>XYZ</td>
<td>2003</td>
<td> 800</td>
<td> 30</td>
</tr>
</table>
（基本为10，Alwnce为26，30对于Conv。这些是每个员工的重复编号。
请分享您的想法。
(10 for Basic,26 for Alwnce,30 for Conv. These are repeated no. for every employee)
Kindly share your idea.
推荐答案
最后我做到了。
听到是一个解决方案。
Finally I made it.
Hear is a solution.
protected void btnUpload_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
if (fUpload.FileName != null)
{
string fileLocation = Server.MapPath("~/Attachment/") + fUpload.FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
fUpload.SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileLocation + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
string fileExtension = System.IO.Path.GetExtension(fUpload.FileName);
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source="
+ fileLocation + ";Extended Properties='Excel 8.0 Xml;HDR=YES'";
}
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ fileLocation + ";Extended Properties='Excel 12.0 Xml;HDR=YES'";
}
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
excelConnection.Close();
excelConnection1.Close();
StartUploadEmails(ds);
}
else
{
lblErrro.Text = "Please select file to upload.";
}
}
public void StartUploadEmails(DataSet ds)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string EmpName = ds.Tables[0].Rows[i]["EmpName"].ToString();
string EmpCode = ds.Tables[0].Rows[i]["EmpCode"].ToString();
string Basic = ds.Tables[0].Rows[i]["EmpCode"].ToString();
string Allowance = ds.Tables[0].Rows[i]["Allowance"].ToString();
string Cony = ds.Tables[0].Rows[i]["Cony"].ToString();
string query = "";
for (int j = 0; j < 3; j++)
{
switch (j)
{
case 0:
query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Basic) + "', '" + (long)TypeCode.Basic + "')";
ExecuteData(query);
break;
case 1:
query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Allowance) + "', '" + (long)TypeCode.Allowance + "')";
ExecuteData(query);
break;
case 2:
query = "insert into GeetEmployee (EmpName, EmpCode, Amount, CompanyCode) values ('" + EmpName + "','" + EmpCode + "', '" + Convert.ToInt32(Cony) + "', '" + (long)TypeCode.Cony + "')";
ExecuteData(query);
break;
default:
break;
}
}
}
}
private void ExecuteData(string query)
{
try
{
cn.Open();
cmd = new SqlCommand(query, cn);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
cn.Close();
}
}
public enum TypeCode
{
Basic = 10,
Allowance = 20,
Cony = 30
}
你需要 OleDb连接字符串 [ ^ ]或
Excel数据阅读器 [ ^ ]读取Excel并获取数据集中的数据。
然后你可以使用你的自定义方法来构建查询，或使用批量插入将记录插入数据库。
HTTP：// stacko verflow.com/questions/6093007/copying-data-from-datatable-to-sql-server [ ^ ]
如何将数据从DataSet保存到T-SQL表 [ ^ ]
You need either OleDb Connection String [^] Or
Excel Data reader[^] to read the Excel and get the data in Data Set.
Then you can use your custom method to build query, or use bulk insert to insert the records to database.
http://stackoverflow.com/questions/6093007/copying-data-from-datatable-to-sql-server[^]
How to save data from a DataSet into a T-SQL table[^]
1。将您的Excel文件保存为.csv格式。
然后尝试fiollow这种方法。我希望它会帮助你
1. Save ur Excel file into .csv Format.
Then try to fiollow this method. I hope it ll help you
这篇关于如何使用ASP.net c＃将Excel工作表导入Sql server 2008 r2数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
