Question
Prime Therapeutics
US
Last activity: 29 Oct 2015 8:54 EDT
Bestway to send Excel-sheet as downloadable link to external-system(Angular JS)
What are the best options to send a excel attachment as a downloadable link to external system(angular JS) from Pega7.?
scenario example:
Anugular JS has a Export2Excel Icon, when the user click on the export button, excel file needs to download and data needs to be send from pega.Currently i am sending one report results via service-REST as JSON response, AnjularJS showing these report results on that portalUI but not able to generate excel file.Then when user click on that export2excel icon same results need send as excel downloadable file.
Please let me know your thoughts to build this scenario.
Thanks
M.K
Pegasystems Inc.
GB
Hi Mohan,
So if I understand the requirement here ..... you have a (separate?) Web App which is built on the AngularJS framework.
You have implemented a (back-end?) call from your Web App which initiates a HTTP (REST) call to PRPC: PRPC responds with JSON data.
You are able to display/format this JSON data on your Web App with no issue.
Instead of (or I suspect: as well as) displaying the data on the Web App : you need to convert JSON to an EXCEL format.....
PRPC has several ways of creating data suitable for EXCEL : the simplest (used in the 'Export To Excel' button on PRPC Reports) is to provide HTML; but it is possible to generate native binary EXCEL formats in PRPC (using the Apache POI library) and also it is fairly easy to generate XML-based EXCEL formats using XSLT (or other XML transformation techniques).
Do you need JSON *and* an EXCEL format to be fetched from PRPC ? Or do you need a way of transforming JSON to an EXCEL format ?
Thanks,
John
I'm not quite sure of your setup here....but I can think of two basic variations here; can you let us know which (if any) fit your setup ?
Hi Mohan,
So if I understand the requirement here ..... you have a (separate?) Web App which is built on the AngularJS framework.
You have implemented a (back-end?) call from your Web App which initiates a HTTP (REST) call to PRPC: PRPC responds with JSON data.
You are able to display/format this JSON data on your Web App with no issue.
Instead of (or I suspect: as well as) displaying the data on the Web App : you need to convert JSON to an EXCEL format.....
PRPC has several ways of creating data suitable for EXCEL : the simplest (used in the 'Export To Excel' button on PRPC Reports) is to provide HTML; but it is possible to generate native binary EXCEL formats in PRPC (using the Apache POI library) and also it is fairly easy to generate XML-based EXCEL formats using XSLT (or other XML transformation techniques).
Do you need JSON *and* an EXCEL format to be fetched from PRPC ? Or do you need a way of transforming JSON to an EXCEL format ?
Thanks,
John
I'm not quite sure of your setup here....but I can think of two basic variations here; can you let us know which (if any) fit your setup ?
1. Using PRPC as a BACKEND for the Web App - the Browser talks ONLY to the Web App - not PRPC
OR:
2. 'Mashing-up' Data from PRPC and the Web App - where the browser is fetching data from both PRPC and the Web App : possibly the Web App is also contacting PRPC for content as well ?
Pegasystems Inc.
GB
For the purposes of demonstration about the 'simplest' EXCEL format (which is actually HTML); you can do this by hand.
Create a simple HTML page, which contains a single TABLE like this:
<html> <table border="1"> <tr> <th>Agent Name</th> <th>Agent Number</th> </tr> <tr> <td> James Bond </td> <td> 007 </td> </tr> </table> </html>
Now here's the trick: re-save this with the extension '.xls'.
You can now double-click the file to launch in Excel - it will complain (because 'xls' doesn't actually reflect the right format, but whatever) - it will say:
"The file format and extension of 'table.xls' don't match. The file could be corrupted or unsafe. Unless you trust it's source, don't open it. Do you want to open it anyway ?"
But if you click 'Yes', it will open and display it in Excel:
For the purposes of demonstration about the 'simplest' EXCEL format (which is actually HTML); you can do this by hand.
Create a simple HTML page, which contains a single TABLE like this:
<html> <table border="1"> <tr> <th>Agent Name</th> <th>Agent Number</th> </tr> <tr> <td> James Bond </td> <td> 007 </td> </tr> </table> </html>
Now here's the trick: re-save this with the extension '.xls'.
You can now double-click the file to launch in Excel - it will complain (because 'xls' doesn't actually reflect the right format, but whatever) - it will say:
"The file format and extension of 'table.xls' don't match. The file could be corrupted or unsafe. Unless you trust it's source, don't open it. Do you want to open it anyway ?"
But if you click 'Yes', it will open and display it in Excel:
The OOTB 'export-to-excel' in PRPC's reporting mechanism actually does some fancier formatting of data formats and styles than shown here - but it's the same principle.
So you *might* be able to get PRPC to build you two 'views' of your data : one in JSON (that is already working), and one (using the same input data, but just formatting differently) in HTML (EXCEL) format here - dunno.....
Let us know your thoughts....
Capgemini
US
Thank you John for your detailed response.
I think you understood the scenario correctly.
I need to send report results as a Excel Binary string to AnjularJS via Service. Is it possible to do it with Service-Rest?( as a http Downloadable link).
Pegasystems Inc.
GB
I don't have the solution working here - but I'm going to post what I tried so far - somebody else may be able to get this approach working (or suggest another one).
I'm trying to use a PRPC 'HTTP Service' here: check your prpc help for http://host:port/prhelp/rule-/rule-service-/rule-service-http/main.htm for more info; but the key thing is that help says that this type of rule offers:
A low-overhead stateless alternative to SOAP, Service HTTP rules can implement services in a way that is consistent with REST (Representational State Transfer).
So first of all I create an Activity which can be run directly from the Designer Studio and it successfully delivers an EXCEL file to the client:
I know you requested a 'binary' Excel file here - but for our tests it doesn't really matter what the underlying format is: I have opted for the simple HTML style for simplicity.
The file contents for the test is just a variation of what I posted previously (but fixed the problem where '007' would lose it's zeros...):
I don't have the solution working here - but I'm going to post what I tried so far - somebody else may be able to get this approach working (or suggest another one).
I'm trying to use a PRPC 'HTTP Service' here: check your prpc help for http://host:port/prhelp/rule-/rule-service-/rule-service-http/main.htm for more info; but the key thing is that help says that this type of rule offers:
A low-overhead stateless alternative to SOAP, Service HTTP rules can implement services in a way that is consistent with REST (Representational State Transfer).
So first of all I create an Activity which can be run directly from the Designer Studio and it successfully delivers an EXCEL file to the client:
I know you requested a 'binary' Excel file here - but for our tests it doesn't really matter what the underlying format is: I have opted for the simple HTML style for simplicity.
The file contents for the test is just a variation of what I posted previously (but fixed the problem where '007' would lose it's zeros...):
<html>
<head>
<style type="text/css">
td, th { border-style : solid }
.excelText { vnd.ms-excel.numberformat:@; }
</style>
</head>
<table>
<tr>
<th>Agent Name</th>
<th>Agent Number</th>
</tr>
<tr>
<td class="excelText">James Bond</td>
<td class="excelText">007</td>
</tr>
</table>
</html>
Which is stored in a HTML rule like this:
Then we have an Activity that performs a 'Show-Stream' on this rule - but before doing this, we have to set up HTTP headers to ensure the correct MIME type and also (in the case of accessing this from a BROWSER at least) sets the 'disposition' to 'attachment':
The 'ContextPage' is simply set to the same class as the where the ACTIVITY and the HTML file live:
This works from the Designer Studio:
But how do we access this directly over a single URL ?
This is what I tried:
I used the "Designer Studio > Integration > Services > Service Wizard" menu item from the Designer Studio:
And then selected the options:
Service Purpose= Invoke existing activity rules Service Type = HTTP
Then chose my Activity Class:
Then chose my Activity:
Since in my case there are no parameters, I have skipped the non-interesting screenshots here....
Then I selected 'Configure a new service package':
Then on the review screen, switched OFF 'Requires Authentication?' and 'Suppress Show-HTML?'.
Switching off Authentication just makes testing easier: but it's not a secure thing to do (unless you have other systems in place to prevent people accessing your service) - so you will need to keep this switched on when doing this for real.
Then we create the rules involved.
Now (again, for Production Use, you wouldn't want to do this step) - we have to configure the Activity to allow it to run without Authentication:
Now to test this: we can string together a URL (this is documented in the help for HTTP Services) on the 'more' Tab) to call this HTTP Service, which looks like this for my example:
http://prpchost:7170/prweb/PRHTTPService/GCSGCSAppliWork/Services/DownloadExcel
So , I can now test this - I'm using 'wget' on a Linux client here for simplicity - you could use 'curl' or a Python script or whatever (or just a browser for that matter) instead.
This is where I get stuck - the Activity is called, but the content length is zero. There is nothing in the file, nadda, zilch.
--2015-10-08 15:53:53-- http://prpchost:7170/prweb/PRHTTPService/GCSGCSAppliWork/Services/DownloadExcel
Resolving prpchost... xx.xx.xx.xx
Connecting to prpchost|xx.xx.xx.xx|:7170... connected.
HTTP request sent, awaiting response... 200 OK
Length: 0 [text/plain]
Saving to: `DownloadExcel.10'
0K 0.00 =0s
2015-10-08 15:53:53 (0.00 B/s) - `DownloadExcel.10' saved [0/0]
mmmh.... what is going on here - any why the content type set to 'text/plain' ?
I check my Service Rule 'Response' Tab and see this:
So my 'content type' is wrong here - I can change this to something more suitable : "application/vnd.ms-excel":
Save it, and try again:
--2015-10-08 15:58:17-- http://prpchost:7170/prweb/PRHTTPService/GCSGCSAppliWork/Services/DownloadExcel
Resolving prpchost... xx.xx.xx.xx
Connecting to prpchost|xx.xx.xx.xx|:7170... connected.
HTTP request sent, awaiting response... 200 OK
Length: 0 [application/vnd.ms-excel]
Saving to: `DownloadExcel.12'
0K 0.00 =0s
2015-10-08 15:58:17 (0.00 B/s) - `DownloadExcel.12' saved [0/0]
So we fixed the content-type - but the size of the download is still zero bytes ?
Also: didn't we already fix the Content-Type in the Activity itself ? (That Java step)..
So this is where I am stuck - the Activity runs (I checked this by using the 'Trace Open Rule' feature on the Service rule), but zero-byte file comes back to the client....
Lets see if somebody else can suggest a fix for this (or a totally different way of doing this?).
Cheers
John
Perficient
US
Rather than use Show-Stream, I would recommend using Property-Set-Stream to generate the Excel contents, then use the sendFile() method from the Public API to send the content to the browser. You can set your Content Type header as part of the aHTTPHeaders parameter in sendFile(). See Code-Pega-PDF.View for an example of how this works.