2

How would i go about saving the printable area on an excel sheet as a PDF?

If I need to use a different lib then I can work with that, but I need it to be as close to the same look as possible as when printing from excel, I know that is hard in general.

Alan Doyle
  • 98
  • 16

3 Answers3

2

On the assumption that you are working on a Windows server, I am going to suggest a different approach: using the COM or DOTNET class libraries available in PHP on Windows (http://php.net/manual/en/book.com.php). If not, please disregard my answer as it will not work.

My idea is to use the native Excel macro capability to select the Print Area and using the built-in Export to PDF function. In this way cell formats should remain intact. So your .xlsw becomes a .xlsm file with the addition of the required VBA code to select the proper range (Print Area) and Export to a PDF. Then the COM or DOTNET class libraries would be used to instatiate the spreadsheet and access exposed objects (macros included). You might also be able to drive the export through COM/DOTNET automation and not use a macro if you do not have control over the spreadsheet itself.

Brad K
  • 342
  • 1
  • 6
  • i am no, but this is still an option that might end up being the answer. its a .xlsx file, but if the COM/DOTNET can initiate the export or save as PDF, than yes this would work, depending on where it saves the file.. i will have to look into this. – Alan Doyle Jan 30 '17 at 17:47
  • after looking into this, this is the only viable solution that i can see working so far. and if i use networked drives and scripts on the window server side, i can still do this with Linux. so thank you very much! – Alan Doyle Jan 30 '17 at 17:53
  • Glad this may be helpful. I also like your idea of using networked drives and WIN scripts with Linux to control it all. Reminded me that Amazon has an affordable virtual WIN desktop platform ( https://aws.amazon.com/workspaces/pricing/ ) with and without MS Office suite for those who don't have these WIN assets at their disposal. – Brad K Jan 30 '17 at 20:37
  • i have a Virtural server so adding a windows server is nothing, and also, i am looking into C# to do the windows side, so that i dont have to deal with PHP running on windows.. but it is possible with PHP doing it :) – Alan Doyle Jan 31 '17 at 00:27
  • Setting up a VM environment to get the answer a try and see how well it works – Alan Doyle Jan 31 '17 at 16:53
  • Confirmed. this works very well. ended up using powershell to do the converting.. modified version of this script https://blogs.technet.microsoft.com/heyscriptingguy/2010/09/06/save-a-microsoft-excel-workbook-as-a-pdf-file-by-using-powershell/ – Alan Doyle Feb 23 '17 at 00:00
1

The only print options available within dompdf are paper size and orientation. Further, printable area is an excel-specific directive that does not affect export to other formats. Think of printable area as specialized markers and nothing more.

The best option in this case is emptying or hiding all of the cells outside of the printable area via loop before conversion to pdf.

Serg Chernata
  • 12,280
  • 6
  • 32
  • 50
  • I will try to swap over to DomPDF, as i am currently using mPDF, to see how much of a difference that make... and i will look into the hiding of the print area via code.. Thank you for the reply – Alan Doyle Jan 26 '17 at 19:32
  • You're welcome man, I don't think it matters what PDF library you use. The main point is that printable area is just a marker used by Excel and that's about it. It really isn't a true printable area in a sense you're thinking. Looping over cells and deleting everything outside of the area is most likely the only choice. – Serg Chernata Jan 26 '17 at 19:34
  • well i cant delete.. i have to just hide i think, depending on how converting to PDF deals with hidden cells..... if i can manage to make it just hide columns than i think this solution will work, and wont be that hard on the CPU compared to other ideas i was thinking (horrible ideas that involved shell commands and Linux office) – Alan Doyle Jan 26 '17 at 19:40
  • I was going to suggest turning the text of those cells to white color, but when you do go to print it may still be treated as invisible content. What if you make a duplicate, delete bad content entirely, create PDF and remove the duplicate? – Serg Chernata Jan 26 '17 at 19:46
  • The issue is is that all the content that i don't want to be in the PDF is all formulas that do math for the cells that i do want to show.. so I'm not sure how PHPExcell treats cells that reference other cells if they get deleted.. does PHPExcell calculate all the formulas and than get the result in the array or does it calculate on the fly? – Alan Doyle Jan 26 '17 at 20:21
  • That I don't know. You're just going to have to experiment and see what happens. – Serg Chernata Jan 26 '17 at 20:22
  • `rangeToArray($print_area_range)` Only gets me plain text no styles.. hmm.. – Alan Doyle Jan 26 '17 at 20:30
  • Going though and hiding all the columns and rows that are not in apart of the print area seems to be VERY processing intensive.. (1 min avg on 8core xenon) – Alan Doyle Jan 26 '17 at 20:38
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134120/discussion-between-serg-chernata-and-alan-doyle). – Serg Chernata Jan 26 '17 at 20:38
1
  1. Xlsx file - ZIP package (link)
  2. Info about printable area contained in the file 'xl/workbook.xml'. Get contents of 'xl/workbook.xml' with php ZipArchive class and its ZipArchive::getFromName method
  3. Element that contains printable area is <definedName name='_xlnm.Print_Area'> </definedName>. Extract info from this tag using PHP SimpleXMLElement class and its method SimpleXMLElement::xpath
  4. When info about printable area will have extracted:
    • make a copy of xlsx file
    • extract a file named 'xl/worksheets/sheet1' (or similar)
    • loop through the elements of file in the section 'sheetData' and delete cells that are not included in printable area
    • zip the modified xml to the copy of xlsx
  5. Convert xlsx file using PHPExcel or other lib.
Ans
  • 527
  • 5
  • 9