KoolReport's Forum

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

Total column in exported Excel showing 2 decimals instead of 9 #1350

Open Edmund opened this topic on on Mar 17, 2020 - 10 comments

Edmund commented on Mar 17, 2020

Hi, when i export a table to excel which has a Total column with 0 decimal, it shows as 2 decimals in the excel file. I tried setting decimals to 0 using new ColumnMeta as well as in Table::create.

Not sure if it's a bug or issue with my codes. Any help is appreciated.

Thanks.

    protected function setup()
    {
        if (isset($this->params['mthfrom'])) {
            $mthfrom = Carbon::createFromDate($this->params['mthfrom'])->startOfMonth()->toDateString();   
        } else {
            $mthfrom = Carbon::createFromDate()->startOfMonth()->toDateString();   
        }
        if (isset($this->params['mthto'])) {  
            $mthto = Carbon::createFromDate($this->params['mthto'])->endOfMonth()->toDateString();
        } else { 
            $mthto = Carbon::createFromDate()->endOfMonth()->toDateString();
        }
        if (isset($this->params['listing_type'])) {  
            $listing_type = $this->params['listing_type'];
        } else { 
            $listing_type = array('1','2');  
        }

        //Now you can use Eloquent inside query() like you normally do
        $this->src('elo')->query(
            District::select('districts.id', 'districts.name AS district_name', 'regions.name AS region_name',
                DB::raw("sum(case when listings.house_type_id = '1' then 1 else 0 end) AS Terrace"),
                DB::raw("sum(case when listings.house_type_id = '2' then 1 else 0 end) AS CornerTerrace"),
                DB::raw("sum(case when listings.house_type_id = '3' then 1 else 0 end) AS SemiD"),
                DB::raw("sum(case when listings.house_type_id = '4' then 1 else 0 end) AS Detached"),
                DB::raw("sum(case when listings.house_type_id = '5' then 1 else 0 end) AS GCB"),
                DB::raw("sum(case when listings.house_type_id = '6' then 1 else 0 end) AS Shophouse"),
                DB::raw("sum(case when listings.house_type_id = '7' then 1 else 0 end) AS Land"),
                DB::raw("sum(case when listings.house_type_id = '8' then 1 else 0 end) AS Cluster"),
                DB::raw("FORMAT(count(listings.id),0) AS total")
            )
            // ->join('admins', 'listings.admin_id', '=', 'admins.id')
            // ->whereBetween('listings.created_at', [$mthfrom,$mthto])
            // ->whereIn('listing_status_id', array(1, 4))
            ->leftjoin('listings', function($join) use ($mthfrom,$mthto,$listing_type)
            {
                $join->on('listings.district_id', '=', 'districts.id');
                $join->whereBetween('listings.created_at', [$mthfrom,$mthto]);
                $join->whereIn('listings.listing_status_id', array(1));
                $join->whereIn('listings.listing_type_id', $listing_type);
            })
            ->leftjoin('regions', 'districts.region_id', '=', 'regions.id')
            ->leftjoin('admins', function($join)
            {
                $join->on('listings.admin_id', '=', 'admins.id');
                $join->where('admins.active', '=', 1);
            })
            ->whereIn('districts.id', [10, 11, 21, 13, 14, 15, 16, 19, 20, 28])
            ->groupBy("districts.id") 
        )
        ->pipe(new ColumnMeta(array(
            "total"=>array(
            "type" => "number",
              "decimals" => 0,
              "thousand_sep" => ",",
              "dec_point" => ".",
              "prefix" => "",
              "suffix" => "",
            ),
        )))
        ->pipe($this->dataStore("listings"));
    }
        Table::create(array(
            "dataStore"=>$this->dataStore('listings'),
            // "paging"=>array(
            //     "pageSize"=>50,
            //     "pageIndex"=>0,
            // ),
            "grouping"=>array(
                "region_name"=>array(
                    "calculate"=>array(
                        "{sumTotal}"=>array("sum","total"),
                        "{sumTerrace}"=>array("sum","Terrace"),
                        "{sumCornerTerrace}"=>array("sum","CornerTerrace"),
                        "{sumSemiD}"=>array("sum","SemiD"),
                        "{sumDetached}"=>array("sum","Detached"),
                        "{sumGCB}"=>array("sum","GCB"),
                        "{sumShophouse}"=>array("sum","Shophouse"),
                        "{sumLand}"=>array("sum","Land"),
                        "{sumCluster}"=>array("sum","Cluster")
                        
                    ),
                    // "top"=>function($calculated_results){
                    //     return $calculated_results["{sumInt}"]/$calculated_results["{sumTotal}"];
                    // }
                    "top"=>function($store){
                        return "<td><b>".$store["{region_name}"]." Region</b></td>
                        <td><b>".$store["{sumTotal}"]."</b></td>
                        <td><b>".$store["{sumTerrace}"]."</b></td>
                        <td><b>".$store["{sumCornerTerrace}"]."</b></td>
                        <td><b>".$store["{sumSemiD}"]."</b></td>
                        <td><b>".$store["{sumDetached}"]."</b></td>
                        <td><b>".$store["{sumGCB}"]."</b></td>
                        <td><b>".$store["{sumShophouse}"]."</b></td>
                        <td><b>".$store["{sumLand}"]."</b></td>
                        <td><b>".$store["{sumCluster}"]."</b></td>";
                    }                    
                ),               
            ),           
            "sorting"=>array(
                "id"=>"asc"
            ),
            "showFooter"=>"top",
            "columns"=>array(
                "district_name"=>array(
                    "label"=>"District",
                    "footerText"=>"<b>Total Country</b>"
                ),
                "total"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Total",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "Terrace"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Terrace",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "CornerTerrace"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Corner Terrace",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "SemiD"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Semi-D",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "Detached"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Detached",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "GCB"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"GCB",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "Shophouse"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Shophouse",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "Land"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Land",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                ),
                "Cluster"=>array(
                    "type"=>"number",
                    "decimals"=>0,
                    "label"=>"Cluster",
                    "prefix"=>"",
                    "footer"=>"sum",
                    "footerText"=>"<b>@value</b>"
                )
            ),
            "cssClass"=>array(
                "table"=>"table table-bordered table-striped",
                "tf"=>"darker"
            )
        ));

