PHP библиотека PHPExcel для работы с Excel

Для работы с Excel использовал библиотеку PHPExcel. Установка простейшая – кладем папку Classes в нужную папку на сервере, указываем корректные пути в include/require.

Примеры кода по чтению/генерации файлов Excel можно посмотреть на github странице библиотеки.

 

Красивости

и этим не ограничивается функционал, это лишь то, что использовал:

  • mergeCells(“cell_range”) – Объединение указанных ячеек в одну. Данные должны лежать в первой ячейке, иначе они теряются.
$doc->getActiveSheet()->mergeCells("B3:C3");
  • setSize(16) – Делаем размер шрифта 16 для указанных ячеек.
$doc->getActiveSheet()->getStyle('B2:G2')->getFont()->setSize(16);
  • setBold(true) – Делаем текст “жирным”
$doc->getActiveSheet()->getStyle('A1:O1')->getFont()->setBold(true);
  • setWrapText(true) – Делаем перенос слов по умолчанию для всех ячеек
$doc->getDefaultStyle()->getAlignment()->setWrapText(true);
$doc->getDefaultStyle()->getAlignment()->setWrapText(false); #disable
  • setAutoFilter – Включить фильтр по умолчанию
$doc->getActiveSheet()->setAutoFilter('A1:K1');
  • freezePane – Закрепить какие либо строки, например первую
$doc->getActiveSheet()->freezePane('A2');
  • borders – делается через создание стиля, а потом его применение на указанный диапазон ячеек
$styleBorder = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN
    )
  )
);


$doc->getActiveSheet()->getStyle('B2:G2')->applyFromArray($styleBorder);
  • color – Аналогично с помощью стилей меняем цвет шрифта (Font)
$styleFontColor = array(
  'font' => array(
   'color' => array('rgb' => 'FF0000'),
));
$doc->getActiveSheet()->getStyle('D10')->applyFromArray($styleFontColor);
  • setARGB – Изменить цвет ячейки, например
    • всей первой строки
    • конкретной ячейки (делал так цвет был переменным и задавался на основе данных – формировался разноцветный показательный Excel)
    • диапазона ячеек по диагонали
$doc->getActiveSheet()->getStyle('A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE8E5E5');
$doc->getActiveSheet()->getStyle("A$val:$last_column$val")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE8E5E5');

$doc->getActiveSheet()->getStyle('I1:Z1000')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFE8E5E5');
ЧТЕНИЕ

Код для чтения (два столбца):

<!doctype>
<html>
<head>
</head>
<body>
<?php
require_once "Classes/PHPExcel.php";
$tmpfname = "test.xlsx";
$excelReader = PHPExcel_IOFactory::createReaderForFile($tmpfname);
$excelObj = $excelReader->load($tmpfname);
$worksheet = $excelObj->getSheet(0);
$lastRow = $worksheet->getHighestRow();
echo "<table>";
for ($row = 1; $row <= $lastRow; $row++) {
echo "<tr><td>";
echo $worksheet->getCell('A'.$row)->getValue();
echo "</td><td>";
echo $worksheet->getCell('B'.$row)->getValue();
echo "</td><tr>";
}
echo "</table>";
?>
</body>
</html>
Редактирование

Открываем файл  test.xlsx, на его основе создаем новый  new.xlsx с измененными парой ячеек.

<?php 
error_reporting(E_ALL);
require_once('Classes/PHPExcel.php');
$excel2 = PHPExcel_IOFactory::createReader('Excel2007'); 
$excel2 = $excel2->load('test.xlsx');
$excel2->setActiveSheetIndex(0); 
$excel2->getActiveSheet()->setCellValue('J103', 'LOLOLOLOL') 
 ->setCellValue('O103', 'LOLOLOLOL') 
 ->setCellValue('Q103', 'LOLOLOLOL');
$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007'); 
$objWriter->save('new.xlsx'); 
?>

 

ГЕНЕРАЦИЯ

Пример генерации на основе результата MySQL (не тестил, использовал универсальную функцию ниже).

Если нужно протестить базовую работу генерации на основе двумерного массива

<!doctype>
<html>
<head>
</head>
<body>
<?php

