前言:该文章主要是利用poi去导出excel表格的总结。
- 导出excel核心代码,大致分为6步,可见代码注释。
public void exportRemarkExcel(IllegalWordVideoRemark videoRemark, HttpServletResponse res) throws BusinessErrorException {
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("证据言辞排查");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
row.setHeight((short)480);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
// 指定单元格垂直居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 创建一个居中格式
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeight((short) 220);
style.setFont(font);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
row.setRowStyle(style);
//设置标题头信息
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("录音录像名称");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("录音录像时间");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("办案人员");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("讯问时间");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("批注");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("标签");
cell.setCellStyle(style);
// 第四步,写入实体数据 实际应用中这些数据从数据库得到,
//获取该案件下所有的录像批注
List<IllegalWordVideoRemark> pageRemarkList = this.queryRemarkListbByCaseId(videoRemark);
CaseDTO cases = casesMapper.findByCaseId(videoRemark.getCaseId());
if (pageRemarkList !=null && pageRemarkList.size() > 0) {
//录像批注的序号
int remarkNum = 1;
for (int i = 0; i < pageRemarkList.size(); i++) {
row = sheet.createRow((int) i + 1);
IllegalWordVideoRemark remark = pageRemarkList.get(i);
// 第五步,创建单元格,并设置值
//序号
row.createCell((short) 0).setCellValue(remarkNum++);
//录像名称
row.createCell((short) 1).setCellValue(remark.getVideoName());
//批注时间轴
row.createCell((short) 2).setCellValue(remark.getRecordVideoTime());
//办案人员
row.createCell((short) 3).setCellValue(remark.getRoleEditHistory());
//讯问时间
row.createCell((short) 4).setCellValue(remark.getVideoTime());
//记录
row.createCell((short) 5).setCellValue(remark.getContent());
//标签
row.createCell((short) 6).setCellValue(remark.getLabelContents());
}
}
// 第六步,将文件存到指定位置
try {
String fileName = URLEncoder.encode(fileName, "UTF-8");
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "ISO-8859-1"));
res.setCharacterEncoding("UTF-8");
ServletOutputStream out = res.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
}
} catch (Exception e) {
logger.error("导出全部批注失败", e);
}
}
路漫漫其修远兮,吾将上下而求索