README

ArrayExcelBuilder

The component CREATES an Excel file from the data array or READS from Excel file to array.

Based on PHPOffice/PhpSpreadsheet

For license information check the LICENSE-file.

Requirements:

• PHP 5.6 and higher.
• PHP 7.4 is supported since ArrayExcelBuilder v4.0.1

Installation:

The preferred way to install this extension is through composer.

Either run

php composer.phar require --prefer-dist kebacorp/arrayexcelbuilder

or add

"kebacorp/arrayexcelbuilder": "*"

to the require section of your composer.json.

Usage:

"Hello world!" example

<?php

use KebaCorp\ArrayExcelBuilder\ArrayExcelBuilder;

// Array data
$data = [
    // Sheet 1
    [
        // Sheet options
        'sheetName' => 'Hello world page',
        'data' => [
            // Column 1
            [
                // Row 1
                [
                    // Cell options
                    'value' => 'Hello world!',
                ],
                // Row n...
            ],
            // Column n...
        ],
    ],
    // Sheet n...
];

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder($data);
$arrayExcelBuilder->save();

?>

Matrix data example

<?php

use KebaCorp\ArrayExcelBuilder\ArrayExcelBuilder;

// Array data
$data = [
    // Sheet 1
    [
        // Sheet options
        'sheetName' => 'Hello world page',
        'data' => [
            // Column 1
            [
                'Hello world!', // Row 1
                true,           // Row 2
                100500,         // Row 3
                'Other value',  // Row 4
                [               // Row 4
                    'value' => 'I can configure the cell in more detail',
                    // Other cell options...
                ],
                // Row n...
            ],
            // Column 2
            [
                'Hello world!',         // Row 1
                true,                   // Row 2
                100500,                 // Row 3
                10 => 'Other value',    // Row 9
                // Row n...
            ],
            // Column n...
        ],
    ],
    // Sheet n...
];

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder($data);
$arrayExcelBuilder->save();

?>

Sheet parameter "isRowDirection" example

<?php

use KebaCorp\ArrayExcelBuilder\ArrayExcelBuilder;

// Array data
$data = [
    // Sheet 1
    [
        // Sheet options
        'sheetName' => 'Hello world page',
        'isRowDirection' => true,
        'data' => [
            // Row 1
            [
                'Hello world!', // Column 1
                 true,          // Column 2
                 100500,        // Column 3
                'Other value',  // Column 4
                 // Column n...
            ],
            // Row 2
            [
                'Hello world!', // Column 1
                 true,          // Column 2
                 100500,        // Column 3
                'Other value',  // Column 4
                 // Column n...
            ],
            // Row n...
        ],
    ],
    // Sheet n...
];

// Global cell params
$globalParams = [];

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder($data, $globalParams);
$arrayExcelBuilder->save();

?>

Рекомендации:

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

• Каждый параметр увеличивает время создания файла. Поэтому один параметр "styleArray" (массив с настройками стиля ячейки) будет исполняться существенно быстрее, чем аналогичные параметры (borderBottom, bold, fontColor, fontSize и т. д.) по отдельности.

• Не рекомендуется использовать в качестве ключей массивов колонок и строк не числа. Позиция следующей ячейки в таком случае может сбиться!

Полезно знать:

• Позиция ячейки относительно ключа массива:

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

Например:

<?php

$data = [
    1 => [
        8 => ['value' => 'Hello, World!']
    ]
];

В данном примере текст "Hello, World!" будет расположен в ячейке "B9".

Params:

Sheet options

Global cell options

Cell options

Все параметры ячейки:

Full example:

<?php

use KebaCorp\ArrayExcelBuilder\ArrayExcelBuilder;