require_once('Classes/PHPExcel.php');

$dataArray = array(
 array(
 'str1raw1',
 'str1raw2',
 'str1raw3',
 'str1raw4',
 ),
 array(
 'str2raw1',
 'str2raw2',
 'str2raw3',
 'str2raw4',
 )
);

// FILENAME
$filename = "result.xlsx";

// create php excel object
$doc = new PHPExcel();

// set active sheet 
$doc->setActiveSheetIndex(0);

// read data to active sheet
$doc->getActiveSheet()->fromArray($dataArray);

//save our workbook as this file name
//mime type
//OLD EXCEL header('Content-Type: application/vnd.ms-excel'); 
//NEW EXCEL
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//tell browser what's the file name
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0'); //no cache
// clean data
ob_end_clean();
//OLD EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, 'Excel5');
//NEW EXCEL 
$objWriter = PHPExcel_IOFactory::createWriter($doc, 'Excel2007');

//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
exit;

?>

</body>
</html>

Пример генерации xls из двумерного массива с настройками

  • имени (определяется на основе значения в переменной $_POST[‘filename’]),
  • ширины столбца (на основе $_POST[‘excelSettings’]),
  • bold первой строки (setBold),
  • переноса слов (setWrapText).
<!doctype>
<html>
<head>
</head>
<body>
<?php

require_once('Classes/PHPExcel.php');
//$dataArray = unserialize($_POST['ArrayData']);
$dataArray = json_decode($_POST['ArrayData']);

//print_r($_POST);//print out the whole post

// FILENAME
if (isset($_POST['filename'])) $filename = $_POST['filename'];
//OLD EXCEL $filename = "result.xls";
//NEW EXCEL 
//$filename = "result.xlsx";

// create php excel object
$doc = new PHPExcel();

// set active sheet 
$doc->setActiveSheetIndex(0);

// read data to active sheet
$doc->getActiveSheet()->fromArray($dataArray);

// set bold for first string
$doc->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
// set wrap to all columns
$doc->getDefaultStyle()->getAlignment()->setWrapText(true);

// set column width based on POST
if (isset($_POST['excelSettings'])) $excelSettings = $_POST['excelSettings'];
if ($excelSettings == 'default') 
{
 $doc->getActiveSheet()->getColumnDimension('A')->setWidth(17);
 $doc->getActiveSheet()->getColumnDimension('B')->setWidth(40);
 $doc->getActiveSheet()->getColumnDimension('C')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('D')->setWidth(40);
 $doc->getActiveSheet()->getColumnDimension('E')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('F')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('G')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('H')->setWidth(15);
}
if ($excelSettings == '<vashnaya_tablica_1>') 
{
 $doc->getActiveSheet()->getColumnDimension('A')->setWidth(17);
 $doc->getActiveSheet()->getColumnDimension('B')->setWidth(30);
 $doc->getActiveSheet()->getColumnDimension('C')->setWidth(40);
 $doc->getActiveSheet()->getColumnDimension('D')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('E')->setWidth(15);
 $doc->getActiveSheet()->getColumnDimension('F')->setWidth(80);
}
if ($excelSettings == '<vashnaya_tablica_2>') 
{
 $doc->getActiveSheet()->getColumnDimension('A')->setWidth(20);
 $doc->getActiveSheet()->getColumnDimension('C')->setWidth(40);
}

//save our workbook as this file name
//mime type
//OLD EXCEL header('Content-Type: application/vnd.ms-excel'); 
//NEW EXCEL
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//tell browser what's the file name
header('Content-Disposition: attachment;filename="' . $filename . '"');
header('Cache-Control: max-age=0'); //no cache
// clean data
ob_end_clean();
//OLD EXCEL $objWriter = PHPExcel_IOFactory::createWriter($doc, 'Excel5');
//NEW EXCEL 
$objWriter = PHPExcel_IOFactory::createWriter($doc, 'Excel2007');

//force user to download the Excel file without writing it to server's HD
$objWriter->save('php://output');
exit;

?>

</body>
</html>

Пример вызова и код по генерации кнопки, добавлению к названию файла даты/времени (формат 20170123_003800_Название.xlsx) и переходу на страницу генерации xls.

