При выгрузке отчетов из 1С в Microsoft Excel часто возникает задача не просто передать статические данные, но и сохранить интерактивность документа. Например, добавить формулы расчета сумм и итогов, чтобы пользователи могли моделировать показатели и изменять исходные цены. Когда отчет содержит многоуровневую структуру (группировки), формулы итоговых строк должны точно охватывать диапазон подчиненных детальных записей.
Разберем, как программно определить границы и количество строк подчиненных группировок через COM-соединение с Excel, а также рассмотрим важные нюансы синтаксиса формул и альтернативные подходы платформы 1С.
Когда табличный документ 1С сохраняется в формате XLSX с иерархией, Excel сохраняет структуру уровней группировок строк. В объектной модели Excel за уровень вложенности строки отвечает свойство OutlineLevel объекта Row.
Рассмотрим базовую схему обхода, которую автор темы использовал для поиска максимального уровня:
КоличествоСтрок = Эксель.Cells(1, 1).SpecialCells(11).Row;
МаксимальныйУровеньГруппировки = 0;
Для Сч = 1 По КоличествоСтрок Цикл
ТекущийУровеньГруппировки = Эксель.Rows(Сч).OutlineLevel;
Если ТекущийУровеньГруппировки > МаксимальныйУровеньГруппировки Тогда
МаксимальныйУровеньГруппировки = ТекущийУровеньГруппировки;
КонецЕсли;
КонецЦикла;
Для вычисления количества строк, относящихся к конкретной родительской группировке (уровень $N$), необходимо подсчитать количество следующих за ней подряд строк с уровнем вложенности строго больше $N$.
Проанализируем алгоритм прямого обхода. При обнаружении строки группировки верхнего уровня запускаем вложенный цикл для подсчета количества дочерних строк до следующей группировки того же или более высокого уровня.
Процедура ЗаполнитьФормулыВExcel(ПутьКФайлу)
Попытка
Эксель = Новый COMОбъект("Excel.Application");
Исключение
Возврат;
КонецПопытки;
Эксель.DisplayAlerts = Ложь;
Эксель.Visible = Ложь;
Попытка
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.ActiveSheet;
Исключение
Эксель.Quit();
Эксель = Неопределено;
Возврат;
КонецПопытки;
КоличествоСтрок = Лист.Cells(1, 1).SpecialCells(11).Row;
// Определяем максимальный уровень вложенности
МаксУровень = 0;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
Уровень = Лист.Rows(НомерСтроки).OutlineLevel;
Если Уровень > МаксУровень Тогда
МаксУровень = Уровень;
КонецЕсли;
КонецЦикла;
// Обход строк и расстановка формул
НомерСтроки = 1;
Пока НомерСтроки <= КоличествоСтрок Цикл
ТекущийУровень = Лист.Rows(НомерСтроки).OutlineLevel;
// Обработка строки родительской группировки
Если ТекущийУровень = (МаксУровень - 1) Тогда
КоличествоДеталей = 0;
СледСтрока = НомерСтроки + 1;
// Считаем все подчиненные строки
Пока СледСтрока <= КоличествоСтрок И Лист.Rows(СледСтрока).OutlineLevel > ТекущийУровень Цикл
// Для строк детальных записей проставляем построчные формулы расчета
Лист.Cells(СледСтрока, 11).FormulaR1C1 = "=RC[-3]*RC[-1]";
КоличествоДеталей = КоличествоДеталей + 1;
СледСтрока = СледСтрока + 1;
КонецЦикла;
// Если дочерние строки найдены, проставляем итоговую формулу суммы
Если КоличествоДеталей > 0 Тогда
Лист.Cells(НомерСтроки, 11).FormulaR1C1 = "=SUM(R[1]C:R[" + Формат(КоличествоДеталей, "ЧГ=0") + "]C)";
КонецЕсли;
// Пропускаем уже обработанные дочерние строки
НомерСтроки = СледСтрока;
Иначе
НомерСтроки = НомерСтроки + 1;
КонецЕсли;
КонецЦикла;
Книга.Save();
Книга.Close();
Эксель.Quit();
Эксель = Неопределено;
КонецПроцедуры
Рассмотрим альтернативный вариант: проход по документу от последней строки к первой. Этот способ удобен тем, что не требует вложенных циклов.
СчетчикДеталей = 0.СчетчикДеталей в формулу SUM, после чего сбрасываем счетчик в 0.
СчетчикДеталей = 0;
Для НомерСтроки = КоличествоСтрок По 1 Цикл
ТекущийУровень = Лист.Rows(НомерСтроки).OutlineLevel;
Если ТекущийУровень = МаксУровень Тогда
Лист.Cells(НомерСтроки, 11).FormulaR1C1 = "=RC[-3]*RC[-1]";
СчетчикДеталей = СчетчикДеталей + 1;
ИначеЕсли ТекущийУровень = (МаксУровень - 1) И СчетчикДеталей > 0 Тогда
Лист.Cells(НомерСтроки, 11).FormulaR1C1 = "=SUM(R[1]C:R[" + Формат(СчетчикДеталей, "ЧГ=0") + "]C)";
СчетчикДеталей = 0;
Иначе
СчетчикДеталей = 0;
КонецЕсли;
КонецЦикла;
Разберем типичные ошибки, которые могут приводить к зависанию процессов или некорректным формулам:
FormulaR1C1 принимает названия функций исключительно на английском языке (например, =SUM(...)). Если передать русскую функцию =СУММ(...), Excel запишет текст с ошибкой #ИМЯ?. Для использования русских названий функций необходимо обращаться к свойству FormulaR1C1Local.Эксель.Quit() обязательно нужно присвоить переменной COM-объекта значение Неопределено (Эксель = Неопределено;). В противном случае фоновый процесс EXCEL.EXE останется в диспетчере задач до полного перезапуска сеанса 1С.Формула. При выгрузке отчета методом ТабличныйДокумент.Записать(Путь, ТипФайлаТабличногоДокумента.XLSX) формулы автоматически транслируются в итоговый файл Excel без привлечения сторонних COM-библиотек.