Wrong Excel file format when exporting pivot table with 3 row headers

Frank Sun commented on Dec 31, 2020

Hi,

I tried to add the 3rd row header in Pivot. The report shown on screen is correct. When I tried to export to excel, the excel file was generated successfully. But the file format contains some wrong information. It cannot be shown correctly in Excel.

I try with your example file, it comed out with the same issue.

Example: ./examples/reports/excel/pivottable/MyReport.php

I add a row "productLine" in dimensions:

    $node
    ->pipe(new Filter(array(
        array('customerName', '<', 'Au'),
        array('orderYear', '>', 2003),
    )))
    ->pipe(new Pivot(array(
        "dimensions" => array(
            "column" => "orderYear, orderQuarter",
            "row" => "customerName, productLine, productName",
        ),
        "aggregates" => array(
            "sum" => "dollar_sales",
            "count" => "dollar_sales",
        ),
    )))
    ->pipe($this->dataStore('salesPivot'));

The report shown on screen is correct. But when export to excel, the file cannot be shown correctly in Excel.

Could you please advise how I can avoid it?

Frank Sun commented on Dec 31, 2020

Some more information:

When I mark out the disable subtotal option, the file is correct. // 'hideSubTotalRows' => true, // 'hideSubTotalColumns' => true,

But I really don't want the subtotal line shown in my excel. Is there any solution?

David Winterburn commented on Dec 31, 2020

Hi Frank,

Thanks a lot for your feedback! We've tested it and confirmed it's a bug with "hideSubTotalRows" option in PivotTable excel export when there are three or more fields in the row dimension. We will fix it in the next release of the Excel package and KoolReport Pro. Meanwhile you could apply this small fix right away:

Open the file koolreport/excel/PivotTableBuilder.php and replace these lines:

                    $rowspan = $nodeMark[$f]['numChildren'];
                    if ($hideSubTotalRows && $rowspan > 1) $rowspan--;

with these ones:

                    $rowspan = $hideSubTotalRows ? 
                        $nodeMark[$f]['numLeaf'] : $nodeMark[$f]['numChildren'];

Then test export PivotTable to excel again and let us know if there's any problem. Thanks!

Frank Sun commented on Dec 31, 2020

Thanks. It works.

BTW, When exporting PivotTable, is there any option to make the column width of row header "autoFit"? Or specify a fix width to these columns.

I went through the options of excelStyle, but cannot find what I want.

David Winterburn commented on Dec 31, 2020

Hi Frank,

All exported excel columns are set auto size = true but in reality the column width could be somewhat bigger than the content width. If you want a change you could the file koolreport/excel/PivotTableBuilder.php and replace these lines;

            // $sheet->getColumnDimension($col)->setWidth($maxLength[$i]);
            $sheet->getColumnDimension($col)->setAutoSize(true);

with these ones:

            $sheet->getColumnDimension($col)->setWidth($maxLength[$i]);
            // $sheet->getColumnDimension($col)->setAutoSize(true);

We will research for a more exact auto size column width solution. Thanks!

Frank Sun commented on Dec 31, 2020

Thanks. It solved my problem.

In new solution, please consider the scenario: In PivotTable with multiple row headers, if you calculate the width of first row, normally the row header columns will be squeezed.

Wish you make this great tool more powerful.

KoolReport's Forum

Wrong Excel file format when exporting pivot table with 3 row headers #1811

Build Your Excellent Data Report