Работа с Excel средствами PHP

PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.

Установка PHPExcel

Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:

• Classes
• Documentation
• Tests
• changelog.txt
• install.txt
• license.txt

Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.

В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

require_once 'Classes/PHPExcel.php';
$pExcel = new PHPExcel();

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();

С помощью метода setActiveSheetIndex(0) указываем индекс (номер) активного листа. Нумерация листов начинается с нуля. Далее с помощью метода getActiveSheet() получаем объект этого активного листа, то есть другими словами получаем доступ к нему для работы. И сохраняем этот объект в переменную $aSheet.

Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:

$pExcel->createSheet();

Затем, по аналогии, указываем индекс и получаем объект активного листа.

// Ориентация страницы и  размер листа
$aSheet->getPageSetup()
       ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$aSheet->getPageSetup()
       ->SetPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
// Поля документа
$aSheet->getPageMargins()->setTop(1);
$aSheet->getPageMargins()->setRight(0.75);
$aSheet->getPageMargins()->setLeft(0.75);
$aSheet->getPageMargins()->setBottom(1);
// Название листа
$aSheet->setTitle('Прайс-лист');
// Шапка и футер (при печати)
$aSheet->getHeaderFooter()
       ->setOddHeader('&CТД ТИНКО: прайс-лист');
$aSheet->getHeaderFooter()
       ->setOddFooter('&L&B'.$aSheet->getTitle().'&RСтраница &P из &N');
// Настройки шрифта
$pExcel->getDefaultStyle()->getFont()->setName('Arial');
$pExcel->getDefaultStyle()->getFont()->setSize(8);

Вначале задаем ориентацию листа при помощи метода setOrientation(), которому передаем константу класса PHPExcel_Worksheet_PageSetup:

• ORIENTATION_PORTRAIT — книжная
• ORIENTATION_LANDSCAPE — альбомная

Обратите внимание, что перед методом setOrientation() необходимо вызвать метод getPageSetup(), который предоставляет доступ к настройкам страницы.

Далее вызываем метод SetPaperSize(), который позволяет задать размер страницы для печати. Ему передаем параметром константу PAPERSIZE_A4 класса PHPExcel_Worksheet_PageSetup. Что означает, что размер листа страницы будет установлен А4.

Далее устанавливаем поля документа, то есть отступы от краев документа. Отступы задаются в специальных символьных единицах. Вначале, обратите внимание, вызываем у объекта $aSheet метод getPageMargins(), который вернет объект класса, отвечающего за настройки полей страницы. Затем вызываем методы setTop(), setRight(), setLeft() и setBottom().

Далее при помощи метода setTitle('Прайс лист') задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

• setOddHeader();
• setOddFooter();

Обратите внимание на передаваемые параметры:

• для шапки передаем строку '&CТД ТИНКО: прайс-лист'; метка &C означает, что текст нужно расположить по центру.
• для подвала передаем строку '&L&B'.$aSheet->getTitle().'&RСтраница &P из &N'; это означает, что нужно вывести слева и жирным шрифтом (&L&B) название листа (метод $aSheet->getTitle()), затем справа (&R) вывести номер страницы (&P) из общего количества страниц (&N).

Затем указываем настройки шрифта по умолчанию:

• setName('Arial') — задаем имя шрифта;
• setSize(8) — задаем размер шрифта.

Наполнение документа данными

Для начала давайте зададим ширину столбцов (в символьных единицах), которые нам понадобятся:

$aSheet->getColumnDimension('A')->setWidth(3);
$aSheet->getColumnDimension('B')->setWidth(7);
$aSheet->getColumnDimension('C')->setWidth(20);
$aSheet->getColumnDimension('D')->setWidth(40);
$aSheet->getColumnDimension('E')->setWidth(10);

Теперь заполним несколько ячеек текстом:

