Export to Big Spreadsheet

Get started #

If you don't need chart or pivottable in your spreadsheet file, BigSpreadsheetExportable trait helps you to export huge data faster and uses much less memory than ExcelExportable or CSVExportable.

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

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

<?php
$report = new MyReport;
$report->run()
->exportToXLSX()
//->exportToODS()
//->exportToCSV()
->toBrowser("myreport.xlsx");

Normal spreadsheeet exporting options #

Defines datastores for exporting:

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

Columns option:

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

Pivot spreadsheet exporting options #

Unfortunately, BigSpreadsheetExportable doesn't support Pivot datastore export because streaming data to file doesn't allow for complex cell structure.

Spreadsheet export template #

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

<?php
//exportSpreadsheet.php
include "MyReport.php";
$report = new MyReport;
$report->run();
$report
->exportToXLSX(
//->exportToODS(
    'MyReportSpreadsheet', ['useLocalTempFolder' => true]
)
->toBrowser("MyReport.xlsx");

$report->exportToCSV(array(
    'dataStores' => array(
        'orders' => array(
            'columns' => array('Customer', 'Total', 0, 1),
        ),
    ),
    'BOM' => false,
    'fieldDelimiter' => ';',
    'useLocalTempFolder' => true,
))
->toBrowser("MyReport.csv");
<?php
//MyReportSpreadsheet.view.php
<?php
    use \koolreport\excel\Text;
    use \koolreport\excel\Table;
?>
<div sheet-name="<?php echo $sheetName; ?>">
    <div>
        Report <?php echo $reportName; ?>
    </div>

    <div translation="2:4">
        Text::create([
            "text" => "Orders List of Sales"
        ]);
    </div>

    <div translation="3:5">
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('orders'),
        ));
        ?>
    </div>
</div>

To use a spreadsheet export template file, pass its name (without the extension '.view.php') to the exportToXLSX(), exportToODS() or exportToCSV() 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 worksheet (applicable for xlsx and ods files only).

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

Second level divs represents blocks of content in each worksheet. A block of content could be some text or a table. Each block of content could have its top-left cell set via the div's translation attribute. This attribute translates content by {number of columns}:{number of rows}.

<div sheet-name="sheet1">
    <div translation="2:4">
        Report <?php echo $reportName; ?>
    </div>
</div>

Unlike excel export, for big spreadsheet export we can only use the Table and Text widgets. It's because big spreadsheet utilizes streaming data to file to reduce memory footprint when exporting millions of data rows. This type of streaming rows doesn't allow for chart or pivot table formats.

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

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

Spreadsheet style array #

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

<?php
    $spreadsheetStyleArray = [
        'font' => [
            'bold' => false,
            'italic' => true,
            'underline' => false,
            'strikethrough' => true,
            'name' => 'Arial',
            'size' => '14',
            'color' => '808080',
        ],
        'border' => [
            // 'color' => '000000',
            'width' => 'thick', //'thin', 'medium', 'thick'
            // 'style' => 'solid', //'none', 'solid', 'dashed', 'dotted', 'double'.
            'top' => [
                'color' => '000000',
                'width' => 'medium', //'thin', 'medium', 'thick'
                'style' => 'solid', //'none', 'solid', 'dashed', 'dotted', 'double'.
            ],
            'right' => [],
            'bottom' => [],
            'left' => [],
        ],
        'backgroundColor' => '00ff00',
        'wrapText' => true,
    ];;


?>