При разработке отчетов в 1С часто возникает задача выгрузки данных в формат Excel. Одной из самых неприятных проблем является то, что выгруженные формулы воспринимаются Excel как обычный текст. Пользователю приходится либо вручную «прокликивать» каждую ячейку, либо использовать вспомогательные макросы для активации вычислений. В этой статье мы подробно разберем, как решить эту задачу программно, используя возможности 1С и механизмы COM-соединения.
Проанализируем ситуацию: когда 1С сохраняет табличный документ методом Записать(), она преобразует содержимое ячеек в строковый формат. Даже если строка начинается со знака «равно» (например, "=A1+B1"), Excel при открытии файла может интерпретировать её как текстовую константу. Это происходит из-за того, что формат ячейки изначально установлен как «Текстовый», либо из-за особенностей внутреннего парсера файлов .xls. Чтобы формулы заработали, нам нужно либо правильно подготовить табличный документ в самой 1С, либо «дожать» файл после сохранения через COM-объект Excel.
Наиболее надежный способ — это пост-обработка уже сохраненного файла. Мы можем подключиться к приложению Excel, открыть наш файл и принудительно записать формулы в нужные ячейки, одновременно задав правильный числовой формат. Рассмотрим пример кода, который автоматизирует этот процесс:
// Выбираем путь для сохранения файла
ДиалогВФ = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Сохранение);
ДиалогВФ.Фильтр = "Лист Excel (*.xlsx)|*.xlsx";
Если НЕ ДиалогВФ.Выбрать() Тогда
Возврат;
КонецЕсли;
ПутьКФайлу = ДиалогВФ.ПолноеИмяФайла;
// Сначала сохраняем табличный документ стандартным средством
ТабДок.Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента.XLSX);
// Теперь подключаемся к Excel для "активации" формул
Попытка
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
Лист = Книга.Sheets(1);
// Эксель не должен быть видимым во время обработки
Эксель.Visible = Ложь;
Эксель.DisplayAlerts = Ложь;
// Определяем область, где должны быть формулы (например, колонки с 10 по 12)
// Либо проходим циклом по нужным строкам
НомерСтроки = 8; // Начало данных
Пока ЗначениеЗаполнено(Лист.Cells(НомерСтроки, 1).Value) Цикл
Ячейка = Лист.Cells(НомерСтроки, 10); // Допустим, 10 колонка
// Устанавливаем числовой формат (например, денежный)
Ячейка.NumberFormat = "#,##0.00";
// Перезаписываем формулу. При записи в свойство Formula
// Excel автоматически пересчитывает значение.
ТекущаяФормула = Ячейка.Formula;
Ячейка.Formula = ТекущаяФормула;
НомерСтроки = НомерСтроки + 1;
КонецЦикла;
Книга.Save();
Эксель.Quit();
Сообщить("Файл успешно обработан, формулы активны.");
Исключение
Сообщить("Ошибка при работе с Excel: " + ОписаниеОшибки());
Если Эксель <> Неопределено Тогда
Эксель.Quit();
КонецЕсли;
КонецПопытки;
Важный нюанс: при работе через COMОбъект следует учитывать локализацию. Свойство Formula требует английских названий функций (например, SUM), а FormulaLocal позволяет использовать названия на языке вашей системы (например, СУММ). Если вы используете стиль ссылок R1C1 (как в сообщении автора темы), используйте свойство FormulaR1C1.
Если вам необходимо, чтобы макрос присутствовал в самом файле и пользователь мог запускать его позже, можно внедрить код VBA прямо из 1С. Рассмотрим, как это сделать. Обратите внимание, что для работы этого метода в настройках Excel должен быть разрешен «Доступ к объектной модели проектов VBA».
Разберем по шагам создание модуля в книге Excel:
Эксель = Новый COMОбъект("Excel.Application");
Книга = Эксель.Workbooks.Open(ПутьКФайлу);
// Обращаемся к проекту VBA внутри книги
Попытка
VBP = Книга.VBProject;
НовыйМодуль = VBP.VBComponents.Add(1); // 1 = vbext_ct_StdModule
КодМакроса =
"Sub АктивироватьФормулы()
| Cells.Select
| Selection.Replace What:=""="", Replacement:=""="", LookAt:=2
| Selection.NumberFormat = ""#,##0.00""
|End Sub";
НовыйМодуль.CodeModule.InsertLines(1, КодМакроса);
// Запускаем макрос сразу после вставки
Эксель.Run("АктивироватьФормулы");
// Сохраняем как файл с поддержкой макросов (.xlsm)
Книга.SaveAs(ПутьКФайлу, 52); // 52 = xlOpenXMLWorkbookMacroEnabled
Исключение
Сообщить("Не удалось записать макрос. Проверьте настройки безопасности Excel.");
КонецПопытки;
Эксель.Quit();
Этот метод полезен, когда нужно выполнить сложные преобразования форматов, которые макросом сделать быстрее, чем последовательными вызовами COM-команд из 1С. Однако помните, что современные системы безопасности часто блокируют файлы с макросами, поэтому первый способ (прямая запись свойств) является более предпочтительным и «чистым».
Прежде чем прибегать к тяжеловесному COM-соединению, проверим возможности самой платформы 1С. У объекта ОбластьЯчеекТабличногоДокумента есть встроенные свойства для работы с формулами. Выясним, как их использовать правильно:
Ячейка.Формула. В него нужно записывать выражение в формате R1C1 без знака «равно» в начале, если вы работаете с внутренней логикой 1С, но при выгрузке в Excel 1С сама добавит нужные символы.СодержитФормулу = Истина. Без этого флага 1С воспримет текст как обычную строку.ТипФайлаТабличногоДокумента.XLSX. В нем поддержка формул реализована значительно лучше, чем в старом формате XLS (Excel 97-2003).Пример правильного заполнения ячейки в 1С:
Область = ТабДок.Область(8, 10); // Строка 8, Колонка 10
Область.Текст = ""; // Очищаем текст, чтобы не мешал формуле
Область.Формула = "RC[-2]+RC[-1]"; // Сумма двух предыдущих колонок
Область.СодержитФормулу = Истина;
Проанализируем распространенную ошибку: Произошла исключительная ситуация (Microsoft Excel): Нельзя установить свойство NumberFormat класса Range. Эта ошибка часто возникает, когда мы пытаемся применить формат к защищенному листу или когда Excel находится в режиме редактирования ячейки.
Чтобы избежать проблем с производительностью при больших объемах данных, следуйте этим советам:
Лист.Cells(р, к)), старайтесь работать с диапазонами (Лист.Range("A1:C100")). Это сокращает количество обращений между 1С и COM-сервером Excel, что ускоряет работу в десятки раз.Excel.ScreenUpdating = False. Это уберет мерцание и ускорит выполнение кода.Quit() и обнуляйте переменные, иначе в диспетчере задач будут скапливаться «зомби-процессы» excel.exe.Выбор конкретного метода зависит от ваших задач. Если отчет небольшой, достаточно правильно настроить свойства ТабличногоДокумента. Если же требуется сложная верстка и активация специфических функций Excel, используйте COMОбъект для финальной доработки файла после его сохранения.