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