• Get started
• General exporting options
• Pivot excel exporting options
• Excel export template (version >= 6.0.0)
  • Excel style array

Export to Excel

Get started #

To use the export feature in report, you need to register the ExcelExportable in your report like below code

class MyReport extends \koolreport\KoolReport
{
    use \koolreport\excel\ExcelExportable;
    ...
}

Then now you can export your report to excel like this:

<?php
$report = new MyReport;
$report->run()->exportToExcel()->toBrowser("myreport.xlsx");

General exporting options #

When exporting to excel, you could set a number of property for the excel file.

<?php
$report = new MyReport;
$report->run()->exportToExcel(array(
    "properties" => array(
        "creator" => "",
        "title" => "",
        "description" => "",1
        "subject" => "",
        "keywords" => "",
        "category" => "",
    )
))->toBrowser("myreport.xlsx");

From the version 3.0.0, you have more options:

<?php
$report = new MyReport;
$report->run()->exportToExcel(array(
    "dataStores" => array(
        'salesReport' => array(
            "columns"=>array(
                0, 1, 2, 'column3', 'column4' //if not specifying, all columns are exported
            )
        )
    )
))->toBrowser("myreport.xlsx");

Pivot excel exporting options #

Beside general options, when exporting a pivot data store you could set several options similar to when viewing a pivot table widget.

<?php
$report = new MyReport;
$report->run()->exportToExcel(array(
    "dataStores" => array(
        'salesReport' => array(
            'rowDimension' => 'column',
            'columnDimension' => 'row',
            "measures"=>array(
                "dollar_sales - sum", 
            )
        )
    )
))->toBrowser("myreport.xlsx");

Excel export template (version >= 6.0.0) #

You could programmatically set up a template file for excel export similar to a report's view file.

<?php
//exportExcel.php
include "MyReport.php";
$report = new MyReport;
$report->run();
$setting = ['useLocalTempFolder' => true];
$report->exportToExcel('MyReportExcel', $setting)->toBrowser("MyReport.xls");

<?php
//MyReportExcel.view.php
<?php
    use \koolreport\excel\Table;
    use \koolreport\excel\PivotTable;
    use \koolreport\excel\BarChart;
    use \koolreport\excel\LineChart;
?>
<div sheet-name="<?php echo $sheetName; ?>">
    <div cell="A1">
        <?php echo $reportName; ?>
    </div>
    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('orders'),
        ));
        ?>
    </div>
    <div range="A25:H45">
        <?php
        LineChart::create(array(
            "dataSource" => $this->dataStore('salesQuarterProduct'),
        ));
        ?>
    </div>
    <div>
        <?php
        PivotTable::create(array(
            "dataSource" => 'salesPivot',
        ));
        ?>
    </div>
</div>

To use an excel export template file, pass its name (without the extension '.view.php') to the exportToExcel() method.

In the template file, have access to your report via $this as well as its parameters $this->params and datastore $this->datastore().

The template file consists of 2 level of div tags. Each first level div represents a separated excel worksheet.

<div sheet-name="sheet1">
</div>

Second level divs represents blocks of content in each worksheet. A block of content could be some text, a table, a chart, a pivot table. Each block of content could have its top-left cell set via the div's cell attcell attribute or its range set via the div's range attribute. The range attribute would work for text and chart and for neither table nor pivot table.

<div sheet-name="sheet1">
    <div cell="A1" range="A1:E1">
        <?php echo $reportName; ?>
    </div>
</div>

In the excel package, we have table, pivot table and chart widgets which are similar to the same name widgets in other packages of KoolReport.

When setting a datasource for a widget, you could use either a datastore name or a datastore object of the your report.

<?php
//MyReport.excel.php
<?php
    use \koolreport\excel\Table;
    use \koolreport\excel\PivotTable;
?>
<div sheet-name="sheet1">
    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('orders'),
        ));
        ?>
    </div>
    <div>
        <?php
        PivotTable::create(array(
            "dataSource" => 'salesPivot',
        ));
        ?>
    </div>
</div>

With chart widget, there's another property called "excelDataSource" which could be set to be the name of a table widget in the template. In this case data for the chart would be drawn from the table widget instead of from a datastore.

<?php
//MyReport.excel.php
<?php
    use \koolreport\excel\Table;
    use \koolreport\excel\BarChart;
?>
<div sheet-name="<?php echo $sheetName; ?>">
    <div range="A25:H45">
        <?php
        Table::create(array(
            "name" => "customerSales",
            "dataSource" => $this->dataStore('sales'),
        ));
        ?>
    </div>
    <div range="A25:H45">
        <?php
        BarChart::create(array(
            'excelDataSource' => 'customerSales', 
        ));
        ?>
    </div>
</div>

Excel style array #

For some elements in the template file you could set their excel style. A style array can dictate some main excel styles:

<?php
    $styleArray = [
        'font' => [
            'name' => 'Calibri', //'Verdana', 'Arial'
            'size' => 30,
            'bold' => false,
            'italic' => FALSE,
            'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
            'strikethrough' => FALSE,
            'superscript' => false,
            'subscript' => false,
            'color' => [
                'rgb' => '000000',
                'argb' => 'FF000000',
            ]
        ],
        'alignment' => [
            'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
            'vertical' => 'bottom',//top, center, justify, distributed
            'textRotation' => 0,
            'wrapText' => false,
            'shrinkToFit' => false,
            'indent' => 0,
            'readOrder' => 0,
        ],
        'borders' => [
            'top' => [
                'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
                'color' => [
                    'rgb' => '808080',
                    'argb' => 'FF808080',
                ]
            ],
            //left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
        ],
        'fill' => [
            'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
            'rotation' => 90,
            'color' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'startColor' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
            'endColor' => [
                'argb' => 'FFFFFF',
                'argb' => 'FFFFFFFF',
            ],
        ],
    ];
?>