以指令碼工作處理 Excel 檔案

發行項
03/08/2017

整合服務提供 Excel 連接管理員、Excel 來源和 Excel 目的地，以處理 Microsoft Excel 檔案格式試算表中儲存的資料。本主題所述的技術會使用指令碼工作取得有關可用 Excel 資料庫 (活頁簿檔案) 與資料表 (工作表與具名範圍) 的相關資訊。這些範例可以輕鬆地修改，以使用 Microsoft Jet OLE DB 提供者所支援的任何其他檔案型數據源。

設定封裝以測試範例

範例1：檢查 Excel 檔案是否存在

範例 2：檢查 Excel 數據表是否存在

範例 3：取得資料夾中的 Excel 檔案清單

範例 4：取得 Excel 檔案中的數據表清單

顯示範例的結果

注意

如果您想要建立可更輕鬆地在多個封裝之間重複使用的工作，請考慮使用此指令碼工作範例中的程式碼做為自訂工作的起點。如需詳細資訊，請參閱開發自訂工作。

設定封裝以測試範例

您可以設定單一封裝以測試本主題中的所有範例。範例使用許多相同的套件變數與相同的 .NET Framework 類別。

設定封裝與本主題中的範例搭配使用

在 SQL Server Data Tools (SSDT) 中建立新的整合服務專案，然後開啟預設套件進行編輯。
變數。開啟 [變數] 視窗，並定義下列變數：
- ExcelFile型 String別為的。輸入現有 Excel 活頁簿的完整路徑與檔案名稱。
- ExcelTable型 String別為的。輸入以 ExcelFile 變數值命名之活頁簿中，現有工作表或具名範圍的名稱。此值區分大小寫。
- ExcelFileExists型 Boolean別為的。
- ExcelTableExists型 Boolean別為的。
- ExcelFolder型 String別為的。輸入含有至少一個 Excel 活頁簿的資料夾完整路徑。
- ExcelFiles型 Object別為的。
- ExcelTables型 Object別為的。
Imports 陳述式。大部分的程式碼範例都需要您在指令碼檔案最上方匯入下列一或兩個 .NET Framework 命名空間：
- System.IO，用於檔案系統作業。
- System.Data.OleDb，以開啟Excel檔案做為數據源。
參考。從 Excel 檔案讀取架構資訊的程式代碼範例需要命名空間腳本專案中 System.Xml 的額外參考。
請使用 [選項] 對話方塊中 [一般] 頁面上的 [指令碼語言] 選項，為指令碼元件設定預設的指令碼語言。如需相關資訊，請參閱 General Page。

範例 1 描述：檢查 Excel 檔案是否存在

此範例會判斷 ExcelFile 變數中指定的 Excel 活頁簿檔案是否存在，然後將 ExcelFileExists 變數的布林值設定為結果。您可以為封裝工作流程中的分支使用此布林值。

設定此指令碼工作範例

將新的文稿工作新增至封裝，並將名稱變更為 ExcelFileExists。
在 [指令碼工作編輯器] 的 [指令碼] 索引標籤上，按一下 ReadOnlyVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelFile。
  
  -或-
- 按兩下屬性欄位旁的省略號（...）按鈕，然後在 [選取變數] 對話框中，選取ExcelFile變數。
按一下 ReadWriteVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelFileExists。
  
  -或-
- 按兩下屬性欄位旁的省略號（...）按鈕，然後在 [選取變數] 對話框中，選取ExcelFileExists變數。
按一下 [編輯指令碼]，以開啟指令碼編輯器。
Imports在腳本檔案頂端新增命名空間的語句System.IO。
加入下列程式碼。

