Hi , I am using pivot 2d and want to export data in excel but with pivot 2D excel formatting is not correct but when I use pivot then excel format is fine, can you tell me that any other requirement for pivot 2d
$report->run()
->exportToExcel(array(
"dataStores" => array(
'tat1Field' => array(
'rowSort' => array(
'quantity_c - count' => 'desc',
),
)
),
))->saveAs("reports/test.xlsx");
$node = $this->src("mysql")->query($Query)
->pipe(new Pivot2D(array(
"dimensions"=>array(
"column" => "dateyear",
"row" => "users_zone_c, address_state, CSCName, engname",
),
"aggregates"=>array(
"sum" => "quantity_c, JSWithRating, noRating,Average, oneStar, oneStarPercent, twoStar, twoStarPercent, threeStar, threeStarPercent, fourStar, fiveStar, fourStarPercent, fiveStartPercent",
),
"fieldDelimiter" => $delimiter,
)))
->pipe(new Map(array(
'{value}' => function ($row) use ($delimiter) {
foreach ($row as $colName => $colValue) {
$colFields = substr($colName, 0, strrpos($colName, " || "));
$customAverageColName = $colFields . " || Average - sum";
if($customAverageColName!==' || Average - sum'){
$row[$customAverageColName] = $row[$colFields . " || JSWithRating - sum"] != 0 ? $row[$colFields . " || quantity_c - sum"] / $row[$colFields . " || JSWithRating - sum"] : 0;
}
$customPercentFiveColName = $colFields . " || fiveStartPercent - sum";
if($customPercentFiveColName!==' || fiveStartPercent - sum'){
$row[$customPercentFiveColName] = $row[$colFields . $delimiter . "fiveStar - sum"] != 0 ? $row[$colFields . $delimiter . "fiveStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
}
$customPercentFourColName = $colFields . " || fourStarPercent - sum";
if($customPercentFourColName!==' || fourStarPercent - sum'){
$row[$customPercentFourColName] = $row[$colFields . $delimiter . "fourStar - sum"] != 0 ? $row[$colFields . $delimiter . "fourStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
}
$customPercentThreeColName = $colFields . " || threeStarPercent - sum";
if($customPercentThreeColName!==' || threeStarPercent - sum'){
$row[$customPercentThreeColName] = $row[$colFields . $delimiter . "threeStar - sum"] != 0 ? $row[$colFields . $delimiter . "threeStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
}
$customPercentTwoColName = $colFields . " || twoStarPercent - sum";
if($customPercentTwoColName!==' || twoStarPercent - sum'){
$row[$customPercentTwoColName] = $row[$colFields . $delimiter . "twoStar - sum"] != 0 ? $row[$colFields . $delimiter . "twoStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
}
$customPercentOneColName = $colFields . " || oneStarPercent - sum";
if($customPercentOneColName!==' || oneStarPercent - sum'){
$row[$customPercentOneColName] = $row[$colFields . $delimiter . "oneStar - sum"] != 0 ? $row[$colFields . $delimiter . "oneStar - sum"]*100 / $row[$colFields . $delimiter . "JSWithRating - sum"] : 0;
}
}
//echo '<pre>';print_r($row);
return $row;
},
'{meta}' => function($meta) {
$meta['columns']['Average - sum'] = array(
'type' => 'number',
'decimals' => 2,
);
$meta['columns']['fiveStartPercent - sum'] = array(
'type' => 'number',
'decimals' => 2,
'suffix' => '%',
);
$meta['columns']['fourStarPercent - sum'] = array(
'type' => 'number',
'decimals' => 2,
'suffix' => '%',
);
$meta['columns']['threeStarPercent - sum'] = array(
'type' => 'number',
'decimals' => 2,
'suffix' => '%',
);
$meta['columns']['twoStarPercent - sum'] = array(
'type' => 'number',
'decimals' => 2,
'suffix' => '%',
);
$meta['columns']['oneStarPercent - sum'] = array(
'type' => 'number',
'decimals' => 2,
'suffix' => '%',
);
return $meta;
}
)))
->pipe($this->dataStore('tat1Field'));
report format : http://15.206.180.82/livservReporting_ankit/reports/super_asi.xlsx