Введение

Есть одна мебельная фабрика. Исторически у этой фабрики IT хозяйство было не очень развито и со временем накопилось множество внутренних сервисов для расчета материалов, нагрузки на станки, учета временных затрат работников, доставки и прочего. Исходя из этих расчетов составляется себестоимость продукции. Далее накладывается маржа, и идет расчет доставки и сборки, в зависимости от условий. Фабрика захотела объединить все сервисы в один, основанный на веб, чтобы все расчёты и результаты можно было видеть в одном месте. Было выбрано делать новую веб систему на фреймворке Symfony (PHP).

По задумке данные из разных Excel файлов и локальной базы данных должны стекаться туда, группироваться по проектам и красиво отображаться. Изначально не планировалось изменять какие-либо данные, только просматривать. Также заказчик не хотел полностью отказываться от Excel файлов и переносить формулы в новую систему. Да, это странно со стороны разработчика, но объяснимо со стороны бизнеса: формул очень много, и они очень старые и запутанные. Но они отлично работают и всех устраивают. Их анализ, перенос в новую систему, отладка, и обучение работников пользоваться новой системой не входило в планы и бюджет проекта.

Проблема

На этапе разработки, когда первые Excel файлы были загружены и красиво отображались таблицы с материалами и суммы, клиенту захотелось немного "поиграть" цифрами внутри системы. Вместо одного стола, "поставить" два и посмотреть как изменятся суммы материалов. Естественно, возник вопрос, что мы не можем пересчитать формулы в нашей системе, так как мы их не переносили. Вариант увеличивать итог пропорционально количеству не мог быть использован, так как это не было пропорциональное увеличение. Упрощенный пример: для изготовления стола нужна заготовка столешницы из которой можно сделать пять столов. Но если надо сделать шесть столов, то заготовок нужно уже две. Так же и доставка: если в грузовик влезает сто столов, то чтобы привести сто один стол нужно уже два грузовика. Excel всё это учитывал, а еще скидки для конкретных клиентов, разный расчет стоимости сборки и прочее, прочее.

Первая попытка — PhpSpreadsheet

Кажется встала невыполнимая задача - пересчитывать формулы, которых нет. Но как говорил мой первый наставник и шеф: "Мы можем сделать всё, для программиста нет ничего невозможного". Для импорта Excel файла я использовал библиотеку https://github.com/phpoffice/phpspreadsheet с помощью нее можно прочитать лист внутри файла и получить значение (как саму формулу так и ее результат) из любой ячейки.

use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load('/path/file.xlsx');
$sheet = $spreadsheet->getSheetByName('Totals');
$value = $sheet->getCell('F18')->getCalculatedValue();

У этой библиотеки есть также функция задать значение в ячейку. То есть теоретически можно создать форму на сайте, например с полями "количество товара". При отправке формы, получить данные и поменять в файле количество товара но новое, рассчитать новые значения сумм после этого.

use PhpOffice\PhpSpreadsheet\IOFactory;

$newQuantity = 5;

$spreadsheet = IOFactory::load('/path/file.xlsx');
$sheet = $spreadsheet->getSheetByName('Products');
$sheet->setCellValue('D:5', $newQuantity);

Но на практике это не сработало - формулы не могли рассчитаться так как было много листов и перекрестных ссылок. PhpOffice\PhpSpreadsheet это не полноценный движок Excel а лишь библиотека для работы с готовым файлом. А значит, можно попробовать использовать полноценный движок. Что если установить на сервер headless LibreOffice и делать перерасчет формул там? Стал пробовать.

Вторая попытка — использовать настоящий движок Excel

Установил LibreOffice на сервер

sudo apt update
sudo apt install -y libreoffice libreoffice-calc fonts-dejavu fontconfig

И все получилось!

use PhpOffice\PhpSpreadsheet\IOFactory;

$newQuantity = 5;

$spreadsheet = IOFactory::load('/path/file.xlsx');
$sheet = $spreadsheet->getSheetByName('Products');
$sheet->setCellValue('D5', $newQuantity);

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setPreCalculateFormulas(false);
$writer->save('/path/file.xlsx');

$cmd = sprintf(
  'HOME=/tmp libreoffice --headless --nologo --nofirststartwizard --calc --convert-to xlsx --outdir %s %s',
  escapeshellarg(dirname($absolutePath)),
  escapeshellarg($absolutePath)
);

exec($cmd, $output, $code);

if ($code !== 0) {
  throw new RuntimeException('LibreOffice recalculation failed');
}

$updatedSpreadsheet = IOFactory::load('/path/file.xlsx');
$updatedSheet = $updatedSpreadsheet->getSheetByName('Totals');
$updatedValue = $updatedSheet->getCell('F18')->getCalculatedValue();

В итоге Excel так и остался внутри системы — но уже не как файл, из которого берём данные, а как движок бизнес-логики.

Вывод

Да, решение получилось немного необычным: веб-приложение на Symfony меняет входные данные в Excel, затем headless LibreOffice пересчитывает формулы, и система забирает обратно готовые результаты. На практике это занимает около 15 секунд. Но для пользователя, который делает расчёт проекта мебели, это вполне приемлемо. Ему важнее менять параметры и видеть результат.

Можно ли было переписать все формулы на PHP? Конечно. Но это заняло бы месяцы работы, большой бюджет и риск сломать давно работающую логику.

Наверное такой подход можно использовать в MVP где надо много менять формулы, и это должен делать не программист, а например менеджер или сам клиент.

Иногда подходящее решение - не переписать всё заново, а грамотно встроить существующий инструмент в новую систему. И в этот раз Excel оказался не пережитком прошлого, а вполне рабочим микросервисом для расчётов. Вместо того, чтобы бороться с существующим инструментом, проще признать: Excel уже является системой расчётов. И его можно использовать программно.