Adding suffix "%" to numbers in excel table

Aljaz Ketis commented on Jun 2, 2021

I want to render and export an excel file with a datatable inside of it and it does not want to apply suffix element "%" to it. I apply it like so:

"changes"                          => array(
                        "type"     => "number",
                        "decimals" => 1,
                        "suffix"   => "%",
                    ),

tried to add it inside view under "columns" and inside report.php with ColumnMeta, both don't work.

Sebastian Morales commented on Jun 3, 2021

Thank you. We found a bug in the excel format code and will fix it in the next version. Meanwhile you could apply this fix yourself. Pls open the file koolreport/excel/TableBuilder.php and replace this line:

    $formatCode = Util::get($cell, 'formatCode');

with this one:

    $formatCode = Util::get($styleArray, 'formatCode');

Let us know how this works for you. Tks,

Aljaz Ketis commented on Jun 3, 2021

Thank you, it works like a charm, can you notify me when this bug is gonna be resolved with new release?

Sebastian Morales commented on Jun 3, 2021

No problem at all, Aljaz. Cheers,

Aljaz Ketis commented on Jun 3, 2021

Is there any chance that i could make a prefix for numbers that are bigger than 0? I would like to add them "+" in front, but since in excel we cannot use format_value function and if i would like to format it as string, the decimal delimeter wont change based on locale, since that is what it is doing inside excel. Any clue what I could try to do?

Sebastian Morales commented on Jun 3, 2021

If you want total control of formatted values I suggest using the Map process in report's setup:

https://www.koolreport.com/docs/processes/map/

Then set the column's "type" to "string" so that already formatted values are kept exactly in excel. Rgds,

Aljaz Ketis commented on Jun 3, 2021

Even if I do that with Map I got 2 problems:

for this column decimal delimiter will be ".", becouse numbers in db are saved like that, while on other columns that are of type "number", which are not Mapped we got delimiter ",", becouse of my current locale and Excel changing it.
If it is a string, Excel puts warnings inside of it with which you can transform it to numbers...we cannot have that inside reports
Also column titles by the way, how can i make them as big as cell with the max_width? Currently they are as big the title of the column.

Sebastian Morales commented on Jun 4, 2021

Ok, I see that if your excel files were to be used by users in multiple locales the formatted string solution wouldn't work. Here's a solution that we might apply for the next version of the Excel package:

1 . Open the file koolreport/excel/TableBuilder.php and find the following lines:

    public function getFormatted($value, $meta)
    ...
        switch ($type) {
            case "number":    
                ...        
                $formatCode = "\"{$prefix}\"#,##0{$zeros}\"{$suffix}\"";

2 . Insert the following line:

    public function getFormatted($value, $meta)
    ...
        switch ($type) {
            case "number":    
                ...        
                $formatCode = "\"{$prefix}\"#,##0{$zeros}\"{$suffix}\"";
                $formatCode = Util::get($meta, "excelFormatCode", $formatCode); //add this line

3 . In either your report's setup or your excel view's columns definition, set the column meta's "formatCode" property like this:

    $prefix = "pre"; 
    $suffix = "$";
    ...
        "numericCol"
                 => [
                     "excelFormatCode" => "\"{$prefix}\"" . "+#,##0.00" . "\"{$suffix}\"" . ";" . "\"{$prefix}\"" . "-#,##0.00" . "\"{$suffix}\"",
                    "type"     => "number",    
                ]

Let us know how this works for you. Rgds,

Aljaz Ketis commented on Jun 4, 2021

It works, thank you again. What about the column_width inside excel? I would like to make a column width the same as the longest text inside a column, currently it takes the width of the headers text. And please also if you could, notify me if the changes will be published to the new koolreport library

Sebastian Morales commented on Jun 7, 2021

Pls open the file koolreport/excel/TableBuilder.php and comment out this line to see if it helps:

    $this->sheet->getColumnDimension($col)->setAutoSize(true);

Rgds,

KoolReport's Forum

Adding suffix "%" to numbers in excel table #2122

Build Your Excellent Data Report