I have a Data table that I am trying to download as an excel sheet. Due to row detail not being a part of the excel/table class options, I have had to create my own way of getting the details into the file. where I go through every row check if it has a detail and then write each row and detail using your excel Text tool.
The problem is that i have reports with thousands of rows, and it is taking too much memory and also timing out my site. Is there a way that I can build a table, and then have the table be read up and converted into the excel sheet? Or do you guys have any plans to add row Detail to your built in options for the table?
I know that this is a weird request, but I need to figure this out so we can get these reports out.
down below is my code for my excel view.
<?php
use koolreport\excel\Table;
use koolreport\excel\Text;
$date1 = $this->params['date1'];
$date2 = $this->params['date2'];
$title = $this->params['reportTitle'];
$columns = $this->params['ColumnHeaders'];
$groupBy = $this->params['groupby'];
$groupbyTitle = $this->params['groupbyTitle'];
$detail = $this->params['detail_yn'];
$resultArray = $this->dataStore('result')->data();
$sheet1 = $title;
$style = $this->params['style']['result'];
$font = $style['font'];
$tableHeaderColor = $style['hf_back_color'];
$headerFontSize = $style['hf_font_size'];
$headerTextColor = $style['hf_font_color'];
$bodyBackgroundColor = $style['body_back_color'];
$bodyFontSize = $style['body_font_size'];
$bodyTextColor = $style['body_font_color'];
$titleFontColor = $style['title_font_color'];
$titleSize = $style['title_font_size'];
unset($columns['groupby']);
$result = [];
$detailResults = [];
$detailFinalRowArr = [];
if($detail){
foreach ($resultArray as $row) {
foreach ($this->params['detailArr'] as $details) {
$detailResults['name'] = $details['detailname'];
$detailResults['instance'] = $details['instance'];
$detailResults['details'] = !$this->dataStore($details['paramName'])->isEmpty()
? $this->dataStore($details['paramName'])->filter(
$details['instance'],
'=',
$row['identifier']
)->data() : $this->dataStore($details['paramName'])->data();
array_push($detailFinalRowArr,$detailResults);
$detailResults = [];
}
$row['details'] = $detailFinalRowArr;
$detailFinalRowArr = [];
array_push($result,$row);
}
}
?>
<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',
'size' => 30,
'bold' => true,
'italic' => false,
'underline' => 'none',
'strikethrough' => false,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => '000000',
'argb' => 'FF000000',
]
],
'alignment' => [
'horizontal' => 'general',
'vertical' => 'bottom',
'textRotation' => 0,
'wrapText' => false,
'shrinkToFit' => false,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'top' => [
'borderStyle' => 'none',
'color' => [
'rgb' => '808080',
'argb' => 'FF808080',
]
],
],
'fill' => [
'fillType' => 'none',
'rotation' => 90,
'color' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'startColor' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFF',
'argb' => 'FFFFFFFF',
],
],
];
$bodyStyleArray = [
'font' => [
'name' => $font, //'Verdana', 'Arial'
'size' => $bodyFontSize,
'bold' => false,
'italic' => false,
'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
'strikethrough' => false,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => $bodyTextColor,
'argb' => $bodyTextColor,
]
],
'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' => [
'allBorders' => [
'borderStyle' => 'hair',
'color' => [
'rgb' => '000000',
'argb' => '000000',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'solid',
'rotation' => 90,
'color' => [
'rgb' => $bodyBackgroundColor,
'argb' => $bodyBackgroundColor,
],
'startColor' => [
'rgb' => $bodyBackgroundColor,
'argb' => $bodyBackgroundColor,
],
'endColor' => [
'argb' => $bodyBackgroundColor,
'argb' => $bodyBackgroundColor,
],
],
];
$headerStyleArray = [
'font' => [
'name' => $font, //'Verdana', 'Arial'
'size' => $headerFontSize,
'bold' => true,
'italic' => false,
'underline' => 'none', //'double', 'doubleAccounting', 'single', 'singleAccounting'
'strikethrough' => false,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => $headerTextColor,
'argb' => $headerTextColor,
]
],
'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' => [
'allBorders' => [
'borderStyle' => 'hair',
'color' => [
'rgb' => '000000',
'argb' => '000000',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'solid',
'rotation' => 90,
'color' => [
'rgb' => $tableHeaderColor,
'argb' => $tableHeaderColor,
],
'startColor' => [
'rgb' => $tableHeaderColor,
'argb' => $tableHeaderColor,
],
'endColor' => [
'argb' => $tableHeaderColor,
'argb' => $tableHeaderColor,
],
],
];
$detailStyleArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 16,
'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' => false,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'top' => [
'borderStyle' => 'none',
'color' => [
'rgb' => '808080',
'argb' => 'FF808080',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'solid',
'rotation' => 90,
'color' => [
'rgb' => 'FFFFFFFF',
'argb' => 'FFFFFFFF',
],
'startColor' => [
'rgb' => 'FFFFFFFF',
'argb' => 'FFFFFFFF',
],
'endColor' => [
'argb' => 'FFFFFF',
'argb' => 'FFFFFFFF',
],
],
];
?>
<div cell="A1" range="A1:H1" excelstyle='<?php echo json_encode($styleArray); ?>' >
<?php echo($title) ?>
</div>
<?php
if ($detail) {
$alpha = 'A';
$forward = 0;
foreach ($result[0] as $key => $val) {
if ($key != 'identifier' && $key != 'details') {
echo('<div cell="' . $alpha . '2">');
Text::create([
"text" => $key,
"excelStyle" => $headerStyleArray
]);
echo('</div>');
$alpha++;
}
}
$alpha = 'A';
$numeric = 3;
for ($i = 0; $i <= count($result); $i++) {
foreach ($result[$i] as $key => $val) {
if ($key != 'identifier' && $key != 'details') {
echo('<div cell="' . $alpha . $numeric . '">');
Text::create([
"text" => $val,
"excelStyle" => $bodyStyleArray
]);
echo('</div>');
$alpha++;
}
if($key == 'details')
{
foreach($val as $detailRows){
if($detailRows['details']){
$numeric++;
$alpha = 'B';
echo('<div cell="' . $alpha . $numeric . '" >');
Text::create([
"text" => $detailRows['name'],
"excelStyle" => $detailStyleArray
]);
echo('</div>');
$alpha = 'B';
$numeric ++;
foreach($detailRows['details'][0] as $key=>$details){
if($key != $detailRows['instance']){
echo('<div cell="' . $alpha . $numeric . '" >');
Text::create([
"text" => $key,
"excelStyle" => $headerStyleArray
]);
echo('</div>');
$alpha++;
}
}
$alpha = 'B';
$numeric ++;
for($j = 0; $j < count($detailRows['details']); $j++){
foreach($detailRows['details'][$j] as $key=>$details){
if($key != $detailRows['instance']){
echo('<div cell="' . $alpha . $numeric . '" >');
Text::create([
"text" => $details,
"excelStyle" => $bodyStyleArray
]);
echo('</div>');
$alpha++;
}
}
$numeric ++;
$alpha = 'B';
}
}
}
}
}
$alpha = 'A';
if ($forward != 0) {
$numeric = $forward;
$forward = 0;
} else {
$numeric++;
}
}
}