Skip to Content
Author's profile photo Eng Swee Yeoh

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

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

Update 10 May 2016: Add new optional parameters headerRow and onlyValidCharsInXMLName (courtesy of Jacob Frank Vandborg).

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

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 Excel to XML conversion. Certain parameters will automatically inherit the default values if it is not configured.

Parameter Name Allowed values Default value Remarks
converterClass PI – com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
CPI – com.equalize.converter.core.Excel2XMLConverter
Required field. Determines conversion class.
sheetName The name of the active Excel sheet to extract. Either sheetName or sheetIndex must be populated.
sheetIndex Integer values beginning from 0 The index of the active Excel sheet to extract (starts from 0). Either sheetName or sheetIndex must be populated.
skipEmptyRows Y, N Y Empty rows to be skipped or not
rowOffset Integer values beginning from 1 0 Starting row to begin extracting content from (i.e. 0 = start from first row, 1 = start from second row). If processFieldNames = ‘fromFile’ and rowOffset = 0, first line will be automatically be headerRow+1
headerRow Integer values beginning from 1 0 Available only when processFieldNames = ‘fromFile’. Determines which row to retrieve header column names from (0-based so 0 is first row, 1 is second row)
columnOffset Integer values beginning from 1 0 Starting column to begin extracting content from (i.e. 0 = start from first column, 1 = start from second column)
processFieldNames fromFile, fromConfiguration, notAvailable Required field. Determines the naming of each column of the rows, and the number of columns to extract:

  • fromFile = Column names and number of columns are determined from header line of the sheet
  • fromConfiguration = Column names and number of columns are determined from parameter fieldNames
  • notAvailable = Column names will be set as ColumnX, where X = 1,2,3,4. Number of columns will be determined from parameter columnCount
fieldNames Name of columns. Required field when processFieldNames = ‘fromConfiguration’
onlyValidCharsInXMLName Y, N N Available only when processFieldNames = ‘fromFile’. Removes invalid XML characters from column names retrieved from header row of sheet
columnCount Integer values beginning from 1 Number of columns for extraction. Required field when processFieldNames = ‘notAvailable’
recordName Record XML element name for row of record in output
documentName Required field. Document name of root element of XML output
documentNamespace Required field. Namespace of root element of XML output
formatting excel, raw excel Controls how the cell contents are formatted in XML output

  • excel = Cells are displayed the same way as Excel formatting of corresponding cell
  • raw = Raw value of cells are displayed
evaluateFormulas Y, N Y Controls how cell contents with formulas are displayed in XML output

  • YES = Cells are displayed with result of formula evalution
  • NO = Cells are displayed with actual formula
emptyCellOutput suppress, defaultValue suppress Controls how empty cells are displayed in XML output

  • suppress = Empty cells are not displayed (no corresponding XML tags for empty cells)
  • defaultValue = Empty cells will be displayed with default value
emptyCellDefaultValue <blank> If emptyCellOutput = ‘defaultValue’, all empty cells will be populated with value in this parameter
indentFactor Integer values beginning from 1 0 Determines the number of indentation spaces for each level in the XML output
debug Y, N N Displays contents in Audit Log of each cell extracted. WARNING: Use this only for debugging in non-productive systems

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

Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetName Sheet1
processFieldNames fromFile
documentName MT_Order
documentNamespace urn:equalize:com

Result

Input /wp-content/uploads/2014/10/in1_567666.png
Output /wp-content/uploads/2014/10/out1_567688.png

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

Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetIndex 0
processFieldNames fromConfiguration
fieldNames Order,Date,Material,Quantity
rowOffset 2
recordName Line
documentName MT_CustomOrder
documentNamespace urn:equalize:com
formatting raw

Result

Input /wp-content/uploads/2014/10/in2_567687.png
Output /wp-content/uploads/2014/10/out2_567686.png

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

Parameter Name Parameter Value
converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
sheetIndex 0
processFieldNames notAvailable
columnCount 5
rowOffset 1
documentName MT_CustomOrder
documentNamespace urn:equalize:com
skipEmptyRows N
evaluateFormulas N
emptyCellOutput defaultValue
emptyCellDefaultValue space

Result

Input /wp-content/uploads/2014/10/in3_567714.png
Output /wp-content/uploads/2014/10/out3_567715.png

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

