Статья
04/01/2012

Как просматривать файлы и таблицы Excel с помощью контейнера «цикл по каждому элементу»

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

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

Создайте строковую переменную, которая будет принимать значение текущего пути и имени файла Excel для каждой итерации цикла. Чтобы избежать проверки данных, присвойте переменной в качестве начального значения путь и имя существующего файла Excel. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)
При желании создайте другую строковую переменную, которая будет хранить значение аргумента для дополнительных свойств строки соединения с Excel. Этот аргумент содержит серию значений, которые задают версию Excel и определяют, будет ли первая строка содержать имена столбцов и будет ли использован режим импорта. (Образец выражения, показанный ниже, использует в этой процедуре имя переменной ExtProperties с начальным значением «Excel 8.0;HDR=Yes».)

Если вы не используете переменную в качестве аргумента расширенных свойств, необходимо добавить его вручную в выражение, результатом которого является строка соединения.
Добавьте контейнер «цикл по каждому элементу» к вкладке Поток управления. Сведения о настройке контейнера «цикл по каждому элементу» см. в разделе Как настроить контейнер «цикл по каждому элементу».
На странице Коллекция в редакторе циклов по каждому элементу выберите перечислитель с циклом для каждого файла, задайте папку, в которой будет размещена книга Excel и файловый фильтр (обычно *.xls).
На странице Сопоставление переменной сопоставьте Index 0 со строковой переменной, определенной пользователем, которая будет принимать значение текущего пути Excel и имени файла на каждой итерации цикла. (Образец выражения, показанный ниже, использует в этой процедуре переменную ExcelFile.)
Закройте Редактор циклов по каждому элементу.

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

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

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

Необходимо также использовать значения по умолчанию False для свойства RetainSameConnection диспетчера соединений Excel. При изменении этого значения на True каждая итерация цикла будет по-прежнему открывать первую книгу Excel.

Выберите новый диспетчер соединений с Excel, щелкните свойство Выражения в окне свойств и нажмите кнопку обзора.
В редакторе выражений свойств выберите свойство ConnectionString и нажмите кнопку обзора.
В построителе выражения введите следующее выражение.
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
```
Обратите внимание на использование escape-символа «\» для обозначения внутреннего знака вопроса, требуемого около значения аргумента расширенных свойств.

Аргумент расширенных свойств является обязательным. Если для этого значения не используется переменная, его необходимо вручную добавить в выражение, как в следующем примере для файла Excel 2003:
```
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=Excel 8.0"
```
Создайте задачи в контейнере «цикл по каждому элементу», которые используют диспетчер соединений с Excel для выполнения одинаковых операций для каждой книги Excel, соответствующей заданному положению и шаблону файла.

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

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

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

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

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

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