KoolReport's Forum

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

Excel exporting Table data presentation problem #1169

Closed Peter Harari opened this topic on on Nov 19 - 10 comments

Peter Harari commented on Nov 19

Hi.

I'm trying to export a list to Excel, but I'm having issues on how the data format has to be to be displayed correctly in the sheet.

This is the dataSource I set:

array(
        "dataSources" => array(
            "data" => array(
                "class" => '\koolreport\datasources\ArrayDataSource',
                "dataFormat" => "associate",
                "data" => $this->tableSettings['data']
            )
        )
    );

The data is a simple associative array that I'm using in Table to print in the browser and to export to PDF. But in the Excel sheet, the whole data is displayed in a single cell, like that:

ColumnA | ColumnB | Row 1 Cell 1 | Row 1 Cell 2 | Row 2 Cell 1 | Row 2 Cell 2 and so on....

I tried the following settings with no luck:

Columns:

array [
  "code" => "code"
  "name" => "name"
]

Data:

array:46 [
  0 => array:2 [
    "code" => "code"
    "name" => "name"
  ]
  1 => array:2 [
    "code" => "062"
    "name" => "AutoFill 4.7"
  ]
  2 => array:2 [
    "code" => "063"
    "name" => "AutoFill 5"
  ]
  3 => array:2 [
    "code" => "032"
    "name" => "Focus Test 1"
  ]
  and so on...
]

I'm following this example but it is using a csv file as data source, not an array like I am. It seems that all other excel examples are similar, so I can't be sure how to achieve it.

Any advice or reference to help me on this problem? Thank you in advance.

David Winterburn commented on Nov 20

Hi Peter,

Would you please post your php code for the report's setup and export? And please try to show a table with your array data in the report's view file to see if it works. Thanks!

Peter Harari commented on Nov 20

Hi David.

Actually I'm using the same array data to display a Table in the browser for printing and to generate a PDF. I'm only changing data structure to export as Excel. The report setup is also the same for the 3 cases, here it goes:

public function settings()
{
    return array(
        "dataSources" => array(
            "data" => array(
                "class" => '\koolreport\datasources\ArrayDataSource',
                "dataFormat" => "associate",
                "data" => $this->tableSettings['data']
            )
        )
    );
}

protected function setup()
{
    $this
        ->src('data')
        ->pipe($this->dataStore("result"));
} 

Above is a class of mine called ListReport, which extends KoolReport. Below is how I generate the export(being $report an instance of the class mentioned above):

$report
    ->run()
    ->exportToExcel('ListReportExcel')
    ->toBrowser("$reportName.xlsx");

And in the ListReportExcel.view.php I just call the Table:

Table::create(array(
    'data' => ...,
    'columns' => ...);

The very same setup above works for all 3 cases, as they use the same class. I just change the rendering method for each case in the Controller, like:

$report = new \App\Reports\ListReport(array(
    "data" => $this->result->data,
    "columns" => $columns
));

$reportName = $this->result->headerTitle . " - " . $this->result->headerSubtitle;

if ($this->result->toPdf)
{
    $report
        ->export('ListReportPdf')
        ->pdf(array(
            "format" => "A4",
            "orientation" => "portrait"
        ))
        ->toBrowser("$reportName.pdf");
}
else if ($this->result->toExcel)
{
    $report
        ->run()
        ->exportToExcel('ListReportExcel')
        ->toBrowser("$reportName.xlsx");
}
else 
{
    return view('report')->with(['report' => $report, 'model' => $this->result]);
}

I hope this clarifies my situation. If array data should be in another format for the excel, no problem, I just need to know which format is it.

Thank you.

David Winterburn commented on Nov 21

Hi Peter,

In the file ListReportExcel.view.php, please set the Table widget's dataSource like this:

Table::create(array(
    'dataSource' => $this->dataStore("result"),
    'columns' => ...);

Let us know the result. Thanks!

Peter Harari commented on Nov 21

Hi David.

Still, the same result. Check the export result here.

Also, here is a var_dump of the settings used in Table to generate that result.

I hope this helps. If there is any additional info needed, please let me know.

Thank you!!

David Winterburn commented on Nov 21

Hi Peter,

Please try removing the columns property in ListReportExcel.view.php like this:

Table::create(array(
    'dataSource' => $this->dataStore("result")
);

Let us know the result. Thanks!

Peter Harari commented on Nov 21

HI David.

Same result, unfortunately. In this test, only data(with the data array) and dataSource keys where set for the Table settings.

David Winterburn commented on Nov 21

Hi Peter,

I could not replicate your issue with the following example. Please try the exact follow code and let me know the result:

//ListReport.php
public function settings()
{
    return array(
        "dataSources" => array(
            "data" => array(
                "class" => '\koolreport\datasources\ArrayDataSource',
                "dataFormat" => "associate",
                "data" => [
                    ["code" => '083', 'name' => 'Highlight 2']
                ]
            )
        )
    );
}

protected function setup()
{
    $this
        ->src('data')
        ->pipe($this->dataStore("result"));
} 
//ListReportExcel.view.php
<div>
    <div>
        <?php
            Table::create(array(
                "dataSource" => $this->dataStore('result'),
            ));
        ?>
    </div>
</div>

Thanks!

Peter Harari commented on Nov 21

Hi David.

Same problem. Got a OneDrive link this time, just to try another view: https://1drv.ms/x/s!AuiE2y4jKwA3hBNjE6Jjxl7nKjuk

Going to add some prints of my code, in case I missed something of what you have asked:

Controller:

Report class ListReport:

View ListReportExcel:

Thank you.

David Winterburn commented on Nov 22

Hi Peter,

I think I found the bug. In your excel view file, please use the excel Table widget and not the core one. The core widget is used for rendering html in browsers only. So please replace this:

//ListReportExcel.view.php
use \koolreport\widgets\koolphp\Table;

with:

//ListReportExcel.view.php
use \koolreport\excel\Table;

Hope that solves your problem. Thanks!

Peter Harari commented on Nov 22

Hi David,

That is great! That was the problem actually. I probably missed that reference detail in the examples.

Thank you!!

KoolReport Is Free and Open-Source!

KoolReport is a professional php reporting framework which saves you tons of time to construct dynamic data report & dashboard.

  • Connect to various datasources such as MySQL, SQL Server, MongoDB or even from CSV or Excel file.
  • Contain series of powerful built-in data processes for your data manipulation.
  • Generate stunning charts and graphs to help you communicate data insights to your audiences effectively.
  • Integrate seamlessly with any php frameworks such as Laravel, CodeIgniter, Symfony.
Download Now and register our tutorials to get started!

Download KoolReport
help needed
solved

Excel