- SAP Managed Tags
Hello Sapians,
Sometimes we encountered an issue where the third party is sending the file in Excel format and we have to read that file, convert to XML and process in SAP CPI. As we are familiar we don't have any direct function available as of now to convert excel file to XML. To overcome this issue, below are the step by step process to implement in CPI to read and convert Excel file.
This converter is based on Apache POI 3.17 library. In order for conversion the following JAR files need to be referenced/imported into the project.
- commons-collections4-4.1.jar (https://jar-download.com/artifacts/org.apache.commons/commons-collections4/4.1/source-code)
- converter-core-2.3.0.jar (https://jar-download.com/artifact-search/converter)
- poi-3.17.jar (https://jar-download.com/artifacts/org.apache.poi/poi/3.17/source-code)
- poi-ooxml-3.17.jar (https://jar-download.com/artifacts/org.apache.poi/poi-ooxml/3.17/source-code)
- poi-ooxml-schemas-3.17.jar (https://jar-download.com/?search_box=poi-ooxml-schemas-3.17)
- xmlbeans-2.6.0.jar (https://jar-download.com/?search_box=xmlbeans-2.6.0&p=2)
The library files can be downloaded from above mentioned links or Apache website.
Sample Excel file in .xsls format.
To import the JAR files, Go to
<<IFlow>> --> Click on blank panel (you will get Integration Flow) --> References --> Add --> Archive
Select the required Jar files from your system and click on Add.
Import all the above mentioned JAR files to your Iflow.
Sender Adapter is configured with HTTPS adapter.
Add the content modifier, as we have to configure some parameters in exchange properties.
Content Modifier properties :
1. converterClass : com.equalize.converter.core.Excel2XMLConverter
2. sheetName : << Specify the sheet Name that need to be process from your Excel File>>
3. processFieldNames : <<The header fields will be processed from the file>>
4. documentName : << Specify the name in which the XML header tag to be generated>>
5. documentNamespace : << Specify the name in which the XML namespace to be generated>>
To understand more about parameters that can be added, you can check below parameter reference.
Below is a list of the parameters for configuration 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:
| |
| 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
|
| evaluateFormulas | Y, N | Y | Controls how cell contents with formulas are displayed in XML output
|
| emptyCellOutput | suppress, defaultValue | suppress | Controls how empty cells are displayed in XML output
|
| 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 |
After that add the groovy script to convert the file to XML.
Groovy Script :
-----------------------------------------------------------------------------------------------------
Add content modifier to read the XML generated.
To test the scenario, we are testing through postman.
Add you file in binary format.
This conclude the blog, the conversion of Excel to XML file😊.
In case of any queries, you can comment in the blog. Will try to resolve your concerns.
Accepted Solutions (0)
Answers (1)
Hi,
You can click on the link and download from the download option.
If it is not working for you, then you can try to download from other available websites. It is available for free from many sites.
Bluesky| User | Count |
|---|---|
| 11 | |
| 7 | |
| 6 | |
| 5 | |
| 4 | |
| 4 | |
| 4 | |
| 3 | |
| 3 | |
| 3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.