Выгрузка из 1С Excel с листами. Помощь в интеграции

Предпосылки: импортозамещение и реальность серверной инфраструктуры

Работая над собственным интеграционным продуктом для выгрузки данных во внешние системы, мы столкнулись с типичной для сегодняшнего дня проблемой. Сервер 1С может быть развёрнут в совершенно разных окружениях: Windows, Linux, Docker-контейнер, облачная инфраструктура. Объединяет их одно — там может не быть установленного Microsoft Excel. А задача стояла конкретная: автоматически выгружать данные во внешнюю систему в строго определённом формате — файл Excel с несколькими именованными листами.

Стандартные подходы один за другим отпадали:

• COM-объекты — требуют установленного Excel на сервере, что в Linux-окружении невозможно в принципе;
• Native-компоненты — несут риски неконтролируемого потребления памяти, плохо предсказуемы в долгосрочной эксплуатации;
• Работа на стороне клиента (через штатные функции БСП) — неприемлема при автоматической фоновой выгрузке, где пользователь в процессе не участвует.

При этом на момент анализа в платформе 1С мы не заметили возможность формирования Excel-файла с несколькими листами без внешних зависимостей. Табличный документ умеет сохраняться в XLSX, но только в виде одного листа. В конце статьи так же приложим вариант вывода по листам через объект "ПакетОтображаемыхДокументов".

Пришлось реализовывать механизм самостоятельно — на чистом 1С, без каких-либо внешних зависимостей.

Что такое XLSX изнутри

Прежде чем решать задачу, нужно понять структуру формата. Файл .xlsx — это обычный ZIP-архив. Если переименовать его в .zip и распаковать, внутри окажется набор XML-файлов и каталогов.

В нашей реализации мы работаем со следующими ключевыми компонентами архива:

• xl/workbook.xml — описание книги: список листов с их именами и идентификаторами;
• xl/_rels/workbook.xml.rels — связи книги: соответствие между идентификаторами листов и физическими файлами;
• xl/worksheets/sheet1.xml, sheet2.xml, ... — сами листы с данными ячеек;
• xl/sharedStrings.xml — общий пул текстовых строк: ячейки не хранят текст напрямую, а ссылаются на индекс в этом файле;
• xl/styles.xml — стили ячеек: шрифты, заливки, границы, форматы чисел.

Именно это понимание структуры стало отправной точкой для реализации.

Проблемы при объединении файлов

На первый взгляд кажется, что задача несложная: берём несколько XLSX-файлов, распаковываем, копируем листы в один архив и прописываем их в workbook.xml. Но на практике этого категорически недостаточно.

Проблема 1: мало просто добавить лист в workbook

Чтобы Excel увидел новый лист, необходимо синхронно внести изменения в два файла:

• в workbook.xml добавить запись <sheet name="..." sheetId="..." r:id="rId..."/>;
• в workbook.xml.rels добавить соответствующую связь <Relationship Id="rId..." Target="worksheets/sheetN.xml"/>.

При этом идентификаторы sheetId и rId должны быть уникальными в рамках всей книги. Если взять значения «как есть» из исходных файлов, неизбежно возникнут коллизии, и Excel либо откажется открывать файл, либо откроет его с ошибками.

Схематично добавление листа выглядит так:

НСr = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
// В workbook.xml
НовыйSheetУзел.УстановитьАтрибут("sheetId", Строка(МаксSheetId));
НовыйSheetУзел.УстановитьАтрибут(НСr, "r:id", "rId" + МаксRIdNum);

// В workbook.xml.rels
НовыйRel.УстановитьАтрибут("Id",     "rId" + МаксRIdNum);
НовыйRel.УстановитьАтрибут("Target", "worksheets/" + ИмяФайлаЛиста);

Проблема 2: ссылки внутри самих листов становятся невалидными

Это более серьёзная проблема. Ячейки листа не хранят строковые значения напрямую — они хранят индекс в файле sharedStrings.xml. Аналогично со стилями: каждая ячейка содержит атрибут s — индекс в styles.xml.

