KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Excel Export Really Slow #1353

Open Eric opened this topic on on Mar 20, 2020 - 7 comments

Eric commented on Mar 20, 2020

My excel export runs incredibly slowly. When trying to export a 10,000 line array, it can take upwards of 5 minutes for the file to finish building.

Below is my code.

<?php
use \koolreport\excel\Table;

$sheetName = 'sheet1';
?>
<div sheet-name="<?php echo $sheetName; ?>">
    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->exportData,
        ));
        ?>
    </div>
</div>
public function exportNamedReport(Request $request)
    {
        $report = $request->route()->getAction()['report'];
        $pageTitle = $request->route()->getAction()['pageTitle'];
        $use = "App\Reports\\$report";

        $report = new $use;
        $report->run();
        $report->exportToXLSX('reportExport')->toBrowser($pageTitle.".xlsx");
    }

To reproduce these issues, I have been sending 10,000 line arrays via this code:

while ($i < 10000){
                $export[] = array('test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test');
                $i += 1;
            }
$this->exportData = new \koolreport\core\DataStore($export);

Any ideas would be appreciated.

KoolReport commented on Mar 20, 2020

In case that you only need to export a Table to excel with large number of rows, I suggest you to try the BigSpeadSheet. Here is the example.

Eric commented on Mar 20, 2020

That is what I am currently using. On my report I have

use \koolreport\excel\BigSpreadsheetExportable;
David Winterburn commented on Mar 23, 2020

Hi Eric,

To improve excel export speed, please pipe your data to a datastore in your report' setup file and use that datastore's name as datasource in your excel template's Table:

//MyReport.php
...
function set()
{
    $this->src(...)
    ...
    ->pipe($this->dataStore('myDataStore');
}

//MyReportExcel.php
        Table::create(array(
            "dataSource" => "myDataStore",
        ));

With this setup, KoolReport won't have to first render 10.000 lines of data to the template file before converting it to excel, which mostly accounted for the current slowness.

Please try it and let us know the result. Thanks!

Eric commented on Mar 23, 2020

I am having an issue getting an array into the datastores. Because I want to have more dynamic report creation, I have it setup a bit differently then the examples. Also, as a side note, I am using Laravel.

So I have a controller that runs this code

public function getNamedReport(Request $request)
    {
        $report = $request->route()->getAction()['report'];
        $path = $request->path();
        $pageTitle = $request->route()->getAction()['pageTitle'];
        $use = "App\Reports\\$report";

        $report = new $use;
        $report->run();
        return view("admin.reports.layout", ["report" => $report, "pageTitle" => $pageTitle, "path" => $path]);
    }

and then I have separate reports that generate their own data. Right now I have them generating that data in the setup function which is what I was doing with this:

while ($i < 10000){
                $export[] = array('test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test');
                $i += 1;
            }
$this->exportData = new \koolreport\core\DataStore($export);

and then they get displayed like this:

Table::create(array(
    "dataStore"=>$this->data,
    ...
));

Is there a more proper way of generating and passing arrays without having to do this?

$report = new MyReport(array(
    "data"=>array(
        array("Item","Cost"),
        array("Accessories",3000),
        array("Human Resource",20000),
        array("Material",50000),
        array("Machine",30000),
        array("Accessories",5000),
        array("Machine",10000),
        array("Material",20000),
    )
));
$report->run()->render();
David Winterburn commented on Mar 24, 2020

Hi Eric,

Please try ArrayDataSource:

https://www.koolreport.com/docs/datasources/arraydatasource/

Then pipe the data source to a datastore in your report's setup. Let us know if you have any question. Thanks!

Eric commented on Mar 24, 2020

Those examples show only static setting of arrays. How would I put an array that gets pulled from a database and manipulated into there?

David Winterburn commented on Mar 25, 2020

Sorry I don't get your point very clearly. If your data is from a database why don't you use PDODataSource, MySQLDataSource, SQLSRVDataSource, etc? If that's not possible just pull data with php in the report's setting() function and put it in ArrayDataSource.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

Excel