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