姊姊❉心得報告

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

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

能說不口以！乾！嗎？

這個需求是這樣的：

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

平台：ASP .NET VB、SQL 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格式： 

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