$aSheet->mergeCells('A1:E1');
$aSheet->getRowDimension('1')->setRowHeight(20);
$aSheet->setCellValue('A1','ТД ТИНКО');
$aSheet->mergeCells('A2:E2');
$aSheet->setCellValue('A2','Поставка технических средств безопасности');
$aSheet->mergeCells('A4:C4');
$aSheet->setCellValue('A4','Дата создания прайс-листа');

Здесь мы сначала объеденяем ячейки с А1 до E1 при помощи метода mergeCells(), далее задаем высоту строки: вначале получаем доступ к строке 1 при помощи метода getRowDimension('1'), затем задаем высоту — setRowHeight(20). Далее при помощи метода setCellValue('A1','ТД ТИНКО'), устанавливаем значение ячейки А1.

Далее давайте в ячейку D4 запишем текущую дату:

// Записываем данные в ячейку
$date = date('d-m-Y');
$aSheet->setCellValue('D4',$date);
// Устанавливает формат данных в ячейке (дата вида дд-мм-гггг)
$aSheet->getStyle('D4')->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14);

Теперь, используя метод setCellValue(), а также цикл while() наполним данными наш прайс-лист:

mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_query('SET NAMES utf8');
mysql_select_db(DB_NAME);

// Создаем шапку таблички данных
$aSheet->setCellValue('A6','№');
$aSheet->setCellValue('B6','Код');
$aSheet->setCellValue('C6','Наименование');
$aSheet->setCellValue('D6','Описание');
$aSheet->setCellValue('E6','Цена');

$query = "SELECT `code`, `name`, `description`, `price` FROM `products` WHERE 1 LIMIT 10";
$res = mysql_query( $query );

$i = 1;
while( $prd = mysql_fetch_assoc($res) ) {
    $aSheet->setCellValue('A'.($i+6), $i);
    $aSheet->setCellValue('B'.($i+6), $prd['code']);
    $aSheet->setCellValue('C'.($i+6), $prd['name']);
    $aSheet->setCellValue('D'.($i+6), $prd['description']);
    $aSheet->setCellValue('E'.($i+6), $prd['price']);
    $i++;
}

Стилизация данных

Давайте немного украсим наш прайс-лист, то есть каждой ячейке добавим стилей. Для этого необходимо создать массив со стилями и при помощи метода applyFromArray(), применить этот массив к ячейке (или ячейкам):

// массив стилей
$style_wrap = array(
    // рамки
    'borders'=>array(
        // внешняя рамка
        'outline' => array(
            'style'=>PHPExcel_Style_Border::BORDER_THICK,
            'color' => array(
                'rgb'=>'006464'
            )
        ),
        // внутренняя
        'allborders'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN,
            'color' => array(
                'rgb'=>'CCCCCC'
            )
        )
    )
);

$aSheet->getStyle('A1:F'.($i+5))->applyFromArray($style_wrap);

Теперь, по аналогии, применим стили к остальным ячейкам:

// Стили для верхней надписи (первая строка)
$style_header = array(
    // Шрифт
    'font'=>array(
        'bold' => true,
        'name' => 'Times New Roman',
        'size' => 15,
        'color'=>array(
            'rgb' => '006464'
        )
    ),
    // Выравнивание
    'alignment' => array(
        'horizontal' => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
    ),
    // Заполнение цветом
    'fill' => array(
        'type' => PHPExcel_STYLE_FILL::FILL_SOLID,
        'color'=>array(
            'rgb' => '99CCCC'
        )
    ),
    'borders'=>array(
        'bottom'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN,
            'color' => array(
                'rgb'=>'006464'
            )
        )
    )
);
$aSheet->getStyle('A1:E1')->applyFromArray($style_header);

