ExcelTransformBean Part 2: Convert simple XML to various Excel formats easily

[engswee]

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

[Former Member]

Hi Eng,

Its nice blog, I think, I may need to use it in one of my requirements.

I am planning to practice this blog for my future requirement.

I do not have much idea on Java, new to it.

I have installed NWDS and collected the required jar files for Adapter Module Development.

My SAP PI System version is 7.1, can you please mentor me on building it manually and compile it for SAP PI 7.1.

Thanks,Akhila

[engswee]

Hi Akhila

Thank you for your interest in this module.

There are plenty of resources on SCN on how to develop/build/compile the adapter module. You can find some of these in the Reference section of the following blog.

Standalone testing of Adapter Module in NWDS

Once you have created the EAR/EJB projects in NWDS, you can download the source code and reference libraries and add them to the project before building and deploying the module.

I'll try to blog about the steps involved to use this in build and deploy this in 7.1x but it will take some time.

Rgds

Eng Swee

[Former Member]

Thank you Eng.

Will try as per suggestion.

Regards,

Akhila

[engswee]

Hi Akhila

As requested, here is the guide.

Recompile com.equalize.xpi.af.modules as EJB 2.1 modules in NWDS 7.1x

Rgds

Eng Swee

[Former Member]

Hi Eng,

Thanks a lot, it will help lot of consultants like me.

Regards,

Akhila

[former_member226609]

Hi Eng,

I always love your blog, something new everytime. Thank you.

I need your help in 1 of my scenario.

It is outbound soap sync to inbound proxy sync.

The xml data I receive through proxy will be mapped to target binary field(Base64Binary field)

I'm trying to apply the 2nd scenario & wondering even though I use this payload bean how shall I map the binary output to the base 64

basically its like

Proxy xml data -> SOAP xml data + Binary(Base64)

I need to populate the data per request in real time either xml data is requested or excel file is requested.

This excel file I'm attaching as soap response.

I hope it's not confusing.

COuld you please help me with this scenario.

Basically soap is requesting Document data with document reference number-> PI forwards the data to ECC via proxy-> From internal tables proxy is retrieving the data & sending as response to proxy. PI is mapping this data to SOAP response.

I'm planning to add soapsync -> proxy sync /async with File & use this module in file channel.

Please advice.

[Former Member]

Hello Eng Swee,

This is one of most required and amazing blog I have read on scn.

First of all thank you so much for putting in such great efforts to help others!!

Secondly, I need your guidance in one of my scenario wherein I have to convert the xml format to Excel format.

My requirement details are in following link:

https://scn.sap.com/thread/3926914

[ambrish_mishra]

Hi Eng Swee,

Nice blog and you seem to have covered various options associated with excel creation.

Cheers,

Ambrish

[Former Member]

Hello Eng Swee,

Could you please guide me on how to proceed on developing an adapter module for converting xml to xsl?

Thanks,

Indu Khurana.

[Former Member]

Hi Eng,

I have a ASYNC to SYNC scenario. Where the SOAP Synchronous response has an excel file(binary) in its body. I want to dump this excel file into an FTP. How do i dynamically extract the excel and push it as a file using my FTP adaptor ?  Key requirement is, this interface should handle any excel file and should not be specific to one excel sheet and it columns.

Thanks
Mithun

[TimVDB92]

Hi Eng,

Is it possible to transform numbers from XML into XLSX cells?
Currently a field, e.g. <Quantity>10</Quantity>, will result in a TEXT-value in the output Excel.

Thank you in advance,

Kind regards, Tim.

[engswee]

Hi Tim,

 

Currently the logic in the module always creates the cells using String/Text type.

Are you using this for PI or CPI?

If PI, then I don't actively maintain the project anymore, so I'd suggest that you fork the repository and make the changes to suit your requirements.

If CPI, then you can create an issue in the GitHub repo, and mark it as an enhancement request. I will have to see if this is something that can be whipped up quite easily or not.

 

Regards

Eng Swee

 

[shreya91]

Hello Eng Swee Yeoh,

Thanks for such a great explanatory post.

I have tried using the XmltoExcel conversion feature in CPI following both of your blogs.
I have downloaded the required jar files from gitHub and uploaded into the CPI Iflow. I am using the same groovy script as shown here.
It produces some sort of a distorted output although there is no error in CPI.

I have attached the screenshots of my content modifer, resources & output over here.
Can you please help me in understanding the anomaly? Am I missing any step?

[shivaprasad1]

Hi Eng swee Yeoh,

Using this bean can we create two sheets in one excel in I Flow run ?

 

Thanks in advance

Shiva 

[Joe_Girard]

Hi Shiva,

I am also in need of being able to output multiple sheets in one xls file.

Were you able to get this to work?

 

Thaks,

Joe

[SATHEESHKUMAR_1]

Hi shreyakar91,

I'm also getting same out put in postman tool while doing XML to Excel conversion SAP CPI.

Please help me if you resolve the above issue .

Thanks ,

satheesh

[jyotis]

Hi,

Thanks for this utility tool. Its amazing and saves a ton of time. I was wondering if it can generate excel with number values as numbers and not as text.

Thanks for your help!

Br,

Jyoti