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
Pegasystems Inc.
GB
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"
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>
DCS
GB
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>
Pegasystems Inc.
GB
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
DCS
GB
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.