// Стили для слогана компании (вторая строка)
$style_slogan = array(
    // шрифт
    'font'=>array(
        'bold' => true,
        'italic' => true,
        'name' => 'Times New Roman',
        'size' => 12,
        'color'=>array(
            'rgb' => '006464'
        )
    ),
    // выравнивание
    'alignment' => array(
        'horizontal' => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
        'vertical' => PHPExcel_STYLE_ALIGNMENT::VERTICAL_CENTER,
    ),
    // заполнение цветом
    'fill' => array(
        'type' => PHPExcel_STYLE_FILL::FILL_SOLID,
        'color'=>array(
            'rgb' => '99CCCC'
        )
    ),
    //рамки
    'borders' => array(
        'bottom' => array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN,
            'color' => array(
                'rgb'=>'006464'
            )
        )
    )
);
$aSheet->getStyle('A2:E2')->applyFromArray($style_slogan);

// Стили для текта возле даты
$style_tdate = array(
    // выравнивание
    'alignment' => array(
        'horizontal' => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_RIGHT,
    ),
    // заполнение цветом
    'fill' => array(
        'type' => PHPExcel_STYLE_FILL::FILL_SOLID,
        'color'=>array(
            'rgb' => 'EEEEEE'
        )
    ),
    // рамки
    'borders' => array(
        'right' => array(
            'style'=>PHPExcel_Style_Border::BORDER_NONE
        )
    )
);
$aSheet->getStyle('A4:D4')->applyFromArray($style_tdate);
 
// Стили для даты
$style_date = array(
    // заполнение цветом
    'fill' => array(
        'type' => PHPExcel_STYLE_FILL::FILL_SOLID,
        'color'=>array(
            'rgb' => 'EEEEEE'
        )
    ),
    // рамки
    'borders' => array(
        'left' => array(
            'style'=>PHPExcel_Style_Border::BORDER_NONE
        )
    ),
);
$aSheet->getStyle('E4')->applyFromArray($style_date);
 
// Стили для шапки таблицы (шестая строка)
$style_hprice = array(
    // выравнивание
    'alignment' => array(
    'horizontal' => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_CENTER,
    ),
    // заполнение цветом
    'fill' => array(
        'type' => PHPExcel_STYLE_FILL::FILL_SOLID,
        'color'=>array(
            'rgb' => 'CFCFCF'
        )
    ),
    // шрифт
    'font'=>array(
        'bold' => true,
        /* 'italic' => true, */
        'name' => 'Times New Roman',
        'size' => 10
    ),
);
$aSheet->getStyle('A6:E6')->applyFromArray($style_hprice);

// Cтили для данных в таблице прайс-листа
$style_price = array(
    'alignment' => array(
    'horizontal' => PHPExcel_STYLE_ALIGNMENT::HORIZONTAL_LEFT,
    )
);
$aSheet->getStyle('A7:E'.($i+5))->applyFromArray($style_price);

Сохранение документа

Осталось только сохранить наш документ:

/*
$objWriter = PHPExcel_IOFactory::createWriter($pExcel, 'Excel5');
$objWriter->save('simple.xls');
*/
$objWriter = PHPExcel_IOFactory::createWriter($pExcel, 'Excel2007');
$objWriter->save('simple.xlsx');

или так

/*
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
$objWriter->save('simple.xls');
*/
$objWriter = new PHPExcel_Writer_Excel2007($pExcel);
$objWriter->save('simple.xlsx');

Если нужно вывести документ в браузер

/*
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename="simple.xls"');
$objWriter = new PHPExcel_Writer_Excel5($pExcel);
*/
header('Content-Type:xlsx:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attachment;filename="simple.xlsx"');
$objWriter = new PHPExcel_Writer_Excel2007($pExcel);
$objWriter->save('php://output');

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

// формула для вычисления суммы
$formula = '=SUM(D2:D4)';
$aSheet->setCellValue('D5', $formula);

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

// Подключаем библиотеку
require_once 'Classes/PHPExcel.php';
$pExcel = PHPExcel_IOFactory::load('simple.xlsx');

// Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
    // выгружаем данные из объекта в массив
    $tables[] = $worksheet->toArray();
}

Теперь можно вывести массив:

