Сниппеты PHPExcel: загрузка книги, формулы, пересчёт и формат чисел

Контекст

PHPExcel давно снят с поддержки; для новых задач логичнее сразу брать PhpSpreadsheet и совместимый с ним PHP. Ниже — компактные приёмы именно для уже существующего кода на PHPExcel в связке с типичным Битрикс‑проектом: шаблоны XLSX, формулы и отображение чисел.

Загрузить документ Excel

Через фабрику читателя получаем объект книги, активируем первый лист и дальше работаем с $sheet как с текущим листом.

$reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel = $reader->load($templateFileName);
$excel->setActiveSheetIndex(0);
$sheet = $excel->getActiveSheet();

Путь к файлу шаблона должен быть валиден для окружения веб‑сервера (права, open_basedir); при выдаче готового файла пользователю не смешивайте с выводом буфера страницы до отправки заголовков.

Вставить формулу

Пример: в ячейке A3 посчитать процент от произведения A1 и A2 — через строку формулы, координаты подставлять явно:

$sheet->setCellValue('A3', sprintf('=%s*%s/100', 'A1', 'A2'));

В поставке PHPExcel смотрите Examples/03formulas.php — там много готовых паттернов.

Excel, LibreOffice и пересчёт при сохранении

В Microsoft Excel формула после открытия файла обычно пересчитывается сама. В LibreOffice поведение может отличаться: если значения «застыли», перед записью файла у writer можно включить предрасчёт формул:

// $objWriter — например PHPExcel_Writer_Excel2007
$objWriter->setPreCalculateFormulas(true);

Это увеличивает время сохранения и может упереться в формулы или функции, которые движок PHPExcel поддерживает не полностью — проверяйте на реальных книгах.

Значение формулы из PHP

Нужно именно вычисленное число/строка из ячейки — используйте калькулятор PHPExcel или «старое» сохранённое значение из файла:

$calculated = $sheet->getCell('A3')->getCalculatedValue();
$cached    = $sheet->getCell('A3')->getOldCalculatedValue();

getCalculatedValue() запускает расчёт через движок библиотеки; getOldCalculatedValue() читает то, что Excel уже посчитал при последнем сохранении шаблона — быстрее, но может быть устаревшим относительно текущих данных.

Формат ячейки (два знака после запятой)

$sheet->getStyle('A3')->getNumberFormat()->setFormatCode('#,##0.00');

Длинное целое без научной нотации

Если ИНН вроде 320801082253 отображается как 3,20801E+11, задайте явный числовой формат и при записи значения верните объект ячейки третьим аргументом setCellValue (в PHPExcel это флаг «вернуть ячейку» для цепочки вызовов):

$sheet
    ->setCellValue('A3', $inn, true)
    ->getStyle()
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);

Для строго текстового отображения (без преобразования в число) иногда безопаснее писать значение как строку или использовать явный тип ячейки — зависит от того, должна ли дальше работать формула в файле.

Кратко

• Загрузка XLSX через PHPExcel_IOFactory::createReader('Excel2007') и работа с активным листом.
• Формулы — строки с =; для кроссплатформенного пересчёта — setPreCalculateFormulas(true) с оговоркой по времени и поддержке функций.
• Из кода: getCalculatedValue() vs getOldCalculatedValue().
• Форматы через getNumberFormat()->setFormatCode(); длинные целые — FORMAT_NUMBER и цепочка с setCellValue(..., true).
• Новые проекты планируйте на PhpSpreadsheet.