Export OBASE Reports as Microsoft Excel Spreadsheets
To meet the needs of the business and even IT staff, often, you need the ability to produce reports in a variety of formats. One client came to CM First requesting the ability to export OBASE reports to Excel. There are a few options, but the one that appeared to work best was to build a pattern that would generate a Microsoft Excel XML file using Meta code that would iterate over the fields on the report headers and detail formats to produce the column headings and data rows. It met the goals of enabling the new capability with the easiest implementation and without changing existing patterns.
The new pattern works with OBASE “Print key break report.” FNC option NME/ExcelXML Generate Option is used to determine if Excel XML should be generated with the default to not generate it. A new Meta function developed to perform this processing–FNC – ExcelXML Function Options.
Here are the function options:
+++Define Field: +Current Option
+++Define Field: OBASE/Function name
+++Set Value To Current Field: OBASE/Function name
+++Undefine Field: +ExcelXML Generate File
+++Define Name: ExcelXML Generate Option
+For Defined Value Field: OBASE/Function name
+For Each Property FNC option NME
+++Set Value To Current Field: +Current Option, .Target
+If EQ Field: +Current Option, Name: ExcelXML Generate Option
+If TRP value SYS, System: Yes
+++Define Field: +ExcelXML Generate File
An Excel XML file is created and opened on the IFS and the Excel XML header is written to IFS file for Workbook and Worksheet. A 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”
The column heading comes from: FLD left label LBL, FLD top label LBL, or Field Object Name
To build column headings:
+++Define Field: +Current Field
+For each field
+++Set Value To Current Field: +Current Field
++Name Defined Field: +Current Field, Environment<*Object name>
+++Undefine Label: ExcelXML Field Label
* Use left label, top label, or field name for column heading
Set ExcelXML Field Label Text<ExcelXML Field Label Text> = Blanks
+For each property target FLD left label LBL
+++Define Label: ExcelXML Field Label
+++Set Value To Current Label: ExcelXML Field Label
++Text Defined Label: ExcelXML Field Label, ExcelXML Field Label Text<ExcelXML Field Label Text>
+If Label: ExcelXML Field Label
+Else
+For each property target FLD top label LBL
+++Define Label: ExcelXML Field Label
+++Set Value To Current Label: ExcelXML Field Label
++Text Defined Field: Label: ExcelXML Field Label, ExcelXML Field Label Text<ExcelXML Field Label Text>
+If Label: ExcelXML Field Label
+Else
If ExcelXML Field Label Text<ExcelXML Field Label Text> IS <ExcelXML Field Label Text.Blanks>
+++Define Label: ExcelXML Field Label
+++Set Value To Current Label: ExcelXML Field Label
++Name Defined Label: ExcelXML Field Label, ExcelXML Field Label Text<ExcelXML Field Label Text>
* Add column heading cell using ExcelXML Field Label Text<ExcelXML Field Label Text>
Call “ExcelXML Add Cell” using ExcelXML Column Heading<ExcelXML Column Heading Data> for ExcelXML Data, ExcelXML Field Label Text<ExcelXML Field Label Text> for ExcelXML Cell Data, “String” for ExcelXML Cell Data Type, and ExcelXML<IFS File Handle>
If error occurred, then perform standard error processing
Set ExcelXML<ExcelXML Column Count> = ExcelXML<ExcelXML Column Count> + <ExcelXML Column Count.One>
A meta-function is called to iterate over each header and detail the format field to write data row to IFS file–FNC – ExcelXML Build Format Data
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 Format Data”
FLD edited by LBL is used to format data for display.
+++Define Field: +Current Field
+For each field
+++Set Value To Current +Current Field
+++Undefine Label: ExcelXML Field Label
* Use edited by label for masking the data
Set ExcelXML Field Label Text<ExcelXML Field Label Text> = Blanks
+For each property target FLD edited by LBL
+++Define Label: ExcelXML Field Label
+++Set Value To Current Label: ExcelXML Field Label
++Text Defined Label: ExcelXML Field Label, ExcelXML Field Label Text<ExcelXML Field Label Text>
* Add column data using ExcelXML<Field-Data> – numerics will be converted to text!
++Cast To ExcelXML Field<ExcelXML Field Data>
* Call the function to transform the field data using the formatting label if required
+If Label: ExcelXML Field Label
Call “ExcelXML Format Field” with ExcelXML Field<ExcelXML Field Data>, and ExcelXML Field Label Text<ExcelXML Field Label Text>
If error occurred, then perform standard error processing
* Add column heading cell using ExcelXML Field<ExcelXML Field Data>
Call “ExcelXML Add Cell” using ExcelXML Format<ExcelXML Format Data> for ExcelXML Data, ExcelXML Field<ExcelXML Field Data> for ExcelXML Cell Data, and “String” for ExcelXML Cell Data Type, and ExcelXML<IFS File Handle>
If error occurs, then perform standard error processing
The Excel XML footer is written to IFS file for Workbook and Worksheet and the Excel XML file is closed on the IFS. The Excel XML IFS file distributed to user via email.
It’s easy to implement. Just enter: FNC option NME/ExcelXML Generate Option
…TRP value Sys/Yes
Generation includes the necessary code to gen XML output.
Enhancements are planned to include more desired features. Check it out – this might be the solution you’re looking for.
Lucio Gayosso, MIS/M, PSM I, Sr. Consultant/Nearshore Team Lead
