KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

Excel export empty #1452

Closed Advanced Applications GmbH opened this topic on on May 19 - 11 comments

Advanced Applications GmbH commented on May 19

I've recently bought the Koolreport Pro license to export my (tabular) reports to Excel. Sadly, my exports have always been empty except for the static texts I have in the template (such as titles). So far I have done some debugging to verify that the datastore which I use to generate the table is indeed filled properly. Still, the Excel remains empty. My report is somewhat special as I have a huge amount of data to deal with with quite long running queries. Also, I do the final data processing in the OnRunEnd() event. This processing is absolutely necessary. The report displays fine when exporting to HTML, just the Excel is empty. Can you give me some suggestions on what might be going wrong?

Regards Marcel (Advanced Applications GmbH)

KoolReport commented on May 19

Hi,

May I know how large is your data? If you try to limit your data, does the Excel work?

Could you please post some of your code to illustrate.

Advanced Applications GmbH commented on May 20

The final datastore used for the table in the report isn't actually that large, only 87 rows with 23 columns. It's the data which gets processed to create that datastore which is large; it consists of 5 datastores of the following sizes: 88 rows à 2 columns, 20 rows à 3 columns, 36150 rows à 3 columns, 4173 rows à 3 columns and 16211 rows à 3 columns. Sadly, I can't post the code of the data transformation in OnRunEnd() as it is very specific about confidential data. But I can post the Excel template:


<?php
    use \koolreport\excel\Table;

    $sheet1 = "VM weekly backup report";
?>


<?php

/**
 * For efficiency, the table columns are created outside the create function and then referenced later
 */

$reportTableColumns = array(
    "name" => array(
        "label" => "VM name",
        "formatValue" => function($value) {
            return "<span class=\"d-block pt-1\">$value</span>";
        }
    )
);

$backupStrategies = array("SN", "DR", "AA");
$weekdays = array("mon", "tue", "wed", "thu", "fri" /* , "sat", "sun" */);

function preg_associate(array $connections, string $subject) {
    foreach ($connections as $pattern => $output) {
        if (preg_match($pattern, $subject)) return $output;
    }
    return null;
}

/* Insert columns for each weekday */
foreach ($weekdays as $weekday) {
    /* Insert a column for each backup strategy */
    foreach($backupStrategies as $columnLabel) {
        $dataColumnName = strtolower($weekday) . "_" . strtolower($columnLabel);
        $reportTableColumns[$dataColumnName] = array(
            "label" => $columnLabel,
            "formatValue" => function($value) {

                if (is_array($value)) {
                    $evaluation = $value[0];
                    $comments = $value[1];
                    $commentAttr = "";
                    foreach($comments as $backupStrategy => $backupComment) {
                        if (strtolower($backupComment) == "ok") continue;
                        $commentAttr .= "<b>$backupStrategy:</b> $backupComment<br>";
                    }
                    preg_replace('/<br>$/im', "", $commentAttr);
                } else {
                    $evaluation = $value;
                }

                $valueClass = "d-block text-center p-1 " . preg_associate(
                        array(
                            "/.*vio.*/im" => "bg-danger text-white",
                            "/.*N\/A.*/im" => "bg-secondary text-white"
                        ),
                        $evaluation
                    );

                return "<span" .
                    (!empty($valueClass) ? " class=\"$valueClass\"" : null) .
                    (is_array($value) && !empty($commentAttr) && strtolower($evaluation) != "ok" ?
                        " data-toggle=\"tooltip\" data-html=\"true\" title=\"$commentAttr\"" :
                        null) .
                    ">$evaluation</span>";
            }
        );
    }
}
?>

<meta charset="UTF-8">
<meta name="description" content="Weekly overview of VM backups">
<meta name="keywords" content="Excel,HTML,CSS,XML,JavaScript">
<meta name="creator" content="Koolreport">
<meta name="subject" content="">
<meta name="title" content="VM weekly backup report">
<meta name="category" content="Backup Report">
<meta name="company" content="Advanced Applications GmbH">

