Зачем 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);

В предыдущей статье «Bitrix24 API — разбор демо приложения третьего типа» я пообещал написать продолжение с примером выгрузки данных из Битрикс24 по событию (например, по закрытию сделки). Хотел бы написать, что не прошло и года, но… год прошёл.
Пример будет такой. Основано на реальной потребности одной организации. Нужно для всех сделок передавать данные в систему управления складскими запасами. Сделки заключает менеджер, за запасами материалов следит производственник. Т.е. после оформления сделки в Битрикс24 и перехода её в нужный стартовый статус, должен запуститься механизм производства. Нужно вовремя заказать материалы, необходимые для производства (с учётом времени, когда запасы понадобятся и текущих имеющихся запасов). Вобщем, понятно, что данные по сделке нужны в каких-то внешних системах (всё это делать в Битрикс24 не получается, да и не нужно).
Итак, приступаем. Что портебуется:

1. Аккаунт администратора Битрикс24
2. Сайт в интернете, куда будем отсылать данные из Битрикс24

Создаём принимающий скрипт

Мы будем подписываться на событие ONCRMDEALUPDATE (как именно подписываться, см. ниже). Это событие в Битрикс24 возникает при изменении сделки. Многие ожидают, что событие «изменения сущности» вернёт те данные, которые изменились. В большинстве случаев это было бы удобно. Но Битрикс24 возвращает только идентификатор изменившейся сущности. Определить через API что именно изменилось — вообще нельзя. Можно только где-то иметь старую копию сущности и при изменении проверить все параметры и отыскать, что же изменилось.
Итак на вход наш скрипт получит идентификатор изменившейся сделки. Не густо. Нужно самостоятельно вытянуть все данные по этой сделке. Сделать это можно через метод API crm.deal.get. Вот пример, как это может быть реализовано в php:

// Получаем идентификатор изменившейся сделки 
$deal_id = $_REQUEST['data']['FIELDS']['ID'];
// Запрашиваем данные этой сделки
$auth = $_REQUEST['auth']['access_token'];
$domain = $_REQUEST['auth']['domain'];
$data = call($domain, "crm.deal.get", array(
    "auth" => $auth,
    "id" => $deal_id    
    )
);

Функция call() взята из демо-приложения, которое рассмотрено мною в предыдущей статье. Полный код этой функции можно увидеть в файле config.php в этом репозитории.
Полезные данные будут в массиве $data[‘result’]. Для начала можно весь этот массив записать в файл для анализирования. Например, можно это сделать так:

logger('Разбираем данные по сделке. var_export($data, 1) = ' . var_export($data, 1) . "n", 'common');

Функция logger() определена всё в том же файле config.php. После анализа массива данных, станет понятно, в каких ключах есть интересующие данные.
Так я выяснил, что статус сделки приходит в значении $data[‘result’][‘STAGE_ID’]. Мне не нужны сделки с начальным статусом (т.к. эти сделки могут сорваться — они ещё прорабатываются менеджером и клиентом). В моём случае код начального статуса — NEW (сделки с таким статусом пропускаем).
Как я определял коды сделок уже не помню. Сейчас можно, например, открыть в Битрикс24 страницу с настройками статусов сделок [CRM / Настройки / Статусы сделки] и в браузерных инструментах разработчика поискать в html дереве строку «LIST[DEAL_STAGE]». Код статуса — это то, что указано в атрибуте VALUE скрытого тега input с названием name=»LIST[DEAL_STAGE][111][STATUS_ID]», где число 111 будет разным для разных статусов. Описание весьма запутанное — смотрите пример на снимке ниже:

Кроме определения статуса сделки, мне нужно было записывать сделки во внешнюю систему только один раз, а не добавлять каждый раз при изменении статуса сделки. Но данные по сделке могут измениться, поэтому при повторной отправке той же самой сделки, она будет обновлять данные.
Я работал с данными, передавая их напрямую в базу данных (без каких-либо библиотек и фреймворков). Получился такой код обработки данных заказа:

if (isset($data['result'])) {
    logger("Сделка №" . $data['result']['ID'] . " — " . $data['result']['TITLE'] . "n", 'common');
    // Проверяем, не является ли сделка новой (если новая, то ещё не обязательно, что сделка состоится)
   
 if ($data['result']['STAGE_ID'] != 'NEW') {
        logger("Обрабатываем не новую сделку" . "n", 'common');
        // Ищем эту сделку в базе, - вдруг она уже заведена
        $deal_id_search = "Сделка №" . $deal_id;
        logger('$deal_id_search = ' . $deal_id_search . "n", 'common');
        $result = SQL('SELECT COUNT(*) AS count FROM deal WHERE name LIKE CONCAT(?, "%")', $deal_id_search);
        if ($result[0]['count']) {
            logger('Сейчас будем обновлять данные по сделке' . "n", 'common');
            // Такая сделка уже заведена в базе. Но обновим её данные
            SQL('UPDATE `deal` SET `name` = ?, `date` = ?, `amount` = ?, `comment` = ? WHERE `name` LIKE CONCAT(?, "%")', "Сделка №" . $deal_id . " — " . $data['result']['TITLE'], $data['result']['CLOSEDATE'], $data['result']['OPPORTUNITY'], $data['result']['COMMENTS'], $deal_id_search);
            logger('Обновили сделку в базе. Сделка №' . $deal_id . " — " . $data['result']['TITLE'] . "n", 'common');  
        } else {
            // Такой сделки ещё нет в базе, - добавляем
            SQL('INSERT INTO `deal` (`name`, `date`, `amount`, `comment`, `accounting`) VALUES(?, ?, ?, ?, 1)', "Сделка №" . $deal_id . " — " . $data['result']['TITLE'], $data['result']['CLOSEDATE'], $data['result']['OPPORTUNITY'], $data['result']['COMMENTS']);
            logger('Добавили сделку в базу. Сделка №' . $deal_id . " — " . $data['result']['TITLE'] . "n", 'common');    
        }
    }
}

В данном коде используется функция SQL() — она определена в файле myfunctions.php. Полностью этот файл, а также целиком мой принимающий скрипт (reg-deal.php) можно посмотреть тут.

Создаём обработчик события

Чтобы скрипт, описанный выше стал выполняться каждый раз, когда в Битрикс24 изменится сделка, нужно прописать на портале Битрикс24 обработчик события. Сделать это из админки Битрикс24, к сожалению, пока невозможно. Только через API. Обработчик на событие ONCRMDEALUPDATE можно прописать кликнув на ссылке «Установить обработчик события «Обновление сделки»» из модифицированного скрипта демо-приложения, описанного в предыдущей статье. Также там есть ссылка «Просмотр установленных обработчиков событий» — для проверки, какие события прописаны на портале.

За установку обработчика отвечает код:

$data = call($_SESSION["query_data"]["domain"], "event.bind", array(
    "auth" => $_SESSION["query_data"]["access_token"],
    "EVENT" => "ONCRMDEALUPDATE",
    "HANDLER" => "http://sitename/integration/reg-deal.php"
));

После выполнения скрипта, Битрикс24 каждый раз при срабатывании собятия изменения сделки будет обращаться к скрипту, прописанному в параметре HANDLER, поэтому нужно точно убедиться что в адресе нет ошибок.

Что дальше

После того, как настроен принимающий скрипт и установлено срабатывание запроса из Битрикс24 к этому скрипту, данные будут передаваться автоматически. Но что-то может пойти не так. Например, событие в Битрикс24 сработает, но скрипт не отработает (может, ваш сайт будет временно недоступен из-за сетевых проблем). Получится необработанное событие. Нужно либо придумать механизм контроля, либо учитывать ненадёжность передачи.
Во-вторых, принимающий скрипт «торчит» в интернет, а значит любой злоумышленник может обратиться к нему и подставить фейковые данные, которые что-то сломают на вашем сайте, либо нарушат ваши данные. Значит, нужно придумать какую-то защиту, — хотя бы первичную верификацию данных и проверку, откуда исходил запрос. Данная статья не затрагивает эти вопросы! В каждой реализации это будет свой уникальный комплекс мер.