Using existing pega activities to create an excel spreadsheet - Page Orientation
There's an activity called ViewExportToExcel whose short description is 'Build a table from the query results and send it to MS Excel'.
This activity calls CreateExportData which 'Build a table from the query results'.
Also, it calls activity ViewExcelData.
I'm trying to get the Excel spreadsheet to be created with the Landscape Orientation instead of the
default, Portrait Orientation. How do I can this be accomplished? I've looked for properites that
may be similar to: "contentType" ,"application/vnd.ms-excel". Is there a OrientationType? Or something similar?
***Updated by moderator: Lochan to close 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.
Pegasystems Inc.
GB
Hi Gary,
Can you clarify that when you say 'Landscape' or 'Portrait' here - that you mean when the EXCEL file is printed out ? Or something else ? (or do we want to 'flip' the cols/rows in the data being returned from PRPC ?
Thanks in advance,
John
Pegasystems Inc.
GB
Thanks for that - so I haven't been able to solve this yet - but it looks like in Excel - you can actually store this value within the document itself (and even per worksheet).
It would appear that in the HTML format* - there is a special style tag that can be included to switch the page-orientation - but I found it had no effect on the test file I was editing (I am just manually editing a PRPC-generated 'xls' (actually HTML) file here and loading it into EXCEL 2010 at this point).
*I'm not actually not familiar with where the Activity "ViewExportToExcel" is being called from - but I think it might be from the 'Export To Excel' button on the report/listview screen in PRPC - in which case the output format is the HTML format: if it is - and we get can get that style tag working - this would probably mean a change to the particular HTML/JSP (the name of which escapes me at this point) within PRPC which generates this output.
Thanks for that - so I haven't been able to solve this yet - but it looks like in Excel - you can actually store this value within the document itself (and even per worksheet).
It would appear that in the HTML format* - there is a special style tag that can be included to switch the page-orientation - but I found it had no effect on the test file I was editing (I am just manually editing a PRPC-generated 'xls' (actually HTML) file here and loading it into EXCEL 2010 at this point).
*I'm not actually not familiar with where the Activity "ViewExportToExcel" is being called from - but I think it might be from the 'Export To Excel' button on the report/listview screen in PRPC - in which case the output format is the HTML format: if it is - and we get can get that style tag working - this would probably mean a change to the particular HTML/JSP (the name of which escapes me at this point) within PRPC which generates this output.
Pegasystems Inc.
GB
So looking at Activity - it uses a HTML Stream called "ListViewExcelData | Pega-Reporting:07-10-15" (I am looking at a PRPC 7.1.7 system here) to create the EXCEL sheet.
This is a FINAL rule and has a bit in it which creates a 'style' section in the resultant EXCEL, like this:
if ( !forDocument )
{ if ( !embedded )
appendString( "<HTML>" );
appendString( "<head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"CHARSET=UTF-8\">" );
// to avoid 03-02-01 becoming 03/02/2001 etc....
appendString( "<style>" );
appendString( ".excel-hide-row{display:none;}" );
appendString( ".excelText{vnd.ms-excel.numberformat:@;}" );
appendString( ".excel-datetime{vnd.ms-excel.numberformat:m/d/yyyy h:mm AM/PM}" );
appendString( ".excel-date{vnd.ms-excel.numberformat:m/d/yyyy}" );
appendString( ".excel-timeofday{vnd.ms-excel.numberformat:hh:mm}" );
appendString( ".excel-number{vnd.ms-excel.numberformat:General;}" );
appendString( "</style>" );
appendString( "</head> ");
}
So I guess this is probably where any extra directives would go.....so the problem seems to break down into:
1. Getting the right style tag (or whatever it turns out to be) to influence a HTML-based EXCEL document ( I tried
So looking at Activity - it uses a HTML Stream called "ListViewExcelData | Pega-Reporting:07-10-15" (I am looking at a PRPC 7.1.7 system here) to create the EXCEL sheet.
This is a FINAL rule and has a bit in it which creates a 'style' section in the resultant EXCEL, like this:
if ( !forDocument )
{ if ( !embedded )
appendString( "<HTML>" );
appendString( "<head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"CHARSET=UTF-8\">" );
// to avoid 03-02-01 becoming 03/02/2001 etc....
appendString( "<style>" );
appendString( ".excel-hide-row{display:none;}" );
appendString( ".excelText{vnd.ms-excel.numberformat:@;}" );
appendString( ".excel-datetime{vnd.ms-excel.numberformat:m/d/yyyy h:mm AM/PM}" );
appendString( ".excel-date{vnd.ms-excel.numberformat:m/d/yyyy}" );
appendString( ".excel-timeofday{vnd.ms-excel.numberformat:hh:mm}" );
appendString( ".excel-number{vnd.ms-excel.numberformat:General;}" );
appendString( "</style>" );
appendString( "</head> ");
}
So I guess this is probably where any extra directives would go.....so the problem seems to break down into:
1. Getting the right style tag (or whatever it turns out to be) to influence a HTML-based EXCEL document ( I tried
@page {mso-page-orientation:landscape;}
- but is didn't work for me yet...)
2. Either making copies of the OOTB activities here (since the rule above is FINAL) - or getting a HFIX request in for this change (if the need is urgent) - or a ENH request (if it would be more of a 'nice-to-have' in a future version).
Catamaran/Optum
US
We're on Version 6.2 SP2. However, the code is very similar in this instance. The question has come down to the value of the style tag. I 'guessed' at some values but have had no success.
Pegasystems Inc.
GB
There seems to be a fair amount of discussion on this topic (streaming an EXCEL file in HTML format to a BROWSER) - even outside of PRPC-world.
For instance: http://forums.iis.net/t/1156329.aspx?Changing+an+Excel+File+
I'll see if I have any luck applying some of the advise in that IIS forum here...
Updated: 22 Jun 2015 12:18 EDT
Pegasystems Inc.
GB
Ok - the following code (which I saved as 'test.xls' (which is what PRPC does, but also results in a 'This Document is a different format....' message when you open it) or 'test.htm') keeps the page orientation as 'Landscape':
It's a 'hybrid' XML/HTML format file:
Ok - the following code (which I saved as 'test.xls' (which is what PRPC does, but also results in a 'This Document is a different format....' message when you open it) or 'test.htm') keeps the page orientation as 'Landscape':
It's a 'hybrid' XML/HTML format file:
<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<style>
<!--table
@page { mso-page-orientation:landscape;}
-->
.excel-hide-row{display:none;}
.excelText{vnd.ms-excel.numberformat:@;}
.excel-datetime{vnd.ms-excel.numberformat:dd/MM/yyyy HH:mm}
.excel-date{vnd.ms-excel.numberformat:dd/MM/yyyy}
.excel-timeofday{vnd.ms-excel.numberformat:hh:mm}
.excel-number{vnd.ms-excel.numberformat:General;}
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Export Data</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
</x:Print>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml><![endif]-->
</HEAD>
<BODY>
<TABLE>
<TR>
<TD bgColor="#dcdcdc" class="excelText">Label</TD>
</TR>
<TR>
<TD>
Test
</TD>
</TR>
</TABLE>
</BODY>
</HTML>
When I open this in EXCEL - we can see the page orientation is remembered:
So in theory this should also work if we edited the HTML rule within PRPC (I haven't tried this yet).
Looks like the key to getting this working (I think) - is that @page CSS directive itself is not enough - it seems you need to explictly define a Worksheet section in the XML portion of the doc for this to work...
Catamaran/Optum
US
i verified your code working above. The excel sheet was in landscape.
everything i've tried with pega involved, results in a portrait orientation instead of landscape.
Pegasystems Inc.
GB
Hi Gary,
I just got this working on 7.1.7 - here's what I did:
I ran a TRACER (with Stream Rules enabled) - ran up a Case Manager Portal and ran an OOTB report ("List of processes entered by operator") and made sure I had been looking at the correct HTML Stream - in fact I wasn't.
The Stream Rule was: Rule-Obj-Report-Definition|pzListViewExcelData|Pega-Reporting:07-10-15
So I did a PRIVATE EDIT on this rule and altered the top of the code as follows:
Hi Gary,
I just got this working on 7.1.7 - here's what I did:
I ran a TRACER (with Stream Rules enabled) - ran up a Case Manager Portal and ran an OOTB report ("List of processes entered by operator") and made sure I had been looking at the correct HTML Stream - in fact I wasn't.
The Stream Rule was: Rule-Obj-Report-Definition|pzListViewExcelData|Pega-Reporting:07-10-15
So I did a PRIVATE EDIT on this rule and altered the top of the code as follows:
<%
oLog.infoForced("**** pzListViewExcelData: PRIVATE CHECKOUT VERSION CALLED ****");
tools.appendString( "<HTML>" );
[...]
tools.appendString("<!--table");
tools.appendString(" @page { mso-page-orientation:landscape;}");
tools.appendString(" -->");
tools.appendString(".excel-hide-row{display:none;}");
tools.appendString(".excelText{vnd.ms-excel.numberformat:@;}");
tools.appendString(".excel-datetime{vnd.ms-excel.numberformat:dd/MM/yyyy HH:mm}");
tools.appendString(".excel-date{vnd.ms-excel.numberformat:dd/MM/yyyy}");
tools.appendString(".excel-timeofday{vnd.ms-excel.numberformat:hh:mm}");
tools.appendString(".excel-number{vnd.ms-excel.numberformat:General;}");
tools.appendString("</style>");
tools.appendString(" <!--[if gte mso 9]><xml>");
tools.appendString(" <x:ExcelWorkbook xmlns:x='urn:schemas-microsoft-com:office:excel'>");
tools.appendString(" <x:ExcelWorksheets>");
tools.appendString(" <x:ExcelWorksheet>");
tools.appendString(" <x:Name>Export Data</x:Name>");
tools.appendString(" <x:WorksheetOptions>");
tools.appendString(" <x:Print>");
tools.appendString(" <x:ValidPrinterInfo/>");
tools.appendString(" </x:Print>");
tools.appendString(" </x:WorksheetOptions>");
tools.appendString(" </x:ExcelWorksheet>");
tools.appendString(" </x:ExcelWorksheets>");
tools.appendString(" </x:ExcelWorkbook>");
tools.appendString(" </xml><![endif]-->");
tools.appendString( "</style>" );
[...]
Then re-ran the Report from the Case Manager Portal (ensuring the infoForced messages showed up in the logs - it did).
The resultant EXCEL file has Landscape Orientation :
This should work in a very similar way on 6.x - but I haven't tried it yet.
I think Private Edit (maybe "Private Checkout") should be available on your 6.x system - but if not, let us know and we can suggest an alternative to using that.
I have attached the resultant EXCEL file.
I would suggest (after you confirm this has worked) - that if you want this functionality to be available OOTB - we at least log a Feedback Item (Enhancement Request) for this facility be considered to be made an OOTB feature (as a configurable option) in future versions of PRPC; in the short term (depending on how important this feature would be to your existing 6.x system) - you would need to request a HFIX (I would say) to have this functionality available to you without Private Edit.....
Thanks again,
John
Pegasystems Inc.
GB
Additionally I have logged the following Feedback Item (Enhancement Request) to give Pega the opportunity to review whether it would be feasible to include a Portrait/Landscape option for ExportToExcel in a future version of PRPC.
FDBK-11736: "Landscape/Portrait Parameter for ExportToExcel"
When I was writing the Feedback Item - it became apparent to me - that altering the rule "pzListViewExcelData" (etc) is only half the story : we would need to introduce a way for the user (developer?) to change this setting.
There are a number of permutations on this - should the setting be global to all users ? Should it be specific to certain reports , certain users ? Or should it be something that should be chosen from the Report Screen etc - I have left these options open on the Feedback Item for consideration.
We welcome your input here - so if you have an ideas regarding what would be the best option - I can add your idea to the Feedback Item.
Thanks again,
John
Catamaran/Optum
US
i took your code from above and pasted it into my 6.2 version(in my private area). The spreadsheet did NOT come out in Landscape. It remained in portrait. Not sure what's wrong.
i took your code from above and pasted it into my 6.2 version(in my private area). The spreadsheet did NOT come out in Landscape. It remained in portrait. Not sure what's wrong.
<%
tools.appendString( "<HTML>" );
tools.appendString( "<head><META HTTP-EQUIV=\"Content-Type\" CONTENT=\"CHARSET=UTF-8\">" );
// to avoid 03-02-01 becoming 03/02/2001 etc....
tools.appendString( "<style>" );
tools.appendString("<!--table");
tools.appendString(" @page { mso-page-orientation:landscape;}");
tools.appendString(" -->");
tools.appendString(".excel-hide-row{display:none;}");
tools.appendString(".excelText{vnd.ms-excel.numberformat:@;}");
tools.appendString(".excel-datetime{vnd.ms-excel.numberformat:dd/MM/yyyy HH:mm}");
tools.appendString(".excel-date{vnd.ms-excel.numberformat:dd/MM/yyyy}");
tools.appendString(".excel-timeofday{vnd.ms-excel.numberformat:hh:mm}");
tools.appendString(".excel-number{vnd.ms-excel.numberformat:General;}");
tools.appendString("</style>");
tools.appendString(" <!--[if gte mso 9]><xml>");
tools.appendString(" <x:ExcelWorkbook xmlns:x='urn:schemas-microsoft-com:office:excel'>");
tools.appendString(" <x:ExcelWorksheets>");
tools.appendString(" <x:ExcelWorksheet>");
tools.appendString(" <x:Name>Export Data</x:Name>");
tools.appendString(" <x:WorksheetOptions>");
tools.appendString(" <x:Print>");
tools.appendString(" <x:ValidPrinterInfo/>");
tools.appendString(" </x:Print>");
tools.appendString(" </x:WorksheetOptions>");
tools.appendString(" </x:ExcelWorksheet>");
tools.appendString(" </x:ExcelWorksheets>");
tools.appendString(" </x:ExcelWorkbook>");
tools.appendString(" </xml><![endif]-->");
tools.appendString( "</style>" ); tools.appendString( "</head> ");
tools.appendString( "<TABLE cellspacing='0' cellpadding='1' width='100%' border='1' style='border-collapse: collapse;'>" );
Updated: 23 Jun 2015 12:19 EDT
Pegasystems Inc.
GB
Hi Gary,
Let's first establish that the correct RULE is being picked up here; I would do two things:
#1 RUN A TRACER:
A. Run a TRACER (with 'Stream Rules' enabled) - pause it.
B. Get to the point where you have run a Report (make sure it is not a Chart or an Aggregate Report at this point - as these may use different rules).
C. Unpause Tracer: click on the 'Export To Excel' button - and download the file - but do NOT open it in EXCEL yet.
Within the TRACER - we should be able to identify the HTML rule (or similar-type of Rule) that is being used to put the EXCEL document together.
#2 ADD LOGGING TO THE MODIFIED RULE.
Locate the Rule that is being used - and add in the changes as well as a 'oLog.infoForced("**** <name of rule> CALLED ***"); somewhere near the top.
Run the same report again - and the 'Export To Excel' , download (but do not open in EXCEL yet) the file.
Make copies of the two downloaded EXCEL files - rename the extension to '.htm' or '.txt'. - so we can compare them.
Addtionally: if you are using Private Checkout - you will need to make sure that you LAUNCH the user Portal from the Designer Studio - rather than logging in another session - otherwise the Privately Checked Out Rule will not be picked up.
Thanks,
John
Hi Gary,
Let's first establish that the correct RULE is being picked up here; I would do two things:
#1 RUN A TRACER:
A. Run a TRACER (with 'Stream Rules' enabled) - pause it.
B. Get to the point where you have run a Report (make sure it is not a Chart or an Aggregate Report at this point - as these may use different rules).
C. Unpause Tracer: click on the 'Export To Excel' button - and download the file - but do NOT open it in EXCEL yet.
Within the TRACER - we should be able to identify the HTML rule (or similar-type of Rule) that is being used to put the EXCEL document together.
#2 ADD LOGGING TO THE MODIFIED RULE.
Locate the Rule that is being used - and add in the changes as well as a 'oLog.infoForced("**** <name of rule> CALLED ***"); somewhere near the top.
Run the same report again - and the 'Export To Excel' , download (but do not open in EXCEL yet) the file.
Make copies of the two downloaded EXCEL files - rename the extension to '.htm' or '.txt'. - so we can compare them.
Addtionally: if you are using Private Checkout - you will need to make sure that you LAUNCH the user Portal from the Designer Studio - rather than logging in another session - otherwise the Privately Checked Out Rule will not be picked up.
Thanks,
John
NOTE: the reason not to open the file up in EXCEL before saving a copy : EXCEL will trigger an automatic conversion to take place, and we'll not be able to perform a 'save-as' of the original format once this has happened.