Mulesoft – Excel download using Apache POI

Excel can be created in Mulesoft using Apache POI library and allow for download

  • Create Maven enabled Mulesoft project
  • Add the Apache POI as a dependency in the pom.xml
 	   <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
          </dependency>
          
  • Create RAML file having endpoint to access excel download URL
/excel:
    get:
          description: fetch worklist details.
          is: [pagination, accept-required, content-type-required, client-credentials-required, standard-error-responses]
          responses:
            200:
              body:
                '*/*':
  •  Create Apache POI utility Implementation which generates XML file
package com.pwn.download;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDownload {

	public InputStream downloadFile() {

		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("Cities");

		Object[][] cities = { { "USA", "Dublin", 94568 }, { "USA", "San Ramon", 94568 },
				{ "USA", "Livermore", 97856 }, { "USA", "Pleasanton", 54689 }, };

		int rowCount = 0;

		for (Object[] city : cities) {
			Row row = sheet.createRow(++rowCount);
			int columnCount = 0;

			for (Object field : city) {
				Cell cell = row.createCell(++columnCount);
				if (field instanceof String) {
					cell.setCellValue((String) field);
				} else if (field instanceof Integer) {
					cell.setCellValue((Integer) field);
				}
			}

		}
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			workbook.write(bos);
			bos.close();

		} catch (IOException e) {
			e.printStackTrace();
		}
		return new ByteArrayInputStream(bos.toByteArray());
	}
}

One important point is xls data should be sent as inputStream(as opposite to  outputStream) to download as mentioned in the below code

ByteArrayOutputStream bos = new ByteArrayOutputStream();
		try {
			workbook.write(bos);
			bos.close();

		} catch (IOException e) {
			e.printStackTrace();
		}
		return new ByteArrayInputStream(bos.toByteArray());

    Register this as a bean

  • Create the flow using API definition file

  • Run the application and test the endpoint using following parameters
Headers – 
	accept:application/json
	client_id:xx
	client_secret:xx
	content-type:application/json
URL - http://localhost:8081/download/v1/excel

 

  • Use the option Send and Download option to execute the endpoint
  • Output looks like as below

Source Code – https://github.com/programmingwithnaveen/excel-download

Java – Load Properties from a XML file

Properties from XML file can be read using loadFromXML() option

 
package com.pwn.property;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.InvalidPropertiesFormatException;
import java.util.Properties;

public class LoadPropertyFromXML {

	public static void main(String[] args) {
		// make new properties instance to load the file into
		Properties prop = new Properties();
		// check to make sure the file exists
		File file = new File("c://install//userdetails.xml");
		if (file.exists()) {
			// load the file
			try {
				prop.loadFromXML(new FileInputStream(file));
			} catch (InvalidPropertiesFormatException e) {
				e.printStackTrace();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			// print out all the properties
			for (String name : prop.stringPropertyNames()) {
				System.out
						.println(name + "=" + prop.getProperty(name));
			}
		} else {
			System.err.println("Error: No file found : ");
		}

	}

}

name=Naveen
country=US

Java – Writing XML using JAXB

 
package com.pwn.xml;

import java.io.File;
import javax.xml.bind.JAXB;

public class XMLWriter {

	public static void main(String[] args) {
		User user = new User();
		user.setName("Johnny");
		user.setUserID(12313);
		try {
			JAXB.marshal(user,
					new File("C://install//userDetail.xml"));
		} catch (Exception e) {
			System.err.println(
					"Exception occurred while writing in XML!");
		} finally {
			System.out.println("XML created");
		}

	}

}


@XmlRootElement
class User {
	private long userID;
	private String name;

	public long getUserID() {
		return userID;
	}

	public void setUserID(long userID) {
		this.userID = userID;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}