// Array data
$data = [
    // Sheet 1
    [
        // Sheet options
        'autoSize' => false,
        'sheetName' => 'First page',
        'freezeCell' => 'B2',
        'isRowDirection' => true,
        'showGridLines' => false,
        'data' => [
            // Column 1
            [
                // Row 1
                [
                    // Cell options
                    'value' => 'John',
                    'fontColor' => 'ffffff',
                    'fillColor' => '4f81bd',
                    'fontSize' => 10,
                    'bold' => true,
                    'hAlignment' => 'center',
                    'vAlignment' => 'top',
                    'wrapText' => false,
                    'comment' => 'John is cool boy.',
                    'mergeColumns' => 4,
                    'mergeRows' => 3,
                    'borderBottom' => 'dashDot',
                    'borderTop' => 'dotted',
                    'borderLeft' => 'mediumDashDotDot',
                    'borderRight' => 'hair',
                    'borderBottomColor' => '000000',
                    'borderTopColor' => '000000',
                    'borderLeftColor' => '000000',
                    'borderRightColor' => '000000',
                    'columnWidth' => 100,
                    'rowHeight' => 20,
                    'url' => 'www.example.com', // or 'sheet://"Sheet name"!A1'
                    'image' => [ // or 'image' => 'img/example.jpg',
                        'path' => 'img/example.jpg',
                        'name' => 'Example',
                        'description' => 'Example image',
                        'offsetX' => 10,
                        'offsetY' => 15,
                        'width' => 200,
                        'height' => 300,
                        'resizeProportional' => true,
                        'rotation' => 70,
                        'hyperLink' => [ // or 'hyperLink' => 'www.example.com',
                            'url' => 'www.example.com',
                            'tooltip' => 'Example site',
                        ],
                    ],
                    'callback' => function($data) {
                        $data['spreadsheet']->getActiveSheet()->getCell($data['cell'])->setValue('Cell value');

                        return $data;
                    },
                    'styleArray' => [
                        'font' => [
                            'name' => 'Arial',
                            'bold' => true,
                            'italic' => false,
                            'underline' => 'double', // none, double, doubleAccounting, single, singleAccounting
                            'strikethrough' => false,
                            'color' => [
                                'rgb' => '808080'
                            ],
                            'size'  => 15,
                            'superscript'  => true,
                            'subscript'  => true,
                        ],
                        'fill' => [
                            'fillType' => 'solid', // none, solid, linear, path, darkDown, darkGray, darkGrid, darkHorizontal, darkTrellis, darkUp, darkVertical, gray0625, gray125, lightDown, lightGray, lightGrid, lightHorizontal, lightTrellis, lightUp, lightVertical, mediumGray
                            'rotation' => 10.5, // Type: float
                            'startColor' => [
                                'rgb' => '000000'
                            ],
                            'endColor' => [
                                'argb' => 'FFFFFFFF'
                            ],
                            'color' => [
                                'rgb' => '000000'
                            ],
                        ],
                        'alignment' => [
                            'horizontal' => 'center', // general, left, right, center, centerContinuous, justify, fill, distributed - Excel2007 only
                            'vertical' => 'center', // bottom, top, center, justify, distributed - Excel2007 only
                            'wrapText' => false, // Type: bool
                            'textRotation' => 0, // Type: integer
                            'shrinkToFit' => false, // Type: bool
                            'indent' => 0, // Type: integer
                        ],
                        'borders' => [
                            'allBorders' => [
                                'borderStyle' => 'dashDot', // none, dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
                                'color' => [
                                    'argb' => 'FFFFFFFF'
                                ]
                            ],
                            'bottom' => [
                                'borderStyle' => 'dashDot',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'top' => [
                                'borderStyle' => 'dashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'left' => [
                                'borderStyle' => 'mediumDashDot',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'right' => [
                                'borderStyle' => 'mediumDashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'diagonal' => [
                                'borderStyle' => 'mediumDashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'vertical' => [
                                'borderStyle' => 'mediumDashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'horizontal' => [
                                'borderStyle' => 'mediumDashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'outline' => [
                                'borderStyle' => 'mediumDashed',
                                'color' => [
                                    'rgb' => '808080'
                                ]
                            ],
                            'diagonalDirection' => 1, // DIAGONAL_NONE: 0, DIAGONAL_UP: 1, DIAGONAL_DOWN: 2, DIAGONAL_BOTH: 3
                        ],
                        'numberFormat' => [
                            'formatCode' => 'General', // see PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_*
                        ],
                        'protection' => [
                            'locked' => 'protected', // inherit, protected, unprotected
                            'hidden' => 'protected', // inherit, protected, unprotected
                        ],
                        'quotePrefix' => true,
                    ],
                ],

                // Row 2
                [
                    // Cell options
                    'value' => 100500,
                ],

                // Row 3
                [
                    // Cell options
                    'value' => 100.500,
                ],

                // Row 4
                [
                    // Cell options
                    'value' => true,
                ],

                // Row 5
                [
                    // Cell options
                    'value' => '=SUM(A2:A3)',
                ],

                // Row n...
            ],
            // Column n...
        ],
        'charts' => [
            // Chart 1
            [
                'name' => 'Chart name',
                'title' => 'Chart title',
                'xLabel' => 'X axis label',
                'yLabel' => 'Y axis label',
                'chartType' => 'lineChart',
                'groupType' => 'stacked',
                'chartStartPosition' => 'B3',
                'chartEndPosition' => 'H18',
                'isLegend' => true,
                'legendPosition' => 'tr',
                'labels' => [
                    // Label 1
                    [
                        'dataType' => 'String',
                        'sheetName' => 'Worksheet',
                        'column' => 'B',
                        'row' => 1,
                    ],
                    // Label n...
                ],
                'xValues' => [
                    [
                        'dataType' => 'String',
                        'sheetName' => 'Worksheet',
                        'startColumn' => 'A',
                        'startRow' => 2,
                        'endColumn' => 'A',
                        'endRow' => 5,
                    ],
                ],
                'yValues' => [
                    // Value 1
                    [
                        'dataType' => 'String',
                        'sheetName' => 'Worksheet',
                        'startColumn' => 'B',
                        'startRow' => 2,
                        'endColumn' => 'B',
                        'endRow' => 5,
                    ],
                    // Value n...
                ],
            ]
            // Chart n...
        ]
    ],
    // Sheet n...
];

// Global cell options
$params = [
    'allBorderBottom' => 'dashDot',
    'allBorderTop' => 'dotted',
    'allBorderLeft' => 'mediumDashDotDot',
    'allBorderRight' => 'hair',
    'allBorderBottomColor' => '000000',
    'allBorderTopColor' => '000000',
    'allBorderLeftColor' => '000000',
    'allBorderRightColor' => '000000',
    'borderBottom' => 'dashDot',
    'borderTop' => 'dotted',
    'borderLeft' => 'mediumDashDotDot',
    'borderRight' => 'hair',
    'borderVertical' => 'dashDot',
    'borderHorizontal' => 'dotted',
    'borderBottomColor' => '000000',
    'borderTopColor' => '000000',
    'borderLeftColor' => '000000',
    'borderRightColor' => '000000',
    'borderVerticalColor' => '000000',
    'borderHorizontalColor' => '000000',
    'value' => 'none',
    'fontColor' => '',
    'fillColor' => '',
    'fontSize' => 10,
    'bold' => true,
    'hAlignment' => 'center',
    'vAlignment' => 'top',
    'wrapText' => false,
    'columnWidth' => 150,
    'rowHeight' => 25,
    'styleArray' => [
        'font' => [
            'name' => 'Arial',
            'bold' => true,
            'italic' => false,
            'underline' => 'double', // none, double, doubleAccounting, single, singleAccounting
            'strikethrough' => false,
            'color' => [
                'rgb' => '808080'
            ],
            'size'  => 15,
            'superscript'  => true,
            'subscript'  => true,
        ],
        'fill' => [
            'fillType' => 'solid', // none, solid, linear, path, darkDown, darkGray, darkGrid, darkHorizontal, darkTrellis, darkUp, darkVertical, gray0625, gray125, lightDown, lightGray, lightGrid, lightHorizontal, lightTrellis, lightUp, lightVertical, mediumGray
            'rotation' => 10.5, // Type: float
            'startColor' => [
                'rgb' => '000000'
            ],
            'endColor' => [
                'argb' => 'FFFFFFFF'
            ],
            'color' => [
                'rgb' => '000000'
            ],
        ],
        'alignment' => [
            'horizontal' => 'center', // general, left, right, center, centerContinuous, justify, fill, distributed - Excel2007 only
            'vertical' => 'center', // bottom, top, center, justify, distributed - Excel2007 only
            'wrapText' => false, // Type: bool
            'textRotation' => 0, // Type: integer
            'shrinkToFit' => false, // Type: bool
            'indent' => 0, // Type: integer
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => 'dashDot', // none, dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
                'color' => [
                    'argb' => 'FFFFFFFF'
                ]
            ],
            'bottom' => [
                'borderStyle' => 'dashDot',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'top' => [
                'borderStyle' => 'dashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'left' => [
                'borderStyle' => 'mediumDashDot',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'right' => [
                'borderStyle' => 'mediumDashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'diagonal' => [
                'borderStyle' => 'mediumDashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'vertical' => [
                'borderStyle' => 'mediumDashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'horizontal' => [
                'borderStyle' => 'mediumDashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'outline' => [
                'borderStyle' => 'mediumDashed',
                'color' => [
                    'rgb' => '808080'
                ]
            ],
            'diagonalDirection' => 1, // DIAGONAL_NONE: 0, DIAGONAL_UP: 1, DIAGONAL_DOWN: 2, DIAGONAL_BOTH: 3
        ],
        'numberFormat' => [
            'formatCode' => 'General', // see PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_*
        ],
        'protection' => [
            'locked' => 'protected', // inherit, protected, unprotected
            'hidden' => 'protected', // inherit, protected, unprotected
        ],
        'quotePrefix' => true,
    ],
];

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder($data, $params);
$result = $arrayExcelBuilder->save('excel/Document', ['format' => 'xlsx'], false);

// --- or --- //

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder($data, $params);
$arrayExcelBuilder->setData($data);
$arrayExcelBuilder->setParams($params);
$result = $arrayExcelBuilder->save('excel/Document', ['format' => 'xlsx'], false);

// --- or --- //

// Create excel file from array
$arrayExcelBuilder = new ArrayExcelBuilder();
$result = $arrayExcelBuilder->setData($data)
                ->setParams($params)
                ->save('excel/Document', ['format' => 'xlsx'], false);

?>

Style array

StyleArray - массив стилей ячейки. Доступны все параметры, которые есть у StyleArray в Phpspreadsheet.

<?php

$styleArray = [
    'font' => [
        'name' => 'Arial',
        'bold' => true,
        'italic' => false,
        'underline' => 'double', // none, double, doubleAccounting, single, singleAccounting
        'strikethrough' => false,
        'color' => [
            'rgb' => '808080'
        ],
        'size'  => 15,
        'superscript'  => true,
        'subscript'  => true,
    ],
    'fill' => [
        'fillType' => 'solid', // none, solid, linear, path, darkDown, darkGray, darkGrid, darkHorizontal, darkTrellis, darkUp, darkVertical, gray0625, gray125, lightDown, lightGray, lightGrid, lightHorizontal, lightTrellis, lightUp, lightVertical, mediumGray
        'rotation' => 10.5, // Type: float
        'startColor' => [
            'rgb' => '000000'
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF'
        ],
        'color' => [
            'rgb' => '000000'
        ],
    ],
    'alignment' => [
        'horizontal' => 'center', // general, left, right, center, centerContinuous, justify, fill, distributed - Excel2007 only
        'vertical' => 'center', // bottom, top, center, justify, distributed - Excel2007 only
        'wrapText' => false, // Type: bool
        'textRotation' => 0, // Type: integer
        'shrinkToFit' => false, // Type: bool
        'indent' => 0, // Type: integer
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => 'dashDot', // none, dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
            'color' => [
                'argb' => 'FFFFFFFF'
            ]
        ],
        'bottom' => [
            'borderStyle' => 'dashDot',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'top' => [
            'borderStyle' => 'dashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'left' => [
            'borderStyle' => 'mediumDashDot',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'right' => [
            'borderStyle' => 'mediumDashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'diagonal' => [
            'borderStyle' => 'mediumDashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'vertical' => [
            'borderStyle' => 'mediumDashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'horizontal' => [
            'borderStyle' => 'mediumDashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'outline' => [
            'borderStyle' => 'mediumDashed',
            'color' => [
                'rgb' => '808080'
            ]
        ],
        'diagonalDirection' => 1, // DIAGONAL_NONE: 0, DIAGONAL_UP: 1, DIAGONAL_DOWN: 2, DIAGONAL_BOTH: 3
    ],
    'numberFormat' => [
        'formatCode' => 'General', // see PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_*
    ],
    'protection' => [
        'locked' => 'protected', // inherit, protected, unprotected
        'hidden' => 'protected', // inherit, protected, unprotected
    ],
    'quotePrefix' => true,
];

?>

Image array data

<?php

$image = [
    'path' => 'img/example.jpg',
    'name' => 'Example',
    'description' => 'Example image',
    'offsetX' => 10,
    'offsetY' => 15,
    'width' => 200,
    'height' => 300,
    'resizeProportional' => true,
    'rotation' => 70,
    'hyperLink' => [ // or 'hyperLink' => 'www.example.com',
        'url' => 'www.example.com',
        'tooltip' => 'Example site',
    ],
];

?>

Global callback arguments

ВАЖНО! Callback-функции не запустятся, если в конструктор ArrayExcelBuilder передан параметр "allowCallback" раным false (см. ArrayExcelBuilder constructor arguments).

Spreadsheet позволяет использовать практически все возможности, которые есть в библиотеке PHPOffice/PhpSpreadsheet.

<?php

$callback = function($data) {
    $spreadsheet = $data['spreadsheet'];
    $dataDto = $data['dataDto'];
    $paramsDto = $data['paramsDto'];
    $sheetsNumber = $data['sheetsNumber'];
    $maxRow = $data['maxRow'];
    $maxColumn = $data['maxColumn'];
    $maxCellCoordinates = $data['maxCellCoordinates'];

    // Actions with Spreadsheet
    $spreadsheet->getActiveSheet()->getCell($data['cell'])->setValue('Cell value');
    $data['spreadsheet'] = $spreadsheet;

    // IMPORTANT! For the changes to apply, you must return a modified data argument
    return $data;
}

?>

Cell callback arguments

ВАЖНО! Callback-функции не запустятся, если в конструктор ArrayExcelBuilder передан параметр "allowCallback" раным false (см. ArrayExcelBuilder constructor arguments).

Spreadsheet позволяет использовать практически все возможности, которые есть в библиотеке PHPOffice/PhpSpreadsheet.

<?php

$callback = function($data) {
    $spreadsheet = $data['spreadsheet'];
    $columnId = $data['columnId'];
    $rowId = $data['rowId'];
    $dataDto = $data['dataDto'];
    $paramsDto = $data['paramsDto'];
    $columnName = $data['columnName'];
    $cell = $data['cell'];
    $sheetsNumber = $data['sheetsNumber'];
    $maxRow = $data['maxRow'];
    $maxColumn = $data['maxColumn'];
    $maxCellCoordinates = $data['maxCellCoordinates'];

    // Actions with Spreadsheet
    $spreadsheet->getActiveSheet()->getCell($data['cell'])->setValue('Cell value');
    $data['spreadsheet'] = $spreadsheet;

    // IMPORTANT! For the changes to apply, you must return a modified data argument
    return $data;
}

?>

ArrayExcelBuilder:

ArrayExcelBuilder constructor arguments

<?php

$arrayExcelBuilder = new ArrayExcelBuilder($data, $params, $allowCallback);
$arrayExcelBuilder->save();

?>

ArrayExcelBuilder methods

<?php

$arrayExcelBuilder = new ArrayExcelBuilder();

$arrayExcelBuilder->setData($data)
    ->setParams($params)
    ->setAllowCallback($allowCallback)
    ->build();

$spreadsheet = $arrayExcelBuilder->getSpreadsheet();
$spreadsheet->getActiveSheet()->setTitle('Sheet title');

$arrayExcelBuilder->save($pathToFile, $saveOptions, $saveToVariable);

?>

Save arguments

$result = $arrayExcelBuilder->save($pathToFile, $saveOptions, $saveToVariable);

Save options

<?php

$saveOptions = [
    'format' => 'xlsx',
    'includeCharts' => true,
    'office2003Compatibility' => false,
    'calculateFormulas' => true,
    'useBOM' => true,
    'enclosure' => '',
    'lineEnding' => "\r\n",
    'delimiter' => ';',
    'sheetIndex' => 0,
    'imagesRoot' => '',
    'pdfBuilder' => 'mpdf',
    'allSheets' => true,
];

?>

Charts:

Chart options

Chart labels options

Chart xValues and yValues options

Full example:

<?php

$charts = [
    // Chart 1
    [
        'name' => 'Chart name',
        'title' => 'Chart title',
        'xLabel' => 'X axis label',
        'yLabel' => 'Y axis label',
        'chartType' => 'lineChart',
        'groupType' => 'stacked',
        'chartStartPosition' => 'B3',
        'chartEndPosition' => 'H18',
        'isLegend' => true,
        'legendPosition' => 'tr',
        'labels' => [
            // Label 1
            [
                'dataType' => 'String',
                'sheetName' => 'Worksheet',
                'column' => 'B',
                'row' => 1,
            ],
            // Label n...
        ],
        'xValues' => [
            [
                'dataType' => 'String',
                'sheetName' => 'Worksheet',
                'startColumn' => 'A',
                'startRow' => 2,
                'endColumn' => 'A',
                'endRow' => 5,
            ],
        ],
        'yValues' => [
            // Value 1
            [
                'dataType' => 'String',
                'sheetName' => 'Worksheet',
                'startColumn' => 'B',
                'startRow' => 2,
                'endColumn' => 'B',
                'endRow' => 5,
            ],
            // Value n...
        ],
    ]
    // Chart n...
];

?>