Excel to PDF (manually)

Topics: Developer Forum
Jan 9, 2015 at 4:17 PM
Edited Jan 9, 2015 at 4:18 PM
Hi there,

We are in the process of switching our office code at work from LibreOffice (trough a com connection) to PHPExcel. We load existing xls and ods files as templates, and fill it with data. The xls/ods export part works great (after fiddling around a bit in low memory scenarios). One problem that persists for month now tho is the conversion to pdf files.

Plainly - non of the PDF renderer work. They output garbage in various ways, with no help in sight. Neither the last official release of PHPExcel nor the current github code change this much.

So I began to write my own converter which works directly with TCPDF (inspired by how this was done before the change to the html sub-step). However, this works only "okay-ish". Sizes are always a few mm off (not critical, but it bugs me a lot - personally). This could be either a rounding problem (I am aware of the column width problematics), or me using the wrong formula. I wil come to this later.
Secondly, I struggle with page fitting and scaling. There are 4 values (fitWidth, fitHeight, fitPage and scale) in PHPExcel that reflect different states, but I can't seem to get it right (sizing is always noticeably off). The only scenario that works is with no fitting and 100% scale, or simply width fitting.

As for how I calculate sizes:
I use TCPDF in "mm" mode (as pt and px only produced garbage). Page Margins are easily calculated via a multiplication with 25.4, and are precise. As for column widths: I calculate the size of the letter "0" via PHPExcel_Shared_Font::getTextWidthPixelsExact in the default font, and multiply this by the width PHPExcel gives me. This result is then multiplied with 25.4, and divided trough 72 (PDI conversion?). As I stated, this results in "okay-ish" result, e.g. with a table which is around 4mm to wide (24 columns).

As for page fitting: I take the width of the actual page (e.h. 297mm for a A4 landscape page), and subtract the margin. This value it than divided trough the width sum of all columns (calculated to mm as in above formula). This scale is then applied to all cells. This creates a nice width-fitting. PHPExcel still outputs a page scale tho (e.g. 71), and I'm not sure what to do with it. There are scenarios where it is needed, and some where it is not. From my understanding of a graphic I found on the net this scale should only be used if fitPage is false, but I couldn't confirm this (as it is sometimes needed with fitPage true).

So bottom line: I hit a dead end, and don't know how to proceed. Also, I'm under a lot of pressure from work. Any suggestions? :/

So long,