[ASP.NET] 把Excel的資料匯入SQL Sever

2010-07-02

59375
0
ASP.NET
2010-07-07

摘要:[ASP.NET][VB.NET] 把Excel的資料匯入SQL Sever

有時有些客戶會要求，希望他可以把他以前的資料用Excel匯入資料庫中，

如果是一筆一筆新增的話，在網站是非常耗效能的，

在資料庫這一來一往的開關，會耗掉非常多效能，

當資料量小時也許沒有差別，但當資料是上萬筆以上，這樣一開一關就非常的耗效能了，

ADO.NET 2.0以上提供了SqlBulkCopy這個類別可以方便的把大量的資料複製，

SqlBulkCopy的詳細內容可以參考MSDN上的說明 (詳細)，

在資料庫中我範例的SQL Script如下：

CREATE TABLE [dbo].[ExcelData](
    [id] [int] NOT NULL,
    [name] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
    [email] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
CONSTRAINT [PK_ExcelData] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

這是新建立的Table還沒有匯入資料，

然後要準備對應的Excel，裡面的欄位要和資料庫的欄位做對應，

在這邊有兩點需要注意的地方，

(1)有黃底的這些欄位是跟資料庫對應的欄位

(2)Excel頁籤是再匯入時的table名稱，需要加上$字號，如ExcelData$

在程式中，我是寫當按"匯入"的按鈕時，便會做資料的匯入，檔案的實體路徑在範例中是寫死的，

如果要修改上傳並寫入再自行修改，

頁面的HTML為

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="test_BulkCopy.aspx.vb" Inherits="test_BulkCopy" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Excel的資料匯入SQL Sever</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnImport" runat="server" Text="匯入" />
    </div>
    </form>
</body>
</html>

程式的部分：

Imports System

Imports System.Collections.Generic

Imports System.IO

Imports System.Web.UI

Imports System.Text

Imports System.Web.Configuration

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.OleDb

Imports System.Data.Common

Imports System.Web.Security

Imports System.Web.UI.HtmlControls

Partial Class test_BulkCopy

Inherits System.Web.UI.Page

Friend Sub ImportDB()

'Excel檔案的實體路徑

Dim ExcelPath As String = "D:\TEST\test.xls"

'Excel的OLEDB ConnectionString

Dim ExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ExcelPath & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

Dim ExcelCn As New OleDbConnection(ExcelConnectionString)

Dim ExcelCmd As New OleDbCommand

Dim ExcelDr As DbDataReader

ExcelCmd.CommandText = "Select id,name,email from [ExcelData$]" '抓取Excel資料的SQL指令

ExcelCmd.CommandType = CommandType.Text

ExcelCmd.Connection = ExcelCn

ExcelCmd.Connection.Open()

ExcelDr = ExcelCmd.ExecuteReader(CommandBehavior.CloseConnection)

Dim cn As New SqlConnection '設定資料庫Connect物件

'設定資料庫Connection連接

cn.ConnectionString = ConfigurationManager.ConnectionStrings("test_conndb").ConnectionString

cn.Open()

Dim BulkCopy As New SqlBulkCopy(cn) '宣告SqlBulkCopy物件

BulkCopy.DestinationTableName = "ExcelData" '定義要匯入的資料庫Table

BulkCopy.WriteToServer(ExcelDr) '寫入資料

ExcelDr.Close()

ExcelCmd.Dispose()

ExcelCn.Dispose()

cn.Dispose()

End Sub

Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click

ImportDB()

End Sub

End Class

按下匯入按鈕後便會把資料做匯入

回首頁