// Цикл по листам Excel-файла
foreach( $tables as $table ) {
    echo '<table border="1">';
    // Цикл по строкам
    foreach($table as $row) {
        echo '<tr>';
        // Цикл по колонкам
        foreach( $row as $col ) {
            echo '<td>'.$col.'</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

Для получения значения отдельной ячейки:

// выбираем лист, с которым будем работать
$pExcel->setActiveSheetIndex(0);
$aSheet = $pExcel->getActiveSheet();
// получаем доступ к ячейке по номеру строки 
// (нумерация с единицы) и столбца (нумерация с нуля) 
$cell = $aSheet->getCellByColumnAndRow($col, $row);
// читаем значение ячейки
$value = $cell->getValue()

или так:

$value = $pExcel->getActiveSheet()->getCellValue('B2')

Еще два примера:

// Цикл по листам Excel-файла
foreach( $pExcel->getWorksheetIterator() as $worksheet ) {
    echo '<h2>Лист «'.$worksheet->getTitle().'»</h2>';
    echo '<table border="1">';
    // Цикл по строкам
    foreach( $worksheet->getRowIterator() as $row ) {
        echo '<tr>';
        // Цикл по колонкам
        foreach( $row->getCellIterator() as $cell ) {
            $value = $cell->getValue();
            // $calcValue = $cell->getCalculatedValue()
            // $dataType = PHPExcel_Cell_DataType::dataTypeForValue($value);
            echo '<td>'.$value.'</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

// Цикл по листам Excel-файла
foreach ($pExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // например, 10
    $highestColumn      = $worksheet->getHighestColumn(); // например, 'E'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns = ord($highestColumn) - 64;
    echo '<h2>Лист «'.$worksheetTitle.'» ';
    echo $nrColumns . ' колонок (A-' . $highestColumn . ') ';
    echo ' и ' . $highestRow . ' строк.</h2>';
    echo '<table border="1">';
    // Цикл по строкам
    for ($row = 1; $row <= $highestRow; $row++) {
        echo '<tr>';
        // Цикл по колонкам
        for ($col = 0; $col < $highestColumnIndex; $col++) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            echo '<td>'.$cell->getValue().'</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}

Дополнительно

• Документация разработчика PHPExcel на русском

Поиск: Excel • MS • PHP • Web-разработка

• Web-разработка
  • HTML и CSS
  • JavaScript и TypeScript
  • PHP и MySQL
  • CMS Битрикс
  • CMS WoprdPress
  • Yii2 и Laravel
  • Python и Flask
  • Web-аналитика
  • Разное
• ОС Linux
  • Команды
  • Сценарии
  • Разное
• 1С:Предприятие
  • Программирование
  • Язык запросов
  • Разное
• Локальная сеть
  • Разное

• 1С:Предприятие (31)
• API (29)
• Bash (43)
• CLI (126)
• CMS (139)
• CSS (50)
• Frontend (75)
• HTML (66)
• JavaScript (150)
• Laravel (72)
• Linux (177)
• MySQL (77)
• PHP (126)
• React.js (66)
• SSH (27)
• Ubuntu (71)
• Web-разработка (509)
• WordPress (73)
• Yii2 (69)
• БазаДанных (97)
• Битрикс (66)
• Блог (29)
• Верстка (43)
• ИнтернетМагаз… (84)
• КаталогТоваров (87)
• Класс (30)
• Клиент (31)
• Ключ (31)
• Команда (90)
• Компонент (60)
• Конфигурация (76)
• Корзина (32)
• ЛокальнаяСеть (33)
• Модуль (34)
• Навигация (31)
• Настройка (153)
• ПанельУправле… (29)
• Плагин (33)
• Пользователь (27)
• Практика (101)
• Сервер (86)
• Событие (28)
• Теория (106)
• Установка (77)
• Файл (51)
• Форма (58)
• Фреймворк (192)
• Функция (36)
• ШаблонСайта (68)

• Web-разработка
• 1С:Предприятие
• ОС Linux
• Регулярные выражения
• Разное