Hi Jiri,

I have just thrown some ideas around here to get the conversation started - I have split the reply into two: (Export/Import).

Cheers

John

EXPORTING FROM PRPC TO EXCEL:

If you need to EXPORT multi-tab EXCEL files - then you will probably need to consider using one of the following mechanisms to generate your EXCEL files:

1. Use VBA / EXCEL Macros.

PRPC uses this mechanism in a few places - where VBA macros are used to perform heavy-lifting (such as fetching data from the server, or dynamically creating rows/colums etc).

[For instance 'Data-Tables' can be edited in EXCEL and saved-back to PRPC].

Downsides:

1. You will probably need to Digitally Sign the EXCEL Macro-enabled Template to avoid security issues.

2. VBA Macros may take some time to write and test - especially those that communicate back to PRPC.

2. Use a Java-based API to generate the document on the back-end.

This approach is used as well in PRPC - for instance in PRPC 7.1.8 - the 'docx4j' library (docx4j)  is used to generate WORD files - but this library also support other Office Formats - such as EXCEL.

So for 7.1.8 - you already have the 'docx4j' JARs available to you.

If you are on a version prior to 7.1.8 - you still have the Apache POI library available to you with OOTB PRPC as well: [ https://poi.apache.org/components/spreadsheet/examples.html ]

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%';

Downsides:

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.

There are XML formats that can be used directly within EXCEL (etc) - for instance the (probably deprecated - but still works) Microsoft Office XML Format (2003) : Microsoft Office XML formats - Wikipedia, the free encyclopedia

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).

WIth this XML format - I tend to use it with XSLT (XSLT - Wikipedia, the free encyclopedia) to convert my 'raw' XML file to EXCEL format.

So you could use the OOTB PRPC Activity "XSLTranslate | Pega-IntegrationEngine:07-10-01" to do this.

OR you might be able to use XML Stream Rules directly - with your property-references 'embedded' into the document itself.

So - here's a quick example of creating a three-tab EXCEL spreadsheet in 2003 XML format:

<?xml version="1.0" encoding="UTF-8"?>

<?mso-application progid="Excel.Sheet"?>

<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">

    <ss:Worksheet ss:Name="FIRST TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, First Tab</ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

    <ss:Worksheet ss:Name="SECOND TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, Second Tab </ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

    <ss:Worksheet ss:Name="THIRD TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, Third Tab </ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

</ss:Workbook>

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.

JAVA APIs.

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.......

XML FORMATS:

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 .......

SUMMARY:

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....)

Hi Jiri,

I have just thrown some ideas around here to get the conversation started - I have split the reply into two: (Export/Import).

Cheers

John

EXPORTING FROM PRPC TO EXCEL:

If you need to EXPORT multi-tab EXCEL files - then you will probably need to consider using one of the following mechanisms to generate your EXCEL files:

1. Use VBA / EXCEL Macros.

PRPC uses this mechanism in a few places - where VBA macros are used to perform heavy-lifting (such as fetching data from the server, or dynamically creating rows/colums etc).

[For instance 'Data-Tables' can be edited in EXCEL and saved-back to PRPC].

Downsides:

1. You will probably need to Digitally Sign the EXCEL Macro-enabled Template to avoid security issues.

2. VBA Macros may take some time to write and test - especially those that communicate back to PRPC.

2. Use a Java-based API to generate the document on the back-end.

This approach is used as well in PRPC - for instance in PRPC 7.1.8 - the 'docx4j' library (docx4j)  is used to generate WORD files - but this library also support other Office Formats - such as EXCEL.

So for 7.1.8 - you already have the 'docx4j' JARs available to you.

If you are on a version prior to 7.1.8 - you still have the Apache POI library available to you with OOTB PRPC as well: [ https://poi.apache.org/components/spreadsheet/examples.html ]

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%';

Downsides:

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.

There are XML formats that can be used directly within EXCEL (etc) - for instance the (probably deprecated - but still works) Microsoft Office XML Format (2003) : Microsoft Office XML formats - Wikipedia, the free encyclopedia

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).

WIth this XML format - I tend to use it with XSLT (XSLT - Wikipedia, the free encyclopedia) to convert my 'raw' XML file to EXCEL format.

So you could use the OOTB PRPC Activity "XSLTranslate | Pega-IntegrationEngine:07-10-01" to do this.

OR you might be able to use XML Stream Rules directly - with your property-references 'embedded' into the document itself.

So - here's a quick example of creating a three-tab EXCEL spreadsheet in 2003 XML format:

<?xml version="1.0" encoding="UTF-8"?>

<?mso-application progid="Excel.Sheet"?>

<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">

    <ss:Worksheet ss:Name="FIRST TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, First Tab</ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

    <ss:Worksheet ss:Name="SECOND TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, Second Tab </ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

    <ss:Worksheet ss:Name="THIRD TAB">

        <ss:Table>

            <ss:Row>

                <ss:Cell>

                    <ss:Data ss:Type="String">HELLO, Third Tab </ss:Data>

                </ss:Cell>

            </ss:Row>

        </ss:Table>

    </ss:Worksheet>

</ss:Workbook>

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.

JAVA APIs.

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.......

XML FORMATS:

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 .......

SUMMARY:

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....)