範例 1 程式碼

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    If File.Exists(fileToTest) Then  
      Dts.Variables("ExcelFileExists").Value = True  
    Else  
      Dts.Variables("ExcelFileExists").Value = False  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
  {  
    string fileToTest;  
  
    fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
    if (File.Exists(fileToTest))  
    {  
      Dts.Variables["ExcelFileExists"].Value = true;  
    }  
    else  
    {  
      Dts.Variables["ExcelFileExists"].Value = false;  
    }  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

範例 2 描述：檢查 Excel 資料表是否存在

此範例會判斷 ExcelTable 變數中指定的 Excel 工作表或具名範圍是否存在於 ExcelFile 變數中指定的 Excel 活頁簿檔案，然後將 ExcelTableExists 變數的布林值設定為結果。您可以為封裝工作流程中的分支使用此布林值。

設定此指令碼工作範例

將新的文稿工作新增至封裝，並將名稱變更為 ExcelTableExists。
在 [指令碼工作編輯器] 的 [指令碼] 索引標籤上，按一下 ReadOnlyVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelTable 並以 ExcelFile 逗號分隔.
  
  -或-
- 按兩下屬性欄位旁邊的省略號（...）按鈕，然後在 [選取變數 ] 對話框中，選取 ExcelTable 和 ExcelFile 變數。
按一下 ReadWriteVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelTableExists。
  
  -或-
- 按兩下屬性欄位旁的省略號（...）按鈕，然後在 [選取變數] 對話框中，選取ExcelTableExists變數。
按一下 [編輯指令碼]，以開啟指令碼編輯器。
在腳本專案中加入元件的參考 System.Xml 。
在Imports腳本檔案頂端新增和 System.Data.OleDb 命名空間的語句System.IO。
加入下列程式碼。

範例 2 程式碼

Public Class ScriptMain  
  Public Sub Main()  
    Dim fileToTest As String  
    Dim tableToTest As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim excelTables As DataTable  
    Dim excelTable As DataRow  
    Dim currentTable As String  
  
    fileToTest = Dts.Variables("ExcelFile").Value.ToString  
    tableToTest = Dts.Variables("ExcelTable").Value.ToString  
  
    Dts.Variables("ExcelTableExists").Value = False  
    If File.Exists(fileToTest) Then  
      connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _  
        "Data Source=" & fileToTest & _  
        ";Extended Properties=Excel 8.0"  
      excelConnection = New OleDbConnection(connectionString)  
      excelConnection.Open()  
      excelTables = excelConnection.GetSchema("Tables")  
      For Each excelTable In excelTables.Rows  
        currentTable = excelTable.Item("TABLE_NAME").ToString  
        If currentTable = tableToTest Then  
          Dts.Variables("ExcelTableExists").Value = True  
        End If  
      Next  
    End If  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
    public void Main()  
        {  
            string fileToTest;  
            string tableToTest;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable excelTables;  
            string currentTable;  
  
            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();  
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();  
  
            Dts.Variables["ExcelTableExists"].Value = false;  
            if (File.Exists(fileToTest))  
            {  
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";  
                excelConnection = new OleDbConnection(connectionString);  
                excelConnection.Open();  
                excelTables = excelConnection.GetSchema("Tables");  
                foreach (DataRow excelTable in excelTables.Rows)  
                {  
                    currentTable = excelTable["TABLE_NAME"].ToString();  
                    if (currentTable == tableToTest)  
                    {  
                        Dts.Variables["ExcelTableExists"].Value = true;  
                    }  
                }  
            }  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
  
        }  
}

範例 3 描述：取得資料夾中 Excel 檔案的清單

此範例會使用在 ExcelFolder 變數值中指定的資料夾內所找到的 Excel 檔案清單，來填滿陣列，然後將陣列複製到 ExcelFiles 變數中。您可以使用 Foreach From Variable 列舉值來反覆運算陣列中的檔案。

設定此指令碼工作範例

將新指令碼工作新增至套件，並將其名稱變更為 GetExcelFiles。
開啟 [指令碼工作編輯器] 的 [指令碼] 索引標籤，並按一下 ReadOnlyVariables，然後使用下列其中一項方法輸入屬性值：
- 輸入 ExcelFolder
  
  -或-
- 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕，然後在 [選取變數] 對話方塊中選取 [ExcelFolder] 變數。
按一下 ReadWriteVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelFiles。
  
  -或-
- 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕，然後在 [選取變數] 對話方塊中選取 [ExcelFiles] 變數。
按一下 [編輯指令碼]，以開啟指令碼編輯器。
Imports在腳本檔案頂端新增命名空間的語句System.IO。
加入下列程式碼。

範例 3 程式碼

Public Class ScriptMain  
  Public Sub Main()  
    Const FILE_PATTERN As String = "*.xls"  
  
    Dim excelFolder As String  
    Dim excelFiles As String()  
  
    excelFolder = Dts.Variables("ExcelFolder").Value.ToString  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN)  
  
    Dts.Variables("ExcelFiles").Value = excelFiles  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
  {  
    const string FILE_PATTERN = "*.xls";  
  
    string excelFolder;  
    string[] excelFiles;  
  
    excelFolder = Dts.Variables["ExcelFolder"].Value.ToString();  
    excelFiles = Directory.GetFiles(excelFolder, FILE_PATTERN);  
  
    Dts.Variables["ExcelFiles"].Value = excelFiles;  
  
    Dts.TaskResult = (int)ScriptResults.Success;  
  }  
}

替代方案

您也可以使用 ForEach 檔案列舉值反覆運算資料夾中的所有 Excel 檔案，以代替使用指令碼工作將 Excel 檔案清單蒐集到陣列中的方式。如需詳細資訊，請參閱使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈。

