共用方式為

使用 Foreach 迴圈容器遍歷 Excel 檔案和資料表

本主題中的程序描述如何使用 Foreach 迴圈容器搭配適當的列舉器，遍歷資料夾中的 Excel 活頁簿，或遍歷 Excel 活頁簿中的數據表。

使用 Foreach 檔案列舉器來迴圈 Excel 檔案

  1. 建立字串變數，以在迴圈的每次迭代接收目前的 Excel 路徑和檔案名稱。 若要避免驗證問題，請將有效的 Excel 路徑和檔名指派為變數的初始值。 （此程式稍後顯示的範例表示式會使用變數名稱 ExcelFile。）

  2. 或者，建立另一個字串變數，以保存 Excel 連接字串之 Extended Properties 自變數的值。 這個自變數包含一系列值，指定 Excel 版本，並判斷第一個數據列是否包含數據行名稱，以及是否使用匯入模式。 （此程式稍後顯示的範例表達式會使用變數名稱 ExtProperties，初始值為 “Excel 8.0;HDR=Yes ”

    如果您未針對 Extended Properties 自變數使用變數，則必須手動將它新增至包含連接字串的運算式。

  3. 將 Foreach 循環容器新增至 [控制流程 ] 索引標籤。如需如何設定 Foreach 迴圈容器的詳細資訊，請參閱 設定 Foreach 迴圈容器。

  4. 在 Foreach 循環編輯器的 [集合] 頁面上，選取 [Foreach 檔案] 列舉值、指定 Excel 活頁簿所在的資料夾，以及指定檔案篩選條件（通常是 *.xls）。

  5. 在 變數對應 頁面上，將索引 0 對應至使用者定義的字串變數，以接收迴圈每次迭代時的當前 Excel 路徑和檔案名稱。 （此程式中稍後顯示的範例表示式會使用變數名稱 ExcelFile。）

  6. 關閉 Foreach 循環編輯器。

  7. 如 在套件中新增、刪除或共用連接管理器中所述，將Excel連接管理器新增至套件。 選取連線的現有 Excel 活頁簿檔案，以避免驗證錯誤。

    這很重要

    若要在設定使用此 Excel 連接管理員的工作和數據流元件時避免驗證錯誤，請在 Excel 連接管理器編輯器中選取現有的 Excel 活頁簿。 在您設定 ConnectionString 屬性的表達式後，連接管理員在執行期間將不會使用此活頁簿，如下所述的步驟所示。 建立並設定封裝之後，您可以在 [屬性] 視窗中清除 屬性的值 ConnectionString 。 不過，如果您清除此值，在 Foreach 循環執行之前，Excel 連接管理員的連接字串屬性將不再有效。 因此，您必須將工作或封裝中使用連接管理員的屬性 True 設定為 DelayValidation，以避免驗證錯誤。

    您也必須為 Excel 連接管理員的 RetainSameConnection 屬性使用 False 的預設值。 如果您將此值變更為 True，迴圈的每個反覆項目都會繼續開啟第一個 Excel 活頁簿。

  8. 選取新的 Excel 連接管理員，按下 [屬性] 視窗中的 運算式 屬性，然後點擊省略號。

  9. 在 [屬性表達式編輯器] 中 ConnectionString ，選取 屬性，然後按下省略號。

  10. 在 [表達式產生器] 中，輸入下列表達式：

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""

    請注意，使用逸出字元 “\” 逸出擴充屬性自變數的值周圍所需的內部引號。

    [擴充屬性] 參數不可省略。 如果您不使用變數來包含其值，則必須手動將它新增至表達式，如下列Excel 2003 檔案範例所示：

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"

  11. 在 Foreach 循環容器中建立工作，以使用 Excel 連接管理員在每個符合指定檔案位置和模式的 Excel 活頁簿上執行相同的作業。

若要透過使用 Foreach ADO.NET Schema Rowset 列舉器來循環操作 Excel 表格

  1. 建立使用 Microsoft Jet OLE DB Provider 連線至 Excel 活頁簿的 ADO.NET 連接管理員。 在 [ 連接管理器 ] 對話框的 [所有] 頁面上，確定您輸入 "Excel 8.0" 做為 [擴充屬性] 屬性的值。 如需詳細資訊，請參閱 在套件中新增、刪除或共用連接管理器。

  2. 建立一個字串變數，用於在迴圈的每次反覆運算中接收當前資料表的名稱。

  3. 將 Foreach 循環容器新增至 [控制流程 ] 索引標籤。如需如何設定 Foreach 循環容器的資訊，請參閱 設定 Foreach 迴圈容器。

  4. 在 Foreach 循環編輯器的 集合頁面上，選取 Foreach ADO.NET Schema 行集列舉器。

  5. 將 連接 的值設為您先前建立的 ADO.NET 連接管理員。

  6. 在架構的值中，選擇表格。

    備註

    Excel 活頁簿中的表格清單包括工作表（具有 $ 後綴）和具名範圍。 如果您只針對工作表或具名範圍篩選清單，您可能必須為此目的在腳本工作中撰寫自定義程序代碼。 如需詳細資訊，請參閱 使用腳本工作處理 Excel 檔案。

  7. 在 [ 變數對應 ] 頁面上，將 [索引 2] 對應至稍早建立的字串變數，以保存目前數據表的名稱。

  8. 關閉 Foreach 循環編輯器。

  9. 在 Foreach 循環容器中建立工作，以使用 Excel 連接管理員在指定活頁簿中的每個 Excel 數據表上執行相同的作業。 如果您使用腳本工作來檢查列舉數據表名稱或處理每個數據表，請記得將字串變數新增至腳本工作的 ReadOnlyVariables 屬性。

另請參閱

使用 SQL Server Integration Services （SSIS）將數據從 Excel 匯入或將數據匯出至 Excel設定 Foreach 循環容器
新增或變更屬性表達式
Excel 連接管理員
Excel 來源
Excel 輸出位置
使用腳本任務處理 Excel 檔案

  • Last updated on