Java Excel解析与生成工具
1. Apache POI
简介：POI是Apache下开放源码函式库，用纯Java代码来读取、写入、修改Excel，创建Excel文档可以添加摘要信息，批注，页眉和页脚，使用公式，生成图形，设置密码，数据下拉式菜单，打印基本设置，超链接等，提供了两类API，usermodel(用户模型)和eventusermodel(事件-用户模型)
(1) maven依赖
(2) HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的比较
HSSFWorkbook：一般用于Excel2003版及更早版本(扩展名.xls)的导出，条数上限是65535行，256列；
XSSFWorkbook：一般用于Excel2007版(扩展名.xlsx)的导出，条数上限是1048576行，16384列；
SXSSFWorkbook：一般用于大数据量的导出，实际上上千条数据就可以考虑使用；
(3) 常用类
| 类名 | 说明 |
| Workbook | Excel的文档对象 |
| Sheet | Excel的表单 |
| Row | Excel的行 |
| Cell | Excel的格子单元 |
| Font | Excel的字体 |
| CellStyle | Excel的格子单元样式 |
缺点是消耗内存比较大
2. Excel POI详解
(1) Excel基本操作
String fileName = "导出excel.xls";
public void exportExcel(String fileName) { Workbook workbook = null; if (fileName.endsWith(".xls")) { workbook = new HSSFWorkbook(); } else if (fileName.endsWith(".xlsx")) { workbook = new XSSFWorkbook(); } Sheet sheet = workbook.createSheet(fileName.substring(0, fileName.indexOf("."))); }
(2) 插入图片
FileInputStream fis = null; try { fis = new FileInputStream("G:\\picture\\1.png"); byte[] bytes = new byte[(int) fis.getChannel().size()]; // 读取图片到二进制数组 fis.read(bytes); ClientAnchor clientAnchor = null; if (fileName.endsWith(".xls")) { clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 3, 3); } else if (fileName.endsWith(".xlsx")) { clientAnchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 0, (short) 3, 3); } int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG); Drawing drawing = sheet.createDrawingPatriarch(); drawing.createPicture(clientAnchor, pictureIdx); } catch (Exception e) { e.printStackTrace(); } finally { // 关闭流 if (fis != null) { try { fis.close(); } catch (IOException e) { e.printStackTrace(); } } }
其中：HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)的参数
dx1：起始单元格的x偏移量，如例子中的0表示直线起始位置距B1单元格左侧的距离；
dy1：起始单元格的y偏移量，如例子中的0表示直线起始位置距B1单元格上侧的距离；
dx2：终止单元格的x偏移量，如例子中的0表示直线起始位置距E5单元格左侧的距离；
dy2：终止单元格的y偏移量，如例子中的0表示直线起始位置距E5单元格上侧的距离；
col1：起始单元格列序号，从0开始计算；
row1：起始单元格行序号，从0开始计算，如例子中col1=1,row1=0就表示起始单元格为B1；
col2：终止单元格列序号，从0开始计算；
row2：终止单元格行序号，从0开始计算，如例子中col2=4,row2=4就表示起始单元格为E5；
3. Easy Excel处理
简介：EasyExcel是一个基于Java的简单、省内存的读写Ecel的开源项目。EasyExcel重写了POI对07版excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降到几M，并且再大的excel不会出现内存溢出，但是使用过程中发现该工具处理稍微复杂的Excel还有很多bug。
（1）maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
(2) 实例参考
@GetMapping("/export")
@ApiOperation("导出弱口令信息")
@ApiImplicitParams({
@ApiImplicitParam(name = "scanTime", value = "开始日期", required = true, paramType = "String"),
@ApiImplicitParam(name = "hostId", value = "分支ID", paramType = "Integer"),
@ApiImplicitParam(name = "username", value = "用户名", paramType = "String"),
@ApiImplicitParam(name = "onPassword", value = "是否显示密码(0-不显示， 1-显示)", defaultValue = "0", paramType = "Integer"),
})
public void export(@RequestParam("scanTime") String scanTime,
@RequestParam(value = "hostId", required = false) Integer hostId,
@RequestParam(value = "username", required = false) String username,
@RequestParam(value = "onPassword", required = false, defaultValue = "0") Integer onPassword,
HttpServletResponse response) throws IOException {
FrailPassword frailPassword = FrailPassword.builder()
.scanTime(scanTime)
.hostId(hostId)
.username(username)
.build();
try {
List<FrailPassword> list = frailPasswordService.getFrailPasswordList(frailPassword, onPassword);
// 导出指定列的字段
Set<String> includeColumnFiledNames = new HashSet<>();
includeColumnFiledNames.add("ip");
includeColumnFiledNames.add("serviceName");
includeColumnFiledNames.add("port");
// 动态头
List<String> ipList = new ArrayList<>(5);
ipList.add("弱口令");
ipList.add("查询条件");
ipList.add("开始时间：");
ipList.add("结束时间：");
ipList.add("资产IP");
List<String> serviceNameList = new ArrayList<>(5);
serviceNameList.add("弱口令");
serviceNameList.add("查询条件");
serviceNameList.add(scanTime);
serviceNameList.add(DateUtil.formatDateTime(new Date()));
serviceNameList.add("服务");
List<String> portList = new ArrayList<>(5);
portList.add("弱口令");
portList.add("");
portList.add("");
portList.add("");
portList.add("端口");
List<List<String>> head = new ArrayList<>(5);
head.add(ipList);
head.add(serviceNameList);
head.add(portList);
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
// 防止中文乱码
String fileName = URLEncoder.encode(String.format("弱口令数据_%s.xlsx", DateUtil.format(new Date(), DatePattern.PURE_DATETIME_PATTERN)), "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
// 需要设置不关闭流
EasyExcel.write(response.getOutputStream(), FrailPassword.class)
.head(head)
.includeColumnFiledNames(includeColumnFiledNames)
.autoCloseStream(Boolean.FALSE).sheet("弱口令")
.doWrite(list);
} catch (Exception e) {
e.printStackTrace();
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
VoResult voResult = VoResult.error("导出弱口令文件文件失败");
response.getWriter().write(JSONUtil.toJsonStr(voResult));
}
}
可参考：POI详解
