При интеграции данных и загрузке прайс-листов часто возникает необходимость прочитать файл Microsoft Excel без использования установленного офисного пакета на компьютере пользователя или сервере. В среде Windows традиционным подходом для этой задачи является использование COM-технологии и провайдера ADODB.Connection с драйвером Microsoft ACE OLEDB.
Однако при составлении запросов к книге Excel разработчики регулярно сталкиваются с синтаксическими ошибками, неверным определением имен колонок и ошибкой «Отсутствует значение для одного или нескольких требуемых параметров». Разберем подробно, как правильно составить строку подключения, написать SQL-запрос и обработать полученный результат.
Выясним причину частых ошибок при обращении к колонкам. В стандартном SQL к таблицам реляционных баз данных обращаются по именам полей. В файлах Excel поведение драйвера зависит от параметра HDR (Header) в строке подключения:
HDR=NO): драйвер воспринимает весь файл как чистые данные, начиная с первой строки. В этом случае обращение по буквам колонок (например, A, B, C) или конструкции вида [Лист1$A:B:C] приводят к ошибке синтаксиса. Драйвер автоматически именует колонки как F1, F2, F3 и так далее (сокращение от Field).HDR=YES): первая строка листа считается строкой заголовков. В запросе SELECT требуется указывать точные текстовые наименования из ячеек шапки. Если обратиться к F1 при включенном режиме заголовков, драйвер посчитает это отсутствующим параметром запроса и выдаст исключение.Если требуется назначить колонкам псевдонимы для последующей загрузки в таблицу значений 1С, используем ключевое слово AS. Если в псевдониме или названии колонки присутствуют пробелы или русские символы, рекомендуется заключать их в квадратные скобки.
Рассмотрим рабочий программный код для чтения колонок листа без заголовков, где колонка A (F1) сопоставляется с полем «Номер», а колонка B (F2) — с полем «ФИО».
Процедура ПрочитатьДанныеXLSX(ПутьКФайлу)
// Формируем строку подключения с указанием провайдера ACE OLEDB
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;"
+ "Data Source=" + ПутьКФайлу + ";"
+ "Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";";
ADODB_Connection = Новый COMОбъект("ADODB.Connection");
Попытка
ADODB_Connection.Open(СтрокаПодключения);
Исключение
Сообщить("Не удалось открыть соединение с файлом: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Обращаемся к колонкам через F1, F2 и задаем псевдонимы
СтрЗапроса = "SELECT F1 AS [Номер], F2 AS [ФИО] FROM [Лист1$]";
Попытка
RecordSet = ADODB_Connection.Execute(СтрЗапроса);
Исключение
ADODB_Connection.Close();
Сообщить("Ошибка при выполнении запроса к листу: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Обходим полученный набор записей
Пока НЕ RecordSet.EOF Цикл
// Обращаться к полям можно по их псевдонимам или индексам
ЗначениеНомера = RecordSet.Fields("Номер").Value;
ЗначениеФИО = RecordSet.Fields("ФИО").Value;
Сообщить(Строка(ЗначениеНомера) + " | " + Строка(ЗначениеФИО));
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
ADODB_Connection.Close();
КонецПроцедуры
Проанализируем ситуацию, когда подключение к файлу прошло успешно, но при вызове метода Execute или Recordset.Open возникает ошибка выполнения. Основные причины такого поведения:
F1, в то время как в строке подключения указано HDR=YES (или этот параметр опущен, и драйвер по умолчанию ожидает реальные названия из первой строки). Если в файле есть заголовок, пишите: SELECT [ИмяКолонки1], [ИмяКолонки2] FROM [Лист1$].[НазваниеЛиста$]. Если знак доллара пропущен, OLEDB будет искать именованный диапазон с таким именем, а не сам лист.[Лист1$A:H], а не перечисление через двоеточия A:B:C.При использовании OLE DB важно учитывать следующие технические ограничения:
NULL. Параметр IMEX=1 помогает обойти эту проблему, но максимальная надежность достигается только при настройке ключа реестра TypeGuessRows = 0.Если на целевой машине отсутствует драйвер OLEDB, либо база данных работает под управлением Linux, рекомендуется использовать встроенные возможности платформы 1С:Предприятие. Объект ТабличныйДокумент позволяет нативно прочитать файл Excel без использования сторонних компонент и внешних приложений.
ТабДок = Новый ТабличныйДокумент;
Попытка
ТабДок.Прочитать(ПутьКФайлу);
Исключение
Сообщить("Не удалось прочитать файл: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
ВсегоСтрок = ТабДок.ВысотаТаблицы;
// Начинаем со второй строки, если первая содержит заголовки
Для НомерСтроки = 2 По ВсегоСтрок Цикл
Номер = ТабДок.ПолучитьОбласть(НомерСтроки, 1).Текст; // Колонка A
ФИО = ТабДок.ПолучитьОбласть(НомерСтроки, 2).Текст; // Колонка B
Сообщить(Номер + " | " + ФИО);
КонецЦикла;
Данный способ является кроссплатформенным, не требует установки дополнительных драйверов и гарантирует чтение строковых представлений любых ячеек без потери данных.