Excel and Spreadsheet Widgets

Text widget (version >= 6.0.0) #

Using an Excel's Text widget for exporting text content together with some properties. This widget works in both Excel and spreadsheet template files.

<div>
    <div>
        <?php
        \koolreport\excel\Text::create([
            "text" => "Orders",
            "excelStyle" => $styleArray,//used in ExcelExportable's template
            "spreadsheetStyle" => $spreadsheetStyleArray // used in ExcelExportable's template
        ]);
        ?>    
    </div>
</div>

Text #

A string to define the displayed text value. This widget works in both Excel and spreadsheet template files.

excelStyle #

A style array to define style of the text cell when using ExcelExportable

spreadsheetStyle (version >= 7.0.0) #

A style array to define style of the text cell when using BigSpreadsheetExportable

Table widget (version >= 6.0.0) #

Using an Excel's Table widget for exporting a table using a datasource and other properties. This widget works in both Excel and spreadsheet template files.

<div>
    <div>
        <?php
        \koolreport\excel\Table::create(array(
            "dataSource" => 'orders',
            //"dataSource" => $this->dataStore('orders'),
            
            "filtering" => function($row, $index) { 
                if (stripos($row['customerName'], "Baane Mini Imports") !== false)
                    return false;
                return true;
            },
            //"filtering" => ['age','between',45,65],

            "sorting" => ['dollar_sales' => function($a, $b) {
                return $a >= $b;
            }],
            //"sorting" => ['dollar_sales' => 'desc'],

            "paging" => [5, 2],

            "showHeader" => false, //default: true

            "showBottomHeader" => true, //default: false

            "showFooter" => true, //default: false

            "map" => [
                "header" => function($colName) { return $colName; },
                "bottomHeader" => function($colName) { return $colName; },
                "cell" => function($colName, $value, $row) { return $value; },
                "footer" => function($colName, $footerValue) { return $footerValue; },
            ],

            "excelStyle" => [ //used in ExcelExportable's template
                "header" => function($colName) { 
                    return $styleArray; 
                },
                "bottomHeader" => function($colName) { return []; },
                "cell" => function($colName, $value, $row) { 
                    return $styleArray; 
                },
                "footer" => function($colName, $footerValue) { return []; },
            ],

            "spreadsheetStyle" => [ //used in BigSpreadsheetExportable's template
                "header" => function($colName) { 
                    return $styleArray; 
                },
                "bottomHeader" => function($colName) { return []; },
                "cell" => function($colName, $value, $row) { 
                    return $styleArray; 
                },
                "footer" => function($colName, $footerValue) { return []; },
            ],

            "rowGroup" => [
                "customerName" => [
                    'direction' => 'desc',
                    'calculate' => [
                        'totalSales' => ['sum', 'dollar_sales']
                    ],
                    "top" => "Customers: {customerName}",
                    "columnTops" => [
                        "dollar_sales" => "Total sales: {totalSales}"
                    ],
                    "bottom" => "Customers: {customerName}",
                    "columnBottoms" => [
                        "dollar_sales" => "Total sales: {totalSales}"
                    ],
                ],
                "productLine" => [
                    "top" => "Product line: {productLine}",
                ]
            ]
        ));
        ?>
    </div>
<div>

filtering #

Filtering data with either an array in the form of [field, operator, value1, ...] or a function returning true or false on a row. Inherit from a DataStore's filter method.

sorting #

Sorting data with an array in the form of [[field1, direction1], ...] where direction is either "asc" or "desc" or a comparing function. Inherit from a DataStore's sort method.

paging #

Paging data with an array in the form of [page size, page number]. Inherit from a DataStore's paging method.

showHeader #

A boolean value to either show or hide the table's header. Default value is true.

showBottomHeader #

A boolean value to either show or hide the table's bottom header. Default value is false.

showFooter #

