Example of an extention to create excel file from template on the server

Example of an extention to create excel file from template on the server

I'm looking for an example of an extention that produces an excel file on the server from an excel template, uses excel methods to create worksheets from data in outsystems sqlserver tables or views and executes excel macro's to produce an excel file that can be uploaded to the client... wishful thinking? I hope not! Any examples welcome....
Hello Orlando!

It is indeed possible to use Excel server side to perform the operations you mention. It is, however, strongly recommended that you try to use alternatives and keep its usage to an absolute minimum. You can find Microsoft’s position on the issue, along with guidelines for doing it, should you decide to, at http://support.microsoft.com/kb/257757. I would also recommend you the reading of http://msdn.microsoft.com/library/default.asp?url=/library/en-us/stagsdk/html/stconPIAs_HV01083002.asp, where you can find information on how to develop .net applications that are independent of the office version.

An alternative to the approach you describe in your post would be to have the Excel template (client side) to include a hidden sheet that would contain a web query or somehow (web services?) retrieve data from the server and store it in that sheet. The other workbook’s sheets could then use that sheet as data source.

If it is not possible to avoid Office automation in the server, then you might use the extension attached to this post as a reference. The extension receives an excel workbook and returns a list record of strings with the excel column that starts at line y and column x. If you want to use it you’ll have to set up the server so that the application runs with security credentials that allow it to access the needed files and registry entries for the COM objects. You will also need to run the application with an account that has already ran that particular office application (this is because some office applications pop up some dialog boxes for initial configuration in the first use). Included with the extension is a test application – you can use it to see how the extension works.

If you plan to use office automation in your server you should also handle all the possible errors/exceptions and make sure you gracefully exit, freeing all the allocated resources.

Please bear in mind that this extension is provided “as is”, is not an OutSystems product and is not supported by OutSystems.