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 (converterClass = ‘com.equalize.xpi.af.modules.excel.XML2ExcelTransformer’). 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
conversionType SimpleXML2Excel Required field. Determines conversion type
converterClass com.equalize.xpi.af.modules.excel.XML2ExcelTransformer 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’

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 /wp-content/uploads/2014/11/in1_576442.png
Output /wp-content/uploads/2014/11/out1_576473.png

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 /wp-content/uploads/2014/11/in2_576495.png
Output /wp-content/uploads/2014/11/out2_576496.png

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 /wp-content/uploads/2014/11/in3_576475.png
Output /wp-content/uploads/2014/11/out3_576480.png
To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Akhila A

    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

    (0) 
    1. Eng Swee Yeoh Post author

      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

      (0) 
  2. Harry Saj

    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.

    (0) 
  3. Indu Khurana

    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

    (0) 
  4. Indu Khurana

    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.

    (0) 
  5. Mithun Parthiban

    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

    (0) 

Leave a Reply