Зачем Excel
Иногда при разработке сайта на Битриксе возникает необходимость генерировать файлы Excel. Лично мне этот формат (т.е. родные форматы Excel) не нравится. Но менеджерам, зачастую, это ближе к сердцу, чем CSV. Как показывает практика, офисные сотрудники не всегда могут совладать с юникодом при открытии файлов CSV через MS Excel (вот, пора уже переходить на открытые аналоги, типа LibreOffice). Для таких вот запущенных случаев, нужно генерировать родные форматы Excel.
Из PHP генерацию фалов Excel можно делать с помощью распространённой библиотеки PHPExcel. Говорят, что эта библиотека не подходит для генерации больших и сложных документов. Но мне нужно было делать совсем маленькие Excel документы с простой таблицей и особым именем листа. Кстати, если вам не нужно давать листам названия, то можно поступить проще, — так же, как это делает сам Битрикс, — генерировать Excel документ через HTML. Но я использовал старую версию библиотеки PHPexcel — https://github.com/PHPOffice/PHPExcel, т.к. мне нужно было настроить работу на PHP 5.5. Вообще же лучше последовать рекомендациям разработчиков и использовать более новый вариант этой библиотеки — https://github.com/PHPOffice/PhpSpreadsheet
Проблема интеграции PHPExcel с Битриксом
Битрикс требует настройки mbstring.func_overload=2 и не работает с другими значениями. PHPExcel требует mbstring.func_overload=0 и не работает с другими значениями. Если на сервере используется Apache, то есть вариант настроить этот параметр в целом для хоста и задать отдельное значение для определённого каталога. Если у вас Apache, то дальше читать вам не обязателно. Но при использовании nginx + php-fpm задать mbstring.func_overload можно только для всего пула, без каких-либо исключений. Так как же быть в этом случае? При определённом стечении обстоятельств, PHPExcel нормально работает и с mbstring.func_overload=2. Проверено, что генерация работает успешно при настройке сайта на работу с UTF-8 и использовании XLSX формата. Нужно лишь отключить встроенную проверку mbstring.func_overload в библиотеке PHPExcel.
Отключаем проверку mbstring.func_overload
Для установки библиотек в Битриксе удобно использовать composer. Если composer установлен глобально, то можно выполнить команду установки:
composer require phpoffice/phpexcelЯ, например, это делал из каталога bitrix/php_interface/include/lib
В моём случае файл, в котором происходит проверка значения mbstring.func_overload — это bitrix/php_interface/include/lib/vendor/phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php
В этом скрипте есть такие строки:
// check mbstring.func_overload
if (ini_get('mbstring.func_overload') & 2) {
throw new PHPExcel_Exception('Multibyte function overloading in PHP must be disabled for string functions (2).');
}
Если эти строки удалить, то проверка значения mbstring.func_overload производиться не будет. Но, если это сделать простам редактированием файла, то при следующем обновлении библиотек через composer update велика вероятность, что обновится и PHPExcel, и это изменение затрётся. Тогда придётся повторять удаление ещё раз. Чтобы немного упростить себе жизнь, автоматизируем это удаление.
Composer позволяет выполнять определённые скрипты по событиям. Нам нужно определить выполнение нашего патча на событие установки (для внедрения) и на событие обновления. В моём случае, в каталоге bitrix/php_interface/include/lib лежит файл composer.json следующего содержимого:
{
"require": {
"phpoffice/phpexcel": "^1.8"
},
"config": {
"bin-dir": "vendor/bin"
},
"scripts": {
"post-install-cmd": [
"phpexcel_mbstring_patch.sh"
],
"post-update-cmd": [
"phpexcel_mbstring_patch.sh"
]
}
}
Тут в секции «require» задана установка библиотеки PHPExcel. В секции
«config» задан каталог для размещения дополнительных скриптов (куда мы и разместим свой патч). В секции «scripts» оп
ределены на какие события, какие скрипты выполнять, — в нашем случае это один и тот же патч для установки и для обновления пакетов через composer.
Создаём скрипт-патч bitrix/php_interface/include/lib/vendor/bin/phpexcel_mbstring_patch.sh со следующим содержимым:
#!/bin/bash# Change directory to the script’s location
cd $(dirname $(readlink -e $0))
file4Patch=»../phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php»
fileTmp=»../phpoffice/phpexcel/Classes/PHPExcel/Autoloader.php.tmp»
# Remove this code:
# // check mbstring.func_overload
# if (ini_get(‘mbstring.func_overload’) & 2) {
# throw new PHPExcel_Exception(‘Multibyte function overloading in PHP must be disabled for string functions (2).’);
# }
cat $file4Patch | awk -v p=1 ‘/mbstring/ {p=0} p {print $0} /}/ {p=1}’ > $fileTmp
mv -v $fileTmp $file4Patch
Поясню, что тут происходит. Это bash скрипт. Сначала определяется рабочий каталог (тот, откуда запускается патч). Затем в две переменные задаём файл, который будем патчить и временный файл, который нужен для обработки (он удаляется в конце). А дальше происходит сама магия. С помощью утилиты awk удаляется всё содержимое между строками (включая сами эти строки), содержащими «mbstring» и «}». Это как раз проверка, которая нам не нужна в PHPExcel (что и обозначено в комментарии перед магической командой). Далее просто временным файлом (в котором произведено удаление строк) подменяется исходный файл.
Это будет работать при каждом обновлении пакетов через composer до тех пор, пока в PHPExcel сохраняется такая проверка mbstring.func_overload.
Бонус
В качестве бонуса в статье приведу код функции, который генерирует XLSX файл с особым названием листа. Первая строка документа — названия колонок. Все последующие строки — данные колонок.
/**
* Метод формирует файл Excel из массива данных
* @param string $fileFullName Полный путь к создаваемому файлу
* @param string $fileTitle Заголовок файла
* @param array $arData Массив данных для записи в файл. Массив из двух вложенных массивов:
* первая позиция с ключём "HEADER"
* вторая позиция с ключём "ROWS"
* @return mixed Возвращает сформированный файл (его полный путь) либо ЛОЖЬ в случае ошибки
*
*/
public function arrayToExcel($fileFullName, $fileTitle, $arData)
{
if (!empty($fileFullName) && is_array($arData)) {
try {
$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator('Site's script')
->setLastModifiedBy('Site's script')
->setTitle($fileTitle)
->setSubject($fileTitle);
// Add some data
foreach ($arData as $dataType => $arDataset) {
if ($dataType == 'HEADER') {
$arColumnValType = [];
foreach ($arDataset as $keyHeader => $valueHeader) {
if (isset($valueHeader['NAME'])) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValueByColumnAndRow(
$keyHeader,
1,
$valueHeader['NAME']
);
if (isset($valueHeader['TYPE'])) {
$arColumnValType[$keyHeader] = $valueHeader['TYPE'];
} else {
$arColumnValType[$keyHeader] = PHPExcel_Cell_DataType::TYPE_STRING2;
}
}
}
} elseif ($dataType == 'ROWS') {
foreach ($arDataset as $indexRow => $row) {
if (count($row) == count($arData['HEADER'])) {
foreach ($row as $indexColumn => $value) {
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicitByColumnAndRow(
$indexColumn,
($indexRow + 2),
$value,
$arColumnValType[$indexColumn]
);
}
} else {
AddMessage2Log('Переданы данные с ошибкой: не совпадает количество столбцов заголовка и количество столбцов данных в строке #' . $indexRow);
}
}
}
}// Установим выравнивание ячеек по ширине содержимого
for ($i = 0; $i <= count($arData[‘HEADER’]); $i++) {
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn($i)->setAutoSize(true);
}
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(‘MyNameOfList’);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);