Excel save documents in PDF on the server

Not long ago, there was a task to create a simple service to create PDF reports based office documents to the intranet. And everything seems to be easy, but that's keeping Excel to PDF problems. I wonder? Ask for cat.


Like I said, at first, everything seemed easy, I had my own experience, Habré was paper . But first things first.

My achievements using COM objects and method excel.application saveAs, it worked fine, until required to take an ordinary, nice paper and make it pdf, but in this case, the files were not so simple.

First, the document assumes three formats - xls, xlsx and xml. Second, all the documents contain macros, and some links to other documents. Third, they contain a lot of sheets, and cross-references between the sheets. Not necessary for the report lists an inconspicuous, and the sheets themselves in porridge subsidiary figures reported information indicating the area press. Needless to say, that saveAs ignores all this wealth and exit after dancing with tambourines get totally readable picture.

Here I think is necessary to make a digression and explain why the files created such a mess. I work in a very large organization, side by side with a bunch of my grandmother retirement age. They can not even make the bold text in the cell, but can perfectly speak superiors that they are not "programmers" and it should not be able to. Bosses have too much, and because of pity for their grandmothers or computer illiteracy, and perhaps at the behest of stars complaints grandmothers are encouraged, and all attempts to clean up the mess in the documents suppressed.

Let us return to our sheep. Included in the above article has been offered the option of converting "As I see" using open office, this option was not I by windy OO relationship to MS. Some documents do open it correctly, but most of the content was traveling before the conversion.

There was also a third option. Print documents to a virtual printer, but I have decided that I will save this option as a last resort, because it is a crutch.

And then I turned to Google and he gave me this wonderful link . The described method ExportAsFixedFormat it was what you need! But again, I suffered no luck.

He completely ignored the print area, and without them receive porridge. I googled, leaves the question on many forums, including the English language. There was no answer. I even moved the article code in C #, but the result has not changed.

After weeks of research, I decided to spit on everything and use the most extreme version of the virtual printer, but what was my astonishment when he ignored the print area, despite the fact that if you send a document from Excel itself everything was normal.

Through trial and error, with tambourines and dancing fascinating reading google, I found the print area is ignored when this method and print in php, C #, but for some reason when using VBScript everything works as it should. What caused this, I, unfortunately, have not figured out.

And so, it was decided from the PHP open VBS creak and already out of it to convert excel to pdf. Yes, it's a crutch, but not as awkward as a virtual printer.

Here's 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's pretty simple, takes two arguments, the first path, Excel, and the second path name for the PDF.

Called from any PL, such as PHP:
exec(APPLICATION_SCRIPT_FOLDER.'\\excel.vbs C:\\tmp\\test.xlsx C:\\tmp\\test.pdf");

The bottom line is we are not very nice, but 100% working method to convert Excel to PDF, which guarantees the result of "How to Print" without drawbacks.

PS: Habr, the place where the comments of this article you will learn more than from the article, so I will be glad if you show me an easier way.

PPS: Error while writing my cross, I checked the text and a Word speller FireFox, but I doubt that helped. Do not swear much better in a personal email, all corrected.