Create Excel file in WebMethods

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

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.

Java
  • Update the Java code to import the below classes.

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

Java
  • 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.  

Java
  • 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.

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

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

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

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

Java
  •  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.

DownloadPackage

 

2 thoughts on “Create Excel file in WebMethods”

Leave a Comment