Заполнение формул в файлах Excel (кроссплатформенное решение)
Интеграция - Загрузка и выгрузка в Excel
Вдохновением послужила статья Выгрузка данных в Excel с добавлением формул (нюансы формирования формул), а также отсутствие в интернете описанного подхода по работе с формулами.
Основная идея в том, что файл Excel по сути является zip-архивом с файлами внутри. Файлы, в свою очередь, можно редактировать с помощью объекта платформы ДокументDOM.
В данном примере приводится заполнение формул со стилем ссылок "по умолчанию", так называемый ссылочный стиль A1. Например: "=A1+B2".
Важно: Предварительно, сам файл должен быть корректно сформирован (корректный формат данных в ячейках).
Есть куча статей, в которых описано, как это сделать для числовых значений (настройка в макете).

Итак, алгоритм действий следующий:
1. Распаковка файла Excel как zip-архива
ВременныйКаталог = КаталогВременныхФайлов() + "\excel_temp\";
СоздатьКаталог(ВременныйКаталог);
ИзвлечьАрхив(ПутьДоФайла, ВременныйКаталог);
2. Работа с XML файлом листа документа Excel и заполнение формул
ПутьДоXMLФайлаЛист1 = ВременныйКаталог + "xl\worksheets\sheet1.xml";
// Чтение распакованного xml-файла листа 1 в ДокументDOM
ДокументDOM = ПолучитьDOMДокументИзXMLФайла(ПутьДоXMLФайлаЛист1);
ВсеЯчейки = ДокументDOM.ПолучитьЭлементыПоИмени("c");
ВсеСтроки = ДокументDOM.ПолучитьЭлементыПоИмени("row");
ВсегоСтрок = ВсеСтроки.Количество();
// Обход ячеек и определение формул по произвольному алгоритму
Для Каждого Ячейка Из ВсеЯчейки Цикл
Формула = Неопределено;
ИмяЯчейки = Ячейка.ПолучитьАтрибут("r");
Если ИмяЯчейки = Неопределено Тогда
Продолжить;
КонецЕсли;
ИмяСтолбца = Сред(ИмяЯчейки, 1, 1);
ИмяСтроки = Сред(ИмяЯчейки, 2);
НомерСтроки = Число(ИмяСтроки);
// Сборка формулы по условиям для ячейки в формате "=A1+B2"
// Тут необходимо прописать логику по определению формулы
Формула = "=A1+B2";
Если Формула = Неопределено Тогда
Продолжить;
КонецЕсли;
Пока Ячейка.ЕстьДочерниеУзлы() Цикл
Ячейка.УдалитьДочерний(Ячейка.ПервыйДочерний);
КонецЦикла;
// Заполнение формулы
ЭлФормула = ДокументDOM.СоздатьЭлемент("f");
ЭлФормула.ТекстовоеСодержимое = Формула;
Ячейка.ДобавитьДочерний(ЭлФормула);
КонецЦикла;
// Запись модифицированного объекта ДокументDOM в xml-файл листа 1
СохранитьDOMДокументКАКXMLФайл(ДокументDOM, ПутьДоXMLФайлаЛист1);
3. Запаковка измененных файлов обратно в архив (Excel-файл) и удаление временных файлов
УдалитьФайлы(ПутьДоФайла);
СобратьАрхив(ВременныйКаталог, ПутьДоФайла);
УдалитьФайлы(ВременныйКаталог);
Для удобства, все вынесено в отдельный общий модуль, привожу его листинг:
Вступайте в нашу телеграмм-группу Инфостарт
Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».
- 0% комиссии — оплата напрямую исполнителю;
- Исполнители любого масштаба — от отдельных специалистов до команд под проект;
- Прямой обмен контактами между заказчиком и исполнителем;
- Безопасная сделка — при необходимости;
- Рейтинги, кейсы и прозрачная система откликов.
См. также
5490 руб.
12.08.2021 48615 656 71
6000 руб.
11.12.2019 68165 1163 3
12444 руб.
29.10.2014 238135 777 544
6000 руб.
26.11.2020 51370 865 4
6000 руб.
01.03.2021 13537 92 0