But what if the data you want to export to Excel is not in a grid? What if you need to programatically export data using PeopleCode.
I'm sure there are many ways to do this but I recently came across this useful technique for exporting RowSet record data to Excel. Here's a working code sample.
The magic is in the use of the FormatResultString method from the Query class. This accepts a rowset as input and will produce a string of HTML which when opened in Excel does just nicely.
|HTML produced by the FormatResultString method|
|HTML opened in Excel|
I've repeated the code below if you want to copy it.
Local string &strExcel, &fileName; Local File &exportFile; /*Create a rowset. This can be a standalone or buffer rowset */ Local Rowset &rs = CreateRowset(Record.PSPRCSRQST); &rs.Fill(); Local object &qryObject = %Session.GetQuery(); /* Create a file on the application server */ &fileName = "Output_" | %UserId | %Date | %Time | ".XLS"; &exportFile = GetFile(&fileName, "W"); If &exportFile.IsOpen Then /*Use the FormatResultString method of the Query object to generate Excel string */ &strExcel = &qryObject.FormatResultString(&rs, %Query_XLS, 1, &rs.RowCount); &exportFile.WriteLine(&strExcel); &exportFile.Close(); End-If; /* Add attachment and view */ &putFileName = "files/" | &fileName; /* Pickup the app server file, attach and view */ /* CommitWork is required as ViewAttachment is a thinktime function. Using an iScript to view the attachment is another option here. See Jim Marion here http://jjmpsj.blogspot.co.uk/2009/01/exporting-attachments-part-2.html */ &res = PutAttachment("record://PSFILE_ATTDET", &fileName, &putFileName, "PS_SERVDIR"); CommitWork(); &res = ViewAttachment("record://PSFILE_ATTDET", &fileName, &fileName);
Please feel free to discuss other methods you know of in the comments below.