Export
to Excel a Report on Web
Exporting a report
in Excel format is usually a requirement in most of the web
application now a days. The administrator or the manager needs to
generate different reports in excel, pdf or word format. Here is a
common utility to generate report in excel.
- Export report in Excel format.
- Generic way to generate report with MxN rows & columns
Download the source
code here:
Technologies and tools used:
- Java 1.6 or above
- Spring 3.1
- Eclipse (with dynamic web application support)
Step 1:
Set up a Java EE based web application
with MVC support. I have used Spring 3.1 MVC in this example.
Step 2:
Create a controller class and a
method (end point) to serve the request for export. Usually the
method accept POST request to submit a form based on which the report
should be generated. In this example, the generated report contains
static data of employees. The controller retrieves (static) data from
business layer and invokes the utility to generate report.
|
@Controller
@RequestMapping("/excel")
public
class
ExcelController {
protected
ExcelService excelService
= new
ExcelService();
@RequestMapping(value
= "/export")
public
ModelAndView save(HttpServletResponse response) throws
IOException {
System.out.println("Export
called");
List<Employee>
employeeList = excelService.getAllEmployees();
…...
…...
…...
return
null;
}
|
Step 3:
Iterate the list of employees
(Collection of domain objects) and convert each employee's (domain
object's) details into list of String (List<String>).
Finally prepare List of List of String.
|
List<List<String>>
employeeDetailsMasterList = convertToListOfList(employeeList);
private
List<List<String>> convertToListOfList(List<Employee>
employeeList) {
List<List<String>>
masterList = new
ArrayList<List<String>>();
ListIterator<Employee>
iterator = employeeList.listIterator();
while(iterator.hasNext())
{
Employee
emp = iterator.next();
List<String>
empDetails = new
ArrayList<String>();
empDetails.add(iterator.previousIndex()+1+"");
empDetails.add(emp.getName());
empDetails.add(emp.getDept());
empDetails.add(emp.getDoj());
masterList.add(empDetails);
}
return
masterList;
}
|
Step 4:
Prepare a list of name of column /
header. Add this list in the list prepared in step 3 above at zeroth
positions.
|
List<String>
myReportHeader = getTrainingReportHeader();
employeeDetailsMasterList.add(0,
myReportHeader);
|
Step 5:
Prepare the workBook object from the
List of List of String. Use the POI api to generate the work book
object as shown below
|
HSSFWorkbook
workBook = ExcelUtils.prepareWorkBook(employeeDetailsMasterList,
"Test
Report");
|
Step 6:
Finally write the work book on the
response object. This is allow the user to download the report in
excel format.
|
ExcelUtils.generateReport(response,
workBook, "Test Report");
|
Putting all
the above steps together, we have following three important methods:
ExcelController.java
|
@Controller
@RequestMapping("/excel")
public
class
ExcelController {
protected
ExcelService excelService
= new
ExcelService();
@RequestMapping(value
= "/export")
public
ModelAndView save(HttpServletResponse response) throws
IOException {
System.out.println("Export
called");
List<Employee>
employeeList = excelService.getAllEmployees();
List<List<String>>
employeeDetailsMasterList = convertToListOfList(employeeList);
List<String>
myReportHeader = getTrainingReportHeader();
employeeDetailsMasterList.add(0,
myReportHeader);
HSSFWorkbook
workBook = ExcelUtils.prepareWorkBook(employeeDetailsMasterList,
"Test Report");
ExcelUtils.generateReport(response,
workBook, "Test Report");
return
null;
}
} |
ExcelUtils.java
|
public
static
void
generateReport(HttpServletResponse response,
HSSFWorkbook
workBook, String workSheetName) throws
IOException {
//
set specific response content type for xls
files.
response.setContentType("application/vnd.ms-excel;
charset=utf-8");
response.setHeader("Content-Disposition",
"attachment; filename="
+
workSheetName + ".xls");
/*
* With this we let the browser know what the default name should
be for
* the user to save this file.
*/
ServletOutputStream
servletOutputStream =
response.getOutputStream(); workBook.write(servletOutputStream);
}
|
ExcelUtils.java
|
public
static
HSSFWorkbook prepareWorkBook(List<List<String>>
reportList, String workSheetName) {
final
String METHOD_NAME = "convertListToExcel";
System.out.println("Entered
" + METHOD_NAME);
if
(StringUtils.isNullOrBlank(workSheetName))
{
workSheetName
= REPORT_EXCEL_SHEET_NAME_DEFAULT;
}
int
lengthOfColumns = ((List<String>) reportList.get(0)).size();
//
Do the POI Things to generate the Excel File create a new workbook
HSSFWorkbook
workBook = new
HSSFWorkbook();
HSSFSheet
sheet = workBook.createSheet(workSheetName);
/*******************************************
* This Part Contains The Cell Formatting Stuff.
* ********************************************/
//
Create a Font For Header
HSSFFont
headerFont = workBook.createFont();
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName(DEFAULT_FONT_FACE);
//
create a style for the header Columns
HSSFCellStyle
columnHeaderStyle = workBook.createCellStyle();
columnHeaderStyle.setFont(headerFont);
//
Create a Font For Data Rows
HSSFFont
rowFont = workBook.createFont();
rowFont.setFontName(DEFAULT_FONT_FACE);
//
create a style for the Records In Next Rows
HSSFCellStyle
rowCellStyle = workBook.createCellStyle();
rowCellStyle.setFont(rowFont);
/**********************************************/
HSSFRow[]
headerRow = new
HSSFRow[reportList.size()];
ListIterator<List<String>>
outerListIterator = reportList
.listIterator();
//
Create Header Row
if
(outerListIterator.hasNext()) {
int
rowIndex = outerListIterator.nextIndex();
List<String>
innerList = outerListIterator.next();
headerRow[rowIndex]
= sheet.createRow((short)
rowIndex);
HSSFCell[]
headerColumns = new
HSSFCell[lengthOfColumns];
ListIterator<String>
innerListIterator = innerList.listIterator();
while
(innerListIterator.hasNext()) {
int
columnIndex = innerListIterator.nextIndex();
headerColumns[columnIndex]
= headerRow[rowIndex]
.createCell(columnIndex);
String
cellValue = innerListIterator.next();
headerColumns[columnIndex].setCellValue(cellValue);
headerColumns[columnIndex].setCellStyle(columnHeaderStyle);
}
}
//
Create other Row(s)
while
(outerListIterator.hasNext()) {
int
rowIndex = outerListIterator.nextIndex();
List<String>
innerList = outerListIterator.next();
headerRow[rowIndex]
= sheet.createRow((short)
rowIndex);
HSSFCell[]
headerColumns = new
HSSFCell[innerList.size()];
//
Create Data for the Header Row
ListIterator<String>
innerListIterator = innerList.listIterator();
while
(innerListIterator.hasNext()) {
int
columnIndex = innerListIterator.nextIndex();
headerColumns[columnIndex]
= headerRow[rowIndex]
.createCell(columnIndex);
String
cellValue = innerListIterator.next();
headerColumns[columnIndex].setCellValue(cellValue);
headerColumns[columnIndex].setCellStyle(rowCellStyle);
}
}
return
workBook;
}
|
