我们在实际的项目开发过程中总是会遇到导出数据功能，就会经常用到HSSFWorkbook这个类，当然网上也可以搜到很多资料，话不多说，上干货！！
1.生成简单的excle表格
public String scanList(HttpServletResponse res) {
List eventList1=new ArrayList();
for(int i=0;i<5;i++){
EventReportPojo eventReportPojo1=new EventReportPojo();
eventReportPojo1.setDate("2018-04-11--"+i);
eventReportPojo1.setEventCount(3+i);
eventReportPojo1.setAcrossCount(3+i);
eventReportPojo1.setRoleTriggerCount(3+i);
eventReportPojo1.setRoleTriggerRatio("100%");
eventReportPojo1.setRuleName("测试"+i);
eventReportPojo1.setRuleCount(3+i);
eventReportPojo1.setRuleRatio("100%");
eventReportPojo1.setWarning("通过"+i);
eventReportPojo1.setReleaseName("测试"+i);
eventList1.add(eventReportPojo1);
}
this.downLoadTemplate(eventList1, res)；
}
/**
* 下载模板方法(excel方式)
* 说明:模版下载后，将用户自定义的列名显示到模版上，最后一列加上主键名
*/
public void downLoadTemplate(List eventList, HttpServletResponse res) {
try {
HSSFWorkbook wb = new HSSFWorkbook();// 第一步，创建一个webbook，对应一个Excel文件
HSSFSheet sheet = wb.createSheet("sheet1");// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFRow row = sheet.createRow((int) 0);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFCellStyle style = wb.createCellStyle();// 第四步，创建单元格，并设置值表头 设置表头居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell(0);
cell.setCellValue("日期");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("事件总数");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("通过数量");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("规则触发总数");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("规则触发占比");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("触发的规则名称");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("该规则触发次数");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("该规则触发占比");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("报警名称");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("发布模式");
cell.setCellStyle(style);
// 写入文件
if(eventList.size()>0){
for(int i=0;i<eventList.size();i++){
row = sheet.createRow((int) i + 1);
EventReportPojo eventReportPojo1=(EventReportPojo) eventList.get(i);
row.createCell(0).setCellValue(eventReportPojo1.getDate());
row.createCell(1).setCellValue(eventReportPojo1.getEventCount());
row.createCell(2).setCellValue(eventReportPojo1.getAcrossCount());
row.createCell(3).setCellValue(eventReportPojo1.getRoleTriggerCount());
row.createCell(4).setCellValue(eventReportPojo1.getRoleTriggerRatio());
row.createCell(5).setCellValue(eventReportPojo1.getRuleName());
row.createCell(6).setCellValue(eventReportPojo1.getRuleCount());
row.createCell(7).setCellValue(eventReportPojo1.getRuleRatio());
row.createCell(8).setCellValue(eventReportPojo1.getWarning());
row.createCell(9).setCellValue(eventReportPojo1.getReleaseName());
}
}
String fileName = new StringBuilder().append("report").append(".xls").toString(); // 文件名和格式
OutputStream os = null; // 输出流定义
try {
os = res.getOutputStream();
res.reset();
res.setHeader("Content-Disposition", "attachment; filename=" + fileName);
res.setContentType("application/octet-stream; charset=utf-8");
wb.write(os);
os.flush();
} catch (IOException e) {
logger.error("自定义数据集下载模版失败！");
} finally {
if (os != null) {
try {
os.close(); // 关闭输出流
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
数据是模拟数据，运行后报表生成如下：
image.png
2.合并单元格
有时候我们需要的不只是这种简单的表格，需要合并单元格
public String scanList(HttpServletResponse res) {
List eventList1=new ArrayList();
for(int i=0;i<5;i++){
EventReportPojo eventReportPojo1=new EventReportPojo();
eventReportPojo1.setDate("2018-04-11--"+i);
eventReportPojo1.setEventCount(3+i);
eventReportPojo1.setAcrossCount(3+i);
eventReportPojo1.setRoleTriggerCount(3+i);
eventReportPojo1.setRoleTriggerRatio("100%");
eventReportPojo1.setRuleName("测试"+i);
eventReportPojo1.setRuleCount(3+i);
eventReportPojo1.setRuleRatio("100%");
eventReportPojo1.setWarning("通过"+i);
eventReportPojo1.setReleaseName("测试"+i);
eventList1.add(eventReportPojo1);
}
EventReportPojo eventReportPojo1=new EventReportPojo();
eventReportPojo1.setEventCount(2);
eventReportPojo1.setAcrossCount(2);
eventReportPojo1.setRoleTriggerCount(2);
eventReportPojo1.setRoleTriggerRatio("100%");
List<EventReportPojo> list=new ArrayList<>();
for(int i=0;i<2;i++){
EventReportPojo eventReportPojo2=new EventReportPojo();
eventReportPojo2.setRuleName("测试"+i+1);
eventReportPojo2.setRuleCount(3);
eventReportPojo2.setRoleTriggerRatio("100%");
eventReportPojo2.setWarning("ceshi"+i+1);
eventReportPojo2.setReleaseName("线上");
list.add(eventReportPojo2);
}
this.downLoadTemplate(eventReportPojo1,list,eventList1, res);
}
/**
* 下载模板方法(excel方式)
* 说明:模版下载后，将用户自定义的列名显示到模版上，最后一列加上主键名
*/
public void downLoadTemplate(EventReportPojo eventReportPojo,List totalList,List eventList, HttpServletResponse res) {
try {
HSSFWorkbook wb = new HSSFWorkbook();// 第一步，创建一个webbook，对应一个Excel文件
HSSFSheet sheet = wb.createSheet("sheet1");// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFRow row = sheet.createRow((int) 0);// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFCellStyle style = wb.createCellStyle();// 第四步，创建单元格，并设置值表头 设置表头居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCell cell = row.createCell(0);
cell.setCellValue("日期");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("事件总数");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("通过数量");
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("规则触发总数");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("规则触发占比");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("触发的规则名称");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("该规则触发次数");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("该规则触发占比");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("报警名称");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("发布模式");
cell.setCellStyle(style);
// 写入文件
if(eventList.size()>0){
for(int i=0;i<eventList.size();i++){
row = sheet.createRow((int) i + 1);
EventReportPojo eventReportPojo1=(EventReportPojo) eventList.get(i);
row.createCell(0).setCellValue(eventReportPojo1.getDate());
row.createCell(1).setCellValue(eventReportPojo1.getEventCount());
row.createCell(2).setCellValue(eventReportPojo1.getAcrossCount());
row.createCell(3).setCellValue(eventReportPojo1.getRoleTriggerCount());
row.createCell(4).setCellValue(eventReportPojo1.getRoleTriggerRatio());
row.createCell(5).setCellValue(eventReportPojo1.getRuleName());
row.createCell(6).setCellValue(eventReportPojo1.getRuleCount());
row.createCell(7).setCellValue(eventReportPojo1.getRuleRatio());
row.createCell(8).setCellValue(eventReportPojo1.getWarning());
row.createCell(9).setCellValue(eventReportPojo1.getReleaseName());
}
}
for(int i=0;i<totalList.size();i++){
row = sheet.createRow((int)eventList.size()+i+1);
EventReportPojo eventReportPojo1=(EventReportPojo) totalList.get(i);
row.createCell(0).setCellValue("汇总");
row.createCell(1).setCellValue(eventReportPojo.getEventCount());
row.createCell(2).setCellValue(eventReportPojo.getAcrossCount());
row.createCell(3).setCellValue(eventReportPojo.getRoleTriggerCount());
row.createCell(4).setCellValue(eventReportPojo.getRoleTriggerRatio());
row.createCell(5).setCellValue(eventReportPojo1.getRuleName());
row.createCell(6).setCellValue(eventReportPojo1.getRuleCount());
row.createCell(7).setCellValue(eventReportPojo1.getRuleRatio());
row.createCell(8).setCellValue(eventReportPojo1.getWarning());
row.createCell(9).setCellValue(eventReportPojo1.getReleaseName());
}
sheet.addMergedRegion(new CellRangeAddress(eventList.size()+1,eventList.size()+totalList.size(),0,0));
sheet.addMergedRegion(new CellRangeAddress(eventList.size()+1,eventList.size()+totalList.size(),1,1));
sheet.addMergedRegion(new CellRangeAddress(eventList.size()+1,eventList.size()+totalList.size(),2,2));
sheet.addMergedRegion(new CellRangeAddress(eventList.size()+1,eventList.size()+totalList.size(),3,3));
sheet.addMergedRegion(new CellRangeAddress(eventList.size()+1,eventList.size()+totalList.size(),4,4));
String fileName = new StringBuilder().append("report").append(".xls").toString(); // 文件名和格式
OutputStream os = null; // 输出流定义
try {
os = res.getOutputStream();
res.reset();
res.setHeader("Content-Disposition", "attachment; filename=" + fileName);
res.setContentType("application/octet-stream; charset=utf-8");
wb.write(os);
os.flush();
} catch (IOException e) {
logger.error("自定义数据集下载模版失败！");
} finally {
if (os != null) {
try {
os.close(); // 关闭输出流
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
生成的表格如下：
image.png
合并单元格用的方法是addMergedRegion，关于一些参数的设置可以参考源码
注：1.网上的一些关于合并单元格的方法参数是Region，但是这个方法已经弃用了，我们目前用的是CellRangeAddress，在HSSFSheet.class中两个方法的源码如下：
/** @deprecated */
public int addMergedRegion(Region region) {
return this._sheet.addMergedRegion(region.getRowFrom(), region.getColumnFrom(), region.getRowTo(), region.getColumnTo());
}
public int addMergedRegion(CellRangeAddress region) {
region.validate(SpreadsheetVersion.EXCEL97);
return this._sheet.addMergedRegion(region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn());
}
2.关于生成的excle表格的文件名，我们用的是report.xls作为默认的文件名，如果用中文的话可能会出现文件名乱码的问题，有一些解决的方法可供参考
res.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1"));
