formatCode in excelStyle

Frank Sun commented on Sep 21, 2021

Hi,

I tryed your example https://www.koolreport.com/examples/reports/excel/pivottable/

The downloaded excel file, the "Number of Sales" field is displayed as 1.00 .

I think it is because of the default format code of numeric field in excel.

Is it possible to specify the format code of this field to display as integer? (1 instead of 1.00)

Thanks. Frank

Sebastian Morales commented on Sep 22, 2021

Frank, in the next version of Excel package we will provide a custom "excelFormatCode" option for excel PivotTable widget to format pivot number exactly like how users want. In the meantime you could use a workaround to remove decimal point for Number of Sales pivot field like this:

->pipe(new Pivot(array(
    "dimensions" => ...
    "aggregates" => array(
        "count" => "salesField", // assuming this is your Number of sales
)))
->pipe(new ColumnMeta(array(
    "salesField - count" => array(
        "type" => "string"
    )
))

This would make the Number of sales pivot field to be of type "string" and not formatted as decimal number.

Frank Sun commented on Sep 22, 2021

Hi Sebastian,

Thanks. It works.

Another question, how to display a blank field instead to '-' when the count is 0 ?

Frank

Sebastian Morales commented on Sep 22, 2021

If the value is null there's a property of PivotTable called "emptyValue" which is default to "-". If the value is 0 you could use "map" property like this:

    PivotTable::create(array(
        ...
        "map" => array(
            'dataCell' => function ($value, $cellInfo) {
                if ($value == 0) return "-";
                else return $value;
            }
        )

Let us know if it works for you. Tks,

Frank Sun commented on Sep 22, 2021

I've tried below:

PivotTable::create(array(
    ...
    "map" => array(
        'dataCell' => function ($value, $cellInfo) {
            return $cellInfo['formattedValue'];
        }
    )

It also works. Thanks.

KoolReport's Forum

FormatCode in excelStyle #2348

Build Your Excellent Data Report