Update 2 Oct 2018: Now available in CPI as well.
Update 10 May 2016: Add new optional parameters headerRow and onlyValidCharsInXMLName (courtesy of jacob.vandborg5).
Update 7 Aug 2015: Add new parameter columnOffset.
Update 25 Mar 2015: ExcelTransformBean has been refactored to be part of FormatConversionBean. Parameter conversionType replaced with converterClass. Parameter indentXML replaced by indentFactor.
Update 11 Feb 2015: Updated source code links to shared module repository on GitHub. Previous GitHub repository will be deleted.
Update 27 Jan 2015: Updated Source code section to indicate EAR deployment file released for download at GitHub repository.
Update 3 Nov 2014: Refactoring of source code to cater for SimpleExcel2XML and SimpleXML2Excel conversions.
Introduction
There are already a handful of blogs on SCN dealing with Excel to XML conversion - so why another one??
Here is my wish list for a comprehensive solution:-
- Able to read all kinds of Excel format (XLS and XLSX)
- Behaves in a similar way as MessageTransformBean
- Highly configurable - develop/deploy once, use multiple times
- Able to handle XML special characters
In the reference section below are some of the more popular approaches, however below are some of the (non-exhaustive) limitations/drawbacks
- XLSX files stores string contents in a separate sharedStrings.xml file in the zipped XLSX file
- JExcel API does not support Excel 2007 XLSX formats
- Limitations in handling formulas, formatting and special characters in cells
ExcelTransformBean (ETB) aims to provide a generic adapter module solution (a la MessageTransformBean) that is highly configurable and reusable. It is based on the
Apache POI API. Utilizing the combined SS interface of the API, it uses a single logic to read all kinds of Excel files (XLS and XLSX).
This first part covers Excel to simple XML conversion, while the
second part covers simple XML to Excel conversion.
Source Code
Refer to following blog on location of source code and/or EAR deployment file.
FormatConversionBean - One Bean to rule them all!
This converter is based on Apache POI 3.9 library. In order for the Java project to compile and build successfully, the following JAR files need to be referenced/imported into the project.
- poi-3.9-20121203.jar
- poi-ooxml-3.9-20121203.jar
- poi-ooxml-schemas-3.9-20121203.jar
- xmlbeans-2.3.0.jar
- dom4j-1.6.1.jar
The library files can be downloaded from Apache's website, direct link to the ZIP file is provided below.
Apache POI 3.9 ZIP file
Note: CPI version has been updated to Apache POI 3.17 - refer to
FormatConversionBean arrives in CPI for details on library dependencies.
Usage of Module in Communication Channel
Module Processing Sequence
Module Parameter Reference
Below is a list of the parameters for configuration of the module for Excel to XML conversion. Certain parameters will automatically inherit the default values if it is not configured.
Example Scenarios
Here are some example scenarios of the behavior of the conversion based on different configuration options.
Scenario 1
Excel 2007 XSLX file format.
Extract Sheet1 with column names determined directly from header line of file.
Special character & automatically converted
Module parameters
Result
Scenario 2
Excel binary XLS file format.
Extract sheet at index 0. Column names are provided from configuration.
Row offset provided to skip first two lines.
No formatting of cells, so raw values displayed.
Module parameters
Result
Scenario 3
Excel 2007 XSLX file format.
Extract sheet at index 0.
Column names are not available. Number of columns = 5.
Row offset provided to skip first line.
Empty rows are included.
Cells with formula are displayed with formula instead of result.
Empty cells are displayed with default value "space".
Module parameters
Result
Reference
Part 2 -
ExcelTransformBean Part 2: Convert simple XML to various Excel formats easily
This article does not cover the steps for creating a custom adapter module. This can be found easily via SCN search. It is also listed in the reference section of the article below regarding adapter module testing.
Standalone testing of Adapter Module in NWDS
Alternative methods for Excel conversion
PI/XI: Reading MS Excel's XLSX and XLSM files with standard PI modules - easily...
Excel Files - How to handle them in SAP XI/PI (The Alternatives)
A Simple approach in Reading Excel File