寫程式,最怕遇到user問:「可以匯成Excel嗎?」、「我不想一筆一筆key,可以直接把Excel匯入嗎?」

可是每次一定會遇到這樣的需求,誰叫微軟無所不在呢?

 

能說不口以!乾!嗎?

 

 

 

 

這個需求是這樣的:

User希望系統介面上有個功能,能將手上現成的Excel檔,直接匯入SQL資料庫,省去他一筆一筆key in的麻煩。

 

平台:ASP .NET VBSQL Server 2005

 

 

 

 

 

以下範例,我為了把需求的變動性控制在最低,設計一個Excel範本,請user依照範本的欄位順序擺放資料,我的程式也依照此範本的欄位順序抓取資料。

如果你的user希望你照著他的格式走,那就要先取得他的格式,並且請他行行好,不要再改了!否則,請孤狗看看有無其他彈性寫法,不然就是你痛苦的開始囉!

 

 

 

Client side 

<asp:FileUpload ID="FileUploadQuestion" runat="server" Width="30%"/>

<asp:Button ID="ButtonUpload" runat="server" Text="匯入"/>

 

 

 

Server side 

Imports System.Data.OleDb

Imports System.Data

 

Partial Class Test

' 按下『匯入』鈕

  Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonUpload.Click

Dim FileUpload As FileUpload ' 抓取Client Side的檔案

    Dim OLEConn As OleDbConnection ' 與Excel連繫用

    Dim OLECommand As OleDbCommand

    Dim OLEDataAdapter As OleDbDataAdapter

    Dim dsResult As New Data.DataSet ' 存放Excel的資料內容

    Dim dtResult As New Data.DataTable

    Dim FileName As String = "" ' Client Side的檔案名稱

    Dim StartIndex As Integer = 0

    Dim ExcelConn As String = "" ' 與Excel連繫用

    Dim ExcelSelect As String = ""

    Dim Field1 As String = "" ' Primary Key

    Dim Field2 As String = ""

    Dim Field3 As Integer = 0

    Dim Field4 As String = ""

    Dim Field5 As String = ""

    Dim Field6 As String = ""

    Dim Field7 As String = ""

    Dim Field8 As String = ""

    Dim SqlString As String = "" ' 組新增至資料庫的SQL指令

    Dim InsertSuccCount As Integer = 0 ' 匯入成功筆數

    Dim InsertFailCount As Integer = 0 ' 匯入失敗筆數

 

    Try

      FileUpload = CType(Me.FindControl("FileUploadQuestion"), FileUpload)

      FileName = FileUpload.PostedFile.FileName

      If FileName = "" Then

        ' ut.MessageBox("請選取一份欲匯入的Excel檔案!", MsgBoxStyle.Information, Me.Page)

        Exit Sub

      Else

        StartIndex = FileName.LastIndexOf(".") + 1

        If FileName.Substring(StartIndex, 3) <> "xls" Then

          ' ut.MessageBox("請確認欲匯入的檔案格式為Excel!", MsgBoxStyle.Information, Me.Page)

          Exit Sub

        End If

      End If

 

      ExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=EXCEL 8.0"

      ExcelSelect = "SELECT * FROM [Sheet1$]"

 

      OLEConn = New OleDbConnection(ExcelConn)

      OLECommand = New OleDbCommand(ExcelSelect, OLEConn)

      OLEDataAdapter = New OleDbDataAdapter(OLECommand)

      OLEConn.Open()

      OLEDataAdapter.Fill(dsResult, "exceltogrid")

      OLEConn.Close()

      dtResult = dsResult.Tables("exceltogrid")

 

      If dtResult.Rows.Count > 0 Then

        For i As Integer = 0 To dtResult.Rows.Count 1

                 Field1 = dtResult.Rows(i).Item(0).ToString.Trim

            If Field1 <> "" Then

              Field2 = dtResult.Rows(i).Item(1).ToString.Trim

Field3 = Convert.ToInt32(dtResult.Rows(i).Item(2).ToString.Trim)

Field4 = dtResult.Rows(i).Item(3).ToString.Trim

Field5 = dtResult.Rows(i).Item(4).ToString.Trim

              Field6 = dtResult.Rows(i).Item(5).ToString.Trim

              Field7 = dtResult.Rows(i).Item(6).ToString.Trim

              Field8 = dtResult.Rows(i).Item(7).ToString.Trim

 

              SqlString = "INSERT INTO TestTable (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8) VALUES ('" & Field1 & "', '" & Field2 & "', " & Field3 & ", '" & Field4 & "', '" & Field5 & "', '" & Field6 & "', '" & Field7 & "', '" & Field8 & "')"

 

' 呼叫貴單位新增資料庫的函數

              If ExecuteInsert(SqlString) Then

                InsertSuccCount += 1

              Else

                InsertFailCount += 1

              End If

            Else

              Exit For

            End If

        Next

 

        ' ut.MessageBox("匯入成功筆數:" & InsertSuccCount.ToString & ".失敗筆數:" & InsertFailCount.ToString, MsgBoxStyle.Information, Me.Page)

' 此處可寫重新查詢GridView的Code,讓user看到匯入成功後的結果

      End If

    Catch ex As Exception

      ' ut.MessageBox(ex.ToString, MsgBoxStyle.Information, Me.Page)

      Exit Sub

    End Try

End Sub

End Class

 

 

 

Excel格式: 

 

A

B

C

D

E

F

G

H

1

Field1 Title 

Field2 Title

Field3 Title

Field4 Title

Field5 Title

Field6 Title

Field7 Title

Field8 Title

2

A

AA

1

AAA

AAAA

AAAAA

AAAAAA

AAAAAAA

3

B

BB

2

BBB

BBBB

BBBBB

BBBBBB

BBBBBBB

4

C

CC

3

CCC

CCCC

CCCCC

CCCCCC

CCCCCCC

5

D

DD

4

DDD

DDDD

DDDDD

DDDDDD

DDDDDDD

6

E

EE

5

EEE

EEEE

EEEEE

EEEEEE

EEEEEEE

 

 

 

 

 

我目前只會這個,其他類似需求請善用孤狗喔!


arrow
arrow
    全站熱搜

    姊姊❉心得報告 發表在 痞客邦 留言(0) 人氣()