Discussion

Creating an Excel report with multiple tabs

Hi,
How to create an excel report with data in 3 different sheets?
I have 3 code-pega-lists which I want to put in 3 sheets of the same excel file. How to do it?
Currently, I am putting each list in a separate excel sheet (comma separated csv files). Business does not like it.
Please suggest a way to create a single excel with 3 tabs.
Thanks,
Shiva
AIG

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

February 10, 2009 - 11:48am

Hi Shiva,

If your project allows you to work with external open source packages, you can use Apache POI or JExcel API for you excel integrations. Those will have functionalities to add multiple sheets, formatting etc…

Thank you
Indrajith

February 10, 2009 - 11:52am

re: Please suggest a way to create a single excel with 3 tabs.

In addition, if using Excel 2002 or later, you can simply export an XML Spreadsheet, by using a Rule-Obj-XML file. Wikipedia's code example is sufficient, though you can also do a SaveAs from Excel into XML Spreadsheet format and get the format you want.

Within Rule-Obj-XML, you can use JSP, so that you can access & render clipboard data.

A Rule-Obj-XML with JSP *may* be easier for business users to customize than an activity's use of POI.

Jon

February 11, 2009 - 1:10pm

Thanks Indrajith and Jon. I tried both the approaches and both works! However, I had to chose the approach given by Jon as in AIG we are not currently using any external classes / jars. I am now able to generate excel with multiple sheets, with colors, bold letters, borders etc. Thanks you both for your suggestions.
-Shiva

April 7, 2011 - 12:21am

Hi jon & shiva,

can u give sample code in xml for creating an excel report with multiple tabs?

if it is with pega syntax, it will be very useful..

Thanks in advance..

waiting for the reply..

January 23, 2015 - 6:18am

Hi Shiva,

 

           Can you please explain how to populate values in different worksheets of a same excel file. Please describe in brief...

 

Thanks

March 30, 2016 - 9:10am

Could you please provide sample code?

Thanks

Chaithanya.

May 25, 2016 - 11:51am

Hi ,

Can you please provide sample code or more eloborated steps it would be of great help .

July 13, 2016 - 3:43am

Hi,

I came across similar sort of a scenario and i managed to export data to multiple tabs, dynamically.  Steps are below. 

1. you need to create an xml version of the excel you are going to create as a Skelton. So create a blank excel file, design your excel sheet, create a sample table with headers / borders / styles etc... I suggest you create few sample tabs too. Purpose of doing this is to generate XML body and sample xml snippets. 

2. This generated XML file contains many tags / styles generated by excel. it also contains the snippets used to generate the sample table / tab etc... Sample code for a tab looks like below.

 <Worksheet ss:Name="Tab1">
  <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="3" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="s63"><Data ss:Type="String">header</Data></Cell>
   </Row>
   <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2"><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell><Data ss:Type="Number">3</Data></Cell>
    <Cell><Data ss:Type="Number">4</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>1</ActiveCol>
     <RangeSelection>R2C2:R2C5</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>

3. Once you get the Skelton, it is time to design / code your new excel. You can use JSP tags to populate data. If you would like to create a new tab for each page in a pega list, you can include above code snippet in between a JSP foreach and embed the parameters. 

https://pdn.pega.com/sites/pdn.pega.com/files/help_v721/procomhelpmain.htm#jsp/foreach/jspforeach.htm

I wanted to create set of rows for each page and code looks like below.

<Table>  
  <pega:forEach name="MyPageList.pxResults"> 

   <Row ss:AutoFitHeight="0"/>
   <Row ss:AutoFitHeight="0">
    <Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="s63"><Data ss:Type="String">Coverage Details :</Data></Cell>
   </Row>
    
	<Row ss:AutoFitHeight="0">
	
    <Cell ss:Index="2" ss:StyleID="s21"><Data ss:Type="String">Coverage ID</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Coverage Code</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Active Entity ?</Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String">Entity ID</Data></Cell>
   </Row>
   
   <Row ss:AutoFitHeight="0">
	
    <Cell ss:Index="2" ss:StyleID="s21"><Data ss:Type="String"><pega:reference name="$THIS.CVRG_ID" mode="text" /></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"><pega:reference name="$THIS.CVRG_CD" mode="text" /></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"><pega:reference name="$THIS.IS_ACTIVE" mode="text" /></Data></Cell>
    <Cell ss:StyleID="s21"><Data ss:Type="String"><pega:reference name="$THIS.ENTITY_ID" mode="text" /></Data></Cell>
   </Row>
   
  </pega:forEach >
 </Table>

At the end of this exercise, you should have an XML version of your excel with JSP tags to manipulate data / worksheets / styles etc..

4. Create a XML Stream. Give any String value for XML Type field. This will be required later on.

5. Uncheck Auto-generated XML option in XML tab. Then paste the XML in to the stream.

 

Next we need to use the stream and generate an excel. For that you need to use Property-Set-XML and ExportToExcel activities. You will need to create a Text Property to hold the generated xml content (e.x .PropertyCreatedToHoldXmlStream)

Set header info as below.

.pyHTTPResponseHeaders.contentType | "application/vnd.ms-excel"

.pyHTTPResponseHeaders.ContentDisposition | "attachment;filename=MyReport.xls"

 

Make sure to define the lists (e.x MyPageList.pxResults) in the pages and classes tab for both (XML Stream and Activity).

When you run this activity, this XML will be populated with the list values and final JSP will be generated. That will be saved in to the PropertyCreatedToHoldXmlStream property which is then passed in the export to excel activity. That creates the excel sheet and will be downloaded.

P.S : This method could be very time consuming / error prone since XML manipulation is tricky. But this is the only way I could generate dynamic tabs. 

 

Thanks

reference

Excelsheet XML info

https://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx