🍁 作者：知识浅谈，CSDN签约讲师&博客专家，华为云云享专家，阿里云专家博主，InfoQ签约作者 📌 擅长领域：全栈工程师、爬虫、ACM算法，大数据，深度学习 💒 公众号：知识浅谈
🎈先上代码和案例
- 案例如下：可以从下图中看到有三层标题
- 总的代码如下
@RestController @RequestMapping("/test") public class Test{ @GetMapping("/download") public void download(HttpServletResponse response) { //根据角色不同，打印的汇总表的人不一样 try { //添加两个数据 List<A> list = new ArrayList<>(); A a = new A(); a.setA1("1");a.setA2("111");a.setA3("111");a.setA4("222");a.setA5("333"); A b = new A(); b.setA1("1");b.setA2("111");b.setA3("112");b.setA4("223");b.setA5("334"); list.add(a); list.add(b); String sheetName = "测试"; String fileName = IdUtil.fastSimpleUUID() + "_" + sheetName + ".xlsx"; //设置文件名 FileUtils.setAttachmentResponseHeader(response, fileName); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8"); ServletOutputStream os = response.getOutputStream(); EasyExcel.write(os, A.class) .autoCloseStream(false) // 自动适配 .registerWriteHandler(new CellMergeStrategy(list, true)) // 合并处理器 .registerWriteHandler(new ExcelDownHandler(null))// 添加下拉框操作 .registerWriteHandler(new CellWriteHandler() { @Override public void afterCellDispose(CellWriteHandlerContext context) { if(BooleanUtils.isNotTrue(context.getHead())){ //设置内容格式 WriteCellData<?> firstCellData = context.getFirstCellData(); WriteCellStyle cellStyle = firstCellData.getOrCreateStyle(); //设置垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //设置框线 cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); //设置字体 WriteFont writeFont = new WriteFont(); writeFont.setFontHeightInPoints((short) 10); cellStyle.setWriteFont(writeFont); //单独设置长度 List<String> headNameList = context.getHeadData().getHeadNameList(); String headName = headNameList.get(1); if(headName.contains("特别标题")){ int columnIndex = context.getCell().getColumnIndex(); WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); //设置符合条件的列的宽度 writeSheetHolder.getSheet().setColumnWidth(columnIndex,60*256); } }else{//设置表头格式 if (context.getCell().getStringCellValue().contains("总表头")) { WriteCellData<?> firstCellData = context.getFirstCellData(); WriteCellStyle headStyle = firstCellData.getOrCreateStyle(); //设置总表头为加粗且字体大小为10 WriteFont writeFont = new WriteFont(); writeFont.setFontHeightInPoints((short) 18); writeFont.setBold(true); headStyle.setWriteFont(writeFont); } } } }) // 大数值自动转换 防止失真 .registerConverter(new ExcelBigNumberConvert()) .head(creatHead()) .sheet(sheetName) .doWrite(list); } catch (Exception e) { log.error("文件生成异常，{}", e.getMessage()); } } private List<List<String>> creatHead() { String title = LocalDate.now().getYear()+"总表头"; List<List<String>> lists = new ArrayList<>(); String[] titles = {"序号","特别标题"}; String[] vote = {"子标题1", "子标题2","子标题3"}; //设计第一部分表头 for (String s : titles) { ArrayList<String> head = new ArrayList<>(); head.add(title); head.add(s); lists.add(head); } //处理第二部分表头 for (String s : vote) { ArrayList<String> head = new ArrayList<>(); head.add(title); head.add("子标题"); head.add(s); lists.add(head); } return lists; } } @Data //使用这个注解需要在maven中引入lombok 依赖 class A{ private String a1; private String a2; private String a3; private String a4; private String a5; }
🎈复杂表头的生成
- 表头的构建 是根据表头最后一行的个数来确定的，比如我们要有 "序号","特别标题","子标题1", "子标题2","子标题3" 5个
- "子标题1", "子标题2","子标题3" 这三个是二级标题
- 总结一下经验：从第一行标题到最后一行标题是一个list 然后加入到list<list< string>> lists 中
private List<List<String>> creatHead() {
String title = LocalDate.now().getYear()+"总表头";
List<List<String>> lists = new ArrayList<>();
String[] titles = {"序号","特别标题"};
String[] vote = {"子标题1", "子标题2","子标题3"};
//设计第一部分表头
for (String s : titles) {
ArrayList<String> head = new ArrayList<>();
head.add(title);
head.add(s);
lists.add(head);
}
//处理第二部分表头
for (String s : vote) {
ArrayList<String> head = new ArrayList<>();
head.add(title);
head.add("子标题");
head.add(s);
lists.add(head);
}
return lists;
}
🍚总结
大功告成，撒花致谢🎆🎇🌟，关注我不迷路，带你起飞带你富。
Writted By 知识浅谈
