Saving Excel documents to PDF on server

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





As I said, at first everything seemed easy, I had my own work, there was an article on Habré. But first things first.



My achievements used the com objects excel.application and the saveAs method, it worked fine, until it was required to take a plain, beautiful document and make pdf of it, but in this case the files were not so simple.

')

First, documents are assumed 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-references between sheets. Sheets not needed for the report are hidden, and on the sheets themselves in the porridge of auxiliary figures the reporting information is highlighted by the printable 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 make a digression and explain why such confusion is created with files. I work in a very large organization, side by side with a bunch of grandmother of retirement age. They cannot even make the text in the cell bold, but they can finely tell the authorities that they are not “programmers” and should not be able to. We have a lot of bosses too, and because of pity for grandmothers or their computer illiteracy, and perhaps, at the behest of the stars, grandmothers' complaints are encouraged, and all attempts to unload the mess in the documents are stopped.



Let's return to our sheep. In the above mentioned article , the “As I see” conversion option was proposed using open office, this option did not suit me due to the windy relationship between OO and MS. Some documents did indeed open in it correctly, but most often the contents rode before conversion.



There was a third option. Printing documents to a virtual printer, but I decided that I would save this option for the most extreme case, so 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 was needed! But I was not lucky again.



He completely ignored areas of the press, and without them porridge turned out. I googled, left questions in a variety of forums, including English ones. There was no answer. I even moved the article code to C #, but the result did not change.



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, moreover, that if you sent a document from Excel itself everything was fine.



Through trial and error, dancing with tambourines and fascinating reading of Google, I found out that the printable area is ignored when calling this method and typing in php, C #, but for some reason when using VBScript everything works as it should. What caused this, unfortunately, I did not find out.



And so, it was decided to open a PHP script from PHP and to convert excel to pdf from it. Yes, it is also a crutch, but not as clumsy 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.Visible = false



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; it takes two arguments as 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:

exec(APPLICATION_SCRIPT_FOLDER.'\\excel.vbs C:\\tmp\\test.xlsx C:\\tmp\\test.pdf");





In the bottom line, we have not quite beautiful, but 100% working method for converting Excel to PDF, which guarantees the result "As on print" without pitfalls.



PS: Habr, the place where from the comments to the article you will learn more than from the article, so I will be glad if I can see a simpler way.



PPS: Mistakes when writing my cross, I checked this text with the Firelight and Word spellchecker, but I doubt that it helped. Do not scold much better write in a personal, all correct.