Assigned Tags

      53 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Javier Alcubilla Alcala
      Javier Alcubilla Alcala

      Great!!! many thanks!!

      Author's profile photo Peter Wallner
      Peter Wallner

      Hello Eng Swee Yeoh,

      thank you for this very interesting and useful adapter module. This will make life much easier dealing with EXCEL sheets without having to write adapter modules anymore.

      Best regards,

      Peter

      Author's profile photo Former Member
      Former Member

      Hi Eng Swee Yeoh,

      Thanks for BLOG,

      I am little confused at using "sheetIndex" value. As I have a requirement to read Excel which has 4 Tabs in it and I need to read first 2 Tabs data and Map it back to RFC.

      And not clear on how to build Data types\Message Types for each Excel sheet fields.

      Is this requirement is possible using this Example Scenario 2.I have already raised SCN query on this. It will be helpful for me if you respond to my query.

      http://scn.sap.com/thread/3763584

      also please let me know where to keep these jar files in PI ..

      • 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

      Thanks & Regards

      Siri

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Dear Siri

      Thank you for your interest in this adapter module.

      Unfortunately, the module as it is can only read 1 sheet in an Excel file. If you want to be able to read more, you can download the source code and further enhance it accordingly.

      Regarding the JAR files, these are bundled in the adapter module's EAR file and are automatically deployed into the PI system once the EAR file is deployed. You do not need to manually upload them into PI's file system.

      Rgds

      Eng Swee

      Author's profile photo Former Member
      Former Member

      Dear Eng,

      Please help with the below thread. Thanks.

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

      Author's profile photo Natarajan Krishnamurthy
      Natarajan Krishnamurthy

      Hi Eng

      I like this pose. But unfortunately I do not know how to use this. I downloaded the poi-bin-3.9-20121203 zip and gave it to our basis team to upload in the server. They said that it was uploaded successfully. But we cannot find the modules. Here below is the screen shot from Netweaver application module. Your help is greatly appreciated.

      netweaver%20application%20module

      netweaver application module

       

      Thanks

      Natarajan

      Author's profile photo Former Member
      Former Member

      Hi Eng Swee Yeoh ,


      Facing error in comm channel on implementing above steps. Please help. Many Thanks....!!


      Implemented Scenario 1

      /wp-content/uploads/2015/07/hjtr_741689.jpg

      Parameter Name Parameter Value
      converterClass com.equalize.xpi.af.modules.excel.Excel2XMLTransformer
      sheetName StoreXL
      processFieldNames fromFile
      documentName MT_Order
      documentNamespace htttp://OrdersofOrders/xi/StoreConversion
      Author's profile photo Alexey Dmitriev
      Alexey Dmitriev

      Hi Rajesh,

      it seems that you didn't deploy the module.

      Please check this out.

      Author's profile photo Former Member
      Former Member

      Hi Alexey,

      Could you please brief or elaborated. I didn't get you.

      You mean we need to deploy the module(Custom_AF_Modules/FormatConversionBean) as followed in below link: I guess it involves basis work.

      http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/c0b39e65-981e-2b10-1c9c-fc3f8e6747fa?overridelayout=t…

      Please correct me If I'm wrong.

      Author's profile photo Alexey Dmitriev
      Alexey Dmitriev

      Donwload the EAR file. Its in the blog aabout FormatConversionBean.

      Then open NWDS and connect it to your system(Java As).

      Open Deployment perspectinve  - import EAR, and press start.

      If successfull pop up window appeared then you can use the module in adapter/

      Author's profile photo Former Member
      Former Member

      Hi Alexey,

      If you have the steps of procedure to work upon for FILE(EXCEL) to FILE(XML) scenario in PI7.0

      Please share. Many Thanks.

      Author's profile photo Alexey Dmitriev
      Alexey Dmitriev

      Hi Rajesh,

      please check in NWA, that the following module is successfully deployed in your system.

      Capture.PNG

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Alexey,

      I tried to reply to your earlier comment about the date formats but I think it was deleted before I managed to post the comment. Anyway here it is below if you still need it:-

      -------------------

      Thank you for the feedback. Glad to know that the modules were deployed successfully and works.

      Regarding the Excel format, to be honest I haven't tested it extensively with different users and date formats.

      The code for it uses the Apache POI DataFormatter as shown below. So that is the default behaviour.

      com.equalize.xpi.af.modules/Excel2XMLTransformer.java at master · engswee/com.equalize.xpi.af.modules · GitHub

      DataFormatter formatter = new DataFormatter(true);

      cellContent = formatter.formatCellValue(cell);

      Unfortunately, I am quite tied up at the moment and do not have time to look into this. As the source code is available publicly on GitHub, may I suggest that you try to modify the module and test if there is some Apache POI method that can cater for it.

      You can build the module and perform standalone testing on it following my blogs below:-

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

      Standalone testing of Adapter Module in NWDS

      Rgds

      Eng Swee

      Author's profile photo Alexey Dmitriev
      Alexey Dmitriev

      Hi Eng Swee,

      I deleted the comment because I figured out that the problem was with Macros in the Excel form.

      Alexey.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Rajesh

      Note that the module and EAR file are not compatible with PI 7.0. I'd suggest you search for other alternative solutions for that particular PI version.

      Rgds

      Eng Swee

      Author's profile photo Former Member
      Former Member

      Hi Eng Swee,

      Thanks alot for the information.

      Could you please suggest if any. I'm just strucked up with this scenario.

      Can the below links be implemented in PI7.0:

      http://scn.sap.com/docs/DOC-58278

      You have some alternatives more:

      PI/XI: Reading MS Excel's XLSX and XLSM files with standard PI modules - easily...

      Author's profile photo Former Member
      Former Member

      Hello Eng,

      Your module has made the life easy. Thanks for that. I tried your module for an excel file with FTP adapter. It worked without any issues. My requirement is to use with an sender email adapter, where xlsx file will come as an attachment. I used PayloadZipBean,  PayloadSwapBean,  FormatConversionBean, XIMailAdapterBean in order with appropriate parameters. It processed the file content using the parameters given in configuration(i could see in audit log with debug on and off) but it fails in intergration engine due to the fact the converted content is still in the mailattachment-1 and not in the main mail message payload. I tried using one more payload swap bean after format conversion bean but it still fails in the integration engine. I can see that there is no problem in your module processing but just want to ensure have you faced any issues with mail adapter. Any help would be appreciated. Thanks.

      Server: PI7.31 Dual Stack

      Regards

      M

      Author's profile photo Alexey Dmitriev
      Alexey Dmitriev

      Hi Manikandan Rajendran,

      why do u use PayloadZipBean? its not necessary, is your attach in not zipped.

      Please exclude from module chain and it should work.

      Use first - localejbs/AF_Modules/PayloadSwapBean with

      swap.keyName     payload-name
      swap.keyValue     MailAttachment-1

      Second - FormatConversionBean

      configure it according your requirement

      Third  - XIMailAdapterBean - standard one

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Thanks Alexey for your assistance here.

      Manikandan, please follow Alexey's suggestions. You don't need to unzip, just need to swap the attachment to the main payload before using FormatConversionBean.

      Rgds

      Eng Swee

      Author's profile photo Former Member
      Former Member

      Hi Eng,

      Thanks for presenting the module.

      I have a interface Sender mail adapter containing .xslx file in attachment.I need to convert the .xslx file to XML and then use it.

      I am using the module in the below sequence..as in the screenshot.

      For the PayloadSwapBean the params are:

      swap.keyName     =Payload-Name

      swap.keyValue1

      ModuleSequence.JPG

      But the problem I am getting is the payload still is in .xslx format.

      This is why the mapping is failing.

      But astonisingly if I dowload the MailAttachment-1.xslx file and see that,it opens in notepad++, and that is the converted xml.

      So the TransformationBean works but my purpose is not solved.

      Payload.JPG

      Please help me with suggestion.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Abhishek

      Thanks for your interest in this development.

      As you are relatively new to SCN, I'd suggest that you post your query as a discussion thread. It is the appropriate place as the comments section of blogs are not suitable for detailed discussions to tackle specific issues.

      Just as a note, I suspect that the PayloadSwapBean might not be working correctly. Can you please provide screenshot of the full configuration parameters for all modules? Also provide screenshot of the audit/message log showing the execution of the modules.

      Regards

      Eng Swee

      Author's profile photo Former Member
      Former Member

      Hi Eng, Opened a new discussion http://scn.sap.com/message/16783874#16783874 Added Parameter for PayloadSwapBean.

      Author's profile photo Miguel Jorge
      Miguel Jorge

      Hi Eng,

      Thanks for this blog and assistance in using this module.

      I am able to successfully test this as a standalone module in NWDS.

      However after deploying it to our PI 7.4 system, I get an error:

      Error: java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

      FormatConversionBean.PNG


      Do you perhaps know how I would go about fixing this error?

      Thanks,

      Miguel

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Miguel

      From the first line of your log, it seems there is already a conversion to XML. Is this done by some standard FCC or MTB?

      Please note that this module expects an Excel file in either binary XLS format or the newer XLSX 2007 format (refer Apache POI - the Java API for Microsoft Documents). The error you are getting is due to the input not being in either of these formats.

      Rgds

      Eng Swee

      Author's profile photo Miguel Jorge
      Miguel Jorge

      Hi Eng,

      Thanks for the reply.

      I have tried it with both formats and it fails.  I then tried the module with a stand-alone test on my local machine through the NWDS and the process worked.  This locally working file, is the file that I am trying to test through PI now and getting this error message.

      Thanks,

      Miguel

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Miguel

      Can you explain what is happening in your channel that causes the file to be converted to XML as shown in the first line (highlighted below)? Are you using File Content Conversion in the channel? Can you share your channel's module configuration screenshot?

      /wp-content/uploads/2015/09/log_780945.png

      Rgds

      Eng Swee

      Author's profile photo Miguel Jorge
      Miguel Jorge

      Hi Eng,

      Yes, I had originally configured the channel for FCC.  As soon as I removed the FCC, it worked perfectly.

      Thanks very much for your help.

      Thanks,

      Miguel

      Author's profile photo Shradha Wanjari
      Shradha Wanjari

      Hi Eng,

      I have a scenario from MAIL to IDOC  where excel file comes as an attachment. This excel sheet contains two worksheets one has header and another has the item level details.

      Using the above i was able convert the excel to xml but only a single sheet at a time .Could you please let me know  how can i use this to read two worksheet from the excel at the same time.

      Thanks,

      Shradha.

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Shradha

      The design of this converter is to read one sheet only. If you want to read more than one sheet you can enhance it according to your needs. The source code is publicly available as described in the Source Code section above.

      Regards

      Eng Swee

      Author's profile photo Apu Das
      Apu Das

      Hi Eng,


      I am trying to implement excel to xml conversion from your below blog -

      ExcelTransformBean Part 1: Convert various Excel ...


      But After deploying it in my java stack I am getting below error and its deployed with warning as well -


      Error while obtaining business methods info for beansap.com/Excel2XMLTransformerEAR*xml|Excel2XMLTransformerEJB.jar*xml|Excel2XMLTransformer. Most probable reason: application sap.com/Excel2XMLTransformerEAR cannot be started. See logs available for this application.


      These are the steps I have followed. I think I have done something wrong. Can you please help me -


      1. Created EJB 3.0 project with name Excel2XMLTransformerEJB and added to EAR - Excel2XMLTransformerEAR

      2.  Added all required jars in ejb project build path.

      3. In EJB project created one session bean (EJB 3.x) with name Excel2XMLTransformer.


      Created normal class with below names -


      AbstractModuleConverter

      AuditLogHelper

      DynamicConfigurationHelper

      ParameterHelper

      4.  Build the EJB project

      5. My ejb-j2ee-engine.xml value -

      Pic1.JPG

      6. My ejb-jar.xml value -

      Pic2.JPG

      7. Export as SAP EJB JAR File

      8. In EAR application-j2ee-engine.xml value -

      Pic3.JPG

      9.  Build and export as SAP EAR File.

      10. Finally Run on server. its deployed with warning.

      Can you please help me with the correct and exact steps. Its very urgent

      Thanks in advance for you help.

      Regards,

      Apu

      Author's profile photo Michael Appleby
      Michael Appleby

      Unless you are asking for clarification/correction of some part of the Document, please create a new Discussion marked as a Question.  The Comments section of a Blog (or Document) is not the right vehicle for asking questions as the results are not easily searchable.  Once your issue is solved, a Discussion with the solution (and marked with Correct Answer) makes the results visible to others experiencing a similar problem.  If a blog or document is related, put in a link.  Read the Getting Started documents (link at the top right) including the Rules of Engagement. 

      NOTE: Getting the link is easy enough for both the author and Blog.  Simply MouseOver the item, Right Click, and select Copy Shortcut.  Paste it into your Discussion.  You can also click on the url after pasting.  Click on the A to expand the options and select T (on the right) to Auto-Title the url.

      Author's profile photo Apu Das
      Apu Das

      Hi Michael,

      Already I have started an discussion but not sure how can I directly reach to Eng, so pasted same here .

      Here is the discussion link - Error in ExcelTransform Bean

      Thanks,

      Apu

      Author's profile photo Michael Appleby
      Michael Appleby

      Right click on Eng's name link.  Copy the url.  Paste it into your Discussion.  Mouse over the link and click on the A popup, then select T for auto title. 

      That will bring it to Eng's attention.

      Cheers, Mike (Moderator)

      SAP Technology RIG

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Thanks Mike for helping to moderate in the PI space 🙂

      Author's profile photo vasudeva Gembali
      vasudeva Gembali

      HI Eng,

       

      Kindly help to check the following error while reading excel file. Current version PI 7.4

      Error :

      Channel CC_XXXXXX: Sending file failed with javax.ejb.TransactionRolledbackLocalException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBTransactionRolledbackException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed; nested exception is: javax.ejb.EJBException: nested exception is: java.lang.RuntimeException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLDocument : cannot initialize class because prior initialization attempt failed - continue processing

       

      Author's profile photo Former Member
      Former Member

      Hi Eng Swee,

      Good day!

      Thanks for sharing this really helpful blog!

      Just a question, is there a capability under this bean that will allow to split a single excel xslx file with 2 sheets into 2 separate xlsx/csv files? 😉 No transformation required, just need to split this single file. Do I just convert the xlsx file into an XML and apply the multi-mapping before converting it back into xls? 😀

       

      Cheers,

      Madina

      Author's profile photo Former Member
      Former Member

      Hello,

      Successfully deployed the EAR file.  Version of PI is 7.4 SP 8.

      I am getting the below error:

       

       

      Below are the configuration details in the Communication Channel.

      Hope you can help me.

       

      Kr,

      Bryan

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      I suggest you perform standalone testing in NWDS so that you can debug your error. You can check one of my other blogs which details more on that.

      Author's profile photo Chuanchuan Zhang
      Chuanchuan Zhang

      it seems like can't add xml.recordsetName & xml.<StructureName>. just like the standard FCC,since I got a deep structure with multiple nodes,is there any solution deal with this?

      BR.

      wyz

      Author's profile photo Gopi krishnan
      Gopi krishnan

      Hi Eng Swee Yeoh,

      thank you  good blog,

       

      We are given xls format in receiver communication channel but the output file data is asking like XML file format, how to default come us xls format can u have any idea

      please find below screenshots

       

      for those popups, we don't want how to directly come

       

       

      Author's profile photo Rajani D
      Rajani D

      Hi Eng Swee Yeoh,

       

      I have implemented this excel transformation bean. I would like to check is there anybody implemented the dynamic sheet name in this sheet name parameter.

      As per my requirement,  I would like to pass the APR-19(month name and two digit year). Any inputs on this requirements.

       

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Hi Rajani

       

      Module parameters in PI are always static values - so you won't be able to pass dynamic values to sheetName parameter.

       

      Regards

      Eng Swee

      Author's profile photo gvkarthik G
      gvkarthik G

      Hi Eng Swee Yeoh,

      Thanks to the ear file you have provided, i was able to deploy com.equalize.xpi.af.modules.app.ear using NWDS.

      The version of PI is 7.5 and i am using the Excel to xml transformation,

      i configured the parameters in the sender CC as detailed below and moved on for testing, 

       

      i am getting the error as attached in the screen shot, can you please guide me to resolve this

      appreciate your response Thanks in advance

      Karthik G

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      Swap the sequence of the module.

      Author's profile photo gvkarthik G
      gvkarthik G

      Thanks for your response, i have swapped and it worked.

      Really appreciate your work, thanks for this module  🙂

       

      Regards,

      Karthik G

      Author's profile photo kuldeep tiwari
      kuldeep tiwari

      Hi Eng Swee ,

       

      I am using Custom_AF_Modules/FormatConversionBean module in SAP PO to convert excel to xml file.

      Excel have 5000-10000 records.

      But it's failing at channel level with below error.

      PO%20Issue

      Sender Channel Configuration:

      Channel%20Config

      Channel Config

      PO Issue

      Appreciate your response Thanks in advance.

      Thanks,

      Kuldeep Kumar

      Author's profile photo Gutam Karthik
      Gutam Karthik

      Hi Eng Swee

      Hope you are doing good, i am also facing similar kind of issue but when i open that excel, select the cell and optimize and save and run the communication channel it is accepting the file and able to read the contents of the file, unable to understand this strange behaviour.

      let me explain my scenario.

      1. I am getting a automated mail with an excel attachment
      2. I am saving this file to application server
      3. I am converting this xlsx file to a .txt file with || delimiter, so that it can be used further

      Problem : i used the configuration  ExcelTransformBean Part 1: Convert various Excel formats to simple XML easily  however it is failing stating that nested exception at index .......

       

      but the same excel if i open it select columns, optimize, save it. Then if i run the Communication Channel it is getting converted.

       

      If you manage some time can you please have a look at it and provide your suggestions.

      Thanks in Advance

      Karthik G

      Author's profile photo Dinesh M
      Dinesh M

      Hi Eng, Team,

      I have uploaded all the JAR into the CPI and trying to extract excel file but getting error as below

       

      Error:

      org.apache.camel.CamelExecutionException: Exception occurred during execution on the exchange: Exchange[ID-76f829be-8a8c-4e7c-6c21-08d4-1638955055121-63-54], cause: java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;
      Could anyone please help me out on this if anyone is using this JARs for development?
      Thanks,
      Dinesh

       

      Author's profile photo Eng Swee Yeoh
      Eng Swee Yeoh
      Blog Post Author

      You need the correct version of Apache POI as listed in the release notes

      https://github.com/engswee/equalize-cpi-converter/releases

      Author's profile photo Gopal KS
      Gopal KS

      Hi Eng Swee,

       

      Below links are not working. so I have downloaded latest version of this files from another website. But still I am getting the error "java.lang.NoSuchMethodError: org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()Lorg/apache/poi/ss/usermodel/CellType;" Can you please confirm what will be the issue.

       

      I have used poi-ooxml lite instead of xml-schemas as it thrown error.

      http://central.maven.org/maven2/org/apache/poi/poi/3.17/poi-3.17.jar
      http://central.maven.org/maven2/org/apache/poi/poi-ooxml/3.17/poi-ooxml-3.17.jar
      http://central.maven.org/maven2/org/apache/poi/poi-ooxml-schemas/3.17/poi-ooxml-schemas-3.17.jar
      http://central.maven.org/maven2/org/apache/xmlbeans/xmlbeans/2.6.0/xmlbeans-2.6.0.jar
      http://central.maven.org/maven2/org/apache/commons/commons-collections4/4.1/commons-collections4-4.1.jar

       

       

      Author's profile photo Hans van der Maarel
      Hans van der Maarel

      I had a working scenario:

      An automated mail with an excel (xls) attachment to iDoc. I changed the login of the sender mail communication channel from basic authentication to OAuth authentication. Now I get the errors:

      failed to call the adapter engine; caused by java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

      Exception caught during processing mail message [0]com.sap.aii.af.lib.mp.module.ModuleException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

      Any idea?

      Regards Hans

      Author's profile photo Emmanuel De Deyne
      Emmanuel De Deyne

      Hello Hans,

      The exception you mention usually occurs when no excel files are present in the fetched mail ( or the module is looking in the wrong place ) , so that's where you could look.

      For that, I think Note 3137755  might be relevant.

      Have you tried to log your incoming message before the conversion step to verify if the reference is still correct?

      Regards Emmanuel

      Author's profile photo Cristian Abad Chico
      Cristian Abad Chico

      Eng Swee Yeoh great blog.

      If we want to receive an Excel sheet over an Http Sender channel, what is the expected format of the request body? Binary? Base64?

      Thanks!

      C.