Spring RestFul Web service with Excel File response using JExcel API and Apache POI

JExcelApi is a Java library that is dedicated for reading, writing and modifying Excel spreadsheets. It supports Excel 2003 file format and older versions. You can download JExcelApi from the following link:

To work with JExcelApi, you need to add its only jar file: jxl.jar - to your project’s classpath. Download JExcel

Here we are going to create spring API to download Excel file as response using JExcel Api.

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.servlet.ModelAndView;

import com.demo.api.exl.services.ExcelOutputService;

@RestController

@RequestMapping(value="/exceloutput")

public class ExcelOutputServiceController {

    @Autowired

    ExcelOutputService excelOutputService;

   

    @RequestMapping(value="/download", method=RequestMethod.GET)

    public ModelAndView downloadExcelOutputExl(HttpServletResponse response){

      

       excelOutputService.createExcelOutputExcel(response);

       return null;

    }

}

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

@Service("excelOutputService")

public class ExcelOutputServiceImpl implements ExcelOutputService{

    private static final Logger LOGGER = Logger.getLogger(ExcelOutputServiceImpl.class);

   

    @Override

    public WritableWorkbook createExcelOutputExcel(HttpServletResponse response) {

       String fileName = "Excel_Output.xls";

       WritableWorkbook writableWorkbook = null;

       try {

           response.setContentType("application/vnd.ms-excel");

           response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

           writableWorkbook = Workbook.createWorkbook(response.getOutputStream());

          

           WritableSheet excelOutputsheet = writableWorkbook.createSheet("Excel Output", 0);

           addExcelOutputHeader(excelOutputsheet);

           writeExcelOutputData(excelOutputsheet);

           

           writableWorkbook.write();

           writableWorkbook.close();

       } catch (Exception e) {

           LOGGER.error("Error occured while creating Excel file", e);

       }

       return writableWorkbook;

    }

   

    private void addExcelOutputHeader(WritableSheet sheet) throws RowsExceededException, WriteException{

       // create header row

        sheet.addCell(new Label(0, 0, "Column 1"));

        sheet.addCell(new Label(1, 0, " Column 2"));

        sheet.addCell(new Label(2, 0, " Column 3"));

        sheet.addCell(new Label(3, 0, " Column 4"));

        sheet.addCell(new Label(4, 0, " Column 5"));

        sheet.addCell(new Label(5, 0, " Column 6"));

        sheet.addCell(new Label(6, 0, " Column 7"));

        sheet.addCell(new Label(7, 0, " Column 8"));

        sheet.addCell(new Label(8, 0, " Column 9"));

        sheet.addCell(new Label(9, 0, " Column 10"));

        sheet.addCell(new Label(10, 0, " Column 11"));

    }

   

    private void writeExcelOutputData(WritableSheet sheet) throws RowsExceededException, WriteException{

             

       for(int rowNo = 1; rowNo<=10; rowNo++){

              sheet.addCell(new Label(0, rowNo, “Col Data ”+ (rowNo+0)));

              sheet.addCell(new Label(1, rowNo, “Col Data ”+ (rowNo+1)));

              sheet.addCell(new Label(2, rowNo, “Col Data ”+ (rowNo+2)));

              sheet.addCell(new Label(3, rowNo, “Col Data ”+ (rowNo+3)));

              sheet.addCell(new Label(4, rowNo, “Col Data ”+ (rowNo+4)));

              sheet.addCell(new Label(5, rowNo, “Col Data ”+ (rowNo+5)));

              sheet.addCell(new Label(6, rowNo, “Col Data ”+ (rowNo+6)));  

              sheet.addCell(new Label(7, rowNo, “Col Data ”+ (rowNo+7)));

              sheet.addCell(new Label(8, rowNo, “Col Data ”+ (rowNo+8)));

              sheet.addCell(new Label(9, rowNo, “Col Data ”+ (rowNo+9)));

              sheet.addCell(new Label(10, rowNo, “Col Data ”+ (rowNo+10)));

       }

    }

}

Now call the service as :

http://localhost:8080/demo/excelOutputService/download

This will download the Excel file to download folder or show download popup window.







Create Excel using Apache POI

POI-XSSF - Java API To Access Microsoft Excel Format Files

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

Prerequisites

·                  DK1.5 or later versions

·                  Apache POI library (http://poi.apache.org/download.html ) 

Add following library to classpath:

“C:\poi-3.9\poi-3.9-20121203.jar;”

“C:\poi-3.9\poi-ooxml-3.9-20121203.jar;”

“C:\poi-3.9\poi-ooxml-schemas-3.9-20121203.jar;”

“C:\poi-3.9\ooxml-lib\dom4j-1.6.1.jar;”

“C:\poi-3.9\ooxml-lib\xmlbeans-2.3.0.jar;.;”

import org.apache.log4j.Logger;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public XSSFWorkbook createExcelOutputFile(){

XSSFWorkbook workbook = null;

    try {          

           

        ClassLoader loader = getClass().getClassLoader();

File file = new File(loader.getResource("Excel_Output.xlsx").getFile());  //file should be at classpath

        FileInputStream is = new FileInputStream(file);

           

        // Get the workbook instance for XLSX file

        workbook = new XSSFWorkbook(is);

XSSFSheet rankerSheet1 = workbook.getSheetAt(0);

        writeExcelOutputData(rankerSheet1, workbook);         

        is.close();

           

    } catch (FileNotFoundException e) {

            LOGGER.error(e);

   } catch (IOException e) {

            LOGGER.error(e);

   }

        return workbook;

}

private void writeExcelOutputData(XSSFSheet rankerSheet, XSSFWorkbook worksheet){

           

            XSSFRow row1 = rankerSheet.createRow(1);

            row1.createCell(0).setCellValue(1);

            row1.createCell(1).setCellValue(1234

            row1.createCell(2).setCellValue(“Test Excel”);

            row1.createCell(3).setCellValue(“Address”);

           

            XSSFCell cell4 = row1.createCell(4);

               cell4.setCellValue(10.00);

            XSSFCellStyle style = worksheet.createCellStyle();

            style.setDataFormat((short)8);      // this will format cell with $ sign Ex: $10.00

            cell4.setCellStyle(style);  

           

           

          //Creating Data format %

            XSSFCellStyle percentStyle = worksheet.createCellStyle();

            percentStyle.setDataFormat(worksheet.createDataFormat().getFormat("0.0%"));

           

            XSSFCell cell5 = row1.createCell(5);

            cell5.setCellStyle(percentStyle);      

            cell5.setCellValue(20.00);   

    }