dustin

user提出想匯入每年每月KPI的資料可以在PPS上給主管看

給的資料是照年份分Sheet的

例：KPI2009、KPI2010

所以會需要跑各Tab再將各Sheet的資料寫入

整個架構如下圖

使用三個參數

第一個資料流程，用指令碼元件去抓Excel上的Sheet名稱再將資料匯入到資料錄目的地

指令碼設定：

１、輸出欄位名稱為colSheet，型態為字串

２、設定連接管理員

３、編輯指令碼，因為檔案中還有其他資料Sheet，所以還要判斷只抓"KPI20"開頭的Sheet，輸出欄位為colSheet

01 Public Class ScriptMain
02     Inherits UserComponent
03
04     Dim xlsconn As OleDb.OleDbConnection
05     Dim dt As DataTable
06
07
08     Public Overrides Sub PreExecute() Overrides Sub PreExecute()
09         MyBase.PreExecute()
10         '
11         ' Add your code here for preprocessing or remove if not needed
12         '
13         Dim strMyXls As String = Me.Connections.Excel連接MESKPI.ConnectionString
14         xlsconn = New OleDb.OleDbConnection(strMyXls)
15         xlsconn.Open()
16         dt = xlsconn.GetSchema("Tables")
17
18     End Sub
19
20     Public Overrides Sub PostExecute() Overrides Sub PostExecute()
21         MyBase.PostExecute()
22         '
23         ' Add your code here for postprocessing or remove if not needed
24         ' You can set read/write variables here, for example:
25         ' Me.Variables.MyIntVar = 100
26         '
27     End Sub
28
29     Public Overrides Sub CreateNewOutputRows() Overrides Sub CreateNewOutputRows()
30         '
31         ' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
32         ' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
33         '
34         With OutputBuffer
35             For Each dr As DataRow In dt.Rows
36                 If dr.ItemArray(2).ToString.Length = 8 Then
37                     If dr.ItemArray(2).ToString.Substring(0, 5) = "KPI20" Then
38                         .AddRow()
39                         .colSheet = dr.ItemArray(2).ToString
40                     End If
41                 End If
42             Next
43         End With
44     End Sub
45
46     Public Overridable Sub ReleaseConnection() Overridable Sub ReleaseConnection()
47         xlsconn.Close()
48     End Sub
49
50 End Class

資料錄集合目的地

１、要設定的參數

２、輸入資料為colSheet

ForEach迴圈抓出每個Sheet名稱

把抓出來的SheetName丟給strExcelSheetname

先將每個Sheet名稱的年份抓出來丟給intYear參數

刪除資料庫中已存在同年份的資料。

因為是使用OLEDB，所以參數是用「？」

如果是用ADO.NET就要改成用「@」的

 設定傳入的參數

迴圈的資料流程

Excel資料來源，這裡使用的資料存取模式是「資料表名稱或檢視表名稱變數」，

因為要使用傳入的sheetname參數

另外要注意的是，Excel的Sheet名稱不可以含有「－」，不然在預覽跟執行時會發生

「索引和長度必須參考字串中的位置」這個錯誤

另外使用Excel來源似乎要將封裝改為32位元來執行，不然也會發生Driver的問題。

再來就直接轉到資料庫不說明了

雖然看似簡單，但是過程中發生一堆錯誤，尤其是Sheet名稱的錯誤，也是試不少時間才發現錯誤的原因。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

參考資料：http://ithelp.ithome.com.tw/question/10032561?tab=opinion