Как выполнить цикл по файлам Excel и таблицам

Изменения: 17 июля 2006 г.

Процедуры в этом разделе описывают, как выполнить цикл по книгам Excel в папке или цикл по таблицам в книге Excel с помощью контейнера «цикл по каждому элементу» с соответствующим перечислителем.

Организация цикла по файлам Excel с помощью перечислителя с циклом по каждому файлу

1. Создайте строковую переменную, которая будет принимать значение текущего пути и имени файла Excel для каждой итерации цикла. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)

   Примечание.
   Чтобы избежать проверки данных, присвойте переменной в качестве начального значения путь и имя существующего файла Excel.

2. Создайте другую строковую переменную, которая будет хранить значение аргумента для расширенных свойств строки соединения с Excel. Этот аргумент содержит серию значений, которые задают версию Excel и определяют, будет ли первая строка содержать имена столбцов и будет ли использован режим импорта. (Образец выражения, показанный ниже, использует в этой процедуре имя переменной ExtProperties с начальным значением "Excel 8.0;HDR=Yes``".)

3. Добавьте контейнер «цикл по каждому элементу» к вкладке Поток управления. Сведения о настройке контейнера «цикл по каждому элементу» см. в разделе Как настроить контейнер «Цикл по каждому элементу».

4. На странице Коллекция в редакторе циклов по каждому элементу выберите перечислитель с циклом для каждого файла, задайте папку, в которой будет размещена книга Excel и файловый фильтр (обычно *.xls).

5. На странице Сопоставление переменной сопоставьте Index 0 со строковой переменной, определенной пользователем, которая будет принимать значение текущего пути Excel и имени файла на каждой итерации цикла. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)

6. Закройте Редактор циклов по каждому элементу.

7. Добавьте диспетчер соединений с Excel к пакету, описанному в разделе Как добавить диспетчер соединений к пакету. Чтобы избежать ошибок проверки, выберите для подключения существующий файл книги Excel.

   Важно!

   Чтобы избежать ошибок проверки после настройки задач и компонентов потоков данных, которые используют этот диспетчер соединений Excel, назначьте существующую книгу Excel в Редакторе диспетчера соединений Excel. Диспетчер соединений не будет использовать эту книгу в процессе выполнения, если настроить выражение для свойства ConnectionString, как показано ниже. После создания и настройки пакета можно очистить значение свойства ConnectionString в окне свойств. Однако если очистить это значение, свойство строки подключения диспетчера соединений Excel более не будет действительно до тех пор, пока не будет запущен контейнер «цикл по каждому элементу». Следовательно, необходимо присвоить свойству DelayValidation значение True в задачах, где используется диспетчер соединений, или в пакете, чтобы избежать ошибок проверки.

8. Выберите новый диспетчер соединений с Excel, щелкните свойство Выражения в окне свойств и нажмите кнопку обзора.

9. В редакторе выражений свойств выберите свойство ConnectionString и нажмите кнопку обзора.

10. В построителе выражения введите следующее выражение.

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

    Обратите внимание на использование символа «\» для обозначения внутреннего знака вопроса, требуемого около значения аргумента расширенных свойств.

11. Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений с Excel для выполнения одинаковых операций для каждой книги Excel, соответствующей заданному положению и шаблону файла.

Организация цикла по таблицам Excel с помощью перечислителя по набору строк схемы ADO.NET

1. Создайте диспетчер соединений ADO.NET, который использует поставщик Microsoft Jet OLE DB для подключения к книге Excel. На странице «Все» диалогового окна Диспетчер соединений убедитесь, что в качестве значения дополнительных свойств введено Excel 8.0. Дополнительные сведения см. в разделе Как добавить диспетчер соединений к пакету.

2. Создайте строковую переменную, которая будет принимать имя текущий таблицы на каждой итерации цикла.

3. Добавьте контейнер «цикл по каждому элементу» к вкладке Поток управления. Сведения о настройке контейнера «цикл по каждому элементу» см. в разделе Как настроить контейнер «Цикл по каждому элементу».

4. На странице Коллекция в редакторе циклов по каждому элементу выберите перечислитель набора строк схемы ADO.NET.

5. В качестве значения Соединения выберите предварительно созданный диспетчер соединений ADO.NET.

6. В качестве значения Схема выберите «Таблицы».

   Примечание.
   Список таблиц в книге Excel включает в себя и листы (которые имеют суффикс $), и именованные диапазоны. Если нужно отфильтровать список только по листам или только по именованным диапазонам, то, возможно, для этой цели понадобится написать свою программу в задаче «Сценарий». Дополнительные сведения см. в разделе Working with Excel Files with the Script Task.

7. На странице Сопоставления переменных сопоставьте Index 2 со строковой переменной, созданной ранее для хранения имени текущей таблицы.

8. Закройте Редактор циклов по каждому элементу.

9. Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений Excel для выполнения одинаковых операций для каждой таблицы Excel в заданной книге. Если задача «Сценарий» используется для анализа имени таблицы перечислителя или работы с каждой таблицей, не забудьте добавить строковую переменную к свойству ReadOnlyVariables задачи «Сценарий».

См. также

Задачи

Как настроить контейнер «Цикл по каждому элементу»
Как создать выражение свойств

Основные понятия

Диспетчер соединений с Excel
Источник Excel
Назначение Excel

Другие ресурсы

Working with Excel Files with the Script Task

Справка и поддержка

Получение помощи по SQL Server 2005