function create_xls($dataArray,$name,$excelSettings = "default")
{
 //$serialized = htmlspecialchars(serialize($dataArray));
 $serialized = htmlspecialchars(json_encode($dataArray));
 $date = date("Ymd");
 $time = date("Gis");
 $filename = "$date-$time-$name.xlsx";
 echo "<form action=create_xls.php method=\"post\">";
 echo "<input type=submit name=s_button value=\"Выгрузить\" id=\"submit\">";
 echo "<input type=\"hidden\" name=\"ArrayData\" value=\"$serialized\"/>";
 echo "<input type=\"hidden\" name=\"filename\" value=\"$filename\"/>";
 echo "<input type=\"hidden\" name=\"excelSettings\" value=\"$excelSettings\"/>";
 echo "</form>";
}

Вызов функции (про функцию iconv_for_xls ниже):

$array_for_xls = array(); 
array_push($array_for_xls,array(iconv_for_xls("Тест"),iconv_for_xls("Тест2"))); 
create_xls($array_for_xls,"Название файла","Тип шаблона настроек для excel, например vashnaya_tablica_1");

 

Особенности

Мусор

Нужно очень внимательно смотреть, чтобы php не генерировал никакого вывода, в противном случае возможны разные ошибки и искажения файла.

Например, возможны кракозябры при генерации файла больше 20-40 строк, если оставить включенным debug типа print_r($_POST); (почему не воспроизводиться при генерации мелких файлов – это вопрос :)).

Аналогично, будет выдавать ошибку:

"Не удаётся открыть файл "название файла. xlsx", так как формат или расширение этого файла являются недопустимыми."

"Excel cannot open the file "____" because the file format or file extension not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".
  1. Если в конце кода php нет “exit;”
  2. Если перед генерацией файла нет очистки буфера вывода через “ob_end_clean();”
  3. Если используется генерация xlsx (Excel2007), но не установлены xml и xmlwriter модули PHP. Так же может выдаваться ошибка “Fatal error: Class ‘XMLWriter’ not found in /<path>/XMLWriter.php on line 37” т.к. библиотека PHPExcel использует наследование класса от XMLWriter (“class PHPExcel_Shared_XMLWriter extends XMLWriter”), т.е. требует установленного модуля xmlwriter. Для “нормальных” дистрибутивов это делается простым sudo apt-get/yum install php-xmlwriter (или php5-xml) и перезагрузкой apache, для gentoo это делается через пересборку всего php с новым модулем.
# php -m | grep xml
 libxml
 xml
 А должно быть:
 # php -m | grep xml
 libxml
 xml
 xmlreader
 xmlwriter

Отправка array на другую страницу

Чтобы функция по генерации xlsx была универсальной, сделал отдельную страницу по генерации, но на эту страницу потребовалось передать двумерный массив. В интернете гуляет два варианта решения: сохранить массив в сессии/куках, передать его через json (лучше) или serialize (хуже).

Через сессии все передавалось, только значение почему то не апдейтилось корректно. Сходу не разобрался в причинах, использовал сначала serialize (полный пример см. в function create_xls), но потом мигрировал на json из-за периодических проблем со спец. символами serialize.

SESSION
 session_start();
 $_SESSION['dataArray'] = $dataArray;

SERIALIZE
 //$serialized = htmlspecialchars(serialize($dataArray));
 $serialized = htmlspecialchars(json_encode($dataArray));
 echo "<form action=create_xls.php method=\"post\">";
 echo "<input type=submit name=s_button value=\"Выгрузить\" id=\"submit\">";
 echo "<input type=\"hidden\" name=\"ArrayData\" value=\"$serialized\"/>";
 echo "</form>";

Кодировка

Если на сайте кодировка cp-1251, то при генерации обязательно нужно использовать iconv в utf-8. В противном случае вместо русских символов в ячейке будет бред (например, “ИСТИНА”).

function iconv_for_xls($string) 
{ 
 iconv('windows-1251', 'utf-8', "$string") 
}

array_push($array_for_xls,array(iconv_for_xls("ТЕСТ"),iconv_for_xls("ТЕСТ2")));

Leave a Reply