Create Excel Files in SQR and PeopleCode

I recently stumbled upon an Open Source project on Source Forge that allows you to create Microsoft Excel Workbooks with multiple sheets, advanced formatting and formulas.

If you are not a reporting guru then you are not aware that PeopleCode cannot create an excel file. The only alternative is to create a comma separate file and have the user import that into Excel.

In SQR, you can run the report to an Excel format starting in release 8.0. However, you have very little control over the formatting and you cannot have formulas. SQR basically creates a native SPF file and then calls some libraries to convert that file to excel format (or PDF, CSV, HTML).

This was written for both SQR and PeopleCode. The PeopleCode version is implemented as an iScript with the intention of pushing a file down to the browser and having the user’s workstation open excel. With some rework of the PeopleCode, you can have your application engines creating rich excel report with formatting.

There are some limitations to this project. Your users have to be running a minimum of Office XP. The code actually creates an XML file in the Office format. If you did not know this was possible, do a “save as” on an excel document and notice you can save the spreadsheet as an “XML Spreadsheet”. I am sure this took some time to develop as I have looked at the Microsoft Office XML Specification and it is pretty large.

You can check the project out at the following link: SQR2XML Project