Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
engswee
Active Contributor

Update 5 Nov 2019: Add new parameter trim to trim XML text nodes

Update 2 Nov 2018: Default parsing in CPI will be using SAX-based XmlSlurper

Update 2 Oct 2018: Now available in CPI as well.

Update 25 Mar 2015: ExcelTransformBean has been refactored to be part of FormatConversionBean. Parameter conversionType replaced with converterClass.

Introduction

This is a continuation of the ExcelTransformBean adapter module. The first part ExcelTransformBean Part 1: Convert various Excel formats to simple XML easily covers Excel to simple XML conversion.

This second part focuses on simple XML to Excel conversion. Similar to the first part, this uses the combined SS interface of Apache POI's API, therefore it is able to write both XLS and XLSX files.

Source Code

Refer to following blog on location of source code and/or EAR deployment file.

FormatConversionBean - One Bean to rule them all!

Usage of Module in Communication Channel

Module Processing Sequence


















Number Module Name Type Module Key


<Depending on position of module in chain>

1) Asynchronous scenario,

Normally before the last module in channels

2) Synchronous scenario,

Before last module to convert request payload

After last module to convert response payload
Custom_AF_Modules/FormatConversionBean Local Enterprise Bean <Any Arbitrary Value>


Module Parameter Reference

Below is a list of the parameters for configuration of the module for XML to Excel conversion. Certain parameters will automatically inherit the default values if it is not configured.

Note: All fields in the simple XML structure must exists. If any field does not exist, this will cause the alignment of the columns to be incorrect. This is similar to the behavior of FCC or MTB's SimpleXML2Plain conversion. To ensure proper column alignments, use MapWithDefault when mapping the fields of the target structure.






















































Parameter Name Allowed values Default value Remarks
converterClass PI - com.equalize.xpi.af.modules.excel.XML2ExcelTransformer
CPI - com.equalize.converter.core.XML2ExcelConverter
Required field. Determines conversion class
excelFormat xls, xlsx xlsx Determines output file format
sheetName Sheet1 Name of sheet in output Excel file
addHeaderLine none, fromXML, fromConfiguration none Determines if there will be a header line in the output file, and how it should be populated:

  • none = No header line

  • fromXML = Column names are determined from XML field names

  • fromConfiguration = Column names are determined from parameter fieldNames


fieldNames Name of columns. Required field when processFieldNames = 'fromConfiguration'
useDOM Y, N N

Available only in CPI

Parser used in parsing input XML

  • N = SAX-based XmlSlurper

  • Y = Document Object Model (DOM) based parser


trim Y, N N

Available only in CPI

Trim whitespace in XML text nodes during parsing


Example Scenarios

Here are some example scenarios of the behavior of the conversion based on different configuration options.

Scenario 1

Default values for all optional parameters.

Excel 2007 XSLX file format with default Sheet1.

No header line.

Module parameters














Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.XML2ExcelTransformer


Result












Input
Output


Scenario 2

Excel binary XLS file format.

Active sheet name provided.

Header line added with details from input XML fields.

Escape sequence &amp; automatically converted to special character &.

Module parameters


























Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.XML2ExcelTransformer
excelFormat xls
sheetName MySheet1
addHeaderLine fromXML


Result












Input
Output


Scenario 3

Excel 2007 XSLX file format with default Sheet1.

Header line added. Column names are provided from configuration.

Certain fields are populated with blank values.

Module parameters






















Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.XML2ExcelTransformer
addHeaderLine fromConfiguration
fieldNames Field1,Date,Field2,Quantity


Result












Input
Output


16 Comments
Labels in this area