In today’s world, where data plays a crucial role, businesses often struggle with managing it efficiently. One common issue they encounter is the difficulty in creating Excel files for working with data, which can slow down their productivity.

In real-time projects, we may get a few requirements where we need to write the purchase order details or invoice details into the Excel file to make it more readable and easier for the client to do further analysis on it.

There are a lot of external JAR libraries available in the market which has predefined classes to create and work with Excel files, in this article we will use Apache POI(Poor Obfuscation Implementation) which is an open-source Java library provided by the Apache Software Foundation. It will help us to read and write Microsoft Office file formats, including Excel and Word.

Exercise :

In this article, we will use the below employee XML to generate an Excel file with all employee records.

Employee XML :

<?xml version="1.0"?>
<Employee>
    <EmployeeDetails>
        <Name>John Doe</Name>
        <Age>23</Age>
        <Designation>Junior Engineer</Designation>
        <Salary>10000</Salary>
    </EmployeeDetails>
    <EmployeeDetails>
        <Name>Price Alex</Name>
        <Age>25</Age>
        <Designation>Software Engineer</Designation>
        <Salary>15000</Salary>
    </EmployeeDetails>
    <EmployeeDetails>
        <Name>Jos Buttler</Name>
        <Age>23</Age>
        <Designation>Junior Engineer</Designation>
        <Salary>15000</Salary>
    </EmployeeDetails>
</Employee>

Implementation :

Create a package with the name ‘HG_Excel_Demo’ and follow below package structure.
Download the POI jar from the Apache website and put it into the below path to make it available for your package to use the jar.
Path: ‘C:SoftwareAGIntegrationServerinstancesdefaultpackagesHG_Excel_Democodejars’ — It may vary based on your installation directory.
Once you have placed the jar file into your package jars directory, restart your integration server to make the jar file usable.
Now we need to add the jar file into the package build path so that in Designer we will not get a compile time error and the IntelliSense will show the classes available in the jar file to use.
Follow the below steps to add the POI jar to the package build path.
- In Designer switch to ‘Java perspective’, if it is not available then in Designer click Window -> Perspective -> Open Perspective -> Java.
- Once you switch to Java Perspective you will see your package Name[ServerHost_Port], if it is not showing then restart your designer and refresh your server.
- Right-click on the package(HG_Excel_Demo) and click ->Build Path -> Configure Build Path.
- Open Libraries select Classpath and click the ‘Add External Jars’ button to select the jar file available in the Package jar folder then click Apply and ‘Apply and Close’.
- Now switch to the Service Development perspective.
Create a document with the name ‘EmployeeDetails’ under the docTypes folder using the XML provided above.
Drag and drop the ‘EmployeeDetails’ document to the input section of the Java service and name it ‘EmployeeDetails’.
Create another string input variable with the name ‘filePath’ which will hold the file path where the Excel file should be saved.
Create one more input string variable with the name ‘fileName’ which will hold the Excel file name.
Now add the try-catch block to the code area of the Java service like below.

package HG_Excel_Demo.v1.services;

import com.wm.data.*;
import com.wm.util.Values;
import com.wm.app.b2b.server.Service;
import com.wm.app.b2b.server.ServiceException;

public final class transformToExcel_SVC

{

	/** 
	 * The primary method for the Java service
	 *
	 * @param pipeline
	 *            The IData pipeline
	 * @throws ServiceException
	 */
	public static final void transformToExcel(IData pipeline) throws ServiceException {
		try {
		
		} catch (Exception e) {
		    e.printStackTrace();

IDataCursor pipelineCursor_1 = pipeline.getCursor();
IDataUtil.put( pipelineCursor_1, "Exception", e );
pipelineCursor_1.destroy();
		}
	}
	
	// --- <<IS-BEGIN-SHARED-SOURCE-AREA>> ---
            
        
        
        // --- <<IS-END-SHARED-SOURCE-AREA>> ---
	final static transformToExcel_SVC _instance = new transformToExcel_SVC();

	static transformToExcel_SVC _newInstance() { return new transformToExcel_SVC(); }

