PHP code example of kebacorp / arrayexcelbuilder
1. Go to this page and download the library: Download kebacorp/arrayexcelbuilder library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once('vendor/autoload.php');
/* Start to develop here. Best regards https://php-download.com/ */
kebacorp / arrayexcelbuilder example snippets
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();
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();
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();
$data = [
1 => [
8 => ['value' => 'Hello, World!']
]
];
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);
$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 = [
'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) {
$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;
}
$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 = new ArrayExcelBuilder($data, $params, $allowCallback);
$arrayExcelBuilder->save();
$arrayExcelBuilder = new ArrayExcelBuilder();
$arrayExcelBuilder->setData($data)
->setParams($params)
->setAllowCallback($allowCallback)
->build();
$spreadsheet = $arrayExcelBuilder->getSpreadsheet();
$spreadsheet->getActiveSheet()->setTitle('Sheet title');
$arrayExcelBuilder->save($pathToFile, $saveOptions, $saveToVariable);
$result = $arrayExcelBuilder->save($pathToFile, $saveOptions, $saveToVariable);
$saveOptions = [
'format' => 'xlsx',
'teFormulas' => true,
'useBOM' => true,
'enclosure' => '',
'lineEnding' => "\r\n",
'delimiter' => ';',
'sheetIndex' => 0,
'imagesRoot' => '',
'pdfBuilder' => 'mpdf',
'allSheets' => true,
];
$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...
];
php composer.phar