Чтение файлов Excel — одна из самых частых задач при интеграции 1С с внешними системами или при импорте прайс-листов и номенклатуры. Существует несколько способов реализации этой задачи: запуск полноценного COM-объекта Excel (что медленно и требует установленного MS Office), использование платформенных механизмов 1С, а также технология ActiveX Data Objects (ADO). Использование технологии ADO через объект ADODB.Connection позволяет считывать данные крайне быстро, работая с файлом Excel как со стандартной базой данных при помощи SQL-запросов.
Однако при разработке кода для ADO программисты часто сталкиваются с неочевидными техническими нюансами. В этой статье мы подробно разберем, почему при чтении файла может «пропадать» единственная строка данных, как правильно ограничить выборку нужным диапазоном строк непосредственно в SQL-запросе, как обратиться к листу Excel по его порядковому номеру, а также рассмотрим современную высокопроизводительную альтернативу.
Рассмотрим стандартную ситуацию. Вы настроили подключение к файлу Excel, открыли сессию связи, написали запрос, но обнаружили, что если в файле присутствует всего одна строка данных (или вы хотите прочитать самую первую строку), она просто не попадает в итоговую выборку Recordset.
Причина кроется в параметре строки подключения HDR (Headers), который передается в составе расширенных свойств (Extended Properties):
СтрокаСоединения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ИмяВременногоФайла + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""";
Давайте проанализируем, как ведет себя драйвер OLE DB при разных значениях этого параметра:
Recordset и использует ее значения для именования полей в коллекции Fields. Если в вашем файле (или выбранном диапазоне) содержится всего одна строка, драйвер посчитает ее заголовком, и сам набор данных окажется пустым.F1, F2, F3 и так далее (где цифра соответствует порядковому номеру колонки).Решение: Если вам необходимо читать абсолютно все строки файла (включая первую), всегда указывайте в строке подключения параметр HDR=No. Для обращения к колонкам в коде 1С используйте их порядковые номера в коллекции полей.
Очень часто структура файла Excel предполагает наличие «шапки» документа: названия организации, реквизитов, даты формирования и прочей метаинформации, расположенной в первых строках. Полезная табличная часть при этом может начинаться, например, только с 5-й или 10-й строки.
В коде разработчики часто пытаются пропускать ненужные строки вручную в цикле обхода выборки, используя метод MoveNext(). Это крайне неэффективное решение, которое сильно снижает скорость обработки больших файлов.
Выясним, как решить эту задачу правильно. Технология ADO позволяет задавать точный диапазон считываемых ячеек прямо в SQL-запросе. Для этого после имени листа (которое должно заканчиваться символом доллара $) указывается стандартный диапазон ячеек Excel через двоеточие. Обратите внимание, что имя листа вместе с диапазоном должно быть заключено в квадратные скобки либо в косые апострофы.
Рассмотрим пример запроса, который выбирает данные начиная с ячейки A5 и до колонки Z включительно:
// Указываем конкретную начальную строку (5) и конечную колонку (Z)
ТекстЗапроса = "SELECT * FROM [Лист1$A5:Z]";
Если мы заранее не знаем точное количество строк в файле, мы можем опустить конечный номер строки в диапазоне (как показано в примере выше). Драйвер автоматически прочитает все заполненные строки от указанной начальной позиции до конца таблицы. При установленном свойстве HDR=No первой строкой вашего результирующего набора данных станет именно 5-я строка файла Excel.
По умолчанию SQL-запросы к Excel требуют точного указания имени листа (таблицы), например: FROM [Лист1$]. Но что делать, если имя листа динамически меняется, или пользователь прислал файл, где первый лист называется произвольно? Нам нужно программно получить имя первого по порядку листа в книге Excel.
Для решения этой задачи воспользуемся COM-объектом ADOX.Catalog. Он позволяет получить полную схему структуры базы данных (в нашем случае — книги Excel) и узнать имена всех ее таблиц (листов).
Разберем по шагам код, выполняющий данную операцию:
// 1. Инициализируем и открываем соединение ADODB
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.Open(СтрокаСоединения);
// 2. Создаем объект ADOX для анализа структуры файла
КаталогADOX = Новый COMОбъект("ADOX.Catalog");
КаталогADOX.ActiveConnection = Соединение;
// 3. Получаем имя первого по порядку листа книги
// Индексация в коллекциях COM начинается с нуля
ИмяПервогоЛиста = "";
Если КаталогADOX.Tables.Count > 0 Тогда
// Получаем объект таблицы по его индексу
ТаблицаExcel = КаталогADOX.Tables.Item(0);
ИмяПервогоЛиста = ТаблицаExcel.Name;
КонецЕсли;
Важный технический нюанс: В коллекции таблиц Catalog.Tables могут находиться не только видимые пользователю листы Excel, но и служебные именованные области, а также скрытые системные диапазоны (например, области автофильтров с суффиксом $_FilterDatabase). Настоящие листы Excel всегда заканчиваются символом доллара $. Чтобы избежать ошибок, при программном поиске листов по индексу обязательно делайте проверку имени:
ИмяНужногоЛиста = "";
Для Индекс = 0 По КаталогADOX.Tables.Count - 1 Цикл
ИмяТаблицы = КаталогADOX.Tables.Item(Индекс).Name;
// Лист Excel должен заканчиваться на символ "$" или "$'"
Если Прав(ИмяТаблицы, 1) = "$" Или Прав(ИмяТаблицы, 2) = "$'" Тогда
ИмяНужногоЛиста = ИмяТаблицы;
Прервать; // Нашли первый настоящий лист
КонецЕсли;
КонецЦикла;
Ниже представлен готовый к использованию, оптимизированный программный код функции, которая считывает данные из временного хранилища, подключается к первому листу файла Excel с помощью ADO, учитывает указанную стартовую строку и возвращает заполненную ТаблицуЗначений.
Функция ПрочитатьExcelЧерезADO(АдресВременногоХранилища, СтруктураКолонок, НомерЛиста = 1, НомерПервойСтроки = 1) Экспорт
ДвоичныеДанные = ПолучитьИзВременногоХранилища(АдресВременногоХранилища);
ИмяВременногоФайла = ПолучитьИмяВременногоФайла(".xls");
ДвоичныеДанные.Записать(ИмяВременногоФайла);
// Создаем результирующую Таблицу Значений в соответствии со структурой
ТЗПрочитанныеДанные = Новый ТаблицаЗначений;
Для Каждого КлючИЗначение Из СтруктураКолонок Цикл
ТЗПрочитанныеДанные.Колонки.Добавить(КлючИЗначение.Ключ);
КонецЦикла;
// Формируем строку подключения для провайдера Microsoft.Jet.OLEDB.4.0
// Указываем HDR=No, чтобы не терять строки данных, и IMEX=1 для чтения смешанных типов как текст
СтрокаСоединения = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + СокрЛП(ИмяВременногоФайла) + ";
|Extended Properties=""Excel 8.0;HDR=No;IMEX=1""";
Соединение = Новый COMОбъект("ADODB.Connection");
Попытка
Соединение.Open(СтрокаСоединения);
Исключение
Ошибка = ОписаниеОшибки();
УдалитьФайлы(ИмяВременногоФайла);
ЗаписатьЛогСобытий("Ошибка открытия ADO-соединения: " + Ошибка);
Возврат Неопределено;
КонецПопытки;
// Получаем настоящее имя листа по его порядковому номеру через ADOX
ИмяЛиста = "";
Попытка
Каталог = Новый COMОбъект("ADOX.Catalog");
Каталог.ActiveConnection = Соединение;
СчетчикЛистов = 0;
Для Индекс = 0 По Каталог.Tables.Count - 1 Цикл
ИмяТаблицы = Каталог.Tables.Item(Индекс).Name;
Если Прав(ИмяТаблицы, 1) = "$" Или Найти(ИмяТаблицы, "$'") > 0 Тогда
СчетчикЛистов = СчетчикЛистов + 1;
Если СчетчикЛистов = НомерЛиста Тогда
ИмяЛиста = ИмяТаблицы;
Прервать;
КонецЕсли;
КонецЕсли;
КонецЦикла;
Исключение
Соединение.Close();
УдалитьФайлы(ИмяВременногоФайла);
Сообщить("Не удалось получить структуру листов файла!");
Возврат Неопределено;
КонецПопытки;
Если ПустаяСтрока(ИмяЛиста) Тогда
Соединение.Close();
УдалитьФайлы(ИмяВременногоФайла);
Сообщить("Указанный лист с номером " + НомерЛиста + " не найден в файле.");
Возврат Неопределено;
КонецЕсли;
// Формируем запрос с указанием диапазона строк для считывания
// Например, если НомерПервойСтроки = 5, то диапазон начнется с ячейки A5
Диапазон = "A" + Формат(НомерПервойСтроки, "ЧГ=0") + ":IV";
ТекстЗапроса = "SELECT * FROM `" + ИмяЛиста + Диапазон + "`";
ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
Попытка
ADODBRecordset.Open(ТекстЗапроса, Соединение);
Исключение
Ошибка = ОписаниеОшибки();
Соединение.Close();
УдалитьФайлы(ИмяВременногоФайла);
Сообщить("Ошибка выполнения SQL-запроса к Excel: " + Ошибка);
Возврат Неопределено;
КонецПопытки;
// Заполняем таблицу значений данными из набора Recordset
Пока НЕ ADODBRecordset.EOF Цикл
СтрокаТаблицыЗначений = ТЗПрочитанныеДанные.Добавить();
Для Каждого КлючИЗначение Из СтруктураКолонок Цикл
// Внимание: Индекс колонки в структуре (например, 1) уменьшаем на 1,
// так как в коллекции Fields индексация начинается с 0.
НомерКолонкиВExcel = КлючИЗначение.Значение - 1;
Если НомерКолонкиВExcel < ADODBRecordset.Fields.Count Тогда
ЗначениеЯчейки = ADODBRecordset.Fields.Item(НомерКолонкиВExcel).Value;
СтрокаТаблицыЗначений[КлючИЗначение.Ключ] = ЗначениеЯчейки;
КонецЕсли;
КонецЦикла;
ADODBRecordset.MoveNext(); // Переходим к следующей строке
КонецЦикла;
// Закрываем соединения и подчищаем временные файлы
ADODBRecordset.Close();
Соединение.Close();
УдалитьФайлы(ИмяВременногоФайла);
Возврат ТЗПрочитанныеДанные;
КонецФункции
Важно помнить, что использование технологии ADO жестко привязывает ваше решение к операционной системе Windows. Если сервер вашего приложения 1С работает под управлением Linux, попытка запустить COM-объекты ADODB.Connection или ADOX.Catalog завершится аварийной ошибкой.
Начиная с версии 1С:Предприятия 8.3 платформа обзавелась нативными возможностями чтения файлов формата `.xls` и `.xlsx` без необходимости использовать сторонние библиотеки или COM-объекты. Метод Прочитать() объекта ТабличныйДокумент работает на любой операционной системе (включая Linux и macOS), делает это невероятно быстро и стабильно.
Разберем по шагам изящный и лаконичный пример, как прочитать данные из Excel-файла с помощью платформенных средств всего за несколько строк кода:
// 1. Создаем объект табличного документа и загружаем в него файл
ТабличныйДок = Новый ТабличныйДокумент;
ТабличныйДок.Прочитать(ИмяФайла, СпособЧтенияТабличногоДокумента.Excel);
// 2. Используем ПостроительОтчета для моментальной выгрузки данных в ТаблицуЗначений
Построитель = Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДок.Область(1, 1, ТабличныйДок.ВысотаТаблицы, ТабличныйДок.ШиринаТаблицы));
Построитель.Выполнить();
// 3. Получаем заполненную таблицу значений
ТаблицаДанных = Построитель.Результат.Выгрузить();
Этот способ примечателен тем, что не только избавляет код от «костылей» платформенной зависимости, но и автоматически парсит структуру таблицы, преобразовывая типы данных ячеек. Вы можете легко управлять диапазонами считывания, передавая нужные координаты в метод Область() (например, указав начальную строку вместо «1»). Рекомендуется отдавать предпочтение именно этому подходу при работе на современных версиях платформы 1С:Предприятие 8.3.