Convert multiple xml's into single Xlsx(MS Excel) using groovy script

[vamsi_dhar_reddy]

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.

1. commons-collections4-4.1
2. converter-core-2.3.0
3. poi-3.17
4. poi-ooxml-3.17
5. poi-ooxml-schemas-3.17
6. 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>rag@yahoo.com</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name> prabhakar</name>
<email>prabhakar@yahoo.com</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>rag@yahoo.com</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>prabhakar@yahoo.com</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>rag@yahoo.com</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>prabhakar@yahoo.com</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>rag@yahoo.com</email>
<gender>M</gender>
<status>P</status>
</Employee>
<Employee>
<id>A26337</id>
<name>prabhakar</name>
<email>prabhakar@yahoo.com</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: