В современной разработке на платформе 1С:Предприятие 8.3 использование COMОбъект("Excel.Application") постепенно уходит в прошлое. Это связано с тем, что данный метод требует обязательной установки Microsoft Excel на сервере или клиентском компьютере, работает только в среде Windows и крайне медленно обрабатывает большие объемы данных. Рассмотрим альтернативные способы, которые позволяют считать данные из файлов .xls и .xlsx напрямую в таблицу значений, сохраняя при этом высокую производительность и кроссплатформенность.
Самый простой и эффективный встроенный метод — использование объекта ТабличныйДокумент. Платформа 1С умеет самостоятельно парсить файлы Excel и преобразовывать их в свою внутреннюю структуру таблиц. Проанализируем, как это реализовать программно.
Для начала разберем по шагам алгоритм загрузки. Нам необходимо прочитать файл, определить границы данных, создать колонки в таблице значений на основе заголовков и перенести данные из ячеек документа в строки таблицы. Схожие алгоритмы парсинга применяются на практике, когда требуется выполнить загрузку отчётов о продажах маркетплейсов в БП 3 из Excel без ручного ввода данных.
Рассмотрим пример функции, которая автоматизирует этот процесс:
Функция ЗагрузитьТаблицуЗначенийИзФайла(ИмяФайла, Параметры = Неопределено) Экспорт
// Создаем объект табличного документа
ТД = Новый ТабличныйДокумент();
// Пытаемся прочитать файл. СпособЧтенияЗначенийТабличногоДокумента.Текст
// гарантирует, что мы получим строковое представление данных без искажений типа.
ТД.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Текст);
ПараметрСтрокаШапки = 1;
Если Параметры <> Неопределено И Параметры.Свойство("СтрокаШапки") Тогда
ПараметрСтрокаШапки = Параметры.СтрокаШапки;
КонецЕсли;
ТЗ = Новый ТаблицаЗначений();
МассивКолонок = Новый Массив();
// Формируем колонки ТЗ на основании первой строки (шапки)
Для ИндКол = 1 ПО ТД.ШиринаТаблицы Цикл
ТекстЗаголовка = СокрЛП(ТД.Область(ПараметрСтрокаШапки, ИндКол).Текст);
Если ПустаяСтрока(ТекстЗаголовка) Тогда
Продолжить; // Пропускаем пустые колонки
КонецЕсли;
// Преобразуем текст заголовка в корректное имя колонки (без пробелов и спецсимволов)
ИмяКолонки = ОчиститьИмяКолонки(ТекстЗаголовка);
ТЗ.Колонки.Добавить(ИмяКолонки);
МассивКолонок.Добавить(ИндКол);
КонецЦикла;
// Читаем данные, начиная со следующей строки после шапки
Для ИндСтр = ПараметрСтрокаШапки + 1 По ТД.ВысотаТаблицы Цикл
// Проверяем, есть ли в строке хоть какие-то данные
ЕстьДанные = Ложь;
НоваяСтрока = ТЗ.Добавить();
Для СчКолонки = 0 По МассивКолонок.Количество() - 1 Цикл
ИндКол = МассивКолонок[СчКолонки];
ЗначениеЯчейки = ТД.Область(ИндСтр, ИндКол).Текст;
Если ЗначениеЯчейки <> "" Тогда
ЕстьДанные = Истина;
КонецЕсли;
НоваяСтрока[СчКолонки] = ЗначениеЯчейки;
КонецЦикла;
// Если строка пустая, удаляем её из таблицы
Если НЕ ЕстьДанные Тогда
ТЗ.Удалить(НоваяСтрока);
КонецЕсли;
КонецЦикла;
Возврат ТЗ;
КонецФункции
Обратите внимание на вспомогательную функцию ОчиститьИмяКолонки. Она критически важна, так как имена колонок в ТаблицаЗначений должны соответствовать правилам именования переменных (не начинаться с цифр, не содержать пробелов).
Функция ОчиститьИмяКолонки(ВходнаяСтрока)
ДопустимыеСимволы = "АБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_";
Результат = "";
Для Поз = 1 По СтрДлина(ВходнаяСтрока) Цикл
Символ = Сред(ВходнаяСтрока, Поз, 1);
Если Найти(ДопустимыеСимволы, ВРег(Символ)) > 0 Тогда
Результат = Результат + Символ;
Иначе
Результат = Результат + "_";
КонецЕсли;
КонецЦикла;
// Если имя начинается с цифры, добавим префикс
ПервыйСимвол = Лев(Результат, 1);
Если Найти("0123456789", ПервыйСимвол) > 0 Тогда
Результат = "Кол_" + Результат;
КонецЕсли;
Возврат Результат;
КонецФункции
Если нам требуется максимальная скорость на сервере (особенно под управлением Linux) или мы работаем с файлами, размер которых превышает возможности ТабличныйДокумент (сотни тысяч строк), выясним причину медленной работы и предложим альтернативу. Современный формат .xlsx — это обычный ZIP-архив, содержащий набор XML-файлов.
Разберем структуру документа:
xl/sharedStrings.xml — здесь хранятся все уникальные строки документа. В самих листах стоят только индексы на эти строки.xl/worksheets/sheet1.xml — содержит структуру ячеек первого листа и их значения (или индексы строк).Алгоритм работы следующий: рассмотрим, как извлечь данные без участия Excel:
1. Используем объект ЧтениеZipФайла для распаковки документа во временный каталог.
2. Читаем sharedStrings.xml с помощью ЧтениеXML и загружаем все строки в массив или таблицу для быстрого поиска по индексу.
3. Читаем XML-файл нужного листа, последовательно обходя узлы <v> (значение) внутри узлов <c> (ячейка).
Этот метод в 5-10 раз быстрее любого другого, так как 1С не тратит ресурсы на визуализацию таблицы, а работает только с «сырыми» данными.
Проанализируем ситуацию, когда на компьютере нет Excel, но установлены системные компоненты доступа к данным (например, Microsoft Access Database Engine). В этом случае мы можем обращаться к файлу Excel как к базе данных через SQL-запросы.
Рассмотрим пример формирования строки подключения:
СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ПутьКФайлу + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";";
Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);
ТекстЗапроса = "SELECT * FROM [Лист1$]";
Recordset = Connection.Execute(ТекстЗапроса);
// Далее обходим Recordset и заполняем ТаблицуЗначений
Важный нюанс: Этот метод не будет работать на Linux-серверах и требует наличия драйвера ACE в операционной системе.
При выборе метода чтения данных важно учитывать следующие моменты. В частности, если файлы поступают регулярно, имеет смысл настроить автоматический сбор прайс-листов и файлов с электронной почты или FTP-серверов.
ТабличныйДокумент.Прочитать() отлично справляется и с .xls, и с .xlsx. Если вы работаете с шаблонами .xlt, их достаточно предварительно переименовать в .xls прямо в коде.ТабличныйДокумент рекомендуется всегда использовать СпособЧтенияЗначенийТабличногоДокумента.Текст, чтобы избежать ошибок автоматического преобразования типов, когда, например, артикул с ведущими нулями превращается в число.ТабличныйДокумент прочитает их все, «склеив» в одну длинную таблицу по вертикали. Если нужно читать листы по отдельности, лучше использовать метод парсинга ZIP/XML или внешние компоненты.ТабличныйДокумент может вызвать переполнение оперативной памяти на сервере. В таких случаях используйте построчное чтение через XML.Таким образом, мы выяснили, что отсутствие установленного Microsoft Office не является препятствием для работы с данными. Для большинства повседневных задач программисту 1С достаточно использовать встроенный ТабличныйДокумент, а для высоконагруженных систем — прямой парсинг XML-структуры файла. Для загрузки файлов без самостоятельной разработки можно использовать универсальную обработку импорта данных из Excel.