Выполнять SQL-запросы к файлам Excel
Хотя действия Excel могут обрабатывать большинство сценариев автоматизации Excel, запросы SQL могут более эффективно извлекать значительные объемы данных Excel и работать с ними.
Предположим, поток должен изменить только те реестры Excel, которые содержат определенное значение. Чтобы реализовать эту функциональность без SQL-запросов, вам потребуются циклы, условные выражения и несколько действий Excel.
Вы также можете реализовать эту функциональность с помощью SQL-запросов, используя только два действия: Открыть SQL-подключение и Выполнять инструкции SQL.
Откройте SQL-подключение к файлу Excel
Перед запуском SQL-запроса вы должны открыть подключение с файлом Excel, к которому вы хотите получить доступ.
Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте его, указав путь к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.
Теперь разверните действие Открыть SQL-подключение и заполните следующую строку подключения в его свойствах.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";
Заметка
Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД Microsoft Access 2010.
Откройте SQL-подключение к файлу Excel, защищенному паролем
Другой подход требуется в сценариях, где вы запускаете SQL-запросы к файлам Excel, защищенным паролем. Действие Открыть SQL-подключение не может подключиться к файлам Excel, защищенным паролем, поэтому вам необходимо снять защиту.
Для этого запустите файл Excel с помощью действие Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль.
Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл>Информация>Защита книги>Зашифровать паролем. Дополнительные сведения об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия можно найти в Автоматизировать классические приложения.
После выбора Зашифровать паролем заполните пустую строку во всплывающем диалоговом окне, используя действие Заполнить текстовое поле в окне. Чтобы заполнить пустую строку, используйте следующее выражение: %""%.
Чтобы нажать на ОК в диалоговом окне и применить изменения, разверните действие Нажать кнопку в окне.
Наконец, разверните действие Закрыть Excel, чтобы сохранить незащищенную книгу как новый файл Excel.
После сохранения файла следуйте инструкциям в Открытие SQL-подключения к файлу Excel, чтобы открыть к нему подключение.
Когда работа с файлом Excel будут завершена, используйте действие Удалить файлы для удаления незащищенной копии файла Excel.
Чтение содержимого электронной таблицы Excel
Хотя действие Считать с листа Excel может считывать содержимое листа Excel, циклы могут занять значительное время для итерации полученных данных.
Более эффективный способ получения определенных значений из электронных таблиц — это рассматривать файлы Excel как базы данных и выполнять на них SQL-запросы. Этот подход быстрее и увеличивает производительность потока.
Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действие Выполнить инструкцию SQL.
SELECT * FROM [SHEET$]
Заметка
Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:
SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'
Заметка
Чтобы применить этот SQL-запрос в ваших потоках, замените:
- SHEET с именем электронной таблицы, к которой вы хотите получить доступ.
- COLUMN NAME столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
- VALUE со значением, которое вы хотите найти.
Удалить данные из строки Excel
Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.
Точнее, вы можете использовать следующий SQL-запрос:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
При разработке потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Заполнители COLUMN1 а также COLUMN2 представляют имена всех столбцов для обработки. В этом примере два столбца, но в реальном сценарии количество столбцов может быть другим. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Часть запроса [COLUMN1]='VALUE'определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.
Получить данные Excel, кроме определенной строки
В некоторых сценариях может потребоваться получить все содержимое электронной таблицы Excel, кроме определенной строки.
Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.
Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:
SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL
Заполнители COLUMN1 а также COLUMN2 представляют имена всех столбцов для обработки. В этом примере два столбца, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.