Для начала устанавливаем питон! Звучит как шутка? Нет, это действительно самый простой способ. Итак, устанавливаем питон
и пакет 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>, подробности в логе ошибок.';
Пример шаблона
Результат работы
Почему openpyxl, а не PhpSpreadsheet/PhpOffice?
Потому что openpyxl работает с теми шаблонами, на которых PhpSpreadsheet виснет. Шаблоны отчётов присылают клиенты и приходится работать с тем, что дали. Если бы PhpSpreadsheet работал со всеми шаблонами, я бы использовал его. Был ещё вариант использовать nodejs, но там совсем грустно и по ресурсам, и по установке, и по использованию.
Почему json_to_xlsx.py весь на английском языке?
В данный момент я не пишу на питоне и не знаю его особенностей, поэтому английский язык. Но и так вроде неплохо выглядит.
Что делать, если нужно дополнительно форматировать ячейки?
Из PHP передавать дополнительную информацию в JSON, а в json_to_xlsx.py костылять костыли.
Не слишком ли затратно вызывать питон из пхп?
Пусть лучше сервер тратит свои ресурсы на эти отчёты, чем я.