A boolean value to either show or hide the table's footer which shows each column's footerText and/or aggregate method like "sum", "count", etc. The footer properties should be defined in the datastore's columns' metadata. Default value is false.

 ->pipe(new ColumnMeta(array(
    "amount"=>array(
        "name"=>"sale_amount"
        "footer"=>"sum",
        "footerText"=>"Total: @value",
    ),
 )))

map #

An array of functions returning string value to map the table's headers, bottom headers, footers and cells values

    "map" => [
        "header" => function($colName) { return $colName; },
        "bottomHeader" => function($colName) { return $colName; },
        "cell" => function($colName, $value, $row) { return $value; },
        "footer" => function($colName, $footerValue) { return $footerValue; },
    ],

excelStyle #

An array of functions returning excel style array to set the excel style of the table's headers, bottom headers, footers and cells when using ExcelExportable

    "excelStyle" => [
        "header" => function($colName) { 
            ...
            return $styleArray; 
        },
        "bottomHeader" => function($colName) { 
            ...
            return $styleArray; 
        },
        "cell" => function($colName, $value, $row) { 
            ...
            return $styleArray; 
        },
        "footer" => function($colName, $footerValue) { 
            ...
            return $styleArray;  
        },
    ]

spreadsheetStyle (version >= 7.0.0) #

An array of functions returning style array to set the style of the table's headers, bottom headers, footers and cells when using BigSpreadsheetExportable

    "spreadsheetStyle" => [
        "header" => function($colName) { 
            ...
            return $styleArray; 
        },
        "bottomHeader" => function($colName) { 
            ...
            return $styleArray; 
        },
        "cell" => function($colName, $value, $row) { 
            ...
            return $styleArray; 
        },
        "footer" => function($colName, $footerValue) { 
            ...
            return $styleArray;  
        },
    ]

rowGroup (version >= 8.0.0) #

You could define multiple row groups for the Table widget like this example:

    <?php
    \koolreport\excel\Table::create(array(
        ...
        "rowGroup" => [
            "customerName" => [
                'direction' => 'desc',
                'calculate' => [
                    'totalSales' => ['sum', 'dollar_sales']
                ],
                "top" => "Customers: {customerName}",
                "columnTops" => [
                    "dollar_sales" => "Total sales: {totalSales}"
                ],
                "bottom" => "Customers: {customerName}",
                "columnBottoms" => [
                    "dollar_sales" => "Total sales: {totalSales}"
                ],
            ],
            "productLine" => [
                "top" => "Product line: {productLine}",
            ]
        ]
    ));
    ?>

In each row group, you have the following properties:

PropertyDefault valueMeaningExample values
direction"asc"Sorting direction of a row group"asc", "desc"
calculate[]List of aggregated measurement in the form of [$aggregatedOperator, $field]["totalSales" => ["sum", "dollar_sales", "numberOfOrders" => ["count", "orderId"]]
top""Template string to fill in the main column of a top group rowCustomer: {customerName}
bottom""Template string to fill in the main column of a bottom group rowProduct: {productName}
columnTops[]List of template strings to fill in table columns of a top group row["customerName" => "Number of sales: {numberOfOrders}"]
columnBottoms[]List of template strings to fill in table columns of a bottom group row["customerName" => "Total sales: {totalSales}"]

Since version 10.0.0 you could format rowGroup's values by setting the calculated column meta type, decimals, decimalPoint, thousandSeparator or set "format" directly in "calculate":

    \koolreport\excel\Table::create(array(
        ...
        "rowGroup" => [
            "customerName" => [
                'direction' => 'desc',
                'calculate' => [
                    'totalSales' => [
                        'sum', 'dollar_sales', 
                        'format' => [
                            "type" => "number",
                            "decimals" => 2,
                            "decimalPoint" => ",",
                            "thousandSeparator" => ".",
                            "suffix" => "%" 
                        ]
                    ]
                ],

removeDuplicate (version >= 8.5.0) #

Similarly to core\Table widget's removeDuplicate, this property if set true would merge continuous row cells with the same values. Since version 9.0.0 this property works with both column names and column orders:

    \koolreport\excel\Table::create(array(
        ...
        "removeDuplicate" => [0, 1] // ["customerName", "productLine"]
    ));

rowspan (version >=9.0.0) #

An alias for removeDuplicate.

groupCellsInColumns (version >=9.0.0) #

An alias for removeDuplicate.

complexHeaders (version >= 10.5.0) #

Similarly to Datagrid's DataTables widget's "complexHeaders" property, this property of excel Table will merge similar prefix parts of continuous columns. For example, "Name - First" and "Name - Last" columns will have the same parent header called "Name":

    \koolreport\excel\Table::create(array(
        ...
        "columns" => ["Name - First", "Name - Last"]
        "complexHeaders" => true,
        "headerSeparator" => " - ", // by default "headerSeparator" = " - " though you can set it to any string you want
    ));

complexHeaderLabels (version >= 10.5.0) #

Similar to "complexHeaders" but it merges headers based on column labels instead of column keys:

    \koolreport\excel\Table::create(array(
        ...
        "columns" => [
            "firstName" => [
                "label" => "Name - First"
            ], 
            "lastName" => [
                "label" => "Name - Last"
            ]
        ],
        "complexHeaderLabels" => true,
        "headerSeparator" => " - ",
    ));

mergeCells (version >= 10.5.0) #

By default "mergeCells" = true for Table's merged cells such as "complexHeaders" or "rowspan". If you export very large tables it's advisable to disable "mergeCells" to increase export speed. When "mergeCells" = false, we use blank borders to simulate merging cells so that visually a pivot table looks the same.

    \koolreport\excel\Table::create(array(
        ...
        "rowspan" => [0, 1] // ["customerName", "productLine"]
        "mergeCells" => false,
    ));

excelFormatCode (version >= 8.5.0) #

When your column is of type number (meta: "type" => "number") Table widget uses a default excel format code. But now users have further ability to set a custom excel format code like this:

    //MyReportExcel.view.php
    \koolreport\excel\Table::create(array(
        ...
        "columns" => array(
            "column1" => array(
                "type" => "number",
                "excelFormatCode" => "\"{$prefix}\"" . "+#,##0.00" . "\"{$suffix}\"",
            )
        )
    ));

columnAutoSize (version >= 10.0.0) #

By default "columnAutoSize" = true for Table widget when exporting to excel (no autosize option is availabe for big spreadsheet). You could disable it like this:

    //MyReportExcel.view.php
    \koolreport\excel\Table::create(array(
        ...
        "columnAutoSize" => false,
    ));

Chart widget (version >= 6.0.0) #

Using an Excel's Chart widget for displaying a chart with several properties. This widget only works in Excel template and not in spreadsheet template.

dataSource #

Either a datastore name or a datastore to act as a chart's data

excelDataSource #

An excel table name to act as a chart's data

title #

A string to be set as a chart's title

xAxisTitle #

A string to be set as a chart's X axis title

yAxisTitle #

A string to be set as a chart's Y axis title

stacked #

A boolean indicating whether a chart's bars, columns should be stacked or not. Default value is false

direction #

An enum string ('horizontal' or 'vertical') indicating a chart's X, Y axes. Default value is 'vertical'

<div>
    <div>
        <?php
        \koolreport\excel\Table::create(array(
            "name" => "TableOrders",
            "dataSource" => 'Orders',
        ));
    </div>

    <div range="A2:H2">
        <?php
        \koolreport\excel\LineChart::create([
            "dataSource" => $this->dataStore('Orders'),
            "dataSource" => "Orders",
            "excelDataSource" => "TableOrders",
            'title' => 'Sales Orders',
            'xAxisTitle' => 'Orders List',
            'yAxisTitle' => 'Sales($)',
            'stacked' => true, //default: true
            'direction' => 'horizontal', //default: 'vertical'
        ]);
        ?>    
    </div>
</div>

PivotTable widget (version >= 6.0.0) #

Using an Excel's PivotTable widget for exporting a pivot table with several properties. This Excel package's PivotTable shares most of the properties with Pivot package's PivotTable widget including: dataSource, rowDimension, columnDimension, measure, rowSort, columnSort, hideSubTotalRows, hideSubTotalColumns, hideTotalRow, hideTotalColumn, hideGrandTotalRow, hideGrandTotalColumn, showDataHeaders, map. One difference between Excel's PivotTable and Pivot's one is that the former replace the later's cssClass map with excelStyle map. This widget only works in Excel template and not in big spreadsheet template.

excelStyle #

An array of functions returning excel style array for a PivotTable's dataFields zone, column headers, row headers and data cells.

<div>
    <div range="A2:H2">
        <?php
        \koolreport\excel\PivotTable::create(array(
            "dataSource" => 'salesPivot',
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(
                ...
            ),
            'rowSort' => array(
                ...
            ),
            'columnSort' => array(
                ...
            ),
            'hideTotalRow' => true,
            'hideTotalColumn' => true,
            'hideSubTotalRows' => true,
            'hideSubTotalColumns' => true,
            'showDataHeaders' => true,
            'map' => array(
                'rowField' => function($rowField, $fieldInfo) {
                    return $rowField;
                },
                'columnField' => function($colField, $fieldInfo) {
                    return $colField;
                },
                'dataField' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                },
                'waitingField' => function($waitingField, $fieldInfo) {
                    return $waitingField;
                },
                'rowHeader' => function($rowHeader, $headerInfo) {
                    $v = $rowHeader;
                    return $v;
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    return $v;
                },
                'dataCell' => function($value, $cellInfo) {
                    return $value;
                },
            ),
            'excelStyle' => array(
                "dataFields" => function($dataFields) {
                    ...
                    return $styleArray;
                },
                'columnHeader' => function($header, $headerInfo) {
                    ...
                    return $styleArray;
                },
                'rowHeader' => function($header, $headerInfo) {
                    ...
                    return $styleArray;
                },
                'dataCell' => function($value, $cellInfo) {                    
                    ...
                    return $styleArray;
                },
            )
        ));
        ?>    
    </div>
</div>

mergeCells (version >= 10.0.0) #

By default "mergeCells" = true for PivotTable's excel export. If you export very large pivot tables it's advisable to disable "mergeCells" to increase export speed. When "mergeCells" = false, we use blank borders to simulate merging cells so that visually a pivot table looks the same.

    \koolreport\excel\PivotTable::create(array(
        ...
        "mergeCells" => false,
    ));

showDuplicateRowHeaders and showDuplicateColumnHeaders (version >= 10.0.0) #

When "mergeCells" = false, you have options to show duplicate row/column headers.

    \koolreport\excel\PivotTable::create(array(
        ...
        "mergeCells" => false,
        "showDuplicateRowHeaders" => true,
        "showDuplicateColumnHeaders" => true,
    ));

PivotMatrix widget (version >= 9.0.0) #

This widget is similar to the PivotTable one except that it uses a little different template which shows both the row and column fields.

<div>
    <div range="A2:H2">
        <?php
        \koolreport\excel\PivotMatrix::create(array(
            "dataSource" => 'salesPivot',
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(...),
            'rowSort' => array(...),
            'columnSort' => array(...),
            'hideTotalRow' => true,
            'hideTotalColumn' => true,
            'hideSubTotalRows' => true,
            'hideSubTotalColumns' => true,
            'showDataHeaders' => true,
            'map' => array(...),
            'excelStyle' => array(...)
        ));
        ?>    
    </div>
</div>

Get started with KoolReport

KoolReport will help you to construct good php data report by gathering your data from multiple sources, transforming them into valuable insights, and finally visualizing them in stunning charts and graphs.