範例 4 描述：取得 Excel 檔案中的資料表清單

此範例會使用在 ExcelFile 變數值中指定的 Excel 活頁簿檔案內找到的工作表清單和具名範圍，來填滿陣列，然後將陣列複製到 ExcelTables 變數中。您可以使用 Foreach From Variable 列舉值來反覆運算陣列中的資料表。

注意

Excel 活頁簿中資料表清單包含活頁簿 (具有 $ 後置詞) 及具名範圍。如果您必須只篩選清單中的工作表或具名範圍，必須加入其他程式碼以達成此目的。

設定此指令碼工作範例

將新指令碼工作新增至套件，並將其名稱變更為 GetExcelTables。
開啟 [指令碼工作編輯器] 的 [指令碼] 索引標籤，並按一下 ReadOnlyVariables，然後使用下列其中一項方法輸入屬性值：
- 輸入 ExcelFile。
  
  -或-
- 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕，然後在 [選取變數] 對話方塊中選取 [ExcelFile] 變數。
按一下 ReadWriteVariables，並使用下列其中一項方法輸入屬性值：
- 輸入 ExcelTables。
  
  -或-
- 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕，然後在 [選取變數] 對話方塊中選取 [ExcelTables] 變數。
按一下 [編輯指令碼]，以開啟指令碼編輯器。
在文稿專案中新增命名空間的參考 System.Xml 。
Imports在腳本檔案頂端新增命名空間的語句System.Data.OleDb。
加入下列程式碼。

範例 4 程式碼

Public Class ScriptMain  
  Public Sub Main()  
    Dim excelFile As String  
    Dim connectionString As String  
    Dim excelConnection As OleDbConnection  
    Dim tablesInFile As DataTable  
    Dim tableCount As Integer = 0  
    Dim tableInFile As DataRow  
    Dim currentTable As String  
    Dim tableIndex As Integer = 0  
  
    Dim excelTables As String()  
  
    excelFile = Dts.Variables("ExcelFile").Value.ToString  
    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _  
        "Data Source=" & excelFile & _  
        ";Extended Properties=Excel 8.0"  
    excelConnection = New OleDbConnection(connectionString)  
    excelConnection.Open()  
    tablesInFile = excelConnection.GetSchema("Tables")  
    tableCount = tablesInFile.Rows.Count  
    ReDim excelTables(tableCount - 1)  
    For Each tableInFile In tablesInFile.Rows  
      currentTable = tableInFile.Item("TABLE_NAME").ToString  
      excelTables(tableIndex) = currentTable  
      tableIndex += 1  
    Next  
  
    Dts.Variables("ExcelTables").Value = excelTables  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
        {  
            string excelFile;  
            string connectionString;  
            OleDbConnection excelConnection;  
            DataTable tablesInFile;  
            int tableCount = 0;  
            string currentTable;  
            int tableIndex = 0;  
  
            string[] excelTables = new string[5];  
  
            excelFile = Dts.Variables["ExcelFile"].Value.ToString();  
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
                "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";  
            excelConnection = new OleDbConnection(connectionString);  
            excelConnection.Open();  
            tablesInFile = excelConnection.GetSchema("Tables");  
            tableCount = tablesInFile.Rows.Count;  
  
            foreach (DataRow tableInFile in tablesInFile.Rows)  
            {  
                currentTable = tableInFile["TABLE_NAME"].ToString();  
                excelTables[tableIndex] = currentTable;  
                tableIndex += 1;  
            }  
  
            Dts.Variables["ExcelTables"].Value = excelTables;  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

替代方案

您也可以使用 Foreach ADO.NET 結構描述資料列集列舉值，反覆運算在 Excel 活頁簿檔案中的所有資料表 (也就是，工作表與具名範圍)，以代替使用指令碼工作將 Excel 資料表清單蒐集到陣列中的方式。如需詳細資訊，請參閱使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈。

顯示範例的結果

如果您已在相同封裝中設定此主題中的每個範例，可以將所有的指令碼工作連接至其他顯示所有範例輸出的指令碼工作。

設定指令碼工作以顯示本主題中的範例輸出

將新指令碼工作新增至套件，並將其名稱變更為 DisplayResults。
依序連線這四個範例指令碼工作，好讓每個工作在前一個工作順利完成之後接著執行，然後將第四個範例工作連線至 DisplayResults 工作。
開啟 [指令碼工作編輯器] 中的 DisplayResults 工作。
在 [指令碼] 索引標籤上，按一下 ReadOnlyVariables 並使用下列其中一個方法，新增設定套件以測試範例中的所有七個變數：
- 輸入每個變數名稱，並以逗號分隔。
  
  -或-
- 按一下屬性欄位旁邊的省略符號 ([...]) 按鈕，然後在 [選取變數] 對話方塊中選取變數。
按一下 [編輯指令碼]，以開啟指令碼編輯器。
在Imports腳本檔案頂端新增和 System.Windows.Forms 命名空間的語句Microsoft.VisualBasic。
加入下列程式碼。
執行封裝並檢查在訊息方塊中顯示的結果。

可顯示結果的程式碼

Public Class ScriptMain  
  Public Sub Main()  
    Const EOL As String = ControlChars.CrLf  
  
    Dim results As String  
    Dim filesInFolder As String()  
    Dim fileInFolder As String  
    Dim tablesInFile As String()  
    Dim tableInFile As String  
  
    results = _  
      "Final values of variables:" & EOL & _  
      "ExcelFile: " & Dts.Variables("ExcelFile").Value.ToString & EOL & _  
      "ExcelFileExists: " & Dts.Variables("ExcelFileExists").Value.ToString & EOL & _  
      "ExcelTable: " & Dts.Variables("ExcelTable").Value.ToString & EOL & _  
      "ExcelTableExists: " & Dts.Variables("ExcelTableExists").Value.ToString & EOL & _  
      "ExcelFolder: " & Dts.Variables("ExcelFolder").Value.ToString & EOL & _  
      EOL  
  
    results &= "Excel files in folder: " & EOL  
    filesInFolder = DirectCast(Dts.Variables("ExcelFiles").Value, String())  
    For Each fileInFolder In filesInFolder  
      results &= " " & fileInFolder & EOL  
    Next  
    results &= EOL  
  
    results &= "Excel tables in file: " & EOL  
    tablesInFile = DirectCast(Dts.Variables("ExcelTables").Value, String())  
    For Each tableInFile In tablesInFile  
      results &= " " & tableInFile & EOL  
    Next  
  
    MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information)  
  
    Dts.TaskResult = ScriptResults.Success  
  End Sub  
