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

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

<div range="A2:H2">
    \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>

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