最近项目要用到excel导出功能，之前也写过类似的代码。因为这次项目中多次用到excel导出。这次长了记性整理了一下 分享给大伙
欢迎一起讨论
生成excel的主工具类：
public class ExcelFactory<T> {
private static final String EXCEL_FONT_FAMILY_SETTING = "Monaco"; //设置字体
private static final int EXCEL_FONT_SIZE_SETTING = 12; //设置字体大小
private WritableWorkbook writableWorkbook = null;
private WritableSheet writableSheet = null;
public WritableWorkbook createExcel(OutputStream os, Excel excel,
List<String> header, List<T> datas, ExcelMapper<T> mapper)
throws RowsExceededException, WriteException { //生成excel
try {
writableWorkbook = Workbook.createWorkbook(os);
writableSheet = writableWorkbook.createSheet(excel.getSheetName(),
excel.getSheetNum());
SheetSettings settings = writableSheet.getSettings();
settings.setVerticalFreeze(1);
// Write the title
if (header != null && header.size() > 0) {
for (int i = 0; i < header.size(); i++) {
writableSheet.addCell(new Label(i, 0, header.get(i),
setHeaderCellStyle(new CellStyle(EXCEL_FONT_FAMILY_SETTING, EXCEL_FONT_SIZE_SETTING))));
}
}
// Write data to file
if (datas != null && datas.size() > 0) {
for (int i = 1; i <= datas.size(); i++) {
mapper.mapToExcel(writableSheet, datas.get(i - 1),
setBodyCellStyle(new CellStyle(EXCEL_FONT_FAMILY_SETTING, EXCEL_FONT_SIZE_SETTING)), i);
}
}
} catch (IOException e) {
return null;
}
return writableWorkbook;
}
private WritableCellFormat setHeaderCellStyle(CellStyle style) throws WriteException {
WritableFont font = new WritableFont(
WritableFont.createFont(style.getFont()), style.getFontSize(), WritableFont.BOLD);
WritableCellFormat cellFormat = setCentre(style, font);
return cellFormat;
}
private WritableCellFormat setBodyCellStyle(CellStyle style) throws WriteException {
WritableFont font = new WritableFont(
WritableFont.createFont(style.getFont()), style.getFontSize(), WritableFont.NO_BOLD);
WritableCellFormat cellFormat = setCentre(style, font);
return cellFormat;
}
private WritableCellFormat setCentre(CellStyle style, WritableFont font)
throws WriteException {
WritableCellFormat cellFormat = new WritableCellFormat(font);
cellFormat.setBackground(style.getBgColor());
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
return cellFormat;
}
}
excel model类：
public class Excel {
private String sheetName;
private int sheetNum;
public Excel() {
}
public Excel(String sheetName, int sheetNum) {
this.sheetName = sheetName;
this.sheetNum = sheetNum;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public int getSheetNum() {
return sheetNum;
}
public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}
}
CellStyle的model类：
public class CellStyle {
private String font;
private int fontSize;
private Colour bgColor = Colour.WHITE;
public CellStyle() {}
public CellStyle(String font, int fontSize) {
this.font = font;
this.fontSize = fontSize;
}
public Colour getBgColor() {
return bgColor;
}
public void setBgColor(Colour bgColor) {
this.bgColor = bgColor;
}
public int getFontSize() {
return fontSize;
}
public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}
public String getFont() {
return font;
}
public void setFont(String font) {
this.font = font;
}
}
ExcelMapper的接口类（用于写数据）：
public interface ExcelMapper<T> {
void mapToExcel(WritableSheet sheet, T t, WritableCellFormat wcf, int rowNum);
}
举个例子：
public class FinanceExcelMapper implements ExcelMapper<Finance>{
public void mapToExcel(WritableSheet sheet, Finance finance, WritableCellFormat wcf, int rowNum) {
try {
sheet.addCell(new Label(0, rowNum, String.valueOf(finance.getStudentCourseId())));
sheet.addCell(new Label(1, rowNum, finance.getCardNum()));
sheet.addCell(new Label(2, rowNum, finance.getStuName()));
DefaultDictionaryManager manager = DefaultDictionaryManager.getInstance();
sheet.addCell(new Label(3, rowNum, manager.dictionary(finance.getSignUpComeFrom(), "signUpComeFrom").getItemValue()));
sheet.addCell(new Label(4, rowNum, finance.getDepartmentName()));
sheet.addCell(new Label(5, rowNum, finance.getMajorName()));
sheet.addCell(new Label(6, rowNum, finance.getCourseName()));
sheet.addCell(new Label(7, rowNum, String.valueOf(finance.getCourseTuition())));
sheet.addCell(new Label(8, rowNum, manager.dictionary(finance.getCourseDiscount(), "courseDiscount").getItemValue()));
sheet.addCell(new Label(9, rowNum, String.valueOf(finance.getActualTuition())));
sheet.addCell(new Label(10, rowNum, finance.getFinanceUser()));
sheet.addCell(new Label(11, rowNum, finance.getFinanceTime().toString()));
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
然后在controller调用传值，然后字节流读取 在写到输出流里就好了。