如何使用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数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！