开发后台系统的过程中,常常有导出Excel的需求,虽然程序早已上线,闲暇之时也觉应总结。有关更多POI的文档资料,参考http://poi.apache.org/。Apache POI是Apache软件基金会的开放源码函式库,POI提供给Java程式对Microsoft Office格式档案读和写的功能。HSSF提供读写Mircosoft Excel XLS格式档案的功能,HSSF是Horrible(可怕的) Spreadsheet(电子表格) Format的缩写。是在JSP页面中添加如下Code:
前端页面显示一个如图所示的按钮:
然后添加JS代码:
$(document).ready(function(){
$("#exportExcel").click(function(){
var spId = ${spId};
var addTimeStart = '';
var addTimeEnd = '';
var url="/complaint/export_excel.do?";
url += "spId="+spId;
url += "&addTimeStart=" +addTimeStart;
url += "&addTimeEnd=" + addTimeEnd;
window.location.href=url;
})
})
在控制层中添加如下Java代码:
@RequestMapping(value = "/complaint/export_excel")
public void exportExcel(HttpServletResponse _response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook(); //生成Excel工作薄
Map style = createStyles(workbook);
HSSFSheet sheet = workbook.createSheet("通道平均信息分析报表"); //生成工作表
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); //合并单元格
sheet.setDefaultColumnWidth(25); //设置默认列宽度
HSSFRow row0 = sheet.createRow(0);
HSSFCell titleCell = row0.createCell(0);
titleCell.setCellValue("通道平均信息分析报表");
titleCell.setCellStyle(style.get("title"));
HSSFRow row1 = sheet.createRow(1);
HSSFCell headerCell0 = row1.createCell(0);
headerCell0.setCellValue("时间");
headerCell0.setCellStyle(style.get("header"));
HSSFCell headerCell1 = row1.createCell(1);
headerCell1.setCellValue("订单确认率");
headerCell1.setCellStyle(style.get("header"));
HSSFCell headerCell2 = row1.createCell(2);
headerCell2.setCellValue("PO成功率");
headerCell2.setCellStyle(style.get("header"));
HSSFCell headerCell3 = row1.createCell(3);
headerCell3.setCellValue("MO成功率");
headerCell3.setCellStyle(style.get("header"));
HSSFCell headerCell4 = row1.createCell(4);
headerCell4.setCellValue("MR成功率");
headerCell4.setCellStyle(style.get("header"));
HSSFCell headerCell5 = row1.createCell(5);
headerCell5.setCellValue("计费转化率");
headerCell5.setCellStyle(style.get("header"));
for(int i=2; i<100; i++){
HSSFRow tempRow = sheet.createRow(i);
tempRow.setHeightInPoints(22);
for(int j=0; j<6; j++){
HSSFCell cellCell = tempRow.createCell(j);
cellCell.setCellValue(i+"--"+j);
if(j%2==0){
cellCell.setCellStyle(style.get("cell1"));
}else{
cellCell.setCellStyle(style.get("cell2"));
}
}
}
FileOutputStream fops = new FileOutputStream("F://Excel//通道平均信息分析报表.xls");
workbook.write(fops);
fops.close();
InputStream inst = new FileInputStream("F://Excel//通道平均信息分析报表.xls");
BufferedInputStream bis = new BufferedInputStream(inst);
OutputStream oust = _response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(oust);
_response.setContentType("application/vnd.ms-excel");
_response.setHeader("Content-disposition", "attachment;filename=YourDownloadExcel.xls");
_response.setCharacterEncoding("utf-8");
int flag = 0;
byte[] tempBuffer = new byte[5 * 1024];
while((flag = bis.read(tempBuffer)) != -1){
bos.write(tempBuffer, 0, flag);
}
bis.close();
bos.close();
inst.close();
oust.close();
}
在同一个类中添加入代码:
/**
* Create a library of cell styles
*/
private static Map createStyles(Workbook wb){
Map styles = new HashMap();
CellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short)25);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER); // 设置水平对齐方式
style.setFillPattern(CellStyle.SOLID_FOREGROUND); //设置填充方式
style.setFillBackgroundColor(HSSFColor.ORANGE.index); //设置背景颜色
style.setFillForegroundColor(HSSFColor.TEAL.index); //设置前景颜色
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short)20);
monthFont.setColor(IndexedColors.WHITE.getIndex());
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.GOLD.index);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.LAVENDER.index);
styles.put("cell1", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.ROSE.index);
styles.put("cell2", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
导出后的Excel如下图所示:
Excel分前景色和后景色,前景色的设置需要调用如下方法:
void org.apache.poi.ss.usermodel.CellStyle.setFillForegroundColor(short bg)
,背景色的设置需要调用方法是:
void org.apache.poi.ss.usermodel.CellStyle.setFillBackgroundColor(short bg)
填充参数bg的一个简单例子----HSSFColor.ORANGE.index。HSSFColor索引对照表见博客最下方的对照表。
设置填充模式使用
void org.apache.poi.ss.usermodel.CellStyle.setFillPattern(short fp)
填充参数fp的一个简单例子-----CellStyle.SOLID_FOREGROUND。CellStyle索引对照表见下方图片:
HSSFColor索引对照表:
.
HSSFColor.GREY_80_PERCENT
.
HSSFColor.INDIGO
.
HSSFColor.PLUM
HSSFColor.BROWN
.
HSSFColor.OLIVE_GREEN
.
HSSFColor.DARK_GREEN
.
HSSFColor.SEA_GREEN
.
HSSFColor.DARK_TEAL
.
HSSFColor.GREY_40_PERCENT
.
HSSFColor.BLUE_GREY
.
HSSFColor.ORANGE
.
HSSFColor.LIGHT_ORANGE
.
HSSFColor.GOLD
.
HSSFColor.LIME
.
HSSFColor.AQUA
.
HSSFColor.LIGHT_BLUE
.
HSSFColor.TAN
.
HSSFColor.LAVENDER
.
HSSFColor.ROSE
.
HSSFColor.PALE_BLUE
.
HSSFColor.LIGHT_YELLOW
.
HSSFColor.LIGHT_GREEN
.
HSSFColor.LIGHT_TURQUOISE
.
HSSFColor.SKY_BLUE
.
HSSFColor.BLUE
.
HSSFColor.TEAL
.
HSSFColor.DARK_RED
.
HSSFColor.VIOLET
.
HSSFColor.TURQUOISE
.
HSSFColor.YELLOW
.
HSSFColor.PINK
.
HSSFColor.DARK_BLUE
.
HSSFColor.LIGHT_CORNFLOWER_BLUE
.
HSSFColor.ROYAL_BLUE
.
HSSFColor.CORAL
.
HSSFColor.ORCHID
.
HSSFColor.LIGHT_TURQUOISE
.
HSSFColor.LEMON_CHIFFON
.
HSSFColor.PLUM
.
HSSFColor.CORNFLOWER_BLUE
.
HSSFColor.GREY_50_PERCENT
.
HSSFColor.GREY_25_PERCENT
.
HSSFColor.TEAL
.
HSSFColor.VIOLET
.
HSSFColor.DARK_YELLOW
.
HSSFColor.DARK_BLUE
.
HSSFColor.GREEN
.
HSSFColor.DARK_RED
.
HSSFColor.TURQUOISE
.
HSSFColor.PINK
.
HSSFColor.YELLOW
.
HSSFColor.BLUE
.
HSSFColor.BRIGHT_GREEN
.
HSSFColor.RED
.
HSSFColor.WHITE
.
HSSFColor.BLACK