При выгрузке отчетов и печатных форм из 1С в формат Excel разработчики часто сталкиваются с проблемой: формулы, записанные в ячейки табличного документа, экспортируются как обычные текстовые строки. Пользователь видит в ячейке текст вида =A1+B1 вместо расчетного значения, а сам расчет не происходит автоматически до тех пор, пока по ячейке не кликнут дважды. Особенно остро этот вопрос встает, когда выгрузка происходит на сервере под управлением Linux или на компьютере, где не установлен пакет Microsoft Office (что исключает использование классического COM-соединения).
В этой статье мы подробно разберем, почему так происходит, и рассмотрим три практических способа решения этой задачи: от полностью автономной модификации файла средствами 1С до простых пользовательских лайфхаков.
---
Давайте проанализируем, как платформа «1С:Предприятие» сохраняет файлы. Когда мы вызываем стандартный метод ТабличныйДокумент.Записать(Путь, ТипФайлаТабличногоДокумента.XLSX), платформа формирует стандартный ZIP-архив с расширением .xlsx, содержащий XML-структуру книги Excel.
Если в ячейке табличного документа был записан текст, начинающийся со знака равенства (например, =СУММ(A1:A10)), 1С сохраняет его в Excel как обычную текстовую строку (тип данных s — Shared String). С точки зрения спецификации OpenXML, такая ячейка не является формулой, поэтому Excel при открытии файла отображает её «как есть», не запуская вычислительный движок.
---
Поскольку формат .xlsx — это обычный ZIP-архив, мы можем программно распаковать его средствами 1С, найти XML-файл нужного листа, заменить текстовое представление формул на настоящие XML-теги формул и упаковать файл обратно. Этот способ абсолютно автономен, работает на любой операционной системе (включая Linux) и не требует стороннего ПО.
При этом важно помнить ключевое правило: все имена функций в XML-структуре Excel должны быть написаны исключительно на английском языке. Вместо СУММ необходимо использовать SUM, вместо ЕСЛИ — IF, вместо ВПР — VLOOKUP и так далее.
Давайте разберем по шагам и рассмотрим пример кода, который реализует данный алгоритм:
// Функция преобразует сохраненный файл XLSX, превращая текстовые формулы в вычисляемые
Процедура ПреобразоватьТекстовыеФормулыВExcel(ПутьКФайлуXLSX) Экспорт
// Шаг 1. Создаем уникальный временный каталог для распаковки архива
ИмяВременнойПапки = ПолучитьИмяВременногоФайла("");
СоздатьКаталог(ИмяВременнойПапки);
// Шаг 2. Распаковываем XLSX как ZIP-архив
Архив = Новый ЧтениеZipФайла;
Архив.Открыть(ПутьКФайлуXLSX);
Архив.ИзвлечьВсе(ИмяВременнойПапки, РежимВосстановленияПутейФайловZIP.Восстанавливать);
Архив.Закрыть();
// Шаг 3. Определяем путь к XML-файлу первого листа книги
ПутьКЛисту = ИмяВременнойПапки + "/xl/worksheets/sheet1.xml";
ФайлЛиста = Новый Файл(ПутьКЛисту);
Если Не ФайлЛиста.Существует() Тогда
УдалитьФайлы(ИмяВременнойПапки);
ВызватьИсключение "Не удалось найти структуру первого листа в файле Excel!";
КонецЕсли;
// Шаг 4. Читаем XML через объектную модель DOM для безопасного изменения структуры
ЧтениеXML = Новый ЧтениеXML;
ЧтениеXML.ОткрытьФайл(ПутьКЛисту);
ПостроительDOM = Новый ПостроительDOM;
ДокументDOM = ПостроительDOM.Прочитать(ЧтениеXML);
ЧтениеXML.Закрыть();
// Находим все элементы ячеек <c> (cell)
Ячейки = ДокументDOM.ПолучитьЭлементыПоИмени("c");
Для Каждого Ячейка Из Ячейки Цикл
// Предположим, мы заранее пометили ячейки с формулами особым образом
// или ищем их по конкретным адресам. Для примера найдем ячейку "C5"
АдресЯчейки = Ячейка.ПолучитьАтрибут("r");
Если АдресЯчейки = "C5" Тогда
// Удаляем атрибут t="s" (указывающий, что значение текстовое из таблицы общих строк)
Ячейка.УдалитьАтрибут("t");
// Полностью очищаем старое содержимое ячейки (значения <v>)
Пока Ячейка.ПервыйДочерний <> Неопределено Цикл
Ячейка.УдалитьДочерний(Ячейка.ПервыйДочерний);
КонецЦикла;
// Создаем новый узел формулы <f>
УзелФормулы = ДокументDOM.СоздатьЭлемент("f");
ТекстФормулы = ДокументDOM.СоздатьТекст("SUM(C1:C4)"); // Формула на английском
УзелФормулы.ДобавитьДочерний(ТекстФормулы);
Ячейка.ДобавитьДочерний(УзелФормулы);
// Добавляем пустой или дефолтный узел значения <v>, чтобы Excel корректно его инициализировал
УзелЗначения = ДокументDOM.СоздатьЭлемент("v");
ТекстЗначения = ДокументDOM.СоздатьТекст("0");
УзелЗначения.ДобавитьДочерний(ТекстЗначения);
Ячейка.ДобавитьДочерний(УзелЗначения);
КонецЕсли;
КонецЦикла;
// Шаг 5. Записываем измененный XML-документ обратно
ЗаписьXML = Новый ЗаписьXML;
ЗаписьXML.ОткрытьФайл(ПутьКЛисту, "UTF-8");
ЗаписьDOM = Новый ЗаписьDOM;
ЗаписьDOM.Записать(ДокументDOM, ЗаписьXML);
ЗаписьXML.Закрыть();
// Шаг 6. Архивируем измененные файлы обратно в исходный файл XLSX
ЗаписьАрхива = Новый ЗаписьZipФайла;
ЗаписьАрхива.Открыть(ПутьКФайлуXLSX, РежимСжатияZIP.Максимальный);
ЗаписьАрхива.Добавить(ИмяВременнойПапки + "/*", РежимСохраненияПутейZIP.СохранятьОтносительныеПути, ИспользоватьПодкаталогиZIP.УказыватьПодкаталоги);
ЗаписьАрхива.Записать();
// Очищаем временные файлы
УдалитьФайлы(ИмяВременнойПапки);
КонецПроцедуры
Этот способ требует аккуратности при работе с XML, но гарантирует стопроцентный результат без установки какого-либо стороннего софта на сервер 1С.
---
Если структура вашего отчета очень сложная, содержит много листов и динамических формул, ручной разбор XML средствами 1С может оказаться слишком трудоемким. В этом случае мы можем применить внешнюю утилиту на Python с популярной библиотекой openpyxl, которая умеет нативно работать с файлами Excel.
Для реализации этого подхода выполним следующие шаги:
pip install openpyxl.fix_formulas.py, который будет автоматически находить текстовые формулы и перезаписывать их как вычисляемые.Посмотрим на пример кода Python-скрипта:
import openpyxl
import sys
def process_excel(file_path):
# Открываем книгу Excel
wb = openpyxl.load_workbook(file_path)
for sheet in wb.worksheets:
for row in sheet.iter_rows():
for cell in row:
# Если ячейка содержит строку, начинающуюся с "=", переводим её в режим формулы
if isinstance(cell.value, str) and cell.value.startswith('='):
formula_text = cell.value
cell.value = formula_text # openpyxl запишет это именно как формулу
# Сохраняем изменения в исходный файл
wb.save(file_path)
if __name__ == '__main__':
if len(sys.argv) > 1:
process_excel(sys.argv[1])
Теперь из кода 1С мы можем сохранить наш табличный документ стандартным методом, а затем вызвать этот скрипт в фоновом режиме:
// Код 1С для вызова Python-скрипта обработки Excel
ПутьКФайлуXLSX = "C:\Reports\MyReport.xlsx";
ТабДок.Записать(ПутьКФайлуXLSX, ТипФайлаТабличногоДокумента.XLSX);
ПутьКСкрипту = "C:\Scripts\fix_formulas.py";
ПараметрыЗапуска = """" + ПутьКСкрипту + """ """ + ПутьКФайлуXLSX + """";
// Запускаем консольное приложение без открытия черного окна
ЗапуститьПриложение("python " + ПараметрыЗапуска, , Истина);
---
Если доработка конфигурации по каким-то причинам невозможна в текущий момент, а пользователю нужно срочно посчитать формулы в сохраненном из 1С файле, можно воспользоваться простым трюком в самом интерфейсе Microsoft Excel.
Давайте разберем эту последовательность действий по шагам:
=.=.Почему это работает? При выполнении данной операции Excel принудительно осуществляет парсинг содержимого каждой ячейки заново. Обнаружив знак равенства в начале значения, внутренний парсер Excel понимает, что перед ним формула, мгновенно пересчитывает таблицу и выводит корректные результаты вычислений.