Когда мы объединяем несколько файлов в один, каждый из них имеет собственный sharedStrings.xml и styles.xml с собственной индексацией. При наивном слиянии индексы из разных источников начнут указывать на чужие строки и стили — данные «перемешаются».

Решение — построить общий объединённый пул строк и стилей, и переиндексировать ссылки в каждом листе:

// Для каждой ячейки листа переназначаем индексы
Если УзелC.ПолучитьАтрибут("t") = "s" Тогда  // строковая ячейка
    СтарыйИндекс = УзелV.ТекстовоеСодержимое;
    НовыйИндекс  = КартаИндексов[СтарыйИндекс]; // маппинг старый -> новый
    УзелV.ТекстовоеСодержимое = НовыйИндекс;
КонецЕсли;

// Аналогично для стилей
СтарыйСтиль = УзелC.ПолучитьАтрибут("s");
НовыйСтиль  = КартаСтилей[СтарыйСтиль];
УзелC.УстановитьАтрибут("s", НовыйСтиль);

При этом при объединении sharedStrings мы дедуплицируем строки — одинаковые значения из разных источников получают один общий индекс, что уменьшает размер итогового файла. Аналогичная дедупликация происходит для стилей: шрифтов (fonts), заливок (fills), границ (borders) и форматов чисел (numFmts).

Что получилось в итоге

Реализованное решение полностью работает на стороне сервера 1С без каких-либо внешних зависимостей: не нужен Excel, не нужны COM-объекты, нет нативных компонент — только встроенные средства платформы для работы с ZIP-архивами и XML.

Интерфейс намеренно сделан гибким по входным данным. Настройки передаются через таблицу значений, где каждая строка — это будущий лист итогового файла:

Настройки = tss_excel_ВспомогательныеФункцииExcel.ПолучитьНастройкиДляОбъединения();

// Вариант 1: путь к файлу на сервере
tss_excel_ВспомогательныеФункцииExcel.ДобавитьЛистДляОбъединения(Настройки, "Продажи",  "C:\reports\sales.xlsx");

// Вариант 2: объект ТабличныйДокумент
tss_excel_ВспомогательныеФункцииExcel.ДобавитьЛистДляОбъединения(Настройки, "Остатки",  ТабДокОстатки);

// Вариант 3: ДвоичныеДанные
tss_excel_ВспомогательныеФункцииExcel.ДобавитьЛистДляОбъединения(Настройки, "Закупки",  ДвоичныеДанные);

РезультатДвоичные = tss_excel_ВспомогательныеФункцииExcel.ОбъединитьExcelФайлыВОдин(Настройки);

На выходе — ДвоичныеДанные готового XLSX-файла с именованными листами в том порядке, в котором они были добавлены в настройки. Этот результат можно сразу записать в файл, передать в HTTP-ответе или сохранить в хранилище.

PS: По результатам замеров для 6 листов время работы составляет от 0,3 до 0,7 секунды (это на сервере с HDD и небольшим объемом памяти). Основные затраты времени приходятся на операции чтения и записи файлов — поэтому на SSD алгоритм работает заметно быстрее.

PSS: Коротко о поставке. В расширении находится универсальный общий модуль для работы с Excel-файлами для объединения, а также демонстрационная обработка, иллюстрирующая порядок вызова функций модуля.

PSS: Большое спасибо, что дали обратную связь в комментарии. 
При первичном осмотре методов платформы 1С мы не сразу нашли отдельный объект, который частично решал нашу проблему. В итоге, можно немного сократить процесс вывода табличных документов в excel с помощью следующего кода:
 

Книга = Новый ПакетОтображаемыхДокументов;

ТабДок1 = Новый ТабличныйДокумент;
//Заполнение

ТабДок2 = Новый ТабличныйДокумент;
//Заполнение

Книга.Состав.Добавить(ПоместитьВоВременноеХранилище(ТабДок1));
Книга.Состав.Добавить(ПоместитьВоВременноеХранилище(ТабДок2));

Книга.Записать("ИмяФайла",ТипФайлаПакетаОтображаемыхДокументов.XLSX);