PHP

PHPExcel. Мануал по созданию и форматированию excel файлов

К сожалению разработчики прекратили поддержку и разработку проекта, но PHPExcel все равно остается популярной библиотекой которая выполняет свои задачи, последняя версия нормально работает на PHP 7.

Проект на GitHub, архив 1.8.1.

1

Создание документа

require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel.php';
require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php';

$xls = new PHPExcel();

Если нужно открыть существующий файл:

require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel.php';
require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php';
require_once __DIR__ . '/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';

$xls = PHPExcel_IOFactory::load(__DIR__ . '/file.xlsx');

Установка сводки документа:

$xls->getProperties()->setTitle("Название");
$xls->getProperties()->setSubject("Тема");
$xls->getProperties()->setCreator("Автор");
$xls->getProperties()->setManager("Руководитель");
$xls->getProperties()->setCompany("Организация");
$xls->getProperties()->setCategory("Группа");
$xls->getProperties()->setKeywords("Ключевые слова");
$xls->getProperties()->setDescription("Примечания");
$xls->getProperties()->setLastModifiedBy("Автор изменений");
$xls->getProperties()->setCreated("25.03.2019");

Защита книги паролем:

$xls->getActiveSheet()->getProtection()->setSheet(true);
$xls->getActiveSheet()->getProtection()->setSort(true);
$xls->getActiveSheet()->getProtection()->setInsertRows(true);
$xls->getActiveSheet()->getProtection()->setFormatCells(true);
$xls->getActiveSheet()->getProtection()->setPassword('123456');

Создаем новый лист, далее работаем с ним через переменную $sheet.

$xls->setActiveSheetIndex(0);
$sheet = $xls->getActiveSheet();
$sheet->setTitle('Название листа');

Параметры печати

// Формат
$sheet->getPageSetup()->SetPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

// Ориентация
// ORIENTATION_PORTRAIT — книжная
// ORIENTATION_LANDSCAPE — альбомная
$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

// Поля
$sheet->getPageMargins()->setTop(1);
$sheet->getPageMargins()->setRight(0.75);
$sheet->getPageMargins()->setLeft(0.75);
$sheet->getPageMargins()->setBottom(1);

// Верхний колонтитул
$sheet->getHeaderFooter()->setOddHeader("Название листа");

// Нижний колонтитул
$sheet->getHeaderFooter()->setOddFooter('&L&B Название листа &R Страница &P из &N');

Запись в ячейку и ее формат

$sheet->setCellValue("A1", "Значение");

Формат ячеек определяется автоматически, иногда это вызывает проблемы. Например, значения 1., 10.0, 0.1 выведутся как 1, 10, 0,1. Чтобы это исправить нужно использовать метод setCellValueExplicit() в место setCellValue().

