Отчёты в шаблоне XLSX на PHP

Для начала устанавливаем питон! Звучит как шутка? Нет, это действительно самый простой способ. Итак, устанавливаем питон и пакет openpyxl:

apt install python3-pip
pip3 install openpyxl

Где-нибудь в системе создаём скрипт json_to_xlsx.py:

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from copy import copy
from argparse import ArgumentParser
import json

parser = ArgumentParser(description='Insert data from CSV file to XLSX template and save result as XLSX file.')
parser.add_argument('template', nargs=1, help='path to the XLSX file to be used as a template', metavar='PATH_TO_TEMPLATE_XLSX')
parser.add_argument('-d', '--data', nargs=1, help='path to the CSV file to be used as a source of rows (default: stdin)', metavar='PATH_TO_DATA_CSV', default=['/dev/stdin'])
parser.add_argument('-o', '--output', nargs=1, help='path to the XLSX file to be used as a result (default: stdout)', metavar='PATH_TO_RESULT_XLSX', default=['/dev/stdout'])
parser.add_argument('-s', '--start-row', nargs=1, help='row number to set data (default: 2)', type=int, metavar='N', default=[2])

args = vars(parser.parse_args())
arg = lambda x: args[x][0] if isinstance(args[x], list) else args[x]

template = arg('template')
data_file = arg('data')
result_file = arg('output')
current_row = arg('start_row')
styled_row = current_row

wb = load_workbook(filename=template)
ws = wb.active

with open(data_file, 'r') as read_obj:
    for row_data in read_obj:
        row = json.loads(row_data)
        for col, entry in enumerate(row, start=1):
            # Replace row number (for formulas)
            value = entry.replace('{ROW}', str(current_row)) if isinstance(entry, str) else entry

            # Place data in new cell
            new_cell = ws.cell(row=current_row, column=col, value=value)

            # Copy styles
            styled_cell = ws["{0}{1}".format(get_column_letter(col), styled_row)]
            new_cell.font = copy(styled_cell.font)
            new_cell.border = copy(styled_cell.border)
            new_cell.fill = copy(styled_cell.fill)
            new_cell.number_format = copy(styled_cell.number_format)
            new_cell.protection = copy(styled_cell.protection)
            new_cell.alignment = copy(styled_cell.alignment)

        current_row += 1

wb.save(result_file)

Пример запуска программы:

$ python3 json_to_xlsx.py --help
usage: json_to_xlsx.py [-h] [-d PATH_TO_DATA_CSV] [-o PATH_TO_RESULT_XLSX] [-s N] PATH_TO_TEMPLATE_XLSX

Insert data from CSV file to XLSX template and save result as XLSX file.

positional arguments:
  PATH_TO_TEMPLATE_XLSX
                        path to the XLSX file to be used as a template

optional arguments:
  -h, --help            show this help message and exit
  -d PATH_TO_DATA_CSV, --data PATH_TO_DATA_CSV
                        path to the CSV file to be used as a source of rows (default: stdin)
  -o PATH_TO_RESULT_XLSX, --output PATH_TO_RESULT_XLSX
                        path to the XLSX file to be used as a result (default: stdout)
  -s N, --start-row N   row number to set data (default: 2)

Подготовьте шаблон template.xlsx, куда будут выгружаться данные. Если нужно, отформатируйте начальную строку, с которой начнутся данные, при добавлении новых строчек формат будет копироваться. Единственное, что я не сделал, это копирование объединения ячеек.

Теперь пишем скрипт на PHP, который будет генерировать отчёт:

<?php

// Устанавливаем локаль, чтобы суммы выгружались с запятой, а не точкой
setlocale(LC_NUMERIC, 'ru_RU.utf8');

// Начальная строка, с которой начинаются данные
$startRow = 2;

// Данные для отчёта
$data = [
//   A         B       C      D
   ['Пончики', 123.33, 10.5, '=B{ROW}*C{ROW}'],
   ['Напитки', 321.45, 20  , '=B{ROW}*C{ROW}'],
];

// Конечная строка для расчёта сумм
$endRow = $startRow + (count($data) ?: 1) - 1;

// Добавляем итоговые суммы
$data[] = ['Итого'  , "=SUM(B{$startRow},B{$endRow})", "=SUM(C{$startRow},C{$endRow})", "=SUM(D{$startRow},D{$endRow})"];

// Путь до шаблона
$template = 'template.xlsx';

// Временный файл для отчёта
$tmpfname = tempnam(sys_get_temp_dir(), 'report');

// Команда для запуска генератора на питоне
$command = join(' ', [
    'python3',
    escapeshellarg('json_to_xlsx.py'),
    '--start-row ' . escapeshellarg($startRow),  // Говорим, с какой строчки вставлять данные
    '--output ' . escapeshellarg($tmpfname),     // Выходной файл
    escapeshellarg($template)                    // Путь до шаблона
]);

// Дескрипторы для конвеера
$descriptors = [
    0 => ['pipe', 'r'],                                      // stdin - канал, из которого дочерний процесс будет читать
    1 => ['pipe', 'w'],                                      // stdout - канал, в который дочерний процесс будет записывать
    2 => ['file', ini_get('error_log') ?: '/dev/null', 'a']  // stderr - ошибки пишем в лог ошибок
];

// Запускаем генератор
$res = proc_open($command, $descriptors, $pipes, __DIR__);

// Если команда запустилась
if ($res) {
    // Заполняем отчёт данными
    foreach ($data as $row) {
        fputs($pipes[0], json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE) . PHP_EOL);
    }

    // Завершаем работу генератора
    fclose($pipes[0]);
    proc_close($res);
    
    // Отдаём файл пользователю
    
    // Сбрасываем буферы, если есть
    while (ob_get_level()) {
        ob_end_clean();
    }

    // Имя файла для скачивания
    $filename = 'Квартальный отчёт.xlsx';

    // Говорим браузеру, что содержимое есть файл для скачивания
    header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', true);
    header('Content-Disposition: attachment; filename*=UTF-8\'\''. strtr(urlencode($filename), ['+' => '%20']));

    // Отдаём файл и удаляем его
    readfile($tmpfname);
    unlink($tmpfname);
    die;
} else {
    unlink($tmpfname);
}

echo 'Не удалось выполнить команду <code>' . Html($command) . '</code>, подробности в логе ошибок.';

Пример шаблона

Template.xlsx

Результат работы

Template.xlsx

Если возникли вопросы

Почему openpyxl, а не PhpSpreadsheet/PhpOffice?

Потому что openpyxl работает с теми шаблонами, на которых PhpSpreadsheet виснет. Шаблоны отчётов присылают клиенты и приходится работать с тем, что дали. Если бы PhpSpreadsheet работал со всеми шаблонами, я бы использовал его. Был ещё вариант использовать nodejs, но там совсем грустно и по ресурсам, и по установке, и по использованию.

Почему json_to_xlsx.py весь на английском языке?

В данный момент я не пишу на питоне и не знаю его особенностей, поэтому английский язык. Но и так вроде неплохо выглядит.

Что делать, если нужно дополнительно форматировать ячейки?

Из PHP передавать дополнительную информацию в JSON, а в json_to_xlsx.py костылять костыли.

Не слишком ли затратно вызывать питон из пхп?

Пусть лучше сервер тратит свои ресурсы на эти отчёты, чем я.

Хорошая статья, мне понравилась. Оставлю отзыв!