End Class

public class ScriptMain  
{  
  public void Main()  
        {  
            const string EOL = "\r";  
  
            string results;  
            string[] filesInFolder;  
            //string fileInFolder;  
            string[] tablesInFile;  
            //string tableInFile;  
  
            results = "Final values of variables:" + EOL + "ExcelFile: " + Dts.Variables["ExcelFile"].Value.ToString() + EOL + "ExcelFileExists: " + Dts.Variables["ExcelFileExists"].Value.ToString() + EOL + "ExcelTable: " + Dts.Variables["ExcelTable"].Value.ToString() + EOL + "ExcelTableExists: " + Dts.Variables["ExcelTableExists"].Value.ToString() + EOL + "ExcelFolder: " + Dts.Variables["ExcelFolder"].Value.ToString() + EOL + EOL;  
  
            results += "Excel files in folder: " + EOL;  
            filesInFolder = (string[])(Dts.Variables["ExcelFiles"].Value);  
            foreach (string fileInFolder in filesInFolder)  
            {  
                results += " " + fileInFolder + EOL;  
            }  
            results += EOL;  
  
            results += "Excel tables in file: " + EOL;  
            tablesInFile = (string[])(Dts.Variables["ExcelTables"].Value);  
            foreach (string tableInFile in tablesInFile)  
            {  
                results += " " + tableInFile + EOL;  
            }  
  
            MessageBox.Show(results, "Results", MessageBoxButtons.OK, MessageBoxIcon.Information);  
  
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
}

Integration Services 圖示（小型） 使用 Integration Services 保持最新狀態
如需來自Microsoft的最新下載、文章、範例和影片，以及來自社群的所選解決方案，請流覽 MSDN 上的 Integration Services 頁面：

流覽 MSDN 上的 Integration Services 頁面

如需這些更新的自動通知，請訂閱頁面上可用的 RSS 摘要。

另請參閱

Excel 連線管理員
 使用 Foreach 迴圈容器來執行 Excel 檔案和資料表迴圈

共用方式為

以指令碼工作處理 Excel 檔案

設定封裝以測試範例

設定封裝與本主題中的範例搭配使用

範例 1 描述：檢查 Excel 檔案是否存在

設定此指令碼工作範例

範例 1 程式碼

範例 2 描述：檢查 Excel 資料表是否存在

設定此指令碼工作範例

範例 2 程式碼

範例 3 描述：取得資料夾中 Excel 檔案的清單

設定此指令碼工作範例

範例 3 程式碼

替代方案

範例 4 描述：取得 Excel 檔案中的資料表清單

設定此指令碼工作範例

範例 4 程式碼

替代方案

顯示範例的結果

設定指令碼工作以顯示本主題中的範例輸出

可顯示結果的程式碼

另請參閱

其他資源