本文介绍了如何使用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.
推荐答案
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
}
这篇关于如何使用ASP.net c#将Excel工作表导入Sql server 2008 r2数据库表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!