My Code:
->pipe(new Group(array(
"by"=>"SM_CODE",
)))
->pipe(new CalculatedColumn(array(
"Difference"=>function($row){
$result = $row["SALESCYA"] - $row["SALESLYA"];
$result=round($result);
//if($result < 0) return "( ".$result." )";
if(is_infinite($result) || is_nan($result)){$result = 0;}
return $result;
},
->pipe(new ColumnMeta(array(
"Difference"=>array(
//"type"=>"number",
//"prefix"=>''
),
MyReportExcel.view
---------------------------
<?php
use \koolreport\excel\Table;
use \koolreport\excel\PivotTable;
use \koolreport\excel\BarChart;
use \koolreport\excel\LineChart;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\CalculatedColumn;
use \koolreport\processes\DateTimeFormat;
use \koolreport\processes\CopyColumn;
use \koolreport\processes\Group;
$sheet1 = "Sales by Customer";
?>
<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="<?php echo $sheet1; ?>">
<?php
$styleArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => false,
'italic' => FALSE,
'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
'strikethrough' => FALSE,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => '000000',
'argb' => 'FF000000',
]
],
'alignment' => [
'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
'vertical' => 'bottom',//top, center, justify, distributed
'textRotation' => 0,
'wrapText' => false,
'shrinkToFit' => false,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'top' => [
'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
'color' => [
'rgb' => '808080',
'argb' => 'FF808080',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
'rotation' => 90,
'color' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'startColor' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFF',
'argb' => 'FFFFFFFF',
],
],
];
?>
<div cell="A1" range="A1:H1" excelstyle='<?php echo json_encode($styleArray); ?>' >
Sales Report
</div>
<div>Orders</div>
<div>
<?php
Table::create(array(
"dataSource"=>$this->dataStore('sales'),
"grouping"=>array(
"R_DESC"=>array(
"calculate"=>array(
"{currentActual}"=>array("sum","SALESCYA"),
"{lastActual}"=>array("sum","SALESLYA"),
"{currentBudget}"=>array("sum","SALESCYB"),
"{currentDifference}"=>array("sum","Difference"),
"{currentPerDifference}"=>array("sum","PerDifference"),
"{currentRequire}"=>array("sum","Require"),
"{currentPerRequire}"=>array("sum","PerRequire"),
),
"top"=>"<b>Region : {R_DESC}</b>",
"bottom"=>"<td><b>Total</b></td><td></td><td></td><td style='text-align:right;'><b>{currentActual}</b></td><td style='text-align:right;'><b>{lastActual}</b></td><td style='text-align:right;'><b>{currentDifference}</b></td><td style='text-align:right;'><b>{currentPerDifference}</b></td><td style='text-align:right;'><b>{currentBudget}</b></td><td style='text-align:right;'><b>{currentRequire}</b></td><td style='text-align:right;'><b>{currentPerRequire}</b></td>",
),
),
"sorting"=>array(
"R_DESC"=>"asc"
),
"showFooter"=>true,
"columns"=>array(
"SM_CODE"=>array(
"label"=>"Salesman Code",
"footerText"=>"<b>Grand Total</b>"
),
"STYPE_DESC"=>array(
"label"=>"Sub Category",
),
"SM_NAME"=>array(
"label"=>"Name",
),
"SALESCYA"=>array(
"label"=>"2021 Net Sales",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
"SALESLYA"=>array(
"label"=>"2020 Net Sales ",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
"Difference"=>array(
"label"=>"Difference",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
/* array(
"label"=>"Difference",
"value"=>function($row) {
$result = $row["SALESCYA"] - $row["SALESLYA"];
$result=round($result);
if($result < 0) return "( ".$result." )";
return $result;
},
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
*/
/* array(
"label"=>"% of Diff",
"value"=>function($row) {
$result = round($row["SALESCYA"]) - round($row["SALESLYA"]);
$result =round($result, PHP_ROUND_HALF_UP);
$percentage = (($result) / round($row["SALESLYA"]));
$percentage=round($percentage);
if($percentage < 0) return "( ".$percentage." )";
return $percentage;
},
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
*/
"PerDifference"=>array(
"label"=>"% of Diff",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
"SALESCYB"=>array(
"label"=>"2021 Sales Target",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
/* array(
"label"=>"Require to Ach.",
"value"=>function($row) {
$result = $row["SALESCYB"] - $row["SALESCYA"];
$result =round($result);
if($result < 0){ return "( ".$result." )"; }
else{
return $result;
}
},
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
*/
"Require"=>array(
"label"=>"Require to Ach",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
/* array(
"label"=>"% of Ach.",
"value"=>function($row) {
$result = $row["SALESCYB"] - $row["SALESCYA"];
$result =round($result, PHP_ROUND_HALF_UP);
$percentage = (($result) / $row["SALESCYA"]);
$percentage=round($percentage,2);
if($percentage < 0) return "( ".$percentage." )";
return $percentage;
},
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
*/
"PerRequire"=>array(
"label"=>"% of Ach.",
"footer"=>"sum",
"footerText"=>"<b>@value</b>",
"cssStyle"=>"text-align:right;width:8em",
"thousandSeparator" => ","
),
),
"cssClass"=>array(
"table"=>"table-bordered",
"tf"=>"darker"
)
));
?>
</div>
</div>
Query Result:
But After exporting excel file - Excel says