	static transformToExcel_SVC _cast(Object o) { return (transformToExcel_SVC)o; }

}

Update the Java code to import the below classes.

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.w3c.dom.Document; import org.w3c.dom.Element;
import org.w3c.dom.NodeList; import org.xml.sax.InputSource;
import org.apache.poi.hssf.*;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.FileOutputStream;
import java.io.StringReader;
import java.io.File;

In the try block insert the below code to create a workbook object and instantiate a sheet in it.

// Create a new Excel workbook 
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Sheet1");

We have four fields in our document so let’s create four columns and add the header for it, update the Java code, and insert the below code.

// Add a header row HSSFRow 
headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Name");
headerRow.createCell(1).setCellValue("Age");
headerRow.createCell(2).setCellValue("Designation");
headerRow.createCell(3).setCellValue("Salary");

Let’s generate the code to accept the input variable in the Java service, right-click on the Java service -> Generate Code -> Select ‘For Implementing this service’ and click finish. This will copy the auto-generated code to the clipboard.
Paste the copied code after the create header code.

// Pipeline
IDataCursor pipelineCursor = pipeline.getCursor();

// EmployeeDetails
IData EmployeeDetails = IDataUtil.getIData(pipelineCursor, "EmployeeDetails");
if (EmployeeDetails != null) {
    IDataCursor EmployeeDetailsCursor = EmployeeDetails.getCursor();
    String __version = IDataUtil.getString(EmployeeDetailsCursor, "@version");

    // i.Employee
    IData Employee = IDataUtil.getIData(EmployeeDetailsCursor, "Employee");
    if (Employee != null) {
        IDataCursor EmployeeCursor = Employee.getCursor();

        // i.EmployeeDetails
        IData[] EmployeeDetails_1 = IDataUtil.getIDataArray(EmployeeCursor, "EmployeeDetails");
        if (EmployeeDetails_1 != null) {
            for (int i = 0; i < EmployeeDetails_1.length; i++) {
                IDataCursor EmployeeDetails_1Cursor = EmployeeDetails_1[i].getCursor();
                String Name = IDataUtil.getString(EmployeeDetails_1Cursor, "Name");
                String Age = IDataUtil.getString(EmployeeDetails_1Cursor, "Age");
                String Designation = IDataUtil.getString(EmployeeDetails_1Cursor, "Designation");
                String Salary = IDataUtil.getString(EmployeeDetails_1Cursor, "Salary");
                EmployeeDetails_1Cursor.destroy();
            }
        }
        EmployeeCursor.destroy();
    }
    EmployeeDetailsCursor.destroy();
}

String filePath = IDataUtil.getString(pipelineCursor, "filePath");
String fileName = IDataUtil.getString(pipelineCursor, "fileName");

pipelineCursor.destroy();
// Pipeline

Add the below code inside the loop to create a row with each iteration.

HSSFRow row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(Name);
row.createCell(1).setCellValue(Age);
row.createCell(2).setCellValue(Designation);
row.createCell(3).setCellValue(Salary);

Update the Java code with the below code to create the directory in case it does not exist.

File directory = new File(filePath);

if (!directory.exists()) {
    directory.mkdirs();
}

Insert below Java code to save the file into your file system.

try (FileOutputStream outputStream = new FileOutputStream(filePath + "\\" + fileName + ".xls")) {
    workbook.write(outputStream);
    workbook.close();
    System.out.println("Excel file generated successfully.");
}

Till now if you have followed everything correctly then your Java service code should look like below now.

package HG_Excel_Demo.v1.services;

import com.wm.data.*;
import com.wm.util.Values;
import com.wm.app.b2b.server.Service;
import com.wm.app.b2b.server.ServiceException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.apache.poi.hssf.*;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.FileOutputStream;
import java.io.StringReader;
import java.io.File;