$sheet->setCellValueExplicit("A1", '1.', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit("A2", '10.0', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setCellValueExplicit("A3", '0.1', PHPExcel_Cell_DataType::TYPE_STRING);
2

Размеры ячеек

Ширина столбцов

// Ширина столбца A 
$sheet->getColumnDimension("A")->setWidth(100);

// Авто ширина колонки по содержимому
$sheet->getColumnDimensionByColumn("A")->setAutoSize(true);

Высота строк

// Высота 1-й строки
$sheet->getRowDimension("1")->setRowHeight(50);

Вставленный в ячейку длинный текст будет выходить за ее пределы, переносы \r\n работать не будут.

$sheet->setCellValue("A1", "Excel — программа \r\n для работы с электронными таблицами");

Метод setWrapText(true) включает переносы строк и авто высоту.

$sheet->setCellValue("A1", "Excel — программа \r\n для работы с электронными таблицами");
$sheet->getStyle("A1")->getAlignment()->setWrapText(true);
3

Объединение ячеек

// Объединение ячеек в колонке
$sheet->mergeCells("A1:A6");
$sheet->setCellValue("A1", "A1:A6");

// Объединение ячеек в строке
$sheet->mergeCells("C2:I2");
$sheet->setCellValue("C2", "C2:I2");

// Объединение ячеек по диапазону
$sheet->mergeCells("C4:I6");
$sheet->setCellValue("C4", "C4:I6");
Объединение ячеек PHPExcel
4

Стили текста

// Шрифт Times New Roman
$sheet->getStyle('A1')->getFont()->setName('Times New Roman');

// Размер шрифта 18
$sheet->getStyle("A2")->getFont()->setSize(18);

// Цвет шрифта
$sheet->getStyle("A3")->getFont()->getColor()->setRGB('ff0000');

// Жирный
$sheet->getStyle("A4")->getFont()->setBold(true);

// Курсив
$sheet->getStyle("A5")->getFont()->setItalic(true);

// Подчеркнутый текст
$sheet->getStyle("A6")->getFont()->setUnderline(true);

// Зачеркнутый текст
$sheet->getStyle("A7")->getFont()->setStrikethrough(true);

Стили текста PHPExcel

Также можно задать сразу несколько стилей для ячейки, массивом:

$style = array(
    'font' => array(
        'name'      => 'Times New Roman',
        'size'      => 18,   
        'color'     => array('rgb' => 'FF0000'),              
        'bold'      => true,
        'italic'    => true,
        'underline' => true,
        'strike'    => true,
    )
);

$sheet->getStyle('A1')->applyFromArray($style);

Установить стили шрифта для всего документа:

$sheet->getDefaultStyle()->getFont()->setName('Times New Roman');
$sheet->getDefaultStyle()->getFont()->setSize(18);

Или:

$style = array(
    'font' => array(
        'name' => 'Times New Roman',
        'size' => 18,  
    )
);

$sheet->getDefaultStyle()->applyFromArray($style);
5

Выравнивание в ячейке

По горизонтали:

// По левому краю
$sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

// По центру
$sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

// По правому краю
$sheet->getStyle("A1")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

По вертикали:

// Сверху
$sheet->getStyle("A1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

// По центру
$sheet->getStyle("A1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

// Снизу
$sheet->getStyle("A1")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_BOTTOM);
6

Фон ячейки

Стили фона устанавливаются массивом значений, type – определяет стиль заливки, далее в зависимости от выбранного стиля задаются его настройки в параметрах color, startcolor, endcolor, rotation.

PHPExcel_Style_Fill::FILL_SOLID:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => '01B050')
    )
);
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_SOLID

PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
        'rotation' => 90
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR

PHPExcel_Style_Fill::FILL_GRADIENT_PATH:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_GRADIENT_PATH,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_GRADIENT_PATH

PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKDOWN

PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKGRAY

PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKGRID

PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKHORIZONTAL

PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKTRELLIS

PHPExcel_Style_Fill::FILL_PATTERN_DARKUP:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKUP,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKUP

PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_DARKVERTICAL

PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_GRAY0625

PHPExcel_Style_Fill::FILL_PATTERN_GRAY125:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_GRAY125,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_GRAY125

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTDOWN

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRAY

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTGRID

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTHORIZONTAL

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTTRELLIS

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTUP

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_LIGHTVERTICAL

PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY:

$bg = array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY,
        'startcolor' => array('rgb' => '01B050'),
        'endcolor' => array('rgb' => 'f1ee3b'),
    )
);                 
$sheet->getStyle("B2")->applyFromArray($bg);

PHPExcel_Style_Fill::FILL_PATTERN_MEDIUMGRAY

7

Границы

Стили линий задаются костантами:

Вид Константа
  PHPExcel_Style_Border::BORDER_NONE
PHPExcel_Style_Border::BORDER_THIN
PHPExcel_Style_Border::BORDER_MEDIUM
PHPExcel_Style_Border::BORDER_THICK
PHPExcel_Style_Border::BORDER_DOUBLE
PHPExcel_Style_Border::BORDER_HAIR
PHPExcel_Style_Border::BORDER_DOTTED
PHPExcel_Style_Border::BORDER_DASHED
PHPExcel_Style_Border::BORDER_DASHDOT
PHPExcel_Style_Border::BORDER_DASHDOTDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHDOTDOT
PHPExcel_Style_Border::BORDER_MEDIUMDASHED
PHPExcel_Style_Border::BORDER_SLANTDASHDOT

Внешняя рамка у ячеек

$border = array(
    'borders'=>array(
        'outline' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        ),
    )
);

$sheet->getStyle("B2:J5")->applyFromArray($border);

Внешняя рамка у ячеек

Внутриния рамка у ячеек

$border = array(
    'borders'=>array(
        'inside' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        ),
    )
);

$sheet->getStyle("B2:J5")->applyFromArray($border);

Внутриния рамка у ечеек

Таблица

