Отчёт на первом листе, сводная на втором. Excel на сервере не нужен

Просьба звучит одинаково у всех: "пришлите выгрузку, и чтобы на втором листе была сводная". Живая, чтобы получатель развернул её по своим складам.

Ответить на это из 1С нечем: у ТабличныйДокумент лист один, его он в xlsx и запишет, а сводную не умеет тем более.

Третий путь короче обоих привычных:

Настройки = ФорматированиеXLSX.НовыеНастройки();
ФорматированиеXLSX.ДобавитьСводнуюТаблицу(Настройки, "A2:E10", "Склад", "Сумма");
ФорматированиеXLSX.Сохранить(ТабДок, "D:\отчёт.xlsx", Настройки);

Файл собрал сервер 1С, Excel не участвовал. Второй лист появился сам, и на нём настоящая сводная:

Названия строк   Сумма по полю Сумма
Центральный      100 100,75
Южный             63 171,15
Северный         103 610
Общий итог       266 881,90

Почему привычный ответ здесь не работает

Excel.Application требует установленного офиса, живёт только под Windows и в фоновом задании недоступен: код, отработавший на машине разработчика, в регламентном падает. А переехал сервер на Linux - не появится там никогда.

Готовой библиотеки я не нашёл: всё, что попадалось на встроенном языке, умеет только чтение xlsx. Искал плохо - поправьте в комментариях.

xlsx - это ZIP с XML внутри, и оба формата платформа читает сама

Самый чужой раздел в статье. Нужен результат - листайте дальше. Читать стоит, если придётся разбираться, почему файл однажды не откроется.

Проверяется за минуту: запишите табличный документ в xlsx и распакуйте штатным ЧтениеZipФайла:

Каталог = ПолучитьВременныйКаталог() + "/" + Строка(Новый УникальныйИдентификатор);
СоздатьКаталог(Каталог);

Чтение = Новый ЧтениеZipФайла("D:\проба.xlsx");
Чтение.ИзвлечьВсе(Каталог, РежимВосстановленияПутейФайловZIP.Восстанавливать);
Чтение.Закрыть();

Для Каждого Файл Из НайтиФайлы(Каталог, "*.xml", Истина) Цикл
	Сообщить(Файл.ПолноеИмя);
КонецЦикла;

Внутри обычные файлы, в мире формата их зовут частями. У документа с шестью ячейками их четырнадцать, разбираться нужно с четырьмя:

xl/worksheets/sheet1.xml   - сам лист: ячейки и всё его оформление
xl/styles.xml              - стили: цвета, числовые форматы
xl/workbook.xml            - книга: какие в ней вообще есть листы
[Content_Types].xml        - объявление типов всех частей архива

Всё это обычный XML, который платформа читает и пишет сама. Значит внешние компоненты не нужны в принципе, а "неумение" табличного документа сводится к элементам, которых он в лист не дописывает. Отсюда способ: распаковать готовый файл, дописать недостающее, запаковать обратно.

Второй лист: чего в архиве не хватает

Положить в архив ещё один sheet.xml мало. Чтобы лист появился в книге, нужны три записи в трёх разных файлах, по строке на каждый:

[Content_Types].xml
  <Override PartName="/xl/worksheets/sheetPivot1.xml"
    ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>

xl/workbook.xml, внутрь <sheets>
  <sheet name="Сводная" sheetId="2" r:id="rId7"/>

xl/_rels/workbook.xml.rels
  <Relationship Id="rId7" Target="worksheets/sheetPivot1.xml"
    Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"/>

Идентификатор связи (rId7) любой незанятый, важно лишь чтобы совпал в двух местах. Пропустили любую из трёх записей - Excel файл не откроет.

Сама сводная это ещё три части:

xl/pivotCache/pivotCacheDefinition1.xml   - откуда брать данные и какие там поля
xl/pivotTables/pivotTable1.xml            - что в строках, что в данных, какая операция
xl/pivotTables/_rels/pivotTable1.xml.rels - связь сводной с её источником

Занятная деталь: XML листа с данными не меняется, сводная привязана к нему через файл связей. Остальное - подсветка, списки, фильтр - дописывается прямо в лист.

Грабля первая: имя части архива только латиницей

Лист сводной у меня сначала звался "Свод", и часть архива получила имя по нему: xl/worksheets/sheetСвод1.xml. Внутри всё сходилось: часть на месте, тип объявлен, связь ведёт куда надо, распаковщик архив открывает. А Excel открывать отказался - даже не предложил восстановить.

Имя части внутри архива - это адрес, кириллица в нём должна быть перекодирована посимвольно, и офис ждёт именно такую форму. Имя самого листа кириллическое и никому не мешает, человек видит ярлычок "Сводная". Правило шире xlsx: часть, которую кладёте в архив руками, называйте латиницей.

Сводная приезжает без единой посчитанной цифры

В файле нет ни одного итога, только описание: источник, поле строк, поле данных, операция. Считает офис получателя в момент открытия.

<pivotCacheDefinition saveData="0" refreshOnLoad="1">
	<cacheSource type="worksheet">
		<worksheetSource ref="A2:E10" sheet="Лист_1"/>

saveData="0" означает "посчитанных значений внутри нет", refreshOnLoad="1" - "пересчитай источник, как только откроешь". Всё. Имена полей берутся из первой строки диапазона, текст в A2:E2, поэтому заголовки обязаны быть непустыми и разными.

Грабля вторая: сохранённый кэш не нужен и вреден

Расписывая по стандарту формата, что в какой файл класть, я записал: посчитанные значения возить не обязательно, но лучше бы возить. Замер сказал обратное. Четыре режима, один файл, один Excel:

Третья строка показывает, что рисует сводную именно refreshOnLoad. Вторая - что одних сохранённых значений мало, их пришлось бы ещё раскладывать по ячейкам руками. Четвёртая - что пустая колонка в источнике убивает файл целиком.

Четвёртая опаснее всех, потому что пустые колонки платформа делает сама. Пусть в отчёте объединена шапка, скажем A1:D1. Табличный документ пишет со значением первую ячейку объединения, а три остальные помечает особо: существуют, значения нет. Для сводной такая колонка и есть пустая.

Считать итоги самому, раскладывать по ячейкам, сотни строк работы - ради того, чего Excel добивается одним атрибутом, да ещё с риском убить файл первой объединённой шапкой. Кэш я возить не стал.

Диаграмма встаёт в чужой рисунок

У листа может быть ровно один блок рисунков, и платформа создаёт его всегда - пустым, но создаёт. Своим файлом диаграмму не положить, а подменить платформенный значит выкинуть картинки табличного документа: логотип, подпись, скан печати. Поэтому диаграмма дописывается в конец существующего блока, ничего не сдвигая:

ФорматированиеXLSX.ДобавитьДиаграмму(Настройки, "B3:B10", "D3:D10",
	"Суммы по номенклатуре", "гистограмма", "G2", "Сумма");

На файле со своей картинкой после вставки живы обе фигуры. Мелочь, стоившая мне прогона: в диапазоне значений не должно быть строки заголовка. Попав туда, он становится лишней пустой точкой ряда: на восьми строках данных вышло девять точек.

Порядок тегов, на котором ломается всё остальное

Раздел для тех, кто правит XML руками. Пользуетесь библиотекой - возвращайтесь сюда, когда Excel предложит восстановить файл.

Внутри <worksheet> порядок элементов задан схемой формата (она зовётся OOXML) жёстко. Вот он весь, видно и кто что пишет:

sheetData               пишет платформа
sheetProtection         вставляем мы
autoFilter              вставляем мы
mergeCells              пишет платформа
conditionalFormatting   вставляем мы
dataValidations         вставляем мы
hyperlinks              вставляем мы
pageMargins             пишет платформа
pageSetup               пишет платформа
headerFooter            пишет платформа
drawing                 пишет платформа

