Импорт и Экспорт данных с помощью библиотеки PhpSpreadsheet в Битрикс

• 18 ноября 2021
• 1224
• Нет комментариев

Работать с файлом Excel можно через библиотеку PhpSpreadsheet (является следующей версией библиотеки «PHPExcel»). Данная библиотека понимает форматы «xlsx» и «xls».

Ссылка на библиотеку: github.com/PHPOffice/PhpSpreadsheet

Установка PhpSpreadsheet и требования

Прежде чем начать установку убедитесь, что у вас установлен composer (более подробно про установку composer можно прочесть здесь), а также установлена версия PHP не ниже 7.1. Затем для установки библиотеки выполняем следующую команду:

composer require phpoffice/phpspreadsheet

После того, как composer установит библиотеку и все ее зависимости можно приступать к формированию excel файлов.

Простые примеры работы с библиотекой PhpSpreadsheet в 1С Битрикс

1. Пример выгрузки всех пользователей из Базы данных Битрикс в файл users.xlsx



Здесь мы напрямую работаем с базой данных в 1с Битрикс. Получаем все данные из таблицы b_user, где хранятся все пользователи битрикс.

<?php
//подключаем автозагрузчик
require 'bitrix/vendor/autoload.php';

//подключаем библиотеку PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

global $DB;
//Получаем все записи из бд из таблицы b_user
$dbRes = $DB->Query('SELECT * FROM b_user');

$aRows = array();
while ($row = $dbRes->Fetch())
{

    $aRows[] =  $row;
}
// добавляем в начала массива значение и потом его удаляем с сохранением индекса массива
// делается для того что бы массив с данным у нас начинался с 1 а не 0
array_unshift($aRows,""); unset($aRows[0]);

$number = count($aRows);

$spreadsheet = new Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

// Создаем заголовки для таблицы
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'NAME');
$sheet->setCellValue('C1', 'LOGIN');
$sheet->setCellValue('D1', 'EMAIL');
$sheet->setCellValue('E1', 'DATE_REGISTER');
// выводим значения полученных данных в цикле и записываем их в таблицу Excel
    for ($key = 1; $key <=$number; $key++){
        $i = $key + 1;
        $sheet->setCellValue('A' .$i, $aRows[$key]['ID']);
        $sheet->setCellValue('B' .$i, $aRows[$key]['NAME']);
        $sheet->setCellValue('C' .$i, $aRows[$key]['LOGIN']);
        $sheet->setCellValue('D' .$i, $aRows[$key]['EMAIL']);
        $sheet->setCellValue('E' .$i, $aRows[$key]['DATE_REGISTER']);
    }
    
// Write an .xlsx file
$writer = new Xlsx($spreadsheet);

// Save .xlsx file to the current directory
$writer->save('admin.xlsx');
?>

После того как мы получили всех пользователей из таблицы b_user и записали в массив $aRows, мы создаем наименование столбцов в таблице excel с помощью функции $sheet->setCellValue('A1', 'ID');, в которую передается 2 значения.

Где :

A1 - это номер ячейки.

ID - значение которое запишется в данную ячейку

Сохраняем файл в текущей папке, в которой выполняется скрипт ( Чтобы указать другую папку для сохранения, прописываем полный путь до папки и указываем имя файла)

Чтобы создать файл с расширением xls, а не xlsx, достаточно заменить класс Xlsx на Xls и изменить расширение в названии создаваемого файла.

2. Установка стилей ячеек



Для установки стилей ячеек проще всего использовать метод applyFromArray, который принимает массив параметров. С его помощью можно задать шрифт, цвет текста, границы, выравнивание текста и другие параметры:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\{Font, Border, Alignment};

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B2', 'Hello!');
// Получаем ячейку для которой будем устанавливать стили
$sheet->getStyle('B2')->applyFromArray([
    'font' => [
      'name' => 'Arial',
      'bold' => true,
      'italic' => false,
      'underline' => Font::UNDERLINE_DOUBLE,
      'strikethrough' => false,
      'color' => [
          'rgb' => '808080'
        ]
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => [
                'rgb' => '808080'
            ]
        ],
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
        'wrapText' => true,
    ]
]);

// Выбросим исключение в случае, если не удастся сохранить файл
try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Исключение обработано просто для демонстрации в случае, если не удастся создать файл.

3. Установка размеров и объединение ячеек



Высота и ширина устанавливается на строку и столбец соответственно. Для установки высоты используется метод setRowHeight, а для установки ширины метод setWidth. Чтобы объединить несколько ячеек необходимо использовать метод mergeCells:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B2', 'Hello!');
$sheet->setCellValue('C2', 'Hello there!');
//Установка ширины столбца B
$sheet->getColumnDimension('B')->setWidth(50);
//Установка высоты для 2 строки
$sheet->getRowDimension(2)->setRowHeight(50);
//Объединение ячеек
$sheet->mergeCells('C2:D3');

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Как видно из примера для установки высоты и ширины предварительно необходимо вызвать вспомогательные методы getColumnDimension и getRowDimension.