public final class transformToExcel_SVC {
    /**
     * The primary method for the Java service
     *
     * @param pipeline The IData pipeline
     * @throws ServiceException
     */
    public static final void transformToExcel(IData pipeline) throws ServiceException {
        try {
            // Create a new Excel workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Sheet1");

            // Add a header row
            HSSFRow headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("Name");
            headerRow.createCell(1).setCellValue("Age");
            headerRow.createCell(2).setCellValue("Designation");
            headerRow.createCell(3).setCellValue("Salary");

            // pipeline
            IDataCursor pipelineCursor = pipeline.getCursor();

            // EmployeeDetails
            IData EmployeeDetails = IDataUtil.getIData(pipelineCursor, "EmployeeDetails");
            if (EmployeeDetails != null) {
                IDataCursor EmployeeDetailsCursor = EmployeeDetails.getCursor();
                String __version = IDataUtil.getString(EmployeeDetailsCursor, "@version");

                // i.Employee
                IData Employee = IDataUtil.getIData(EmployeeDetailsCursor, "Employee");
                if (Employee != null) {
                    IDataCursor EmployeeCursor = Employee.getCursor();

                    // i.EmployeeDetails
                    IData[] EmployeeDetails_1 = IDataUtil.getIDataArray(EmployeeCursor, "EmployeeDetails");
                    if (EmployeeDetails_1 != null) {
                        for (int i = 0; i < EmployeeDetails_1.length; i++) {
                            IDataCursor EmployeeDetails_1Cursor = EmployeeDetails_1[i].getCursor();
                            String Name = IDataUtil.getString(EmployeeDetails_1Cursor, "Name");
                            String Age = IDataUtil.getString(EmployeeDetails_1Cursor, "Age");
                            String Designation = IDataUtil.getString(EmployeeDetails_1Cursor, "Designation");
                            String Salary = IDataUtil.getString(EmployeeDetails_1Cursor, "Salary");
                            EmployeeDetails_1Cursor.destroy();

                            // Create rows with runtime data
                            HSSFRow row = sheet.createRow(i + 1);
                            row.createCell(0).setCellValue(Name);
                            row.createCell(1).setCellValue(Age);
                            row.createCell(2).setCellValue(Designation);
                            row.createCell(3).setCellValue(Salary);
                        }
                    }
                    EmployeeCursor.destroy();
                }
                EmployeeDetailsCursor.destroy();
            }

            String filePath = IDataUtil.getString(pipelineCursor, "filePath");
            String fileName = IDataUtil.getString(pipelineCursor, "fileName");
            pipelineCursor.destroy(); // pipeline

            // Create the directory if it doesn't exist
            File directory = new File(filePath);
            if (!directory.exists()) {
                directory.mkdirs();
            }

            // Save the Excel file to the specified directory
            try (FileOutputStream outputStream = new FileOutputStream(filePath + "\\" + fileName + ".xls")) {
                workbook.write(outputStream);
                workbook.close();
                System.out.println("Excel file generated successfully.");
            }
        } catch (Exception e) {
            e.printStackTrace();
            IDataCursor pipelineCursor_1 = pipeline.getCursor();
            IDataUtil.put(pipelineCursor_1, "Exception", e);
            pipelineCursor_1.destroy();
        }
    }

    // --- <<IS-BEGIN-SHARED-SOURCE-AREA>> ---
    // --- <<IS-END-SHARED-SOURCE-AREA>> ---

    final static transformToExcel_SVC _instance = new transformToExcel_SVC();

    static transformToExcel_SVC _newInstance() {
        return new transformToExcel_SVC();
    }

    static transformToExcel_SVC _cast(Object o) {
        return (transformToExcel_SVC) o;
    }
}

It’s time to test our Java service, so let’s run it giving some sample data.

Congratulations!! we did it😊.

Hope you have enjoyed this article and found this useful, please click below download button below to download the source code.

Post Views: 1,947

2 thoughts on “Create Excel file in WebMethods”

Pravallika
28 December 2023 at 07:34
which file path I shoud provide in string?
- harmonigate.com
  28 December 2023 at 14:53
  Hello Pravallika,
  filepath : this should be your system location where you want to store the file.

2 thoughts on “Create Excel file in WebMethods”

Leave a Comment Cancel reply