<div sheet-name="<?php echo $sheet1; ?>">
    <div>VMBackupReportWeekly</div>

    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('backupreporttable'),
            "headers" => array(
                array(
                    "" => array("colSpan" => 1),
                    "Monday" => array("colSpan" => 3, "cssClass" => "border-left"),
                    "Tuesday" => array("colSpan" => 3, "cssClass" => "border-left"),
                    "Wednesday" => array("colSpan" => 3, "cssClass" => "border-left"),
                    "Thursday" => array("colSpan" => 3, "cssClass" => "border-left"),
                    "Friday" => array("colSpan" => 3, "cssClass" => "border-left") //,
                    // "Saturday" => array("colSpan" => 3, "cssClass" => "border-left"),
                    // "Sunday" => array("colSpan" => 3, "cssClass" => "border-left")
                )
            ),
            "columns" => $reportTableColumns
        ));
        ?>
    </div>

</div>

Note that the columns for the table are compiled outside the table as the data can be very dynamic. The report works fine this way in HTML. Also I've tried to create the table by only specifying the dataSource, but still the Excel remains empty.

Advanced Applications GmbH commented on May 20

I've done some more debugging and I can see that in in the onRender() function of koolreport\excel\Widget the data is present and the number of columns is correct:

Somehow, the data gets lost along the way...

David Winterburn commented on May 20

Hi Marcel,

Would you please pipe very simple data to a datastore and use that datastore as datasource for the excel's Table? Please remove all options beside dataSource. Let us know the name of your excel template file and the export command (i.e $report->run()->exportToExcel(...)) as well.

Advanced Applications GmbH commented on May 20

Hey David, I've implemented a simple report with an ArrayDataSource both as a data source in the report settings and as a generated array in the OnRunEnd() function to replicate the behavior of my main report as closely as possible.

  • The name of the Excel Template file was SimpleReport.excel.php
  • The export command was:
$reportClass->run()->exportToExcel($currentReport)->toBrowser($currentReport . '.xlsx');

(The $reportClass and $currentReport variables are passed as GET parameters to my site)

Also here are the sources of my simple report:

SimpleReport.php

<?php

use koolreport\datasources\ArrayDataSource;

require_once 'vendor/autoload.php';

class SimpleReport extends koolreport\KoolReport {
    use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;

    protected function settings() {
        return array(
            "dataSources"=>array(
                "array_example_datasource"=>array(
                    "class"=>'\koolreport\datasources\ArrayDataSource',
                    "dataFormat"=>"associate",
                    "data"=>array(
                        array("customerName"=>"Johny Deep","dollar_sales"=>100),
                        array("customerName"=>"Angelina Jolie","dollar_sales"=>200),
                        array("customerName"=>"Brad Pitt","dollar_sales"=>200),
                        array("customerName"=>"Nicole Kidman","dollar_sales"=>100),
                    )
                ),
            )
        );
    }

    protected function setup() {
        $this->src('array_example_datasource')
            ->pipe($this->dataStore('sales_by_associate'));
    }


    /**
     * Emitted after report is run
     *
     * @return void
     * @throws Exception
     */
    protected function OnRunEnd()
    {
        $dataSet2 = array(
            array("Name" => "Microsoft", "Products" => "Office, Windows, Azure"),
            array("Name" => "Samsung", "Products" => "Phones, Fridges, Laptops"),
            array("Name" => "Sony", "Products" => "Cameras, Phones, Radios")
        );

        $backupReportCommentTable = new ArrayDataSource();
        $backupReportCommentTable->load($dataSet2)
            ->start();
        $backupReportCommentTable->pipe($this->dataStore('dataSet2'));
    }
}

SimpleReport.view.php

<?php
use \koolreport\widgets\koolphp\Table;
?>

<div class="report-content">
    <div class="text-center">
        <h1>Sales By Associate</h1>
    </div>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore('sales_by_associate')
    ));
    ?>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore('dataSet2')
    ));
    ?>
</div>

SimpleReport.excel.php

<?php
use \koolreport\excel\Table;

$sheet1 = "Simple report";
?>

<meta charset="UTF-8">
<meta name="description" content="Very simple report for testing">
<meta name="keywords" content="Excel,HTML,CSS,XML,JavaScript">
<meta name="creator" content="Koolreport">
<meta name="subject" content="">
<meta name="title" content="Simple report">
<meta name="category" content="Report">
<meta name="company" content="Advanced Applications GmbH">

<div sheet-name="<?php echo $sheet1; ?>">
    <div>Simple report</div>

    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('array_example_datasource')
        ));
        ?>
    </div>

</div>

<div sheet-name="Companies">
    <div>Simple report</div>

    <div>
        <?php
        Table::create(array(
            "dataSource" => $this->dataStore('dataSet2')
        ));
        ?>
    </div>

</div>

The behavior is the same as with my main report; the HTML renders fine but the Excel is empty except for the titles. Here is a preview of the rendered HTML:

David Winterburn commented on May 21

Hi Marcel,

Please change your excel template file name to: SimpleReportExcel.view.php and change your export command to:

$reportClass->run()->exportToExcel($currentReport . "Excel")->toBrowser($currentReport . '.xlsx');

Let us know the result or error if there's is. Thanks!

Advanced Applications GmbH commented on May 26

Hey David,

thanks for your suggestion. I've changed the file & code accordingly but unfortunately the Excel remains empty. Also, I've set breakpoints in the Excel.view.php file to examine the datastore at runtime and it is indeed empty.

David Winterburn commented on May 26

Hi Marcel,

Let's try one more thing, please change the dataSource property of the Table widget in your excel view like this:

//MyReportExcel.view.php
        Table::create(array(
            "dataSource" => "array_example_datasource"
        ));

Let us know the result. Thanks!

Advanced Applications GmbH commented on May 26

Hey David,

I've just noticed a mistake on my part regarding the simple report. In the SimpleReport.php file I pipe the array to the datastore sales_by_associate, not array_example_datasource. After correcting this, the Excel contains the data of the first ArrayDataSource and even both the

"dataSource" => "sales_by_associate" and the

"dataSource" => $this->dataStore("sales_by_associate")

work.

Advanced Applications GmbH commented on May 26

2nd post due to the forum software rejecting and truncating posts due to special characters:

But the data from the datastore generated in the OnRunEnd() remains empty. So this issue still needs to be resolved since this is a crucial part of my main report. Right now I'm thinking that I may be missing a function call in the following segment which is needed for the datastore to actually pull the data.

protected function OnRunEnd()
    {
        $dataSet2 = array(
            array("Name" => "Microsoft", "Products" => "Office, Windows, Azure"),
            array("Name" => "Samsung", "Products" => "Phones, Fridges, Laptops"),
            array("Name" => "Sony", "Products" => "Cameras, Phones, Radios")
        );

        $backupReportCommentTable = new ArrayDataSource();
        $backupReportCommentTable->load($dataSet2)
            ->start();
        $backupReportCommentTable->pipe($this->dataStore('dataSet2'));
    }

Maybe the datastore needs to be "started" similar to the datasource?

Advanced Applications GmbH commented on May 27

I solved it!

The issue was very simple logical error. When I create the extra datastore in the OnRunEnd() event, I start the data source and then pipe it to the datastore. But the piping has to happen before the start, otherwise the data doesn't arrive in the datastore. So in the code from my previous post has to be modified as follows:

protected function OnRunEnd() {
    /* [...] */
    $backupReportCommentTable = new ArrayDataSource();
    $backupReportCommentTable->load($dataSet2)
        ->start();
    $backupReportCommentTable->pipe($this->dataStore('dataSet2'));
}

to

protected function OnRunEnd() {
    /* [...] */
    $backupReportCommentTable = new ArrayDataSource();
    $backupReportCommentTable->load($dataSet2)
        ->pipe($this->dataStore('dataSet2'));
    $backupReportCommentTable->start();
}

Of course, when one would generate multiple data sources / stores, a foreach akin to the one in the Koolreport\run() method would be more elegant.

I've tried it with HTML, Excel and even JSON and now the data is there. Why it was working in the HTML before is beyond me, but that's it. Now I modify my main report accordingly.

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
solved

Excel