Question
Coforge
IN
Last activity: 14 Nov 2018 23:54 EST
Create and export data in excel format with multiple excel tabs created dynamically
Hi All,
I have a requirement to create and download the data from clipboard in Excel format. Until now I was using MSOParseExcelFile for mapping the data from clipboard to excel template. However now my new requirement is to create the tabs in excel sheet dynamically based on a pagelist. So for example if my pagelist "TestList" contains 2 records in it while user clicks on the Export button, the program should create 2 tabs in excel sheet and map the corresponding data in those sheet and initiate the download.
Is there any OOTB way of handling the same? If not how best can i handle it?
Thanks for the help.
***Edited by Moderator: Pallavi to update platform capability tags***
Accepted Solution
Pegasystems Inc.
GB
There are a couple of facilities I know about on how to create Excel files from scratch from within PRPC - including multi-tabbed Excel files.
1. Use 'Stream Rules' to map PRPC properties to an XML file in the Microsoft 2003 XML format (https://en.wikipedia.org/wiki/Microsoft_Office_XML_formats).
2. Use the Apache POI library directly from PRPC to programmatically generated (binary, xlsx) Excel files.
I have attached a ZIP file containing a couple of old posts I put together a few years ago: the following direct links to these posts may still work for you (but don't rely on them, since they are not guaranteed to remain available):
https://mesh.pega.com/docs/DOC-89449
https://mesh.pega.com/docs/DOC-89784
See also: https://mesh.pega.com/message/177999
The ZIP also contains examples. Hope this helps - this is not a supported thing I'm afraid - but hopefully gives you some ideas on how to do this sort of thing.
Since the posts are quite old, the version of PRPC was (probably) ~7.1.x - so you may have to tweak a few bits to these working on newer versions.
You can use the same methods to generate other types of Microsoft Documents as well.