$border = array(
    'borders'=>array(
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);

$sheet->getStyle("B2:J5")->applyFromArray($border);

Таблица

Таблица с жирной рамкой

$border = array(
    'borders'=>array(
        'outline' => array(
            'style' => PHPExcel_Style_Border::BORDER_THICK,
            'color' => array('rgb' => '000000')
        ),
        'allborders' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);

$sheet->getStyle("B2:J5")->applyFromArray($border);

Таблица с жирной рамкой

Отдельные линии

            case '':
            case 'bottom':
            case 'diagonal':
            case 'horizontal':
            case '':
            case 'left':
            case '':
            case 'right':
            case 'top':
            case 'vertical':

Сверху:

$border = array(
    'borders'=>array(
        'top' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);
$sheet->getStyle("B2")->applyFromArray($border);

Снизу:

$border = array(
    'borders'=>array(
        'bottom' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);
$sheet->getStyle("B4")->applyFromArray($border);

Слева:

$border = array(
    'borders'=>array(
        'left' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);
$sheet->getStyle("B6")->applyFromArray($border);

Справа:

$border = array(
    'borders'=>array(
        'right' => array(
            'style' => PHPExcel_Style_Border::BORDER_THIN,
            'color' => array('rgb' => '000000')
        )
    )
);
$sheet->getStyle("B8")->applyFromArray($border);
Отдельные линии
8

Изображения

$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setResizeProportional(false);  
$objDrawing->setName('Название картинки');
$objDrawing->setDescription('Описание картинки');
$objDrawing->setPath(__DIR__ . '/logo.png');
$objDrawing->setCoordinates('B2');                      
$objDrawing->setOffsetX(10); 
$objDrawing->setOffsetY(10);                
$objDrawing->setWidth(163); 
$objDrawing->setHeight(50); 
$objDrawing->setWorksheet($sheet);

Изображения в PHPExcel

9

Гиперссылки

$sheet->setCellValue("A1", "Ссылка на example.com");
$sheet->getCell("A1")->getHyperlink()->setUrl("http://example.com");
$sheet->getCell("A1")->getHyperlink()->setTooltip('Подсказка');

// У текста нужно сделать синий цвет и подчеркивание
$sheet->getStyle("A1")->applyFromArray(
    array(
        'font' => array(
            'color' => array(
                'rgb' => '0000FF'
            ), 
            'underline' => 'single'
        )
    )
);

Гиперссылка в PHPExcel

10

Формулы

Русские названия функций не поддерживаются, придется использовать латинские аналоги.

$sheet->setCellValue("A3", "=SUM(A1:A2)");
СУММ SUM Cуммирование значений в ячейках
ЕСЛИ IF Условие
ПРОСМОТР LOOKUP Поиск по значению
ВПР VLOOKUP Поиск значения по диапазону
ПОИСКПОЗ MATCH Поиск положенияв диапазоне ячеек
ВЫБОР CHOOSE Выбор одного значения из списка
ДАТА DATE Возвращает порядковый номер определенной даты
ДНИ DAYS Возвращает число дней между двумя датами
НАЙТИ, НАЙТИБ FIND, FINDB Поиск вхождения одной строки в другую
ИНДЕКС INDEX Возвращает значение или ссылку на него из таблицы или диапазона
11

Сохранение

XLSX

Отдача на скачивание:

header("Expires: Mon, 1 Apr 1974 05:00:00 GMT");
header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header("Cache-Control: no-cache, must-revalidate");
header("Pragma: no-cache");
header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment; filename=file.xlsx");

$objWriter = new PHPExcel_Writer_Excel2007($xls);
$objWriter->save('php://output'); 
exit();    

Сохранение в файл:

$objWriter = new PHPExcel_Writer_Excel2007($xls);
$objWriter->save(__DIR__ . '/file.xlsx');

XLS

Отдача на скачивание:

header("Expires: Mon, 1 Apr 1974 05:00:00 GMT");
header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header("Cache-Control: no-cache, must-revalidate");
header("Pragma: no-cache");
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=file.xls");
    
$objWriter = new PHPExcel_Writer_Excel5($xls);
$objWriter->save('php://output'); 

Сохранение в файл:

$objWriter = new PHPExcel_Writer_Excel5($xls);
$objWriter->save(__DIR__ . '/file.xlsx');
12 июня 2019
В последнее время письма отправляемые с хостингов через функции mail() и mb_send_mail() часто попадают в спам или...
cURL PHP – это библиотека предназначенная для получения и передачи данных через такие протоколы, как HTTP, FTP, HTTPS....
В статье приведены основные примеры работы с расширением PHP PDO. Такие как подключение к БД, получение, изменение и...
Библиотека GD дает возможность работать с изображениями в PHP. Далее представлены примеры как изменить размер, вырезать...