Question

I wanted to Freeze Column Header After Export to Excel.

I wanted to Freeze Column Header After Export to Excel. Is there any way I can do it in Pega. Pls help me on this

**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

December 16, 2015 - 7:14am

I believe there is no ootb way to do it. Did you explore apache POI?

Pega
December 16, 2015 - 11:47am

Hi Rajakulasingam,

I agree with GovardhanGangavaram's answer - there is no OOTB way of doing this.

The 'export to excel' mechanism uses the 'Rule-Obj-HTML' rule:

Rule-Obj-Report-Definition.pzListViewExcelData|Pega-Reporting:07-10-15

To generate the EXCEL output; this is a FINAL rule, so you cannot just over-ride it.

The 'Rule-Obj-HTML' in fact generates a HTML output that EXCEL is able to parse : I don't know for sure , but I don't believe this format will

allow you to specify more advanced features such as locking the first row/column in any case.

You *could* do this using the XML 2003 Format (for instance) ; but again you would need to re-write "pzListViewExcelData" in order to do this, and this is a final rule.

You could use the Apache POI library to generate your EXCEL separately (or indeed use the XML 2003 Format) ; but you then need to write additional 'plumbing' code to run the report and export to excel;

rather than being able to use the default OOTB mechanisms to do this.

Cheers

John

Just for reference the following EXCEL 2003 XML will create a spreadsheet with a frozen first row.

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

  <Author>Pritchard-Williams, John</Author>

  <LastAuthor>Pritchard-Williams, John</LastAuthor>

  <Created>2015-12-16T15:05:10Z</Created>

  <Company>Pegasystems</Company>

  <Version>15.00</Version>

</DocumentProperties>

<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">

  <AllowPNG/>

</OfficeDocumentSettings>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

  <WindowHeight>9585</WindowHeight>

  <WindowWidth>24000</WindowWidth>

  <WindowTopX>0</WindowTopX>

  <WindowTopY>0</WindowTopY>

  <ProtectStructure>False</ProtectStructure>

  <ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

  <Style ss:ID="Default" ss:Name="Normal">

   <Alignment ss:Vertical="Bottom"/>

   <Borders/>

   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>

   <Interior/>

   <NumberFormat/>

   <Protection/>

  </Style>

</Styles>

<Worksheet ss:Name="Sheet1">

  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"

   x:FullRows="1" ss:DefaultRowHeight="15">

<Row ss:AutoFitHeight="0">

    <Cell><Data ss:Type="String">hello</Data></Cell>

   </Row>

  </Table>

  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

   <Selected/>

   <FreezePanes/>

   <FrozenNoSplit/>

   <SplitHorizontal>1</SplitHorizontal>

   <TopRowBottomPane>1</TopRowBottomPane>

   <ActivePane>2</ActivePane>

</WorksheetOptions>

</Worksheet>

</Workbook>

January 27, 2016 - 1:14pm
Response to JOHNPW_GCS

Hi,

Am seeing Property called pyFixedHeader when we select "Do not scroll Header" as true under pyUserInteractions Page. Is there any way we can set this value to the excel sheet in "pzListViewExcelData" to freeze the excel first row. Not sure how to use this property & set the value to the excel in this HTML rule. Please advice.

<pyUserInteractions>

<pyShowDataOnly>false</pyShowDataOnly>

<pyDisableDrillDown>false</pyDisableDrillDown>

<pyPromptForFilters>false</pyPromptForFilters>

<pyFixedHeader>true</pyFixedHeader>

February 9, 2016 - 2:52pm
Response to JOHNPW_GCS

Hi John,

Could you please advice on how to use this pyFixedHeader flag to the excel freezing option

Pega
February 10, 2016 - 12:26pm
Response to HemaA279

Hi Hema,

Can you clarify where you see this Page Data ? I'm not familiar with it ?

<pyUserInteractions>
<pyShowDataOnly>false</pyShowDataOnly>
<pyDisableDrillDown>false</pyDisableDrillDown>
<pyPromptForFilters>false</pyPromptForFilters>
<pyFixedHeader>true</pyFixedHeader>

Thanks

John

February 10, 2016 - 1:19pm
Response to JOHNPW_GCS

Hi,

In the HTML rule - pzListViewExcelData, they are mapping the values from the report definition page to the excel format list, ClipboardPage cbpHeaderColumns = cbpMainPage.getProperty(".pyReportDefinition").getPageValue();

In the same pyReportDefinition - Am seeing Property called pyFixedHeader when we select "Do not scroll Header" as true under pyUserInteractions Page

pyReportContentPage.pyReportDefinition.pyUI.pyUserInteractions

 

Is there any way we can use this fixed header property value & set the value at excel sheet for freezing the first header row.