在進(jìn)行java開發(fā)時(shí)饭寺，經(jīng)常會(huì)需要進(jìn)行excel報(bào)表的導(dǎo)出。現(xiàn)將我目前使用過的導(dǎo)出excel的方式進(jìn)行以下總結(jié)叫挟。
一艰匙、POI
Apache的POI提供API給java程序進(jìn)行OFFICE的讀寫，這種方式優(yōu)點(diǎn)明顯抹恳。結(jié)構(gòu)非常清晰员凝，逐層創(chuàng)建即可。
image
利用POI進(jìn)行excel生成及導(dǎo)出奋献，主要分為以下步驟：
1健霹、創(chuàng)建excel文檔：通過創(chuàng)建XSSFWorkbook，以及sheet瓶蚂、row糖埋、cell，并對(duì)cell進(jìn)行內(nèi)容填充及格式設(shè)置窃这。關(guān)于這部分處理瞳别，參考有個(gè)牛人的詳細(xì)文章：http://www.cnblogs.com/huajiezh/p/5467821.html。
2、獲取輸出流
3祟敛、將創(chuàng)建好的workbook寫入到輸出流中倍奢。
示例代碼如下：
// 1.生成excel
XSSFWorkbook workbook =returnInfoService.getReturnInfoExport(returnInfoVo);
// 2.輸出
OutputStream outputStream = response.getOutputStream();
// 文件名稱
String defaultName ="回款信息表.xlsx";
String fileName =new String(defaultName.getBytes("GBK"), "ISO8859_1");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.setHeader("content-type", "application/x-msdownload");
// 3.將workbook寫入到輸出流中
workbook.write(outputStream);
生成Excel文檔的共通方法示例：
public static XSSFWorkbookappendSheet(XSSFWorkbook workbook, String sheetName, List header, List fieldName, List data, int offset) {
//如果workbook為空則新建一個(gè)
if (workbook ==null) {
workbook =new XSSFWorkbook();
}
//添加一個(gè)sheet
XSSFSheet sheet;
if (StringUtils.isNotEmpty(sheetName)) {
sheet = workbook.createSheet(sheetName);
}else {
sheet = workbook.createSheet();
}
//設(shè)置表頭列(第一行)
XSSFRow headerRow = sheet.createRow(offset >=0 ? offset :0);
XSSFCellStyle headerStyle = workbook.createCellStyle();
//表頭加粗
XSSFFont font = workbook.createFont();
font.setBold(true);
font.setColor(new XSSFColor(Color.WHITE));
headerStyle.setFont(font);
//居中對(duì)其
headerStyle.setAlignment(HorizontalAlignment.CENTER);
//表頭背景色 藍(lán)色
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
headerStyle.setFillBackgroundColor(new XSSFColor(Color.WHITE));
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
for (int i =0; i < header.size(); i++) {
XSSFCell cell = headerRow.createCell(i);
cell.setCellValue(header.get(i));
cell.setCellStyle(headerStyle);
sheet.setColumnWidth(i, 5120);
}
//設(shè)置數(shù)據(jù)
for (int j =0; j < data.size(); j++) {
Object obj = data.get(j);
Class clz = obj.getClass();
XSSFRow row = sheet.createRow(j + (offset >=0 ? offset +1 :1));
for (int k =0; k < fieldName.size(); k++) {
try {
Field field = clz.getDeclaredField(fieldName.get(k));
XSSFCell cell = row.createCell(k);
Method m = (Method) obj.getClass().getMethod(
"get" +getMethodName(field.getName()));
Object value = m.invoke(obj);// 調(diào)用getter方法獲取屬性值
value =getDescOfVo(fieldName.get(k), value);
if (value ==null || StringUtils.isEmpty(value.toString())) {
continue;
}
String typeClz = field.getType().getSimpleName();
DateFormat format =new SimpleDateFormat(Constant.YY_MM_DD);
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
switch (typeClz) {
case "Timestamp":
cell.setCellValue(format.format(value));
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(cellStyle);
break;
case "Date":
cell.setCellValue(format.format(value));
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(cellStyle);
break;
case "BigDecimal":
cell.setCellValue(((BigDecimal) value).doubleValue());
XSSFDataFormat numberFormat = workbook.createDataFormat();
cellStyle.setDataFormat(numberFormat.getFormat("#,##0.00"));
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
cell.setCellStyle(cellStyle);
break;
default:
cell.setCellStyle(cellStyle);
cell.setCellValue((String) value);
}
}catch (Exception e) {
e.printStackTrace();
}
}
}
return workbook;
}
