• excelStyle
• mergeCells (version >= 10.0.0)
• showDuplicateRowHeaders and showDuplicateColumnHeaders (version >= 10.0.0)
• PivotMatrix

PivotTable and PivotMatrix widgets (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 #

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>