При разработке отчетов в 1С часто возникает задача выгрузки данных в формат Excel с сохранением живых формул. Однако стандартный метод Записать() объекта ТабличныйДокумент имеет одну неприятную особенность: даже если вы записываете в ячейку строку вида "=R1C1+R2C1", после открытия файла в Excel эта формула часто отображается как обычный текст. Чтобы она «заработала», пользователю приходится дважды щелкать по ячейке или нажимать Enter. В этой статье мы разберем, почему это происходит и как автоматизировать процесс «оживления» формул программными средствами 1С.
Проблема заключается в том, что при экспорте табличного документа 1С записывает содержимое ячейки в XML-структуру файла Excel как строковое значение. Excel, открывая такой файл, видит строку, начинающуюся со знака равенства, но не интерпретирует её как вычисляемую формулу до тех пор, пока не произойдет событие изменения ячейки или принудительный пересчет. Также важную роль играет формат ячейки: если 1С установила для ячейки текстовый формат, формула не будет вычисляться в принципе.
Рассмотрим первый вариант решения, предложенный в обсуждении. Суть метода заключается в том, чтобы после сохранения файла открыть его через COM-соединение с Excel, пройтись по ячейкам и принудительно перезаписать значение формулы само в себя. Это заставляет Excel перепарсить строку и распознать в ней формулу.
Проанализируем пример кода, который реализует этот подход:
// Вложение - полное имя сохраненного файла эксель
Excel = Новый COMОбъект("Excel.Application");
Книга = Excel.WorkBooks.Open(Вложение);
Excel.Visible = 0;
Excel.ActiveWindow.DisplayWorkbookTabs = 1;
Excel.ActiveWindow.TabRatio = 0.6;
Лист = Книга.WorkSheets(1);
// Определяем границы заполненной области
ВсегоСтрок = Лист.Cells.SpecialCells(11).Row;
ВсегоКолонок = Лист.Cells.SpecialCells(11).Column;
Для а = 1 По ВсегоСтрок Цикл
Для б = 1 по ВсегоКолонок Цикл
// Проверяем, начинается ли значение ячейки со знака равенства
Если Лев(Лист.Cells(а, б).FormulaR1C1, 1) = "=" Тогда
// Перезаписываем формулу саму в себя для активации
Лист.Cells(а, б).FormulaR1C1 = Лист.Cells(а, б).FormulaR1C1;
КонецЕсли;
КонецЦикла;
КонецЦикла;
FullName = Excel.ActiveWorkbook.FullName;
Excel.DisplayAlerts = false;
Excel.ActiveWorkbook.SaveAs(FullName, 51); // 51 - формат xlsx
Excel.Application.Quit();
В данном примере мы используем свойство FormulaR1C1. Это важно, так как 1С по умолчанию оперирует координатами в стиле R1C1 (Row/Column), и Excel лучше воспринимает программную запись именно в этом формате.
Предыдущий метод с перебором каждой ячейки может работать очень медленно на больших документах (тысячи строк). Рассмотрим более эффективный способ, который упоминался в сообщениях форума. Вместо цикла мы воспользуемся встроенной функцией Excel Replace (Найти и заменить). Мы заменим все знаки "=" на "=". Для Excel это выглядит как массовое изменение ячеек, что провоцирует мгновенный пересчет всех формул на листе.
Разберем, как это реализовать правильно:
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.WorkBooks.Open(ПутьКФайлу);
Лист = Книга.WorkSheets(1);
// Выделяем используемый диапазон и меняем формат на "Общий",
// чтобы Excel не считал ячейки текстовыми
Лист.UsedRange.NumberFormat = "General";
// Выполняем замену знака равенства на знак равенства.
// Параметр 2 означает частичное совпадение (xlPart)
Лист.UsedRange.Replace("=", "=", 2);
Книга.Save();
Эксель.Quit();
Этот метод работает в десятки раз быстрее, так как выполняется одной командой над всем диапазоном UsedRange. Обратите внимание на установку NumberFormat = "General" — это критически важный шаг. Если формат ячейки останется «Текстовым», замена не поможет оживить формулу.
Часто возникает ситуация, когда формирование файла происходит в фоновом задании на сервере. Здесь нужно учитывать несколько важных моментов:
COMОбъект("Excel.Application") на сервере должен быть установлен Microsoft Excel.USR1CV8), должен иметь права на запуск DCOM-объектов Microsoft Excel и доступ к временным папкам профиля.Чтобы минимизировать ошибки при передаче формул, придерживайтесь следующих правил:
SUM вместо СУММ), если планируете записывать их через свойство Formula. Если используете FormulaLocal, можно писать на языке локализации ОС.Содержимое как Текст, а не как Параметр, если вы сразу записываете туда готовую строку формулы.#REF!.Подводя итог, можно сказать, что хотя стандартный механизм 1С не обеспечивает полной интеграции формул "из коробки", связка метода Записать() и последующей легкой обработки через COM-объект методом Replace позволяет получить полностью рабочий и интерактивный отчет в формате Excel.