HTML Export to Excel and OOTB activity
Using Pega 6.2 sp 2
I have a report that's created using an activity. Also, i have created the HTML (HTML Stream) in this activity. i would like to export this HTML as an excel spreadsheet, onto the users local computer in directory, C:\PegaReport. I don't want the user to have to Save the file manually or even know the file is being created into this directory. They should be able to run the report, look in the directory, and see the Excel version of the report after they've run the report.
I've looked at ExportToExcel (OOTB activity). However, i don't see anyway of telling ExportToExcel what directory to put the excel report into.? I've looked at MSOGenerateExcelFile, but i'm not sure how to geneate the excel template needed for this OOTB activity.
I'd prefer to use the ExportToExcel activity, but i'm open to creating the excel file anyway i can.
Any help is appreciated.
below is the HTML that gets created by the activity. This is what i'd like to use to create the excel file.
<HTML>
<H2><B><FONT COLOR=GREEN>Quarterly Activity Report</FONT></B></H2>
<H3><FONT COLOR=GREEN>Report Date: 15-Oct-15<br> Client Name: TEST CLIENT<br> Client ID: TESTID AP<br> Reporting Period: 05-Oct-14 to 15-Oct-15</FONT> </H3>
<TABLE cellspacing='0' cellpadding='1' border='1' style='border-collapse: collapse;'>
<TR>
<TD colspan="2" align="center" bgColor='#dcdcdc' class='excelText'>PROFILE STATISTICS</TD>
<TR>
<TD bgColor= class='excelText'>Profiles Reviewed</TD>
<TD bgColor= class='excelText'>24</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>Profiles Validated</TD>
<TD bgColor= class='excelText'>8</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>% of Profiles Validated</TD>
<TD bgColor= class='excelText'>33.00%</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>Total Prescriber with Interventions</TD>
<TD bgColor= class='excelText'>11</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>Total Feedback Responses by Prescribers</TD>
<TD bgColor= class='excelText'>9</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>Prescriber Response Rate</TD>
<TD bgColor= class='excelText'>82.00%</TD>
</TR>
<TR>
<TD bgColor= class='excelText'>Case Response Rate</TD>
<TD bgColor= class='excelText'>100.00%</TD>
</TR>
</TR>
</TABLE>
<br/><br/>
<TABLE cellspacing='0' cellpadding='1' border='1' style='border-collapse: collapse;'>
<TR>
<TD colspan="1" align="left" bgColor='#dcdcdc' class='excelText'>FEEDBACK RESPONSE</TD>
<TD colspan="1" align="left" bgColor='#dcdcdc' class='excelText'>Total Number of Prescriber Feedback Responses</TD>
<TD colspan="1" align="left" bgColor='#dcdcdc' class='excelText'>% Prescriber Feedback Responses</TD>
<TD colspan="1" align="left" bgColor='#dcdcdc' class='excelText'>Total Number of Case Feedback Responses (Medical Necessity Confirmed)</TD>
<TD colspan="1" align="left" bgColor='#dcdcdc' class='excelText'>% of Cases with Medical Necessity Confirmed</TD>
</TR>
<TR>
<TD colspan="1" align="left" bgColor= class='excelText'>Phone</TD>
<TD bgColor= class='excelText'>2</TD>
<TD bgColor= class='excelText'>22.00%</TD>
<TD bgColor= class='excelText'>0</TD>
<TD bgColor= class='excelText'>0%</TD>
</TR>
<TR>
<TD colspan="1" align="left" bgColor= class='excelText'>Feedback Response Form</TD>
<TD bgColor= class='excelText'>7</TD>
<TD bgColor= class='excelText'>78.00%</TD>
<TD bgColor= class='excelText'>0</TD>
<TD bgColor= class='excelText'>0%</TD>
</TR>
</TABLE>
<br/><br/>
<TABLE cellspacing='0' cellpadding='1' border='1' style='border-collapse: collapse;'>
<TR>
<TD colspan="2" align="left" bgColor='#dcdcdc' class='excelText'>POINT OF SERVICE EDITS</TD>
</TR>
<TR>
<TD colspan="1" align="left" bgColor= class='excelText'># Implemented</TD>
<TD bgColor= class='excelText'>0</TD>
</TR>
<TR>
<TD colspan="1" align="left" bgColor= class='excelText'># Pending</TD>
<TD bgColor= class='excelText'>6</TD>
</TR>
</TABLE>
<br/><br/>
<TABLE cellspacing='0' cellpadding='1' border='1' style='border-collapse: collapse;'>
<TR>
<TD colspan="2" align="left" bgColor='#dcdcdc' class='excelText'>SPECIAL INVESTIGATION UNIT REFERRALS</TD>
</TR>
<TR>
<TD colspan="1" align="left" bgColor= class='excelText'># Referred for fradulent activity</TD>
<TD bgColor= class='excelText'>0</TD>
</TR>
</TABLE>
<br/><br/>
</HTML>
Pegasystems Inc.
US
Gary,
Sorry but what you want to do is not possible:
" I don't want the user to have to Save the file manually or even know the file is being created into this directory."
Remember you are working in a browser, allowing a browser to do what you are asking would be a huge security issue. There are certain limitations impossed in a browser environment that Pega has no control over.
Sincerely,
Cecil Howell
Gary,
Sorry but what you want to do is not possible:
" I don't want the user to have to Save the file manually or even know the file is being created into this directory."
Remember you are working in a browser, allowing a browser to do what you are asking would be a huge security issue. There are certain limitations impossed in a browser environment that Pega has no control over.
Sincerely,
Cecil Howell
CSA | Senior Instructor | Pegasystems Inc.
Catamaran/Optum
US
i need to create the report without the user intervention. I'll create an activty that will create the HTML, as above. I'll have the report saved on the app server. How do i get the HTML to be saved as an excel file? ( on the app server).
Pegasystems Inc.
US
Gary,
ExporttoExcel activity simply streams the input data back to the client. You can see this if you open the Activity and examine the java (which I have pasted below). You may be able to do what you want with an activity but perhaps there is a better way (like schedule a report). Perhaps I can help you better if you let me know what you are trying to do beyond create an excel spreadsheet; ie why do you want to create a spreadsheet, what triggers it, what are you going to do with it...
Cecil
String strFileData = getParameterValue("pyFileData");//getPrimaryPage().getValue("pySrc");
Page objHeadersPage = getProperty("pxRequestor.pyHTTPResponseHeaders").getAsPage();
if (objHeadersPage.isBound())
{
objHeadersPage.setValue("contentType" ,"application/vnd.ms-excel");
sendData("", strFileData);
}
else
{
sendData("","Error. Can't get \"pxRequestor.pyHTTPResponseHeaders\" page");
}
Catamaran/Optum
US
Our current application has 50+ clients and is growing. Today, the user runs a report(manually via our application) for each client and then saves the report as an excel spreadsheet to their local drive. Doing this 50+ times takes too much time for the user.
We'd like to run the 50+ clients in batch and create the report (excel spreadsheet). The user can then just pick up the reports the next day from a network drive.
The logic creating the report is via an activity. The HTML for 1 client is listed above. i have a driver activity that reads the clients and submits each client to the report activity. However, creating the excel report has been the issue.
Thanks for the help and information.
Optus
AU
Hi,
If the results are getting through a report, you can follow the below procedure to save the data excel into a predefined server path.
1. Use "@baseclass.pxCallRetrieveReportData" activity by passing your report name, report class and a temp page to get the results on the supplied temp page.
2. Use "PegaAccel-Task-DocumentApp.pzMSOGenerateExcelAndSaveToWorkFileAttachment" to generate the stream. You should save the excel template to binary file rule and need to pass to this activity. (See referencing rules for this activity to get more information on template and binary file rule).
3. Once the stream is genereated use a Connet-file rule to save this to a server path.
Catamaran/Optum
US
step 1. i'm not using a report definition. the report is created using an activity due to the fact that it retrieves data and counts cases based on status,date and other factors. I have the report values in an embedded page. For example. ReportData.A1, ReportData.B1 etc. i can get them as html, however based on your steps above that's not needed.
step 2 I'm using pega 6.2 sp2. when i search for .pzMSOGenerateExcelAndSaveToWorkFileAttachment i get nothing returned. i do see MSOGenerateExcelFile. i've tried to research how to get the excel template into pega, but have not had much luck. is there any documentation on setting up the template in excel. For example, if the page property is ReportData.A1 what's the sytax that i'd enter in the excel spreadsheet cell. Any documentation on this would be appreciated.
step 3 i understand this.
Catamaran/Optum
US
i found some samples of excel templates with extension xlsx in Pega-AppDefinition ruleset. The format for the template is {pyWorkPage.pyCustomerName input}. I'm still curious if there's any documentation on this? i'm still unsure of how to use MSOGenerateExcelFile. also, i can't find anything related to .pzMSOGenerateExcelAndSaveToWorkFileAttachment
Catamaran/Optum
US
Using v6.2 sp2
i have MSOGenerateExcelFile working. However, i need to take the generated file and use the Connect-File to move it to a directory. How do i grab the generated file from MSOGenerateExcelFile? What if i UNCHECK the download checkbox? The java code for the checkbox is
if (tools.getParamAsBoolean(PropertyInfo.TYPE_TRUEFALSE, "DownloadFile")) {
pega_rules_default.downloadFile(xlFile.toString(), tools, true);
}
How do i get this xlFile passed to my Connect-File, which is the next step in my activity?
Pegasystems Inc.
US
Gary,
Filename parameter can be a full path and filename instead of just the filename, that should help.
Sincerely,
Cecil Howell
CSA | Senior Instructor | Pegasystems Inc.
chase
US
guys , i want to do the validation of the uploaded excel , lets say my excel template has 10 column. if user upload a wrong excel with 20 column or same 10 column with different column name - then i need to throe an error . pls advice how to achive this