excel匯入資料庫

發佈時間 2017-07-31 | 在 Angler

使用者有時要大量資料資料要輸入時，不想一筆一筆key，為了結省他們的時間，只好幫他們做上傳excel後，再匯入….

一、Create The Below Table:

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [bigint] NOT NULL,
    [EmployeeName] [varchar](max) NOT NULL,
    [Designation] [varchar](max) NOT NULL,
    [Posting] [varchar](max) NOT NULL,
    [Dept] [varchar](max) NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

二、aspx：

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    <style type="text/css">
        .auto-style1 {
            color: #FF0000;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        員工資料excel匯入(請用excel2007以上的版本附檔名為<span class="auto-style1">*.xlsx</span>，書籤只能用英文 <span class="auto-style1">Sheet1</span><br />
        <br />
    <asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="匯入" OnClick="btnUpload_Click" />
    </div>
    </form>
</body>
</html>

三、CS：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
public partial class test_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string sPath = Server.MapPath("~/excel/" + FileUpload1.FileName);
            FileUpload1.SaveAs(sPath);
            ImporttoSQL(sPath);
        }
    }
    private void ImporttoSQL(string sPath)
    {
        // Connect to Excel 2007 earlier version
        //string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\AgentList.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
        // Connect to Excel 2007 (and later) files with the Xlsx file extension 
        string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
        string sDestConstr = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
        OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
        using (sSourceConnection)
        {
string sql = string.Format("Select [Employee ID],[Employee Name],[Designation],[Posting],[Dept] FROM [{0}]", "Sheet1quot;);
            OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
            sSourceConnection.Open();
            using (OleDbDataReader dr = command.ExecuteReader())
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
                {
                    bulkCopy.DestinationTableName = "Employee";
                    //You can mannualy set the column mapping by  the following  way.
                    //bulkCopy.ColumnMappings.Add("Employee ID", "Employee Code");
                    bulkCopy.WriteToServer(dr);
                }
            }
        }
    }
}