KoolReport's Forum

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

Excel PivotTable: excelStyle -> dataCell on a total cell getting overwritten #1365

Closed Carl Steenkamp opened this topic on on Mar 31, 2020 - 4 comments

Carl Steenkamp commented on Mar 31, 2020

Maybe let me start off with the question rather, is it possible to style 'total' datacells? What I am after is to fill all cells that relate to a total.

'rowHeader' => function ($header, $headerInfo) use ($totalStyleArray, $styleArray) {
	if ($header === '{{all}}') {
		if ($headerInfo['total']) {
			return $totalStyleArray;
		}
	}
	return $styleArray;
},
'dataCell' => function ($header, $headerInfo) use ($totalStyleArray, $styleArray) {
	if (isset($headerInfo['column']['crop_name']) &&
		isset($headerInfo['column']['crop_name']['total']) &&
		$headerInfo['column']['crop_name']['total']) {
			return $totalStyleArray;
	}
	return $styleArray;
},

With the above example it seems like my style is being overridden

David Winterburn commented on Apr 1, 2020

Hi Carl,

I've tested it and confirmed it's possible to style total cells. Please try this code:

use \koolreport\core\Utility;
...
    'dataCell' => function($value, $cellInfo) use ($totalStyleArray, $styleArray) { 
        $cropTotal = Utility::get($cellInfo, ['column', 'crop_name', 'total']);
        return $cropTotal ? $totalStyleArray : $styleArray;
    }

One thing to note is that for excel style color you have to use hex code instead of color name. Let us know if you still have problem with this. Thanks!

Carl Steenkamp commented on Apr 1, 2020

Hi David,

Thank you for your fast response. It is however still not working for me.

Please see the below that I am using to test.

TestReportExcel.view.php

<?php

use koolreport\core\Utility;
use koolreport\excel\PivotTable;

?>
<html>
<body style="margin:1in">

<meta charset="UTF-8">
<meta name="description" content="Free Web tutorials">
<meta name="keywords" content="Excel,HTML,CSS,XML,JavaScript">
<meta name="creator" content="John Doe">
<meta name="subject" content="subject1">
<meta name="title" content="title1">
<meta name="category" content="category1">

<div sheet-name="Crop Unloads PivotTable">
    <?php
    $totalStyleArray = [
        'fill' => [
            'fillType' => 'solid',
            'color' => [
                'rgb' => 'A0A0A0',
                'argb' => 'FFA0A0A0',
            ],
        ],
        'font' => [
            'bold' => true,
        ]
    ];

    $styleArray = [
        'font' => [
            'color' => [
                'rgb' => '000000',
                'argb' => 'ff000000',
            ]
        ],
    ];
    ?>
    <div>Crop Unloads PivotTable</div>
    <div>
        <?php
        PivotTable::create(array(
            "dataSource" => 'unloadsPivot',
            'hideSubTotalRows' => false,
            'hideSubTotalColumns' => true,
            'hideGrandTotalRow' => false,
            'hideGrandTotalColumn' => true,
            "showDataHeaders" => false,
            'excelStyle' => array(
                'rowHeader' => function ($header, $headerInfo) use ($totalStyleArray, $styleArray) {
                    if ($header === '{{all}}' && $headerInfo['total'])
                        return $totalStyleArray;
                    return $styleArray;
                },
                'dataCell' => function ($value, $cellInfo) use ($totalStyleArray, $styleArray) {
                    return Utility::get($cellInfo, ['column', 'crop_name', 'total']) ? $totalStyleArray : $styleArray;
                },
            )
        ));
        ?>
    </div>

</body>
</html>

TestReportExcel.php

<?php

namespace Haulmaster\Reports;

use koolreport\pivot\processes\Pivot;

class TestReportExcel extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
    use \koolreport\export\Exportable;
    use \koolreport\excel\ExcelExportable;

    function settings()
    {
        return array(
            "dataSources" => array(
                "unloads" => array(
                    'filePath' => 'PUT CSV PATH HERE',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"
                ),
            )
        );
    }

    function setup()
    {
        $node = $this->src('unloads');
        $node
            ->pipe(new Pivot(array(
                "dimensions" => array(
                    "column" => "crop_name",
                    "row" => "client_name, field_name, farm_name",
                ),
                "aggregates" => array(
                    "sum" => "weight",
                ),
            )))
            ->pipe($this->dataStore('unloadsPivot'));
    }
}

unloads.csv

client_name;farm_name;field_name;crop_name;weight
Mosiman Bros.;Section 14;West Half;Corn;44571
Mosiman Bros.;Section 15;NE 1/4;Corn;644063
Mosiman Bros.;Section 28;NE 1/4;Corn;56047
Roe Farms;Baloun;Section 30;Soybeans;57587
S&S Farms;Ankrum;Ankrum 25;Corn;2030212
S&S Farms;Hoffman;Section 4;Corn;999562
S&S Farms;S&S Farms;McGee Sec 9;Corn;284326

Result:

Desired Result:

David Winterburn commented on Apr 1, 2020

Hi Carl,

This is probably what you want:

    'dataCell' => function($value, $cellInfo) { 
        $isRowTotal = \koolreport\core\Utility::get($cellInfo, ['row', 'hasTotal']);
        return $isRowTotal ? $totalStyleArray : $styleArray;

Let us know if it helps. Thanks!

Carl Steenkamp commented on Apr 1, 2020

Excellent! Thank you that seems to have done the trick

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
solved

Excel