Generated table. This has a calculated top row and a footertext.

Exported excel:

Edmund commented on Mar 18, 2020

Sorry, i meant to say showing 2 decimals instead of 0 in the topic, not 9.

David Winterburn commented on Mar 18, 2020

Hi Edmund,

This is a bug with numeric column in excel export. We will fix it in the next release of Excel package. Meanwhile you could apply a fix yourself like this:

  1. Open the file excel/Table.php

  2. Replace the following line of $formatCode:

    public static function getFormatted($value, $type, $meta)
    {
        ...
        case "number":
            ...
            $formatCode = ...;
            break; 

with:

    public static function getFormatted($value, $type, $meta)
    {
        ...
        case "number":
            ...
            $zeros = ""; for ($i=0; $i<$decimals; $i++) $zeros .= "0";
            $formatCode = "\"{$prefix}\"#,##0.{$zeros}\"{$suffix}\"";
            break;

Let us know if it works for you. Thanks!

Edmund commented on Mar 18, 2020

Hi David,

Thanks for getting back!

After applying this fix, CalculatedColumn now displays as "0.". If i set as 2 decimal points, it works ok. See screenshot.

        ->pipe(new CalculatedColumn(array(
            "percent_extcount"=>"({total}=='0')?0:{extcount}*100/{total}"
            // "percent_extcount"=>"{extcount}/{total}*100"
        )))
        ->pipe(new ColumnMeta(array(
            "percent_extcount"=>array(
            "type" => "number",
              "decimals" => 0,
              "thousand_sep" => ",",
              "dec_point" => ".",
              "prefix" => "",
              "suffix" => "",
            ),
        )))
David Winterburn commented on Mar 19, 2020

Hi Edmund,

I made a mistake. Please replace:

            $zeros = ""; for ($i=0; $i<$decimals; $i++) $zeros .= "0";
            $formatCode = "\"{$prefix}\"#,##0.{$zeros}\"{$suffix}\"";

with:

            $zeros = ""; for ($i=0; $i<$decimals; $i++) $zeros .= "0";
            if (! empty($zeros)) $zeros = ".$zeros";
            $formatCode = "\"{$prefix}\"#,##0{$zeros}\"{$suffix}\"";

Hope this solve the problem. Thanks!

Edmund commented on Mar 19, 2020

Hi David,

I tried with the following and it seems to solve the problem!

$zeros = ($decimals>0) ? "." : ""; for ($i=0; $i<$decimals; $i++) $zeros .= "0";
$formatCode = "\"{$prefix}\"#{$thousand_sep}##0{$zeros}\"{$suffix}\"";

I will test out your new fix too!

Edmund commented on Mar 19, 2020

Results from your fix below. 0 and 2 decimals work. 1 decimal results in no decimal point.

        ->pipe(new CalculatedColumn(array(
            "percent_intcount"=>"({total}=='0')?0:{intcount}*100/{total}"
            // "percent_intcount"=>"{intcount}/{total}*100"
        )))
        ->pipe(new ColumnMeta(array(
            "percent_intcount"=>array(
            "type" => "number",
            "decimals" => 0,
            "thousand_sep" => ",",
            "dec_point" => ".",
            "prefix" => "",
            "suffix" => "",
            ),
        )))
        ->pipe(new CalculatedColumn(array(
            "percent_extcount"=>"({total}=='0')?0:{extcount}*100/{total}"
            // "percent_extcount"=>"{extcount}/{total}*100"
        )))
        ->pipe(new ColumnMeta(array(
            "percent_extcount"=>array(
            "type" => "number",
            "decimals" => 1,
            "thousand_sep" => ",",
            "dec_point" => ".",
            "prefix" => "",
            "suffix" => "",
            ),
        )))

David Winterburn commented on Mar 19, 2020

Hi Edmund,

You are right. Please replace:

             if (! empty($zeros)) $zeros = ".$zeros";

with:

                if ($decimals > 0) $zeros = ".$zeros";
Edmund commented on Mar 19, 2020

That does the trick!

Any reason why $thousand_sep is left out?

David Winterburn commented on Mar 19, 2020

Hi Edmund,

You asked a spot-on question. We found out that for Excel file, thousand separator and decimal point are decided by the program's (Excel, Libre Calc) locale and not by the file. Therefore whichever format code's thousand separator and decimal point we used for numeric column it would be overriden. There's not much we can do unless Excel and Libre Calc change their behavior (doubtful). Thanks!

Edmund commented on Mar 19, 2020

Thanks for the sharing!

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

Excel