Create Excel Files in SQR and PeopleCode

By Chris Malek | Tue, Oct 12, 2010

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

Article Categories
Author Info
Chris Malek

Chris Malek is a PeopleTools® Technical Consultant with two decades of experience working on PeopleSoft enterprise software projects. He is available for consulting engagements.

About Chris Work with Chris
Looking for pain-free PeopleSoft web services? 😀
PeopleSoft Simple Web Services (SWS)

Introducing a small but powerful PeopleSoft bolt-on that makes web services very easy. If you have a SQL statement, you can turn that into a web service in PeopleSoft in a few minutes.

Integration Broker - The Missing Manual

I am in the process of writing a book called "Integration Broker - The Missing Manual" that you can read online.