I’m trying to fill an excel file with data coming from a mysql server and using phpSpreadSheet. Then, I need to create a graph so users can see the graph already made. I can fill the excel file no problem, but the graph doesn’t show up. So I decided to simplify the problem by adding the headers and the graph in an empty file, read this file, write the data into this file, and export it for users (with a download button). But even in this case, the graph is absent after opening it. I then used a very simple code and it still doesn’t show up. So I think the problem that I can’t resolve is the same in all cases. What am I doing wrong so the graph disappears? This is the code I first used when I created the file and tried to create the graph :
$pdo = new PDO("mysql:host=localhost;dbname=XXXXXXX", "root", "");
$nameSens = $_POST['nameSens'];
$query = "SELECT hours, values from $nameSens";
$stmt = $pdo->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll();
$spreadsheet = $file->getActiveSheet();
$spreadsheet->setTitle($nameSens);
$file->getActiveSheet()->getColumnDimension('A:F')->setAutoSize(true);
$file->getActiveSheet()->getStyle('A1:B1')->getFont()->getColor()->setARGB('FFFFFFFF');
$file->getActiveSheet()->getStyle('A1:B1')->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill::FILL_SOLID)->getStartColor()->setARGB('FF3333CC');
$file->getActiveSheet()->getStyle('F1')->getFont()->getColor()->setARGB('FFFFFFFF');
$file->getActiveSheet()->getStyle('F1')->getFill()->setFillType(PhpOfficePhpSpreadsheetStyleFill::FILL_SOLID)->getStartColor()->setARGB('FF3333CC');
$spreadsheet->getStyle('A1:F1')->getAlignment()->setHorizontal('center');
$spreadsheet->getStyle('A:F')->getAlignment()->setHorizontal('center');
$spreadsheet->setCellValue('A1', 'day');
$spreadsheet->setCellValue('B1', 'hour');
$spreadsheet->setCellValue('F1', 'Values');
$count = 2;
foreach($result as $row) {
$day = substr($row["hours"], 0, -9);
$hour = substr($row["hours"], 11,);
$spreadsheet->setCellValue('A' . $count, $day);
$spreadsheet->getStyle('A' . $count)->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_DATE_YYYYMMDD);
$spreadsheet->setCellValue('B' . $count, $hour);
$spreadsheet->getStyle('B' . $count)->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_DATE_TIME6);
$dayA = $file->getActiveSheet()->getCell('A' . $count)->getValue();
$spreadsheet->setCellValue('C' . $count, $dayA);
$spreadsheet->getStyle('C' . $count)->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$hourB = $file->getActiveSheet()->getCell('B' . $count)->getValue();
$spreadsheet->setCellValue('D' . $count, $hourB);
$spreadsheet->getStyle('D' . $count)->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$dateExcel = $file->getActiveSheet()->getCell('C' . $count)->getValue() + $file->getActiveSheet()->getCell('D' . $count)->getValue();
$dateExcel = DateTime::createFromFormat('Y-m-d H:i', substr($row["hours"], 0, -3))->getTimestamp();
$dateExcel = ((($dateExcel/60)/60)/24)+25569.08;
$spreadsheet->setCellValue('E' . $count, $dateExcel);
$spreadsheet->getStyle('E' . $count)->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
$spreadsheet->setCellValue('F' . $count, $row["values"]);
$count++;
}
$dataSeriesLabels = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$F$1', null, 1),
];
$xAxisTickValues = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$E$2:$E$6', null, 5),
];
$dataSeriesValues = [
new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$F$2:$F$6', null, 5),
];
// Build the dataseries
$series = new DataSeries(
DataSeries::TYPE_LINECHART, // plotType
DataSeries::GROUPING_STACKED, // plotGrouping
range(0, count($dataSeriesValues) - 1), // plotOrder
$dataSeriesLabels, // plotLabel
$xAxisTickValues, // plotCategory
$dataSeriesValues // plotValues
);
$series->setPlotDirection(DataSeries::DIRECTION_COL);
// Set the series in the plot area
$layout1 = new Layout();
$layout1->setShowVal(true);
$plotArea = new PlotArea($layout1, [$series]);
// Set the chart legend
$legend = new Legend(Legend::POSITION_TOPRIGHT, null, false);
$title = new Title('Data F01');
$yAxisLabel = new Title('Values (mm)');
// Create the chart
$chart = new Chart(
'chart1', // name
$title, // title
$legend, // legend
$plotArea, // plotArea
true, // plotVisibleOnly
DataSeries::EMPTY_AS_GAP, // displayBlanksAs
null, // xAxisLabel
$yAxisLabel, // yAxisLabel
);
// Set the position where the chart should appear in the worksheet
$chart->setTopLeftPosition('H1');
$chart->setBottomRightPosition('O15');
// Add the chart to the worksheet
$spreadsheet->addChart($chart);*/
$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($file, 'Xls');
$writer = new Xls($file);
$writer->setIncludeCharts(true);
$file_name = 'donnees-'.$nameSens.'.xls';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$file_name.'"');
header('Cache-Control: max-age=0');
ob_end_clean();
$writer->save('php://output');
exit;
This is what I get (note that if I make the graph manually using columns E and F, it works fine):
And that’s the simple code that I used with an excel file that already contain the graph
$spreadsheet = PhpOfficePhpSpreadsheetIOFactory::load('../data/data.xls');
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->getCell('C3')->setValue('data1');
$worksheet->getCell('D3')->setValue('data2');
$writer = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('write.xls');
Below is what I have. Top is the input file (data.xls) and bottom is the output (write.xls)
Thank you for your help
Laurent
Advertisement
Answer
I fixed my problem thanks to Kramar: PHPSpreadsheet generates invalid file with charts
I simply changed the displayBlanksAs to ‘gap’ instead of 0!