EasyExcel 自定义单元格式的问题。
最近在做一个关于性能测试管理系统，一个新的需求，需要导出测试报告，直接使用了ali的封装的EasyExcel，但是在复杂头与一些样式，就缺少了自定义的灵活性，在官方demo中没有找到很好的解决方法。
不普及基础了，可以直接看官方文档， 直接上解决代码：
调用：
List<ApiStatusVO> apiStatusVOList = new ArrayList<>(); for(int i=0;i<=15;i++){ ApiStatusVO apiStatusVO = new ApiStatusVO(); apiStatusVO.setApiName("Apiname"+i); apiStatusVO.setApiRt("RT"+i); apiStatusVO.setApiStatus("200"); apiStatusVO.setId(i); apiStatusVO.setTransId(String.valueOf(100+i)); apiStatusVO.setResponseAssert("true"); apiStatusVOList.add(apiStatusVO); }
String fileName = URLEncoder.encode(new SimpleDateFormat("yyyy-MM-dd-24h").format(new Date())+".xlsx", "UTF-8");
ExcelKit().parseExcelAndSave(apiStatusVOList，fileName );
ExcelKit代码：
package com.kit; import ……public class ExcelKit { public static void parseExcelAndSave(List<ApiStatusVO> apiStatusVOS,String fileName){ EasyExcel.write(fileName, TestReportVO.class) .head(head("我是一个标题头","描述：本次压测针对UXX常用接口进行压力测试；")) .registerWriteHandler(new CustomCellWriteHandler()) .sheet("TestReport") .doWrite(data(apiStatusVOS)); } /** * Api 返回内容 数据不全，先自定义赋值 * @param statusVOS * @return */ private static List<TestReportVO> data(List<ApiStatusVO> statusVOS){ List<TestReportVO> dataList = new ArrayList<>(); statusVOS.forEach(ApiStatusVO->{ TestReportVO status = new TestReportVO(); // @ExcelProperty("压测接口名称") status.setTestName(ApiStatusVO.getApiName()); // @ExcelProperty("压测时间") status.setTestDate(new Date()); // @ExcelProperty("压测时常（s）") status.setTestDuration("100"); // @ExcelProperty("平均吞吐量（s）") status.setTestHandlingCapacity("101"); // @ExcelProperty("平均响应时间(s)") status.setTestResponseTime(ApiStatusVO.getApiRt()); // @ExcelProperty("最大响应时间（s）") status.setTestMaxResponseTime("102"); // @ExcelProperty("错误率(%)") status.setTestErrorRate("13.18"); // @ExcelProperty("数据库CPU（User）(%)") status.setTestCpuPercentum("15.18"); // @ExcelProperty("数据库TCP连接数") status.setTestTcpLinkNum("105"); // @ExcelProperty("连接池大小") status.setTestLinkPooSize("106"); // @ExcelProperty("网络流量(上传/Mbps)(Min)") status.setTestMinFlow("107"); // @ExcelProperty("网络流量(上传/Mbps)(Max)") status.setTestMaxFlow("108"); dataList.add(status); }); return dataList; } /** * 自定义头 * @param headTitle 统一头 * @param headDescTitle 描述头 * @return 返回整个头list。 头部相同连续的单元格会自动合并。 */ private static List<List<String>> head(String headTitle, String headDescTitle){ List<List<String>> list = new ArrayList<List<String>>(); // 压测名称 压测时间 压测时常（s）平均吞吐量（s） 平均响应时间(s) 最大响应时间（s） 错误率(%) 数据库CPU（User）(%) 数据库TCP连接数 连接池大小 网络流量(上传/Mbps) 网络流量(下载/Mbps) List<String> head1 = new ArrayList<String>(); head1.add(headTitle); head1.add(headDescTitle); head1.add(headDescTitle); head1.add(headDescTitle); head1.add(headDescTitle); head1.add(headDescTitle); head1.add(headDescTitle); head1.add("压测名称"); List<String> head2 = new ArrayList<String>(); head2.add(headTitle); head2.add(headDescTitle); head2.add(headDescTitle); head2.add(headDescTitle); head2.add(headDescTitle); head2.add(headDescTitle); head2.add(headDescTitle); head2.add("压测时间"); List<String> head3 = new ArrayList<String>(); head3.add(headTitle); head3.add(headDescTitle); head3.add(headDescTitle); head3.add(headDescTitle); head3.add(headDescTitle); head3.add(headDescTitle); head3.add(headDescTitle); head3.add("压测时常（s）"); List<String> head4 = new ArrayList<String>(); head4.add(headTitle); head4.add(headDescTitle); head4.add(headDescTitle); head4.add(headDescTitle); head4.add(headDescTitle); head4.add(headDescTitle); head4.add(headDescTitle); head4.add("平均吞吐量（s）"); List<String> head5 = new ArrayList<String>(); head5.add(headTitle); head5.add(headDescTitle); head5.add(headDescTitle); head5.add(headDescTitle); head5.add(headDescTitle); head5.add(headDescTitle); head5.add(headDescTitle); head5.add("平均响应时间(s)"); List<String> head6 = new ArrayList<String>(); head6.add(headTitle); head6.add(headDescTitle); head6.add(headDescTitle); head6.add(headDescTitle); head6.add(headDescTitle); head6.add(headDescTitle); head6.add(headDescTitle); head6.add("最大响应时间（s）"); List<String> head7 = new ArrayList<String>(); head7.add(headTitle); head7.add(headDescTitle); head7.add(headDescTitle); head7.add(headDescTitle); head7.add(headDescTitle); head7.add(headDescTitle); head7.add(headDescTitle); head7.add("错误率(%)"); List<String> head8 = new ArrayList<String>(); head8.add(headTitle); head8.add(headDescTitle); head8.add(headDescTitle); head8.add(headDescTitle); head8.add(headDescTitle); head8.add(headDescTitle); head8.add(headDescTitle); head8.add("数据库CPU（User）(%)"); List<String> head9 = new ArrayList<String>(); head9.add(headTitle); head9.add(headDescTitle); head9.add(headDescTitle); head9.add(headDescTitle); head9.add(headDescTitle); head9.add(headDescTitle); head9.add(headDescTitle); head9.add("数据库TCP连接数"); List<String> head10 = new ArrayList<String>(); head10.add(headTitle); head10.add("负责人："); head10.add("齐冰洋"); head10.add(""); head10.add(""); head10.add(""); head10.add(""); head10.add("连接池大小"); List<String> head11 = new ArrayList<String>(); head11.add(headTitle); head11.add("协助人："); head11.add("石星"); head11.add("张刚强"); head11.add("秦亚飞"); head11.add("王京朝"); head11.add("张凯"); head11.add("网络流量(上传/Mbps)"); List<String> head12 = new ArrayList<String>(); head12.add(headTitle); head12.add(""); head12.add("黄色：瓶颈"); head12.add("橙色：提醒"); head12.add("红色：严重"); head12.add("测试时间"); head12.add("2020-01-01"); //测试时间 head12.add("网络流量(下载/Mbps)"); list.add(head1); list.add(head2); list.add(head3); list.add(head4); list.add(head5); list.add(head6); list.add(head7); list.add(head8); list.add(head9); list.add(head10); list.add(head11); list.add(head12); return list; } }
拦截，写入样式、控制列宽：
package com.kit; import ……public class CustomCellWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler { private static final Integer width = 18; Workbook workbooks; @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean isHead) { this.initCellStyle(writeSheetHolder.getSheet().getWorkbook()); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256); this.setHeadCellStyle(cell,head,integer); } @Override protected void initCellStyle(Workbook workbook) { this.workbooks = workbook; } @Override protected void setHeadCellStyle(Cell cell, Head head, Integer integer) { //头部Top样式 if(cell.getRowIndex()==0){ cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,16)); }else if((cell.getRowIndex()==1 && cell.getColumnIndex()>8)){ //负责人 、协助人 cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN)); }else if(cell.getColumnIndex() == 11 && (cell.getRowIndex() == 2 || cell.getRowIndex() == 3 || cell.getRowIndex() == 4 || cell.getRowIndex() == 5)){ //警告 switch (cell.getRowIndex()){ case 2: cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.YELLOW)); break; case 3: cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.ORANGE)); break; case 4: cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.RED)); break; case 5: cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN)); break; } }else if(cell.getRowIndex() == 7 && cell.getColumnIndex() == 0 ){ //标题底色 cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN)); }else if(cell.getRowIndex() == 7 && cell.getColumnIndex() != 0 ){ //标题底色 cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.ORANGE)); }else { cell.setCellStyle(PoiUtils.getColumnStyle(workbooks)); } if(cell.getRowIndex() > 7){ cell.setCellStyle(PoiUtils.getColumnStyle(workbooks)); } } @Override protected void setContentCellStyle(Cell cell, Head head, Integer integer) { } }
样式设置： 此处代码参考来源：https://blog.csdn.net/qq_15081901/article/details/90202723 （POI封装工具easyexcel导出EXCEL表样式设置）
package com.kit; import……public class PoiUtils { /** * 首头单元格 * @param workbook * @return */ public static CellStyle getColumnTopStyle(Workbook workbook,int FontSize) { if(FontSize == 0){ FontSize=12; } // 设置字体 Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) FontSize); //字体加粗 font.setBold(true); //设置字体名字 font.setFontName("宋体"); //设置样式; CellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(BorderStyle.THIN); //设置底边框颜色; style.setBottomBorderColor((short) 0); //设置左边框; style.setBorderLeft(BorderStyle.THIN); //设置左边框颜色; style.setLeftBorderColor((short) 0); //设置右边框; style.setBorderRight(BorderStyle.THIN); //设置右边框颜色; style.setRightBorderColor((short) 0); //设置顶边框; style.setBorderTop(BorderStyle.THIN); //设置顶边框颜色; style.setTopBorderColor((short) 0); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } /** * 头部自己定义单元格 * @param workbook * @return */ public static CellStyle getColumnTopStyle(Workbook workbook, IndexedColors indexedColors) { // 设置字体 Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) 12); //字体加粗 font.setBold(true); //设置字体名字 font.setFontName("宋体"); //设置样式; CellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(BorderStyle.THIN); //设置底边框颜色; style.setBottomBorderColor((short) 0); //设置左边框; style.setBorderLeft(BorderStyle.THIN); //设置左边框颜色; style.setLeftBorderColor((short) 0); //设置右边框; style.setBorderRight(BorderStyle.THIN); //设置右边框颜色; style.setRightBorderColor((short) 0); //设置顶边框; style.setBorderTop(BorderStyle.THIN); //设置顶边框颜色; style.setTopBorderColor((short) 0); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(false); //设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); /** * 背景色 */ if(indexedColors != null){ style.setFillForegroundColor(indexedColors.getIndex());// 设置背景色 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); } return style; } /* * 字段样式 */ public static CellStyle getColumnStyle(Workbook workbook) { // 设置字体 Font font = workbook.createFont(); //设置字体大小 font.setFontHeightInPoints((short) 10); //字体加粗 // font.setBold(true); //设置字体名字 font.setFontName("宋体"); //设置样式; CellStyle style = workbook.createCellStyle(); //设置底边框; style.setBorderBottom(BorderStyle.THIN); //设置底边框颜色; style.setBottomBorderColor((short) 0); //设置左边框; style.setBorderLeft(BorderStyle.THIN); //设置左边框颜色; style.setLeftBorderColor((short) 0); //设置右边框; style.setBorderRight(BorderStyle.THIN); //设置右边框颜色; style.setRightBorderColor((short) 0); //设置顶边框; style.setBorderTop(BorderStyle.THIN); //设置顶边框颜色; style.setTopBorderColor((short) 0); //在样式用应用设置的字体; style.setFont(font); //设置自动换行; style.setWrapText(true); //设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); //设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } }
TestReportVO
import …… @Data public class TestReportVO { @ExcelProperty("压测名称") private String testName; @ExcelProperty("压测时间") private Date testDate; @ExcelProperty("压测时常（s）") private String testDuration; @ExcelProperty("平均吞吐量（s）") private String testHandlingCapacity; @ExcelProperty("平均响应时间(s)") private String testResponseTime ; @ExcelProperty("最大响应时间（s）") private String testMaxResponseTime; @ExcelProperty(错误率(%)) private String testErrorRate ; @ExcelProperty("数据库CPU（User）(%)) private String testCpuPercentum ; @ExcelProperty("数据库TCP连接数") private String testTcpLinkNum; @ExcelProperty("连接池大小") private String testLinkPooSize; @ExcelProperty("网络流量(上传/Mbps)") private String testMinFlow ; @ExcelProperty("网络流量(上传/Mbps)") private String testMaxFlow; }
导出效果：
有问题可以联系 邮箱 253049953@qq.com
TestReportVO
版权声明：本文为data1原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。