Posted: 7 May 2015 2:45 EDT Last activity: 17 Jul 2017 16:27 EDT
How far can we go with Excel file styling?
Hope things go well!
I have a question that is based on a requirement in of our projects in Germany. A telco company is looking for export to excel feature as well as importing this file back to populate our grids. As we have a huge amount of columns, we grouped certain columns in categories and used tabbed layout to not have a horizontal scrollbar. Each tab would represent a different category.
It seems to be pretty valid point that power users really want to export everything and do it in one huge spreadsheet in Excel.
The question is: How far we can get with the Excel styling? What are our options?
I have never done this and couldn't find anyone here who had. Any response is much appreciated!
***Updated by moderator: Marissa 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.
I am not an expert in this area, however I believe accumulating data from multiple tabs and then generating them as part of a single excel is possible .
You can check out the of the box activity RULE-OBJ-HTML.ViewExportToExcel . The main task it does is building a table from the query results and send it to MS Excel
I think that if you can build a query ,which can fetch all the data from all tabs, then rest can be easily taken care . To get an idea how you can get the data for excel , you can refer another out of the box activity : Rule-Obj-HTML. CreateExportData.
Hope this gives you some pointer to start with !
Posted: 5 years ago
Updated: 5 years ago
Posted: 22 Jun 2015 10:07 EDT Updated: 22 Jun 2015 12:35 EDT
BTW: you can check which JARs/versions are shipped OOTB with PRPC usually by running a SQL query such as:
SELECT * FROM <schema>.PR_ENGINECLASSES
WHERE UPPER(pzjar) like 'POI%';
1. You will probably end up writing a lot-of "Java Steps" within your activities - we recommend you minimize the number Java Steps in Activities whereever possible.
You *might* be able to use (or at least use an example) some OOTB PRPC Activities here : for instance see "MSOGenerateExcelFile" - which is designed for use by OOTB PRPC Functionality - but you maybe able to tailor it to your own needs here ?
3. Use an XML-based Format - convert either on the backend or on the Front-End.
I'm not sure if there are any mechanism already present in PRPC that use this method - but I happen to use it quite a bit outside of PRPC and find it works quite well.
It supports multi-tabs, formatting and formulas - but not charts: so it is a more limited format than the later 'Open Office' Formats that EXCEL now tends to prefer - but it has the advantage of being only a single file format (whereas Open Office is a ZIP file of many different files in fact).
<ss:Data ss:Type="String">HELLO, First Tab</ss:Data>
<ss:Worksheet ss:Name="SECOND TAB">
<ss:Data ss:Type="String">HELLO, Second Tab </ss:Data>
<ss:Worksheet ss:Name="THIRD TAB">
<ss:Data ss:Type="String">HELLO, Third Tab </ss:Data>
Which loads into EXCEL like this:
IMPORTING EXCEL FILES INTO PRPC:
VBA Macros again:
PRPC does this in various places - it tends to use VBA macros - using a specifcally built TEMPLATE files - you have little (or possibly no) control over the formatting/ordering of TABS etc.
PRPC uses signed code to do this.
Apache POI is able to read as well as write EXCEL files (note it has two main categories of file-formats - which use different top-level Java Objects) : HSFF, XSFF - so unless you have control over exactly which formats are in-use , you may have to implement both.
I believe this is used in the OOTB Activity "MSOParseExcelFile | Pega-AppDefinition:07-10-09" -note this ACTIVITY was built as part of some of the OOTB PRPC functionality - but some customers have used it for their customizations (acually - it's a FINAL rule - so they have to make a copy) - this might be enough to get you started - without having to code too many Java Steps.......
In theory - if you have control over the input format of the EXCEL documents - then you could use the EXCEL 2003 single-page XML format here - as the input would simply be XML. However - in practice - it may be burden to expect users to remember to save in that particular format - and you would end-up with many unprocessable inputs I would guess .......
For EXPORT - In my opinion - use either the JAVA APIs (or better: see if you can extend/copy/learn-from/re-use OOTB PRPC Activities) or the XML-based approach for EXPORT.
For INPUT - in my opinion - the only sensible approach (depending on how much control you have over the input documents) - is to use the JAVA APIs (POI, DOCX4j) here.
Note: also check the PRPC help regarding DATA TABLES : this already has a built-in mechanism for allowing users to edit data using EXCEL (it uses the VBA-mechanism) - this might already fit many of the requirements. (Although I fear that styling and mulit-tabs are not going to be configurable here....)