I've created some classes to abstract Excel document creation away from HSSF api and reduce the amount of code needed.
The Enum class will represent the columns headers and the column index for which
data should be allocated. Adding a data to an Excel spreadsheet is as simple as the following:
session.addColumn(EnumClass.EnumConstant, "your data");
The session class will automatically figure out the column index based on the order of the enum constants defined in the enum class. The beauty of this is if you decide to rearrange the order of the columns, then all you have to do is rearrange the order of the enum constants defined in the enum class.
Here are the classes:
This is an
example class using the ExcelSession. See how simple it is to use?
package au.edu.apf.phenomebank.report;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;
import au.edu.apf.phenomebank.appservice.InventoryAppService;
import au.edu.apf.phenomebank.db.StrainFacilityCount;
import au.edu.apf.phenomebank.helper.ExcelSession;
import au.edu.apf.phenomebank.inventory.StorageFacility;
import au.edu.apf.phenomebank.inventory.Technician;
import au.edu.apf.phenomebank.web.ExcelReportingController;
import au.edu.apf.phenomebank.web.ParamNames;
import au.edu.apf.phenomebank.web.Util;
/**
* Produces an Excel Document for reporting the number of mice frozen down for each Strain
* at a particular facility
*
*
* @author Philip Wu
*/
public class NumFrozenStrainFacilityExcelView extends AbstractExcelView {
private InventoryAppService inventoryService;
public InventoryAppService getInventoryService() {
return inventoryService;
}
public void setInventoryService(InventoryAppService inventoryService) {
this.inventoryService = inventoryService;
}
@Override
protected void buildExcelDocument(Map map, HSSFWorkbook workbook,
HttpServletRequest req, HttpServletResponse resp) throws Exception {
// Create the Excel session
ExcelSession session = new ExcelSession(workbook, FrozenColumn.class);
// create the table headers
session.nextRow();
session.createHeaders();
// Add the results
SimpleDateFormat dateFormatter = new SimpleDateFormat("dd/MM/yyyy");
for (StorageFacility facility : StorageFacility.values()) {
List<StrainFacilityCount> results = inventoryService.findNumFrozernByStrainFacility(facility);
for (StrainFacilityCount result : results) {
session.nextRow();
session.addColumn(FrozenColumn.STRAIN_ID, result.getStrainId());
session.addColumn(FrozenColumn.STRAIN_NAME, result.getStrainName());
session.addColumn(FrozenColumn.NUM_MICE, result.getNumFrozen());
if (result.getDateOfFreezing() != null)
session.addColumn(FrozenColumn.DATE_OF_FREEZING, dateFormatter.format(result.getDateOfFreezing()));
session.addColumn(FrozenColumn.FACILITY, facility);
}
}
Util.setAttachmentResponse(resp, ExcelReportingController.excelViews.get(EnumReportType.STRAIN_INVENTORY));
}
}
enum FrozenColumn {
STRAIN_ID("Strain ID"),
STRAIN_NAME("Strain name"),
NUM_MICE("# Mice"),
DATE_OF_FREEZING("Date of Freezing"),
FACILITY("Facility")
;
private String value;
private FrozenColumn(String value) {
this.value = value;
}
public String toString() {
return value;
}
}
This is the
ExcelSession class:
package au.edu.apf.phenomebank.helper;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* Excel session as a framework for simplifying Excel document creation
*
*
* @author Philip Wu
*/
public class ExcelSession {
/**
* Excel workbook
*/
private HSSFWorkbook workbook;
private HSSFSheet sheet;
/**
* The enum class
*/
private Class enumClass;
/**
* The column index location for each enum value
*/
private Map<Object, Integer> indexMap;
/**
* The current row
*/
private HSSFRow curRow;
/**
* The current row number
*/
private int rowNum;
public ExcelSession (HSSFWorkbook workbook, Class enumClass, String worksheet) {
if (worksheet == null || worksheet.length() == 0)
worksheet = "worksheet";
this.enumClass = enumClass;
this.workbook = workbook;
this.sheet = workbook.createSheet(worksheet);
indexMap = EnumHelper.createIndexMap(enumClass);
}
public ExcelSession (HSSFWorkbook workbook, Class enumClass) {
this(workbook, enumClass, null);
}
/**
* Creates the headers for this Excel document based on the enum class
*/
public void createHeaders() {
// Create the headers
//HSSFRow row = sheet.createRow(rowNum);
if (curRow == null) {
nextRow();
}
ExcelHelper.createHeaders(workbook, curRow, enumClass.getEnumConstants());
}
/**
* Moves down to the next row
*/
public void nextRow() {
curRow = sheet.createRow(rowNum);
rowNum++;
}
/**
* Adds a column to the current row
* @param row
* @param enumColumn
* @param value
*/
public void addColumn(Object enumColumn, Object value) {
if (value != null)
ExcelHelper.addColumnDefault(curRow, indexMap.get(enumColumn), value.toString() , "");
}
/**
* Provide the ability specify the column index of where to add the value
* @param columnIndex
* @param value
*/
public void addColumn(int columnIndex, Object value, HSSFFont font) {
if (value != null)
ExcelHelper.addColumn(curRow, columnIndex, value.toString() , font);
}
}
Here is the
ExcelHelper class:
package au.edu.apf.phenomebank.helper;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* Helper for Excel objects
*
*
* @author Philip Wu
*/
public class ExcelHelper {
/**
* A Helper method to create an excel worksheet
* @param workbook
* @param sheetName
* @param columns
* @return
*/
public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName, String[] columns) {
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
createHeaders(workbook, row, columns);
return sheet;
}
/**
* Creates headers for the row using the following columns
* @param workbook
* @param row
* @param columns
*/
public static void createHeaders(HSSFWorkbook workbook, HSSFRow row, Object[] columns) {
HSSFFont bold = workbook.createFont();
bold.setBoldweight(org.apache.poi.hssf.usermodel.HSSFFont.BOLDWEIGHT_BOLD);
for (int i=0 ; i < columns.length; i++) {
Object fieldHeader = columns[i];
HSSFRichTextString headerFieldValue = new HSSFRichTextString(fieldHeader.toString());
headerFieldValue.applyFont(bold);
row.createCell(i).setCellValue(headerFieldValue);
}
}
public static void addColumn(HSSFRow row, int index, String value) {
addColumn(row, index, value, null);
}
/**
* Helper method to add a column
* @param row
* @param index
* @param value
* @param defaultValue The default value to use if value is null
*/
public static void addColumnDefault(HSSFRow row, int index, String value, String defaultValue) {
String setValue = defaultValue;
if (value != null)
setValue = value;
addColumn(row, index, setValue);
}
/**
* Adds a column to the row at the specified index with the given value applying
* a font to the text if it exists.
* @param row
* @param index
* @param value
* @param font
*/
public static void addColumn(HSSFRow row, int index, String value, HSSFFont font) {
if (value == null)
return;
// Without replacing \r characters, excel produces square boxes
value = value.replaceAll("\r", "");
value = value.replaceAll("\n", "");
HSSFRichTextString excelValue = new HSSFRichTextString(value);
if (font != null)
excelValue.applyFont(font);
row.createCell(index).setCellValue(excelValue);
}
}
The
EnumHelper class:
package au.edu.apf.phenomebank.helper;
import java.util.HashMap;
import java.util.Map;
/**
* Helper class for Enum types
*
*
* @author Philip Wu
*/
public class EnumHelper {
// The index position of the ExcelColumn in the enumerated constants
public static int indexOf(Object enumType) {
int index = 0;
for (Object constant : enumType.getClass().getEnumConstants()) {
if (constant.equals(enumType))
return index;
index++;
}
return -1; // not found
}
/**
* Creates a map mapping the Enum constant to its index position
* @param enumClass
* @return
*/
public static Map<Object, Integer> createIndexMap(Class enumClass) {
HashMap<Object, Integer> map = new HashMap<Object, Integer>();
int index = 0;
for (Object constant : enumClass.getEnumConstants()) {
map.put(constant, index);
index++;
}
return map;
}
}
