• Get started
• Normal spreadsheeet exporting options
• Pivot spreadsheet exporting options
• Spreadsheet export template
  • Spreadsheet style array

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.

Since version 10.0.0, BigSpreadsheetExportable trait's csv export function is renamed to exportToBigCSV to avoid duplicate name with CSVExportable trait's one. Since this version CSVExportable's exportToCSV is noticeably faster than BigSpreadsheetExportable's exportToBigCSV and has many more options like "enclosure", "nullString", etc.

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()
//->exportToBigCSV()
->toBrowser("myreport.xlsx");

Normal spreadsheeet exporting options #

Defines datastores for exporting:

<?php
$report = new MyReport;
$report->run()
->exportToXLSX(
//->exportToODS(
//->exportToBigCSV(
    array(
        "dataStores" => array(
            'salesReport' => array(
                // 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' // the 1st, 2nd, 3rd columns and column with names "column3", "column4" are exported
            )
        )
    )
))->toBrowser("myreport.xlsx");

Pivot spreadsheet exporting options #

Since version 10.0.0, BigSpreadsheetExportable supports Pivot datastore export and PivotTable/PivotMatrix widget.

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->exportToBigCSV(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 exportToBigCSV() 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,
    ];;


?>