We had a client that wanted to export their CA Plex OBASE reports as Microsoft Excel spreadsheets. There are several ways to do this, but the best option is to build a pattern that generates a Microsoft Excel XML file using Meta code that iterates over the fields on the report headers and detail formats to produce the column headings and data rows. Our goal was to enable new functionality with the least requirement for final implementation and to develop a contained solution with no changes to existing patterns.
We began by creating a new function option — FNC option NME/ExcelXML Generate Option — which was used to determine if Excel XML should be generated or not with the default being not to generate it. We created a new meta function to perform the processing — FNC – ExcelXML Function Options. Then the following initialization steps occur:
- Excel XML file created and opened on the IFS
- Excel XML header written to the IFS file for Workbook and Worksheet
- Meta function is called to iterate over each header and detail format field to write column heading row to IFS file
- If the format is being printed, the following code is used to invoke the meta function to build row data:
- +For Variable <Format Variable>
- Call Meta Function “ExcelXML Build Column Headings”
- +For Variable <Format Variable>
- Column heading comes from:
- FLD left label LBL, FLD top label LBL, or Field Object Name
Finally, you need to format, print and distribute the result. The meta-function called to iterate over each header and detail format field to write data row to IFS file is FNC – ExcelXML Build Format Data. If you wish to print the format, use the following code to invoke the meta function to build row data:
- +For Variable <Format Variable>
- Call Meta Function “ExcelXML Build Format Data”
- FLD edited by LBL used to format data for display
Final steps include:
- Excel XML footer written to IFS file for Workbook and Worksheet
- Excel XML file closed on the IFS
- Excel XML IFS file distributed to user via email.
We’re planning to enhance this capability in the future to offer the following:
- Add options for formatting of specific columns, rows or even cells based on specific values
- Control Excel Layout Styles
- Format numeric or specialized columns
- Remove hidden report format fields from Excel XML
- Sequence of Excel columns to printed sequence and not triple sequence
- Implement Column Filtering
- Handling of special XML Characters (i.e. & = &)
- Generate Excel XML for panel based functions
This material was presented at the 9th CA 2E/CA Plex Worldwide Developer Conference. If you couldn’t attend, make plans to get to the next one.
By Lucio Gayosso
