При выгрузке отчетов и обработке данных из 1С в Microsoft Excel через COM-соединение разработчики нередко сталкиваются с ситуацией, когда простые арифметические выражения записываются без проблем, а при попытке вставить стандартные функции (например, ROUNDUP, ОКРУГЛВВЕРХ, CEILING) Excel выдает ошибку выполнения COM-объекта (код 0x800A03EC / HRESULT 1004) либо формула отображается некорректно.
Выясним причину такого поведения и разберем по шагам, какие свойства объектной модели Excel необходимо использовать и как правильно передавать в них параметры из 1С.
В объектной модели Excel (COM / OLE Automation) действуют строгие правила разделения свойств для работы с формулами на универсальные (инвариантные) и локализованные:
Formula (и FormulaR1C1) работает исключительно по англоязычному стандарту (en-US). Имена функций должны передаваться строго на английском языке (например, ROUNDUP, SUM, CEILING), в качестве разделителя аргументов обязательна запятая (,), а разделителем дробной части в числах выступает точка (.). Использование точки с запятой (;) или русскоязычных названий функций в свойстве Formula приводит к ошибке.FormulaLocal (и FormulaR1C1Local) ориентировано на текущую региональную настройку операционной системы. Для русской локали Windows функции должны иметь русские имена (например, ОКРУГЛВВЕРХ, СУММ, ПОТОЛОК), разделителем аргументов служит точка с запятой (;), а разделителем дробной части — запятая (,).Иногда может показаться странным, что конструкция вида =SUM(G2;K2) срабатывает в свойстве Formula. Это происходит потому, что в англоязычном синтаксисе Excel символ точки с запятой расценивается как оператор объединения диапазонов (Union). Однако для функций с несколькими обязательными позиционными параметрами (как у ROUNDUP или CEILING) такая подмена синтаксически недопустима, и возникает исключение.
Рассмотрим основной и наиболее универсальный способ записи формул. Передаем имя функции на английском языке, а параметры разделяем запятыми.
Посмотрим на пример статической записи:
Лист.Cells(2, 18).Formula = "=ROUNDUP(Q2, 0)";
Если номер строки или координаты ячеек формируются динамически в цикле 1С, обязательно отключаем группировку триад с помощью функции Формат(), чтобы в строку формулы не попали неразрывные пробелы:
СтрНомера = Формат(ТекСтрока, "ЧГ=0");
Лист.Cells(ТекСтрока, 18).Formula = "=ROUNDUP(Q" + СтрНомера + "*I" + СтрНомера + "/7, 0)";
Если требуется сохранить формулы с русскоязычными именами функций, как они привычны пользователю в русскоязычном интерфейсе Excel, используем свойство FormulaLocal. В этом случае разделителем аргументов выступает точка с запятой:
Лист.Cells(2, 19).FormulaLocal = "=ОКРУГЛВВЕРХ(H2; 0)";
При динамической сборке выражения код на стороне 1С будет выглядеть следующим образом:
СтрНомера = Формат(ТекСтрока, "ЧГ=0");
Лист.Cells(ТекСтрока, 19).FormulaLocal = "=ОКРУГЛВВЕРХ(Q" + СтрНомера + "*I" + СтрНомера + "/7; 0)";
Стиль адресации R1C1 позволяет обращаться к строкам и столбцам относительно текущей ячейки (например, RC[-1] означает ячейку в той же строке на один столбец левее). Это избавляет от необходимости динамически склеивать строковые координаты в цикле.
Посмотрим на пример записи через относительные координаты (функция на английском языке, разделитель — запятая):
Лист.Cells(ТекСтрока, 18).FormulaR1C1 = "=ROUNDUP(RC[-1]*RC[-9]/7, 0)";
Кроме того, использование FormulaR1C1 позволяет оптимизировать производительность выгрузки из 1С: вместо перебора сотен строк в цикле можно установить формулу сразу на весь диапазон ячеек за одну операцию:
Диапазон = Лист.Range(Лист.Cells(2, 18), Лист.Cells(100, 18));
Диапазон.FormulaR1C1 = "=ROUNDUP(RC[-1]*RC[-9]/7, 0)";
Если в формулу требуется передать дробное число, хранящееся в переменной 1С, проанализируем правила форматирования:
Formula или FormulaR1C1 разделителем дробной части должна быть точка:
СтрЧисла = Формат(МоеЧисло, "ЧРД=.; ЧГ=0");
Лист.Cells(ТекСтрока, 18).Formula = "=ROUNDUP(Q2*" + СтрЧисла + ", 0)";
FormulaLocal или FormulaR1C1Local в русской локали разделителем дробной части должна быть запятая:
СтрЧисла = Формат(МоеЧисло, "ЧРД=,; ЧГ=0");
Лист.Cells(ТекСтрока, 18).FormulaLocal = "=ОКРУГЛВВЕРХ(Q2*" + СтрЧисла + "; 0)";
При работе с COM-объектом Excel.Application следует учитывать системные ограничения:
Новый COMОбъект("Excel.Application") работает только в операционных системах семейства Windows при наличии установленного Microsoft Office. На серверах под управлением Linux данный способ неприменим.ТабличныйДокумент с сохранением через метод Записать() в формате XLSX либо формировать файл с помощью средств работы с ZIP и OpenXML.