4. Добавление картинок



Для добавления картинок используется класс Drawing:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

//С помощью класса Drawing можно осуществлять вставку картинок
$drawing = new Drawing();
//Указываем путь до картинки, которая должна быть расположена
//на том же сервере
$drawing->setPath('images/image.jpg');
//Указываем ячейку в которой разместим изображение
$drawing->setCoordinates('B2');
//Можно задать отступ по X или Y оси
$drawing->setOffsetY(50);
//Передаем объект текущего листа
$drawing->setWorksheet($sheet);

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Помимо тех возможностей, что используются в примере, также можно задавать тени у картинок, вращать их и т.д. Более подробно можно обратиться к примерам библиотеки. Также стоит отметить, что путь до файла с картинкой необходимо задавать относительно сервера на котором запускается скрипт создания ваших таблиц. Указать путь в виде ссылки на сторонний ресурс не удастся (на сколько мне известно), для этого сначала необходимо скачать данную картинку на ваш сервер и только потом указывать путь до картинки относительно текущего сервера.

5. Арифметические операции



В библиотеке доступно большое количество операций для выполнения расчетов, таких как: нахождение суммы чисел, нахождение среднего значения, взятие максимального и минимального числа и многие другие вычисления. В примере ниже показано, как вычислить сумму чисел, среднее значение и нахождение максимального из чисел:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('B1', '1');
$sheet->setCellValue('B2', '5');
$sheet->setCellValue('B3', '9');
$sheet->setCellValue('A4', 'Sum:');
$sheet->setCellValue('A5', 'Average:');
$sheet->setCellValue('A6', 'Max:');
$sheet->setCellValue('B4', '=SUM(B1:B3)');
$sheet->setCellValue('B5', '=AVERAGE(B1:B3)');
$sheet->setCellValue('B6', '=MAX(B1:B3)');

try {
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');

} catch (PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    echo $e->getMessage();
}

Узнать более подробно о всех возможных операциях можно из примеров библиотеки.

6. Получение данных из таблицы excel



К примеру у вас есть свой файл excel с какими то данными. Вам поставили задачу экспортировать данные с этого в файла в базу данных.

<?php
//подключаем автозагрузчик
require 'bitrix/vendor/autoload.php';

//подключаем библиотеку PhpSpreadsheet
//--use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;


$spreadsheet = IOFactory::load('admin.xlsx');

$sheet = $spreadsheet->getActiveSheet();

// Store data from the activeSheet to the varibale in the form of Array
$data = $sheet->toArray(null,true,true,true);

// Display the sheet content
echo '<pre>';
print_r($data);
echo '</pre>';

?>

Здесь мы считали данные с файла admin.xlsx и записали его в массив $data. Далее вывели данный массив.

Вот такой массив у нас получился:

Где массив с индексом 1 это заголовок моей таблицы

Array
(
    [1] => Array
        (
            [A] => ID
            [B] => NAME
            [C] => LOGIN
            [D] => EMAIL
            [E] => DATE_REGISTER
        )

    [2] => Array
        (
            [A] => 1
            [B] => Alex
            [C] => admin
            [D] => lab@vega.com.ru
            [E] => 2019-11-07 17:27:35
        )

    [3] => Array
        (
            [A] => 5
            [B] => 
            [C] => dev
            [D] => lab@vega.com.ru
            [E] => 2019-11-14 14:37:50
        )

    [4] => Array
        (
            [A] => 9
            [B] => test
            [C] => t234est
            [D] => t234est@test.ru
            [E] => 2019-11-19 11:44:58
        )

    [5] => Array
        (
            [A] => 22
            [B] => 
            [C] => dev1
            [D] => alfa-lab@yandex.ru
            [E] => 2019-12-10 14:29:50
        )

    [6] => Array
        (
            [A] => 23
            [B] => sdf
            [C] => lab
            [D] => lab@vega.com.ru
            [E] => 2019-12-10 16:26:44
        )

    [7] => Array
        (
            [A] => 26
            [B] => 1С
            [C] => 1c_exchange
            [D] => vt@webcenter.pro
            [E] => 2019-12-13 09:32:13
        )

    [8] => Array
        (
            [A] => 27
            [B] => лилир
            [C] => v.rylov
            [D] => v.rylov@bk.ru
            [E] => 2019-12-16 16:52:26
        )

    [9] => Array
        (
            [A] => 28
            [B] => alex
            [C] => lab95390
            [D] => lab@Vega.com.ru
            [E] => 2020-01-09 14:54:20
        )
)

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