excel匯入資料庫

使用者有時要大量資料資料要輸入時,不想一筆一筆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>

image

三、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}]", "Sheet1

quot;);

            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);

                }

            }

        }


    }

}

四、資料庫:

image

五、EXCEL格式:

1、第一列的標題要和資料庫的欄位一樣。

2、書籤只能用英文的 Sheet1

image

六、執行:

1、

image

2、選擇excel檔

image

3、點選匯入

image

4、

image

5、查詢資料庫

image

回應來自 excel匯入資料庫

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料

近期留言
分類
BlogUpp!