Saving Excel documents to PDF on the server

Not so long ago, the task was to create a simple service for creating PDF reports based on office documents for the intranet. And everything seems to be simple, but there were problems with saving Excel to PDF. Interesting? I ask for cat.


As I said, at first everything seemed easy, I had my own achievements, was Habré article . But first things first.

My achievements using COM objects excel.application and method saveAs , this works great as long as required to take an ordinary, nice paper and make it a pdf, but in this case the files were there so simple.

Firstly, the documents are supposed to be in three formats - xls, xlsx and xml. Secondly, all documents contain macros, and some links to other documents. Third, they contain a bunch of sheets, and cross-links between sheets. Sheets that are not needed for the report are made hidden, and on the sheets themselves in the porridge of auxiliary digits the reporting information was highlighted by the print area. Needless to say, saveAs ignores all this wealth and at the exit after dancing with tambourines we get an absolutely unreadable picture.

Here I think it is necessary to digress and explain why such confusion is happening with the files. I work in a very large organization, side by side with a bunch of grandmothers of retirement age. They cannot even make the text in the cell bold, but they can perfectly tell the authorities that they are not “programmers” and should not be able to. We also have a lot of bosses, and because of pity for grandmothers or their computer illiteracy, or perhaps, at the behest of the stars, the complaints of grandmothers are encouraged, and all attempts to clear up the mess in the documents are stopped.

Back to our rams. In the above mentioned articlethe “As I see” conversion option was proposed using open office, this option did not suit me due to the windy attitude of OO to MS. Some documents did open correctly in it, but most often the contents went even before conversion.

There was a third option. To print documents on a virtual printer, but I decided that I would save this option for the most extreme case, this is a crutch.

And then I turned to Google and he gave me this wonderful link . The ExportAsFixedFormat method described in it was what you needed! But again, luck did not come to me.

He completely ignored the areas of the press, and without them we got porridge. I googled, left questions in many forums, including in English. There was no answer. I even ported the article code in C #, but the result has not changed.

After a week of research, I decided to spit on everything and use the very last option with a virtual printer, but what was my surprise when he ignored the print area, despite the fact that if you sent a document from Excel itself, everything was fine.

Through trial and error, dancing with tambourines and a fascinating reading of Google, I found out that the print area is ignored when this method is called and printed in php, C #, but for some reason when using VBScript everything works as it should. Unfortunately, I did not find out what caused this.

And so, it was decided from PHP to open a VBS script and convert excel to pdf from it already. Yes, it’s also a crutch, but not as awkward as a virtual printer.

Here is the resulting script:

Dim XL
Dim XLWkbk
Dim ObjArgs
Dim paramSourceBookPath
Dim paramExportFilePath

set objargs = wscript.arguments
if objArgs.count <= 1 then
wscript.echo "invalid passed arguments"
wscript.quit
end if

Set XL = CreateObject ("excel.application")
XL. = false Visible

paramSourceBookPath = objargs (0)
paramExportFilePath = objargs (1)

Set XLWkbk = XL.Workbooks.Open (paramSourceBookPath, False)

XLWkbk.ExportAsFixedFormat 0, paramExportFilePath, 1, false, false

XLWkbk.Close False
XL.Quit

Set XLWkbk = Nothing
Set XL = Nothing
set ObjArgs = nothing

It is quite simple, takes two arguments at the input, the first path to the file, Excel the second path and the name of the created PDF.

Called from any PL, for example PHP: In the bottom line, we have a not-so-beautiful, but 100% working method for converting Excel to PDF, which guarantees a print-like result without pitfalls. PS: Habr, the place where you will learn more from the comments on the article than from the article, so I will be glad if they show me a simpler way. PPS: Errors in writing my cross, I checked this text with the firecracker and WordWorker, but I doubt that it helped. Do not scold strongly, it is better to write in a personal, I will correct everything.
exec(APPLICATION_SCRIPT_FOLDER.'\\excel.vbs C:\\tmp\\test.xlsx C:\\tmp\\test.pdf");