Requirement:
We had a requirement to convert 2 XMLs into a single MS Excel(Xslx) file.
Solution:
We can achieve this requirement using the Groovy script.
Prerequisites:
Download and archive the libraries below in the integration flow.
- commons-collections4-4.1
- converter-core-2.3.0
- poi-3.17
- poi-ooxml-3.17
- poi-ooxml-schemas-3.17
- xmlbeans-2.6.0
Example: We have two XML files: one containing employee details and the other containing contractor details.
1. The XML file for employee details is structured as follows:
<Record>
<Employee>
<id>A26339</id>
<name>rag</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name> prabhakar</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
</Record>2. The XML file for contractor details is structured as follows:
<Record>
<Contractor>
<Firstname>harish</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>283939</Employeeid>
<Designation>Consultant</Designation>
<address>Hyd</address>
<country>IND</country>
<Mode>P</Mode>
<Shift>2nd</Shift>
</Contractor>
<Contractor>
<Firstname>hemant</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>227484</Employeeid>
<Designation>Consultant</Designation>
<address>Bng</address>
<country>Ind</country>
<Mode>P</Mode>
<Shift>1st</Shift>
</Contractor>
</Record>IFlow overview:
Step1:
We have 2 HTTP requests – where we are getting employee details and contractor details.
Step2:
Used join pallet to combine both incoming branches (i.e. branch 3 and branch 4)
Step3:
Once the data is joined then use the gather pallet to combine the 2 XML structures into a single XML.
Step4:
Using XSLT mapping for removing multiple root elements.
Step5:
Using Groovy script to convert XML’s into multiple sheets (sheet1, sheet2..etc) into MS Excel file.
Note: Ensure the above-mentioned libraries are downloaded and archived in flow before running the script.
Http configurations:
Maintain a unique base path in address and authorization and the user role will be as it is the default.
Join Output:
Join the 2 XML incoming messages.
Gather Configuration:
Payload after gathering:
<Record>
<Contractor>
<Firstname>harish</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>283939</Employeeid>
<Designation>Consultant</Designation>
<address>Hyd</address>
<country>IND</country>
<Mode>P</Mode>
<Shift>2nd</Shift>
</Contractor>
<Contractor>
<Firstname> hemant</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>227484</Employeeid>
<Designation>Consultant</Designation>
<address>Bng</address>
<country>Ind</country>
<Mode>P</Mode>
<Shift>1st</Shift>
</Contractor>
</Record>
<Record>
<Employee>
<id>A26339</id>
<name>rag</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
</Record>Content Modifier:
We will use the content modifier to create an extra root element. However, if you don't provide a root element, we may get an error stating "illegal to have multiple root elements".
<root>
<Record>
<Contractor>
<Firstname>harish</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>283939</Employeeid>
<Designation>Consultant</Designation>
<address>Hyd</address>
<country>IND</country>
<Mode>P</Mode>
<Shift>2nd</Shift>
</Contractor>
<Contractor>
<Firstname>hemant</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>227484</Employeeid>
<Designation>Consultant</Designation>
<address>Bng</address>
<country>Ind</country>
<Mode>P</Mode>
<Shift>1st</Shift>
</Contractor>
</Record><Record>
<Employee>
<id>A26339</id>
<name>rag</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
</Record>
</root>XSLT Configuration:
In XSLT we will remove repeated root elements to make it a single record.
XSLT Script:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/*">
<xsl:apply-templates select="*[1]"/>
</xsl:template>
<xsl:template match="/*/*[1]">
<xsl:copy>
<xsl:copy-of select="@*|node()|following-sibling::*/*"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>XSLT Output:
<Record>
<Contractor>
<Firstname>harish</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>283939</Employeeid>
<Designation>Consultant</Designation>
<address>Hyd</address>
<country>IND</country>
<Mode>P</Mode>
<Shift>2nd</Shift>
</Contractor>
<Contractor>
<Firstname>hemant</Firstname>
<Lastname>kumar</Lastname>
<Employeeid>227484</Employeeid>
<Designation>Consultant</Designation>
<address>Bng</address>
<country>Ind</country>
<Mode>P</Mode>
<Shift>1st</Shift>
</Contractor>
<Employee>
<id>A26339</id>
<name>rag</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>[email protected]</email>
<gender>M</gender>
<status>P</status>
</Employee>
</Record>Now final step is to create a groovy script for converting multiple XML's into single MS Excel file with multiple sheet1, sheet2..etc.
import com.sap.gateway.ip.core.customdev.util.Message
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import javax.xml.parsers.DocumentBuilderFactory
import org.w3c.dom.Element
Message processData(Message message) {
// Get the XML data from the message body
String xmlData = message.getBody(String.class)
Workbook workbook = new XSSFWorkbook()
def ContractorSheet = workbook.createSheet("Contractor")
def employeeSheet = workbook.createSheet("Employee")
// Create a cell style for headers
CellStyle headerStyle = workbook.createCellStyle()
Font headerFont = workbook.createFont()
headerFont.setBold(true)
headerStyle.setFont(headerFont)
// Parse the XML data
def dbFactory = DocumentBuilderFactory.newInstance()
def dBuilder = dbFactory.newDocumentBuilder()
def doc = dBuilder.parse(new ByteArrayInputStream(xmlData.getBytes("UTF-8")))
doc.getDocumentElement().normalize()
// Process Contractor elements
int rowIndex = 0
def headerRow = ContractorSheet.createRow(rowIndex++)
headerRow.createCell(0).setCellValue("Firstname")
headerRow.createCell(1).setCellValue("Lastname")
headerRow.createCell(2).setCellValue("Employeeid")
headerRow.createCell(3).setCellValue("Designation")
headerRow.createCell(4).setCellValue("address")
headerRow.createCell(5).setCellValue("country")
headerRow.createCell(6).setCellValue("Mode")
headerRow.createCell(7).setCellValue("Shift")
headerRow.getCell(0).setCellStyle(headerStyle)
headerRow.getCell(1).setCellStyle(headerStyle)
headerRow.getCell(2).setCellStyle(headerStyle)
headerRow.getCell(3).setCellStyle(headerStyle)
headerRow.getCell(4).setCellStyle(headerStyle)
headerRow.getCell(5).setCellStyle(headerStyle)
headerRow.getCell(6).setCellStyle(headerStyle)
headerRow.getCell(7).setCellStyle(headerStyle)
def ContractorElements = doc.getElementsByTagName("Contractor")
for (int i = 0; i < ContractorElements.getLength(); i++) {
def Contractor = ContractorElements.item(i)
if (Contractor != null && Contractor.getNodeType() == Element.ELEMENT_NODE) {
def FirstnameElement = Contractor.getElementsByTagName("Firstname").item(0)
def LastnameElement = Contractor.getElementsByTagName("Lastname").item(0)
def EmployeeidElement = Contractor.getElementsByTagName("Employeeid").item(0)
def DesignationElement = Contractor.getElementsByTagName("Designation").item(0)
def addressElement = Contractor.getElementsByTagName("address").item(0)
def countryElement = Contractor.getElementsByTagName("country").item(0)
def ModeElement = Contractor.getElementsByTagName("Mode").item(0)
def ShiftElement = Contractor.getElementsByTagName("Shift").item(0)
def row = ContractorSheet.createRow(rowIndex++)
if (FirstnameElement != null) {
row.createCell(0).setCellValue(FirstnameElement.getTextContent())
}
if (LastnameElement != null) {
row.createCell(1).setCellValue(LastnameElement.getTextContent())
}
if (EmployeeidElement != null) {
row.createCell(2).setCellValue(EmployeeidElement.getTextContent())
}
if (DesignationElement != null) {
row.createCell(3).setCellValue(DesignationElement.getTextContent())
}
if (addressElement != null) {
row.createCell(4).setCellValue(addressElement.getTextContent())
}
if (countryElement != null) {
row.createCell(5).setCellValue(countryElement.getTextContent())
}
if (ModeElement != null) {
row.createCell(6).setCellValue(ModeElement.getTextContent())
}
if (ShiftElement != null) {
row.createCell(7).setCellValue(ShiftElement.getTextContent())
}
}
}
// Process Employee elements
rowIndex = 0
headerRow = employeeSheet.createRow(rowIndex++)
headerRow.createCell(0).setCellValue("id")
headerRow.createCell(1).setCellValue("name")
headerRow.createCell(2).setCellValue("email")
headerRow.createCell(3).setCellValue("gender")
headerRow.createCell(4).setCellValue("status")
headerRow.getCell(0).setCellStyle(headerStyle)
headerRow.getCell(1).setCellStyle(headerStyle)
headerRow.getCell(2).setCellStyle(headerStyle)
headerRow.getCell(3).setCellStyle(headerStyle)
headerRow.getCell(4).setCellStyle(headerStyle)
def employeeElements = doc.getElementsByTagName("Employee")
for (int i = 0; i < employeeElements.getLength(); i++) {
def employee = employeeElements.item(i)
if (employee != null && employee.getNodeType() == Element.ELEMENT_NODE) {
def idElement = employee.getElementsByTagName("id").item(0)
def nameElement = employee.getElementsByTagName("name").item(0)
def emailElement = employee.getElementsByTagName("email").item(0)
def genderElement = employee.getElementsByTagName("gender").item(0)
def statusElement = employee.getElementsByTagName("status").item(0)
if (idElement != null && nameElement != null && emailElement != null && genderElement != null && statusElement != null ) {
def row = employeeSheet.createRow(rowIndex++)
row.createCell(0).setCellValue(idElement.getTextContent())
row.createCell(1).setCellValue(nameElement.getTextContent())
row.createCell(2).setCellValue(emailElement.getTextContent())
row.createCell(3).setCellValue(genderElement.getTextContent())
row.createCell(4).setCellValue(statusElement.getTextContent())
}
}
}
// Create an output stream for the Excel file
ByteArrayOutputStream excelBytes = new ByteArrayOutputStream()
workbook.write(excelBytes)
// Set the response type and body
message.setBody(excelBytes.toByteArray())
message.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
return message
}Mail adapter configuration:
Since we're already familiar with the mail adapter configuration, simply specify the extension as .xlsx to ensure Excel is the standard output format.
Output:
Bluesky
