При автоматизации бизнес-процессов разработчики часто сталкиваются с необходимостью загрузки данных из файлов Microsoft Excel. Исторически для этой задачи на платформе Windows использовался механизм COM-соединения (через объект Excel.Application). Однако этот подход имеет ряд критических недостатков: он требует установленного MS Office на сервере или клиенте, работает медленно и абсолютно не подходит для операционных систем семейства Linux.
С развитием платформы «1С:Предприятие 8» появились встроенные кроссплатформенные механизмы, которые позволяют работать с файлами форматов .xls, .xlsx и .ods напрямую, без привлечения сторонних приложений. В этой статье мы подробно разберем, как программно прочитать Excel-файл по листам (страницам), обсудим нюансы управления точностью данных, а также рассмотрим обратную задачу — запись многостраничного документа.
Начиная с версии платформы 8.3.10, разработчикам доступен полностью штатный механизм чтения листов Excel с помощью знакомого объекта ТабличныйДокумент. Этот метод работает одинаково быстро как на стороне Windows, так и под управлением Linux (на сервере или в тонком клиенте).
При вызове метода Прочитать() для объекта ТабличныйДокумент платформа загружает всю структуру книги Excel в оперативную память. Важно понять, как именно распределяются данные по листам:
ТабличныйДокумент.Области в точности совпадают с названиями вкладок (листов) в исходном файле Excel.ТабличныйДокумент.Области.Количество().Таким образом, нам не нужно знать структуру документа заранее. Мы можем перебирать коллекцию областей в цикле, получать каждую страницу как самостоятельный табличный документ и обрабатывать ее данные.
Рассмотрим базовый пример программного кода, который открывает файл Excel, определяет список всех листов и последовательно считывает данные из каждого листа.
// Создаем экземпляр табличного документа
ТабДок = Новый ТабличныйДокумент;
Попытка
// Читаем файл с диска
ТабДок.Прочитать(ПутьКФайлу);
Исключение
ВызватьИсключение "Не удалось прочитать файл! Ошибка: " + ОписаниеОшибки();
КонецПопытки;
// Перебираем все области, которые соответствуют листам книги Excel
Для Каждого ОбластьЛиста Из ТабДок.Области Цикл
// Получаем имя текущей вкладки (листа)
ИмяЛиста = ОбластьЛиста.Имя;
Сообщить("Обработка листа: " + ИмяЛиста);
// Получаем данные конкретного листа в виде отдельного табличного документа
ТекущийЛист = ТабДок.ПолучитьОбласть(ИмяЛиста);
// Определяем размеры заполненной области на листе
ВсегоСтрок = ТекущийЛист.ВысотаТаблицы;
ВсегоКолонок = ТекущийЛист.ШиринаТаблицы;
// Проходим циклом по ячейкам текущего листа
Для НомерСтроки = 1 По ВсегоСтрок Цикл
Для НомерКолонки = 1 По ВсегоКолонок Цикл
// Получаем значение конкретной ячейки
ЗначениеЯчейки = ТекущийЛист.Область(НомерСтроки, НомерКолонки).Текст;
// Здесь может быть ваша логика разбора данных (например, запись в справочник)
КонецЦикла;
КонецЦикла;
КонецЦикла;
Этот простой пример наглядно показывает, как легко обойти все страницы книги. Однако последовательный перебор ячеек в циклах Для может работать медленно на файлах с десятками тысяч строк. Давайте выясним, как ускорить этот процесс.
Для того чтобы ускорить разбор данных и избежать ресурсоемкого обращения к каждой ячейке в цикле, мы можем использовать эффективный прием — выгрузку области табличного документа в объект ТаблицаЗначений через промежуточный объект ПостроительЗапроса.
Проанализируем пример кода, выполняющий быструю конвертацию полученного листа:
// Определяем заполненные границы листа
Высота = ТекущийЛист.ВысотаТаблицы;
Ширина = ТекущийЛист.ШиринаТаблицы;
Если Высота > 0 И Ширина > 0 Тогда
// Выделяем всю заполненную область листа
ОбластьДанных = ТекущийЛист.Область(1, 1, Высота, Ширина);
// Используем ПостроительЗапроса для трансформации данных
Построитель = Новый ПостроительЗапроса;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьДанных);
Построитель.ЗаполнитьНастройки();
Построитель.Выполнить();
// Выгружаем результат в готовую Таблицу Значений
ТаблицаДанных = Построитель.Результат.Выгрузить();
// Теперь у нас есть стандартная ТаблицаЗначений, с которой удобно работать в коде
// Колонки таблицы будут названы автоматически (Колонка1, Колонка2 и т.д.)
КонецЕсли;
Важный технический нюанс: Если в исходном Excel-файле содержатся объединенные ячейки, при использовании ПостроителяЗапроса значения из объединенной области могут автоматически продублироваться во все входящие в нее ячейки результирующей таблицы. Обязательно учитывайте эту особенность при написании алгоритмов валидации и загрузки данных.
При чтении данных из Excel часто возникает проблема несоответствия отображаемого текста и реального значения ячейки. Например, если в ячейке Excel записано число 125.456, но настроен формат отображения до целых, пользователь видит на экране строку 125.
Чтобы предоставить разработчику контроль над этой ситуацией, в метод Прочитать() объекта ТабличныйДокумент был добавлен дополнительный параметр. Мы можем явно указать системе, как именно считывать данные с помощью перечисления СпособЧтенияЗначенийТабличногоДокумента:
СпособЧтенияЗначенийТабличногоДокумента.Текст (используется по умолчанию): Платформа считает данные именно так, как они визуально представлены на экране (с учетом масок форматирования, округлений, денежных символов). В нашем примере мы получим текстовую строку "125".СпособЧтенияЗначенийТабличногоДокумента.Значение: Платформа считает «сырое» исходное значение ячейки, игнорируя ее визуальное оформление. В 1С загрузится точное число 125.456 или значение типа Дата (вместо отформатированной строки даты).Посмотрим на пример использования этого параметра на практике:
// Читаем файл с сохранением исходных типов данных и максимальной точности
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Иногда перед разработчиком стоит обратная задача — выгрузить данные из 1С в один Excel-файл, распределив информацию по разным листам (например, лист «Товары», лист «Услуги», лист «Итоги»).
Для решения этой задачи встроенными средствами платформы (начиная с версий 8.3.3 и старше) используется специализированный объект ПакетОтображаемыхДокументов. Он позволяет объединить несколько независимых табличных документов в один итоговый файл формата .xlsx.
Разберем детальный пример программной генерации многостраничного файла:
// 1. Создаем отдельные табличные документы для каждого листа
ТабДокТовары = Новый ТабличныйДокумент;
// ... заполняем ТабДокТовары данными ...
ТабДокУслуги = Новый ТабличныйДокумент;
// ... заполняем ТабДокУслуги данными ...
// 2. Создаем объект пакета документов
Пакет = Новый ПакетОтображаемыхДокументов;
// 3. Добавляем первый лист (Товары)
ЭлементПакет1 = Пакет.Состав.Добавить();
ЭлементПакет1.Наименование = "Товары"; // Это имя будет отображаться на вкладке в Excel
ЭлементПакет1.Данные = ПоместитьВоВременноеХранилище(ТабДокТовары);
// 4. Добавляем второй лист (Услуги)
ЭлементПакет2 = Пакет.Состав.Добавить();
ЭлементПакет2.Наименование = "Услуги"; // Имя второй вкладки
ЭлементПакет2.Данные = ПоместитьВоВременноеХранилище(ТабДокУслуги);
// 5. Записываем сформированный пакет в итоговый файл XLSX на диск
Пакет.Записать(ПутьСохраненияФайла, ТипФайлаПакетаОтображаемыхДокументов.XLSX);
Сообщить("Многостраничный файл Excel успешно сформирован!");
Использование этого метода гарантирует полную независимость вашего кода от операционной системы и наличия установленного офисного пакета на сервере. Это современный стандарт разработки в экосистеме «1С:Предприятие 8».