Создание excel в PHP
Периодически появляется необходимость, создания excel таблицы средствами php. Например, выгрузить данные из базы, формирование различных отчетов, актов, смет, каталогов и тд.
Встроенных функций в php для этого нет, но на сегодняшний день, для генерации xlsx документов, можно воспользоваться пакетом phpoffice/phpspreadsheet о нем и пойдет речь в данной статье, разберем установку и примеры использования.
Прежде чем приступать к написанию кода, стоит понять, как работает phpoffice/phpspreadsheet, а именно то, что до момента вывода файла в браузер или сохранения на сервер, мы работаем с виртуальной таблицей, по которой можно перемещаться, как по ячейкам Excel, но только в коде.
Структура проекта
Первоначальная структура проекта:
├── Файл index.php ├── Файл composer.json ├── Директория src └── Файл Excphp.php
Шаг 1 — Проверка наличия composer
Для начала убедитесь, что у Вас установлен composer, сделать это можно командой, в консоле composer -v
Если видим подобное сообщение в ответ, то можно идти дальше, иначе необходимо установить composer
______
/ ____/___ ____ ___ ____ ____ ________ _____
/ / / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__ ) __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
/_/
Composer version 2.0.14 2021-05-21 17:03:37
Usage:
command [options] [arguments]
Options:
-h, --help Display this help message
-q, --quiet Do not output any message
-V, --version Display this application version
--ansi Force ANSI output
--no-ansi Disable ANSI output
-n, --no-interaction Do not ask any interactive question
--profile Display timing and memory usage information
--no-plugins Whether to disable plugins.
-d, --working-dir=WORKING-DIR If specified, use the given directory as working directory.
--no-cache Prevent use of the cache
-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Available commands:
about Shows the short information about Composer.
archive Creates an archive of this composer package.
browse Opens the package's repository URL or homepage in your browser.
cc Clears composer's internal package cache.
check-platform-reqs Check that platform requirements are satisfied.
clear-cache Clears composer's internal package cache.
clearcache Clears composer's internal package cache.
config Sets config options.
create-project Creates new project from a package into given directory.
depends Shows which packages cause the given package to be installed.
diagnose Diagnoses the system to identify common errors.
dump-autoload Dumps the autoloader.
dumpautoload Dumps the autoloader.
exec Executes a vendored binary/script.
fund Discover how to help fund the maintenance of your dependencies.
global Allows running commands in the global composer dir ($COMPOSER_HOME).
help Displays help for a command
home Opens the package's repository URL or homepage in your browser.
i Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
info Shows information about packages.
init Creates a basic composer.json file in current directory.
install Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
licenses Shows information about licenses of dependencies.
list Lists commands
outdated Shows a list of installed packages that have updates available, including their latest version.
prohibits Shows which packages prevent the given package from being installed.
remove Removes a package from the require or require-dev.
require Adds required packages to your composer.json and installs them.
run Runs the scripts defined in composer.json.
run-script Runs the scripts defined in composer.json.
search Searches for packages.
self-update Updates composer.phar to the latest version.
selfupdate Updates composer.phar to the latest version.
show Shows information about packages.
status Shows a list of locally modified packages.
suggests Shows package suggestions.
u Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
update Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
upgrade Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
validate Validates a composer.json and composer.lock.
why Shows which packages cause the given package to be installed.
why-not Shows which packages prevent the given package from being installed.
Итак, composer есть, идем дальше.
Шаг 2 — создаем файл composer.json
Содержимое файла:
{
"require": {
"phpoffice/phpspreadsheet": "^1.18"
},
"autoload": {
"psr-4": {"" : "src/"}
}
}
Небольшие пояснения, первая секция загрузит нам phpoffice/phpspreadsheet»: «^1.18, а секция autoload позволит подключать свои классы в проект из директории src, подробнее описано здесь.
Запускаем в консоле команду composer require из директории, где был создан файл composer.json
Видим подобное сообщение:
Search for a package: ./composer.json has been updated Running composer update Loading composer repositories with package information Updating dependencies Lock file operations: 11 installs, 0 updates, 0 removals - Locking ezyang/htmlpurifier (v4.14.0) - Locking maennchen/zipstream-php (2.1.0) - Locking markbaker/complex (3.0.1) - Locking markbaker/matrix (3.0.0) - Locking myclabs/php-enum (1.8.3) - Locking phpoffice/phpspreadsheet (1.23.0) - Locking psr/http-client (1.0.1) - Locking psr/http-factory (1.0.1) - Locking psr/http-message (1.0.1) - Locking psr/simple-cache (2.0.0) - Locking symfony/polyfill-mbstring (v1.25.0) Writing lock file Installing dependencies from lock file (including require-dev) Package operations: 11 installs, 0 updates, 0 removals - Downloading psr/simple-cache (2.0.0) - Downloading phpoffice/phpspreadsheet (1.23.0) - Installing myclabs/php-enum (1.8.3): Extracting archive - Installing psr/simple-cache (2.0.0): Extracting archive - Installing psr/http-message (1.0.1): Extracting archive - Installing psr/http-factory (1.0.1): Extracting archive - Installing psr/http-client (1.0.1): Extracting archive - Installing markbaker/matrix (3.0.0): Extracting archive - Installing markbaker/complex (3.0.1): Extracting archive - Installing symfony/polyfill-mbstring (v1.25.0): Extracting archive - Installing maennchen/zipstream-php (2.1.0): Extracting archive - Installing ezyang/htmlpurifier (v4.14.0): Extracting archive - Installing phpoffice/phpspreadsheet (1.23.0): Extracting archive 4 package suggestions were added by new dependencies, use `composer suggest` to see details. Generating autoload files 3 packages you are using are looking for funding. Use the `composer fund` command to find out more!
В начале сделаем простую табличку, в виде небольшого расчета с материалами и работами, которые мы будем заполнять статично, не как массив в цикле, и сохраним документ. В процессе рассмотрим некоторые методы. Чтобы понять принцип взаимодействия, далее будем усложнять процесс.
Шаг 3 — создаем простой excel документ средствами php
Согласно структуре проекта, описанной в начале статьи, в директории src, создаем файл класса Excphp.php. Его код с комментариями:
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Excphp{
public function genDoc()
{
// Создаем новый Spreadsheet объект
$spreadsheet = new Spreadsheet();
// Подключение к активной таблице
$sheet = $spreadsheet->getActiveSheet();
// Объединяем ячейки от A1:F1
$sheet->mergeCells('A1:F1');
// Устанавливаем значение ячейке A1
$sheet->setCellValue('A1', 'Стоимость работ');
// Установка значений в шапку таблицы
$sheet->setCellValue('A2', '№ п/п');
$sheet->setCellValue('B2', 'Наименование работ/материалов');
$sheet->setCellValue('C2', 'Ед. изм');
$sheet->setCellValue('D2', 'Кол-во');
$sheet->setCellValue('E2', 'Стоимость');
$sheet->setCellValue('F2', 'Сумма');
// Установка 1-го ряда значений в таблицу
$sheet->setCellValue('A3', '1');
$sheet->setCellValue('B3', 'Укладка асфальта');
$sheet->setCellValue('C3', 'м3');
$sheet->setCellValue('D3', '100');
$sheet->setCellValue('E3', '500');
$sheet->setCellValue('F3', '50000');
// Установка 2-го ряда значений в таблицу
$sheet->setCellValue('A4', '2');
$sheet->setCellValue('B4', 'Доставка асфальта');
$sheet->setCellValue('C4', 'м3');
$sheet->setCellValue('D4', '100');
$sheet->setCellValue('E4', '1000');
$sheet->setCellValue('F4', '100000');
// получение текущей даты, будет использоваться в имени файла
$dt = date('h:i:s');
// создание объекта Xlsx
$writer = new Xlsx($spreadsheet);
// если требуется сохранять файл на сервер, то удалите комментарий у строки ниже
// $writer->save('file-$dt.xlsx');
// отправка файла в браузер
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='file-'.$dt.'.xlsx'');
$writer->save('php://output');
}
}
После создания файла Excphp.php, необходимо выполнить команду, в консоле, в корне проекта composer dump-autoload -o в результате увидим:
Generating optimized autoload files Generated optimized autoload files containing 765 classes
Зачем это нужно, описано здесь
Также создадим файл index.php, в корне документа, согласно структуры проекта. Его код:
<?php set_include_path(__DIR__); require 'vendor/autoload.php'; $obj = new Excphp(); $obj->genDoc();
Если все было сделано, как описано выше, то при открытии в браузере файла index.php, будет происходить автоматическое скачивание файла xlsx, как картинке ниже.
Первый пример создания файла xlsx реализован, естественно это самое простое, что можно было бы сделать. Основное действие по наполнению документа — это передача значений по координатам, как в обычном excel документе, через метод setCellValue(«F4», «100000») куда передается координата ячейки и значение. По ссылке ниже, можно скачать исходники данного примера. Далее рассмотрим более сложные примеры.
Скачать исходники
После скачивания, распакуйте архив с примером, в директорию с проектом и запустите две команды, в консоле :
- composer require
- composer dump-autoload -o
Пробуйте и тестируйте!
Шаг 4 — более сложные примеры
В следующем примере заполним таблицу в цикле foreach из подготовленного массива с данными, добавим формулы, и немного стилевого оформления.
Отредактируйте файл index.php, его новый код:
<?php
set_include_path(__DIR__);
require 'vendor/autoload.php';
$arrToWrite = array(
1 => array(
'NAME' => 'Монтаж металлоконструкций',
'PRICE' => 100,
'QUANTITY' => 5,
'UNIT' => 'шт.'
),
2 => array(
'NAME' => 'Вывоз мусора',
'PRICE' => 1000,
'QUANTITY' => 2,
'UNIT' => 'шт.'
),
3 => array(
'NAME' => 'Монтаж кабеля',
'PRICE' => 120,
'QUANTITY' => 100,
'UNIT' => 'м.п.'
),
4 => array(
'NAME' => 'Монтаж опор освещения',
'PRICE' => 2500,
'QUANTITY' => 10,
'UNIT' => 'шт.'
),
5 => array(
'NAME' => 'Благоустройство территории',
'PRICE' => 5500,
'QUANTITY' => 100,
'UNIT' => 'м2.'
),
6 => array(
'NAME' => 'Проектирование',
'PRICE' => 6000,
'QUANTITY' => 1,
'UNIT' => 'копмл.'
),
7 => array(
'NAME' => 'Щебень фракция 5-20',
'PRICE' => 5000,
'QUANTITY' => 10,
'UNIT' => 'м3.'
),
8 => array(
'NAME' => 'Цемент',
'PRICE' => 1000,
'QUANTITY' => 10,
'UNIT' => 'уп.'
),
9 => array(
'NAME' => 'Алебастр',
'PRICE' => 300,
'QUANTITY' => 5,
'UNIT' => 'уп.'
),
);
$obj = new Excphp();
$obj->genDoc($arrToWrite);
Добавили ассоциативный массив, с данными, на практике что-то подобное будет приходить из базы или по API. Этот массив передаем в качестве параметра, методу genDoc
Изменения произошли и в файле класса Excphp.php. Его новый код:
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Excphp{
public function genDoc($arrToWrite)
{
// Создаем новый Spreadsheet объект
$spreadsheet = new Spreadsheet();
// Подключение к активной таблице
$sheet = $spreadsheet->getActiveSheet();
// Объединяем ячейки от A1:F1
$sheet->mergeCells('A1:F1');
// Устанавливаем значение ячейке A1
$sheet->setCellValue('A1', 'Стоимость строительный работ');
// Установка значений в шапку таблицы
$sheet->setCellValue('A2', '№ п/п');
$sheet->setCellValue('B2', 'Наименование работ/материалов');
$sheet->setCellValue('C2', 'Ед. изм');
$sheet->setCellValue('D2', 'Кол-во');
$sheet->setCellValue('E2', 'Стоимость');
$sheet->setCellValue('F2', 'Сумма');
// получаем номер последней строки с записью и прибавляем 1, чтобы писать на следующей строке
$highestRow = $sheet->getHighestRow() + 1;
// цикл по массиву, наполняем таблицу
foreach($arrToWrite as $key => $value){
$sheet->setCellValue('A$highestRow', '$key');
$sheet->setCellValue('B$highestRow', '{$value['NAME']}');
$sheet->setCellValue('C$highestRow', '{$value['UNIT']}');
$sheet->setCellValue('D$highestRow', '{$value['QUANTITY']}');
$sheet->setCellValue('E$highestRow', '{$value['PRICE']}');
$sheet->setCellValue('F$highestRow', '=D$highestRow*E$highestRow');
// увеличиваем значение последней линии
$highestRow ++;
}
// Выведем внизу таблицы итого, еще раз считаем последнюю строку и прибавим единицу
$highestRow = $sheet->getHighestRow() + 1;
// Объединяем ячейки от A:E
$sheet->mergeCells('A$highestRow:E$highestRow');
// В объединенную ячейку запишем слово 'итого'
$sheet->setCellValue('A$highestRow', 'Итого');
// запишем формулу с суммой, по колонке F, в этом же ряду
// мы знаем, что начинается наполняться данными таблица с 3-го ряда
$SUMRANGE = 'F3:F' . ($highestRow - 1);
$sheet->setCellValue('F$highestRow', '=SUM($SUMRANGE)');
// увеличим ширину ячейки B (Наименование работ/материалов)
$sheet->getColumnDimension('B')->setWidth(70);
// стилевое оформление шапки таблицы
// подготовка массива для задания стилей
$styleArray = [
'font' => [
'bold' => true,
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
],
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
],
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
'color' => [
'rgb' => 'FFFFFF',
],
],
];
// Установка стилей для строки
$spreadsheet->getActiveSheet()->getStyle('A2:F2')->applyFromArray($styleArray);
// получение даты, будет использоваться в имени файла
$dt = date('h:i:s');
// создание объекта Xlsx
$writer = new Xlsx($spreadsheet);
// если требуется сохранять файл на сервер, то раскомментируйте строку ниже
// $writer->save('file-$dt.xlsx');
// отправка файла в браузер
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename='file-'.$dt.'.xlsx'');
$writer->save('php://output');
}
}
В новой редакции файла Excphp.php метод genDoc принимает в качестве аргумента массив, из которого в цикле заполняется таблица. Для наполнения в цикле, был применен метод getHighestRow(), который возвращает номер последней строки, куда были отправлены данные, очень полезный метод, зная это, мы распечатываем массив работ на следующей строке, по своим ячейкам, прибавляя 1 на каждой итерации (инкремент ++). Ячейки F заполняются математическим выражением. Шапка таблицы осталась статичной, к ней были применены стили. В коде я оставил комментарии почти на каждой строчке кода, но если вдруг будут вопросы, пишите в комментариях к статье, постараюсь помочь.
В результате, при открытии файла index.php в браузере, у Вас должен автоматически скачиваться excel документ, как на gif-ке ниже:
Скачать новый пример, можно по ссылке ниже.
Скачать исходники пример 2
После скачивания, распакуйте архив с примером, в директорию с проектом и запустите две команды, в консоле :
- composer require
- composer dump-autoload -o
Пробуйте и тестируйте!
Полный список возможного функционала можно посмотреть по ссылке https://phpspreadsheet.readthedocs.io/en/latest/
Статья будет дополнена, по мере поступления вопросов и комментариев. Всем успехов в разработке!
Дополнение к статье, видео процесса, как сделать excel в php
Рекламный блок, для развития проекта