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);
}
