Hi, this is ma second topic today... but I don't know where is the problem. When I try to open genereted excel file with pivottable I got standard Excel error that - there is unsupported format - file can be broken and so on... I would by appracitate if some one can look on my code and suggest my what can cause the issue. I try to find a solution from couple hours, and I have enough :)
Pivot table is generating correctly My data become from the database, but it should be a problem
Report
`
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
class CustomerSalesCurrentMonthPivot extends \koolreport\KoolReport {
use \koolreport\excel\ExcelExportable;
use \koolreport\codeigniter\Friendship;// All you need to do is to claim this friendship
function settings()
{
return array(
"assets"=>array(
//"url"=>("../public/koolreport_assets"),
"path"=>"../../../public/koolreport_assets"
)
);
}
function setup()
{
//Now you can access database that you configured in codeigniter
$this->src("default")
->query("SELECT TOP 100 CUSTNAME, ORCUSTNAME, INVOICEMONTH,INVOICEYEAR,SALESVALUEPLN,VOLUME_TONS,VOLUME_DELIV_TONS,ITEMNAME
FROM TABLEAU..CURRENT_SALES_NO_MARGIN
WHERE INVOICEDATE BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE())-1, 0) AND GETDATE()")
->pipe(new ColumnMeta(array(
"SALESVALUEPLN"=>array(
"type" => 'number',
"decimals" => 0,
"thousandSeparator" => " ",
"decimalPoint" => ".",
)
)))
->pipe(new Pivot(array(
"dimensions"=>array(
"column"=>"INVOICEYEAR,INVOICEMONTH",
"row"=>"CUSTNAME,ORCUSTNAME,ITEMNAME"
),
"aggregates"=>array(
"sum"=>"SALESVALUEPLN,VOLUME_TONS,VOLUME_DELIV_TONS",
//"sum"=>"SALESVALUEPLN"
)
)))
->pipe($this->dataStore('CustomerSalesCurrentMonth'));
}
}
`
Excel view
<?php
use \koolreport\excel\PivotTable;
$sheet1 = "SalesbyCustomer";
?>
<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; ?>">
<div>Sales PivotTable</div>
<div>
<?php
$allStylesArray =
[
'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',
],
],
];
$styleArray = [
'font' => [
'italic' => true,
'color' => [
'rgb' => '808080',
]
],
];
PivotTable::create(array(
"dataSource" => 'CustomerSalesCurrentMonth',
'hideSubTotalRows' => true,
'hideSubTotalColumns' => true,
'hideGrandTotalRow' => true,
'hideGrandTotalColumn' => true,
"showDataHeaders" => true,
// "map" => [
// 'dataHeader' => function($dataField, $fieldInfo) {
// if ($dataField === "SALESVALUEPLN - sum") return "Wartość sprzedaży";
// else return $dataField;
// }],
'map' => array(
'rowField' => function($rowField, $fieldInfo) {
return $rowField;
},
'columnField' => function($colField, $fieldInfo) {
return $colField;
},
'dataField' => function($dataField, $fieldInfo) {
// Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'SALESVALUEPLN - sum')
$v = 'Sales (in USD)';
else if ($v === 'dollar_sales - count')
$v = 'Number of Sales';
return $v;
},
'dataHeader' => function($dataField, $fieldInfo) {
// Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'SALESVALUEPLN - sum')
$v = 'Sales (in USD)';
else if ($v === 'dollar_sales - count')
$v = 'Number of Sales';
return $v;
},
'waitingField' => function($waitingField, $fieldInfo) {
return $waitingField;
},
'rowHeader' => function($rowHeader, $headerInfo) {
// Util::prettyPrint($headerInfo);
$v = $rowHeader;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'columnHeader' => function($colHeader, $headerInfo) {
$v = $colHeader;
if ($headerInfo['fieldName'] === 'orderYear')
$v = 'Year-' . $v;
else if ($headerInfo['fieldName'] === 'orderQuarter')
$v = 'Quarter-' . $v;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'dataCell' => function($value, $cellInfo) {
return $value;
// Util::prettyPrint($cellInfo);
$rfOrder = $cellInfo['row']['fieldOrder'];
$cfOrder = $cellInfo['column']['fieldOrder'];
$df = $cellInfo['fieldName'];
$dfOrder = $cellInfo['fieldOrder'];
// return "$rfOrder:$cfOrder:$df. $value";
return $cellInfo['formattedValue'];
},
),
'excelStyle' => array(
'dataField' => function($dataFields) use ($styleArray) {
return $styleArray;
},
'dataHeader' => function($dataFields, $fieldInfo) use ($styleArray) {
return $styleArray;
},
'columnHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'rowHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'dataCell' => function($value, $cellInfo) use ($styleArray) {
return $styleArray;
},
)
));
?>
</div>
</div>
export
public function export()
{
$report = new CustomerSalesCurrentMonthPivot;
$report->run();
$report->exportToExcel('CustomerSalesCurrentMonthPivotExcel')->toBrowser("MyReport6666.xlsx");
}