menu

Questions & Answers

Converting from excel to pdf in PHPSpreadsheet without ruining design/style

I tried exporting my PHPSpreadsheet as a PDF. What I did was setting the header like:

header("Content-Disposition: attachment;filename=hello world.pdf");

and the $writer like this:

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet);
$writer->save("php://output");

The rest of my code stays the same as for exporting as XLSX. It seems to not like the styling commands and also imports the cells where there is no text. An example of how I create info in a cell for Excel is like this:

$sheet->setCellValue('A16', $activitiesCount)->getStyle('A16');
$sheet->getStyle('A16')->getFill()->setFillType(Fill::FILL_SOLID);
$sheet->getStyle('A16')->getFill()->getStartColor()->setARGB('d9e6fc');

Here is the original in excel:

enter image description here

This one exports fine if I save it as PDF directly from excel itself. But when I try to do it with my code, this seems to be the result:

enter image description here

As you can see its getting really messy. Does it mean I have to program it in a another way for PDF? I thought it was going to be easy to change between those to.

PS: Dont mind the name differences between the pictures. I tested something in DB to see how it scales when name is really long. It wasnt pretty, let me tell you.

Answers(2) :

This isn't much of an answer, but if you read the documentation you will see a note:

Please note that PDF file format has some limits regarding to styling cells, number formatting, ...

You might have better luck trying a library other than mPDF. From the same page:

The different libraries have different strengths and weaknesses. Some generate better formatted output than others, some are faster or use less memory than others, while some generate smaller .pdf files

Comments:
2023-01-22 23:01:06
Thanks for your answer! Does this mean I can use an another library to convert my current phpspreadsheet library? Like as they overlap?
2023-01-22 23:01:06
A different PDF library yes. The documentation lists the available ones, just install it and change your instance of $writer accordingly.
2023-01-22 23:01:06
I tried both and the results were similarly awful. Cells overwritten or omitted, lines everywhere, spacing and font sizing out enough to totally break a table. It's sounding like there is no solution for this, and that the PDF support is only able to cope with very basic conversions. Fair enough if it doesn't work, though it would help a lot if we knew what was supported and what wasn't so we didn't waste time trying it. It seems the PDF output is nowhere near the outstanding Excel output, unfortunately. 
$extension = 'Mpdf';
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf($spreadsheet, $extension);

if (empty($report)) {
    $reportData['listing_id'] = $id;
    $reportData['year'] = $y;
    $reportData['month'] = $m;
    $reportData['excel_name'] = 'r_' . $id . '_' . $y . $m . '_' . time() . '.pdf';
    $report = ListingReport::create($reportData);
} else {
    $report->update($reportData);
}

$filePath = public_path("files/report/$report->excel_name");
$writer->save($filePath);
$headers = array(
    'Content-Type: application/pdf',
);