Converting XML Data to Excel in PI/PO Without Using Coding via SOAP or Proxy

[-Mustafa_Onur-]

Hello everyone,

I hope everything is all right and well. In my previous articles, I shared with you how to create some scenarios on PI/PO without using ESR objects. Unfortunately, we cannot perform every service without using an ESR object. The most important of these are SOAP and Proxy Services.

Since I was a junior, we have been receiving a lot of SOAP and Proxy services from our customers. These services are generally the easiest to create. However, if you are asked to change the XML format, the situation may become complicated. As far as I remember, I had just completed my first year in this industry when this type of work came to us for the first time. At that time, no one in our team had done this type of work before. This changed after I learned about the existence of Format Conversion Bean. Thanks to this, converting the XML format has become very simple.

I will show you how to convert the XML structure sent via SOAP or Proxy to an Excel file with XLSX extension. Unlike the previous examples, this time we will create ESR objects, but we will not write any code as in the previous examples.

You can find a few blogs about Format Conversion Bean on the internet, but Mr. Engswee's blogs are the more understandable ones I have read so far. You can access his blog about Format Conversion Bean below:

https://community.sap.com/t5/technology-blogs-by-members/formatconversionbean-one-bean-to-rule-them-...

We have no problem sending data in XML structure as Soap or Proxy. That's why we won't use any special methods when creating ESR objects.

There are some points I want to mention here :

• Our service is asynchronous.
• No special method or code was used when creating ESR objects.
• There is no special setting used in the mapping section, all fields are mapped exactly.

Below you can find the example data in XML structure:

<Record>

      <Necropolis>Power Lich</Necropolis>

      <Inferno>Efreet Sultan</Inferno>

      <Castle>Crusader</Castle>

      <Dungeon>Minotaur King</Dungeon>

      <Stronghold>Ancient Behemoth</Stronghold>

      <Tower>Titan</Tower>

      <Conflux>Magma Elemental</Conflux>

      <Fortress>Greater Basilisk</Fortress>

      <Rampart>Gold Dragon</Rampart>

      <Neutral>Azure Dragon</Neutral>

   </Record>

First of all, what we need to do is to create ESR objects. The first important part is to create a Data Type :

The second important part is that while service interfaces are defined, they must be asynchronous:

After the ESR objects are created, we need an SFTP Receiver channel that can convert the XML structure into XLSX format.

We chose Units as the file name :

From here, we go directly to the Module tab without making any changes (We did not use ASMA in this service). Then, by entering the following module and parameters in the module tab, we perform format conversion on the SFTP receiver channel:

Below you can find the modules and parameters we used:

1        Custom_AF_Modules/FormatConversionBean        Local Enterprise Bean        1

2        localejbs/ModuleProcessorExitBean                     Local Enterprise Bean        exit

1            addHeaderLine                                                     fromXML 

1            converterClass                                 com.equalize.xpi.af.modules.excel.XML2ExcelTransformer 

1            excelFormat                                                          xlsx 

1            sheetName                                                            Favorite Units

exit        JNDIName    deployedAdapters/com.sap.aii.adapter.sftp.ra/shareable/com.sap.aii.adapter.sftp.ra

After the procedures here, an Integrated Configuration is created.

There are some points I want to mention here :

• We gave the value fromXML to the addHeaderLine field. In this way, we will see the field names we set in the DT section in the Excel file.
• The value we give to the sheetName field is the sheet name in the Excel file. We can give this place any name we want.
• In the ExcelFormat field, enter the extension of your Excel file.

After these steps, our Excel file will be created without any problems.

Below you can see our file converted to XLSX format:

Thus, we can easily convert XML data to XLSX format without using any code.

Thank you, have a great day!