KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Excel PivotTable with custom styling #2542
Yes, it is possible to apply excel style for excel PivotTable. Here's its documentation and example code:
i use the following styling array:
$allStylesArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => true,
'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' => true,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'allBorders' => [
'borderStyle' => 'medium', //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' => [
'hex' => 'a83234',
]
],
];
but the exported file don't have any styling:
i just need bordered table and column resize to fit the content.
here is the code:
<?php
use \koolreport\excel\PivotTable;
$sheet1 = "Finance Daily Report";
?>
<meta charset="UTF-8">
<meta name="title" content="Finance Daily Report">
<div sheet-name="<?php echo $sheet1; ?>">
<?php
$allStylesArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => true,
'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' => true,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'allBorders' => [
'borderStyle' => 'medium', //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' => [
'hex' => 'a83234',
]
],
];
?>
<div>Finance Daily Report</div>
<div>
<?php
PivotTable::create(array(
"dataSource" => $this->dataStore('FNAFAD0001_100'),
"rowDimension"=>"row",
"columnDimension"=>"column",
"measures"=>[
"Beginning Balance - sum",
"Debit - sum",
"Credit - sum",
"Last Balance - sum",
],
"headerMap"=>[
"Beginning Balance - sum"=>"Beginning Balance",
"Debit - sum"=>"Debit",
"Credit - sum"=>"Credit",
"Last Balance - sum"=>"Last Balance",
],
"hideSubtotalRow"=>true,
"showDataHeaders" => true,
'map' => array(
'rowField' => function($rowField, $fieldInfo) {
return $rowField;
},
'columnField' => function($colField, $fieldInfo) {
return $colField;
},
'dataField' => function($dataField, $fieldInfo) {
$v = $dataField;
},
'waitingField' => function($waitingField, $fieldInfo) {
return $waitingField;
},
'rowHeader' => function($rowHeader, $headerInfo) {
$v = $rowHeader;
return $v;
},
'columnHeader' => function($colHeader, $headerInfo) {
$v = $colHeader;
return $v;
},
'dataCell' => function($value, $cellInfo) {
return $value;
},
),
'excelStyle' => array(
'dataField' => function($dataFields) use ($allStylesArray) {
return $allStylesArray;
},
'dataHeader' => function($dataFields, $fieldInfo) use ($allStylesArray) {
return $allStylesArray;
},
'columnHeader' => function($header, $headerInfo) use ($allStylesArray) {
return $allStylesArray;
},
'rowHeader' => function($header, $headerInfo) use ($allStylesArray) {
return $allStylesArray;
},
'dataCell' => function($value, $cellInfo) use ($allStylesArray) {
return $allStylesArray;
},
)
));
?>
</div>
</div>
Pls try our standard example on your server:
https://www.koolreport.com/examples/reports/excel/pivottable/
See if the output excel file has PivotTable in italic font and gray color? If it does, pls copy and modify the example for your case. Tks,
Oh, I just realize you use these excel styles:
'font' => [
...
'italic' => FALSE,
...
'color' => [
'rgb' => '000000',
'argb' => 'FF000000',
]
],
Of course your excel output would not be in italic or gray color font. Pls stry these styles instead:
'font' => [
...
'italic' => true,
...
'color' => [
'rgb' => '808080',
]
],
it doesn't work. here is the properties that i use:
$allStylesArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => true,
'italic' => true,
'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
'strikethrough' => FALSE,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => '808080'
]
],
'alignment' => [
'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
'vertical' => 'bottom',//top, center, justify, distributed
'textRotation' => 0,
'wrapText' => false,
'shrinkToFit' => true,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'allBorders' => [
'borderStyle' => 'medium', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
'color' => [
'rgb' => '808080'
]
],
//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',
]
],
];
the result:
hi, i already check the result using ms excel app. some of styling are applied. but i need to know one more thing.
how to apply alignment only on some cells or rows? i want to make the numeric cells aligned to right.
and how to place some blank rows or space between 2 tables?
The data cell numbers are right aligned by default. Which version of MS Excel are you using? Office 365 shows the numbers' right alignment ok.
To add spaces between 2 tables/pivottables you must use excel view file for exporting instead of exporting datastores directly.
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
Topic is closed.