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>

columns #

An array defines list of columns from the datasource to be exported:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName', 'dollar_sales'
    ]
));

label #

A column property defines its displayed name when exported:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName' => [
            'label' => 'Product Name'
        ]
    ]
));

A column property defines an aggregate operator such as "sum", "count", "avg", "min", "max", etc to be applied on that column and shown if Table's "showFooter" is true:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName' => [
            'footer' => 'sum'
        ]
    ],
    "showFooter" => true,
));

footerText #

A column property defines an a column's footer text to be shown if Table's "showFooter" is true:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName' => [
            'footer' => 'sum',
            'footerText' => "Total value: \n @value",
        ]
    ],
    "showFooter" => true,
));

width #

A column property defines its excel width. Doesn't apply with big spreadsheet export.

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName' => [
            'width' => 60, // overrides outside "columnWidth" property
        ],

formatValue #

A column property defines its formatted value based on its data row

\koolreport\excel\Table::create(array(
    ...
    'columns' => [
        'productName' => [
            'formatValue' => function($value, $row, $ckey, $meta) {
                return ...;
            },
        ]
    ]
));

Using formatValue column property in function form, users could return an excel widget of Chart, Image, or Hyperlink type for a table column. Remember to add a second parameter with true value in the ::create() static method so that the method returns a widget's render instead of outputing the render immediately.

Here is an example of an image column:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [        
        'image' => [
            'formatValue' => function($value, $row, $ckey, $meta) {
                return \koolreport\excel\Image::create([
                    'path' => '../../../assets/images/bar.png',

                    // use offsetX, offsetY as margin
                    'offsetX' => 5, // default: 0
                    'offsetX2' => -5, // default: 0
                    'offsetY' => 5, // default: 0
                    'offsetY2' => -5, // default: 0
                    
                    // 'name' => 'name', // default: ''
                    // 'description' => 'description', // default: ''
                    // 'roration' => 0, // default: 0
                    // 'hyperlink' => null, //Hyperlink object, default: null
                    // 'shadow' => null, //Shadown object, default: null
                    'shadowVisible' => true, // default: false
                    'shadowDirection' => 45, // default: 0
                ], true);
            }
        ],
    ]
));

Or a hyperlink one:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [        
        'url' => [
            'formatValue' => function ($value, $row, $ckey, $meta) {
                return \koolreport\excel\Hyperlink::create([
                    'url' => 'https://www.example.com',
                    'text' => 'Example site'
                ], true);
            },
        ]
    ]
));

Or a chart one:

\koolreport\excel\Table::create(array(
    ...
    'columns' => [        
        'Chart' => [
            'formatValue' => function($value, $row, $ckey, $meta) {
                return \koolreport\excel\PieChart::create([
                    "dataSource" => [
                        ["Quarter", "Sales"],
                        ["Q1", $row['Q1']],
                        ["Q2", $row['Q2']],
                        ["Q3", $row['Q3']],
                        ["Q4", $row['Q4']]
                    ],
                    "columns" =>[
                        'Quarter', "Sales"
                    ],
                ], true);
            },
        ],
    ]
));

There is one limitation with a column-belonging chart is that its dataSource must always be an array and does not support a data store like a individual chart does.

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:

Property	Default value	Meaning	Example 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 row	Customer: {customerName}
bottom	""	Template string to fill in the main column of a bottom group row	Product: {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" => "%" 
                        ]
                    ]
                ],

rowDetailTable #

Requirement: version >= 11.0.0

After each data row, users could build a detail table by using a function that returns an array of array of cell data. Each cell data is either an array which defines cellValue, rowspan, and colspan or a text which is the cell value itself.

    \koolreport\excel\Table::create(array(
        ...
        "columns" => [
            "customerName", "productLine", 
            // "productName", "dollar_sales"
        ],
        "rowDetailTable" => function($row) {
            return [
                [ // 1st row
                    "cell value text 1", // 1st cell of 1st row
                    "cell value text 2", // 2nd cell of 1st row
                    [ // 3rd  cell of 1st row
                        "cellValue" => $row["productName"] . " : " . $row["dollar_sales"],
                        "rowspan" => 3,
                        "colspan" => 2,
                    ],
                ],
            ];
        }

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:

    \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:

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

columnWidth #

A property that defines width of Table's columns. Its value could be a fixed number or a function that returns a number based on a column name or index:

    \koolreport\excel\Table::create(array(
        ...
        'columnWidth' => '30',
        'columnWidth' => function($columnName, $columnIndex) {
            if ($columnName === 'image') return '30';
            else return 'auto';
        },
    ));

rowHeight #

A property that defines height of Table's rows. Its value could be a fixed number or a function that returns a number based on a row value or index:

    \koolreport\excel\Table::create(array(
        ...
        'rowHeight' => '50',
        'rowHeight' => function($row, $rowIndex) {
            // return 'auto';
            return 10 * ($rowIndex + 1);
        },
    ));

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.