Im create dynamic reporting using phpspreadsheet, when im create single worksheet with chart the output give the fine result.
when im create multiple worksheet with chart, the output saying "The file corupted", im trying find the answer on here , and try ob_end_clean(); before and after header , and the problem not fixed.
Here My Code :
<?php
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
class Master_donor extends CI_Controller{
function __construct()
{
parent::__construct();
$this->load->model('Master_donor_model');
}
function bulanprint(){
$listbulan=$this->Master_donor_model->bulan();
$test="2019";
$a=0;
$spreadsheet = new Spreadsheet();
foreach($listbulan as $key){
$datanya=$this->Master_donor_model->lapbulan($key->id_bulan,$test);
$countdata=count($datanya);
$oke=$spreadsheet->setActiveSheetIndex($a);
$oke->setCellValue('A1', 'Program Donor Darah Tahun '.$test);
$oke->getStyle('A3:E3')->applyFromArray(
[
'font' => ['bold' => true,],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
]
);
$oke->getStyle('A3:E'.($countdata+4))->applyFromArray(
[
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER,],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN,],]
]
);
$oke->setCellValue('A3', 'No');
$oke->setCellValue('B3', 'Site');
$oke->setCellValue('C3', 'Target');
$oke->setCellValue('D3', 'Jumlah Pendaftar');
$oke->setCellValue('E3', 'Jumlah Kantong Darah');
$oke->getColumnDimension('D')->setAutoSize(true);
$oke->getColumnDimension('E')->setAutoSize(true);
$oke->fromArray($datanya,NULL,'A4',true);
$oke->mergeCells('A'.($countdata+4).':B'.($countdata+4));
$oke->setCellValue('A'.($countdata+4), 'Total');
$oke->setCellValue('C'.($countdata+4), '=SUM(C4:C'.($countdata+3).')');
$oke->setCellValue('D'.($countdata+4), '=SUM(D4:D'.($countdata+3).')');
$oke->setCellValue('E'.($countdata+4), '=SUM(E4:E'.($countdata+3).')');
$dataSeriesLabels1 = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$E$3', null, 1), // Target
];
$dataSeriesLabels2 = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$C$3', null, 1), // Jumlah Kantong
];
$xAxisTickValues = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$4:$B$'.($countdata+3), null, $countdata), // Jan to Dec
];
$dataSeriesValues1 = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$E$4:$E$'.($countdata+3), null, $countdata),
];
// Build the dataseries
$series1 = new DataSeries(
DataSeries::TYPE_BARCHART,//plotType
DataSeries::GROUPING_CLUSTERED,//plotGrouping
range(0, count($dataSeriesValues1) - 1),//plotOrder
$dataSeriesLabels1,//plotLabel
$xAxisTickValues,//plotCategory
$dataSeriesValues1//plotValues
);
$series1->setPlotDirection(DataSeries::DIRECTION_COL);
$dataSeriesValues2 = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$C$4:$C$'.($countdata+3), null, $countdata),
];
// Build the dataseries
$series2 = new DataSeries(
DataSeries::TYPE_LINECHART, // plotType
DataSeries::GROUPING_STANDARD, // plotGrouping
range(0, count($dataSeriesValues2) - 1), // plotOrder
$dataSeriesLabels2, // plotLabel
[], // plotCategory
$dataSeriesValues2// plotValues
);
$plotArea = new PlotArea(null, [$series1, $series2]);
// Set the chart legend
$legend = new Legend(Legend::POSITION_BOTTOM, null, false);
$title = new Title('Donor Darah '.$key->bulan);
$yAxisLabel = new Title('Jumlah Kantong Darah');
// Create the chart
$chart = new Chart(
'chart1', // name
$title, // title
$legend, // legend
$plotArea, // plotArea
true, // plotVisibleOnly
0, // displayBlanksAs
null, // xAxisLabel
$yAxisLabel // yAxisLabel
);
// Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('G3');
$chart->setBottomRightPosition('Q22');
$oke->addChart($chart);
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle($key->bulan);
if($a==11){
break;
}
else{
$spreadsheet->createSheet();
}
$a++;
}
// Save Excel 2007 file
$spreadsheet->setActiveSheetIndex($a);
$filename = "laporan_donor_".$test;
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'. $filename .'.xlsx"');
header('Cache-Control: max-age=0');
ob_end_clean();
$writer->save('php://output');
exit;
}
}
?>
The output should be having chart each worksheet
Edit : after limit the iteration to 8 it's work well , but my target limit is 11
*Note my iteration start with 0