Свои шесть табличный документ выдаёт подряд, одной строкой:

</sheetData><mergeCells .../><pageMargins/><pageSetup/><headerFooter/><drawing/></worksheet>

Пять с пометкой "вставляем мы" предстоит дописать, каждую в своё место.

Первое, что приходит в голову, - вставлять перед </worksheet>. Теги встают после <drawing/>, и Excel объявляет файл повреждённым.

Второе - сразу после </sheetData>. На примере из трёх ячеек работает, на реальном отчёте ломается: платформа пишет туда <mergeCells>, а объединения стоят посередине списка. Значит защита листа и автофильтр обязаны встать до них, а подсветка, списки и гиперссылки после. Объединённая шапка есть почти в любой печатной форме, нарваться проще, чем не нарваться.

И самое неприятное: ЧтениеXML эту ошибку не ловит - синтаксис проверяет, порядок по схеме нет. Битый файл разбирается без замечаний, на глаз от здорового не отличается, и мой первый прототип "прошёл проверку" был невалиден.

Как отлаживать, когда Excel молчит

Excel не говорит, что сломано, он предлагает восстановить. Рабочий ход: смотреть на то, что уцелело. Уцелели значки и гистограммы в ячейках, пропали подсветка и числовой формат - ровно те две возможности, что зависят от styles.xml: первые держат цвет внутри своего правила, вторые ссылаются на стиль по номеру. Значит сломан он, а лист цел.

Две вещи, которые пригодятся и без всякого xlsx

СтрНайти с позицией за концом строки бросает исключение

Именно бросает, ноля не возвращает. Цикл разбора вида Позиция = Конец + 1 дойдёт до конца строки ровно тогда, когда последний элемент кончается последним символом, и упадёт на следующем витке. Для XML это норма. Лечится обёрткой:

Функция НайтиС(Текст, Подстрока, Позиция)

	Если Позиция > СтрДлина(Текст) Тогда
		Возврат 0;
	КонецЕсли;

	Возврат СтрНайти(Текст, Подстрока, НаправлениеПоиска.СНачала, Позиция);

КонецФункции

Поучительно тут другое: дефект сидел в коде с самого начала, а прикрывал его второй дефект, тоже мой. Разбор XML принимал за конец элемента первую попавшуюся закрывающую скобку и до конца строки не доходил. Починка второго сняла прикрытие с первого: xlsx на выходе стал правильным и совершенно пустым, и выглядело это поломкой от рефакторинга.

Грабля третья: имя общего модуля нельзя занять переменной

Демо-стенд библиотеки - внешняя обработка, и объект в ней связывался с переменной ФорматированиеXLSX: тем же именем, что у общего модуля, чтобы код примеров работал дословно в обеих упаковках. В базе, где расширение уже стоит, платформа на этой строке падает - Поле объекта недоступно для записи, имя занято модулем.

Случай самый вероятный: стенд открывают ровно там, где библиотеку собираются применять. Поймать можно только выполнением в такой базе, синтаксическая проверка и сборка этого не видят.

Библиотека

Всё описанное собрано и выложено отдельной публикацией: Сводная таблица в Excel из 1С без COM, 10 стартмани.

На первом листе отчёт, на втором сводная. Excel открывает для неё панель "Поля сводной таблицы" - значит, считает её настоящей сводной, а не нарисованной таблицей.

Excel в этой работе понадобился мне самому, для приёмки: единственный способ убедиться, что записанное читается обратно, а не выглядит правильным на глаз.

Вопрос к тем, кто дочитал

Сколько раз за последний год вас просили "то же самое, но со сводной на втором листе", и что вы отвечали? Почти уверен, что самый частый ответ - ручная сборка в Excel раз в месяц, но проверить это мне не на чем.

И для тех, кто уже лазил внутрь xlsx руками: на чём споткнулись вы? У меня первым делом сломался порядок тегов, и я до сих пор не знаю, это общий вход в тему или мне так повезло.