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:
