这段时期的工作涉及了不少报表类的开发，其中用到了主流的Excel处理工具EasyPoi和EasyExcel，下面是关于这两个工具用法小结。
一.Excel的填充(导出的一种)
关于Excel的导出主要分为两种形式，一种是事先给出一个确定的excel文件作为导出模板，然后将生成的数据写入到这个Excel文件中(即excel模板的填充)；另一种就是不需要事先给定好excel文件，而是在生成数据的过程中动态的生成导出模板的格式，相当于把数据和模板样式一起动态的生成。
首先是实体类(实体类不用加单独的注解，就是普通的业务实体类)：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | public class tcExcelDTO { /** * ID */ private Long orderNum; /** * 单位编号 */ private String deptCode; /** * 单位名称 */ private String deptName; /** * 管理类数量 */ private Integer manageNum; /** * 管理类分值 */ private BigDecimal manageScore; /** * 技术质量类数量 */ private Integer technicalQualityNum; /** * 技术质量类分值 */ private BigDecimal technicalQualityScore; /** * 生产类数量 */ private Integer productionNum; /** * 生产类分值 */ private BigDecimal productionScore;} |
1.EasyPoi的填充
（1）Excel模板设置格式：参考EasyPoi教程_V1.0 (mydoc.io)，具体位置如下：
本文使用的例子如下：
（2）参考实现代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public void applyResyltExport(HttpServletResponse response, HrIsProjectApplysBusiBo hrIsProjectApplysBusiBo) throws Exception{ List<tcExcelDTO> exportTemplateListJiTuan = buildJiTuanData(hrIsProjectApplysBusiBo);//sheet1中的数据 List<tcExcelDTO> exportTemplateListCompany = buildCompanyData(hrIsProjectApplysBusiBo);//sheet2中的数据 String fileName = hrIsProjectApplysBusiBo.getYear() +"年"+ hrIsProjectApplysBusiBo.getQuarter() + "季度" + EXCEL_NAME; TemplateExportParams params=new TemplateExportParams("templates/import/finalApplyResult.xlsx",true);//需要填充的模板的路径 Map<String, Object> map = new HashMap<>(); map.put("list",exportTemplateListCompany);//sheet1，list和模板中的list名称相对应 map.put("list2",exportTemplateListJiTuan);//sheet2，list1和模板中的list1名称相对应 Workbook workbook = ExcelExportUtil.exportExcel(params,map);//easypoi的依赖 try { response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+".xlsx"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); } catch (IOException e) { log.error("导出失败，请检查", e); throw new BaseException(ErrorEnum.SYSTEM_ERROR, "导出失败，请检查"); } finally { try { response.getOutputStream().close(); } catch (IOException e) { log.error("导出失败，请检查", e); throw new BaseException(ErrorEnum.SYSTEM_ERROR, "导出失败，请检查"); } }} |
此处主要使用了语法：{{fe:list t.age t.secondList.name}}，需要注意的是使用指令fe 循坏数据，如果使用$fe 嵌套列表第一个字段为空时，第二行后面的数据显示不出来了。
2.EasyExcel的填充
（1）Excel模板设置格式：参考填充Excel | Easy Excel (alibaba.com)，由于官方例子讲述的很详细，此处不在赘述。
二.Excel的导出(动态生成模板)
1.EasyPoi的导出
(1)首先设置相应的实体类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | public class testExcelDTO { /** * ID */ private Long id; /** * 项目编号 */ @Excel(name = "项目编号", orderNum = "1", width = 25) private String projectCode; /** * 项目名称 */ @Excel(name = "项目名称", orderNum = "2", width = 25) private String projectName; /** * 主持人工号 */ @Excel(name = "工号", orderNum = "3", width = 25) private String empNo; /** * 主持人姓名 */ @Excel(name = "姓名", orderNum = "4", width = 25) private String empName; /** * 角色 */ @Excel(name = "角色", replace = { "主持人_100001", "主要参与人_100002", "一般参与人_100003", "辅助参与人_100004" },orderNum = "5", width = 25) private Integer role; /** * 年度 */ private String year; /** * 季度 */ private String quarter; /** * 项目来源 */ private String source;public static HrIsProjectPitemExcelDTO generateBean() { return new HrIsProjectPitemExcelDTO();}} |
(2)代码实现:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public void testExport(HttpServletResponse response, HrIsProjectPitemBusiBo hrIsProjectPitemBusiBo) throws Exception{ // 设置导出数据 List<testExcelDTO> exportTemplateList = buildEmportTemplateData(hrIsProjectPitemBusiBo);//生成需要导出的数据 log.info("exportTemplateList : {}", exportTemplateList); if (!org.apache.commons.collections4.CollectionUtils.isEmpty(exportTemplateList)) { List<Map<String, Object>> excelParamList = new ArrayList<>(); excelParamList.add(ExportExcelUtil.getExcelSheetParam(SHEET_NAME , exportTemplateList, testExcelDTO.class)); StringBuffer fileName = new StringBuffer(hrIsProjectPitemBusiBo.getYear() +"年"+ hrIsProjectPitemBusiBo.getQuarter() + "季度" + EXCEL_NAME); fileName.append(".xlsx"); ExportExcelUtil.exportExcel(excelParamList, ExcelType.XSSF, fileName.toString(), response); } else { throw new BaseException(ErrorEnum.SYSTEM_ERROR, "导出数据为空！"); } |
其中ExportExcel的定义如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | public class ExportExcelUtil { /** * 导出到workbook并输出到response * @param list 数据集 * @param excelType 文件类型ExcelType.HSSF/XSSF * @return * @throws */ public static void exportExcel(List<Map<String, Object>> list, ExcelType excelType, String fileName, HttpServletResponse response) throws IOException { try { Workbook workbook = ExcelExportUtil.exportExcel(list, excelType); if (workbook != null) { response.setHeader("content-Type", "application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"),"iso-8859-1" )); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); // 关闭流 response.getOutputStream().close(); } }catch (IOException e){ throw new IOException("导出表格数据失败"); } } // 流导出 private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException { try { response.reset(); response.setContentType("application/x-download"); response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")); workbook.write(response.getOutputStream()); }catch (IOException e){ throw new IOException("导出表格数据失败"); } } // 生成预选值的sheet页 private static XSSFDataValidationHelper createXSSFDataValidationHelper(Workbook workbook, String name, String[] strList) { Sheet sheet = workbook.createSheet(name); // 循环往该sheet中设置添加下拉列表的值 for (int i = 0; i < strList.length; i++) { Row row = sheet.createRow(i); Cell cell = row.createCell((int) 0); cell.setCellValue(strList[i]); } workbook.setSheetHidden(workbook.getSheetIndex(name), true);//隐藏用于生成下拉框的sheet XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) workbook.getSheet(name)); dvHelper.createFormulaListConstraint(name + "!$A$1:$A$" + strList.length); return dvHelper; } /** * 生成含有下拉框的模板 * firstRow 开始行号(默认为1，下标0开始) * lastRow 根据此项目，默认为最大65535 * firstCol 区域中第一个单元格的列号 (下标0开始) * lastCol 区域中最后一个单元格的列号 * strings 下拉内容 * */ public static void exportContainDownVlue(Workbook workbook, HttpServletResponse response,String mySheetNmme,String fileName,String[]dropDownValue, int firstCol,int lastCol ) throws IOException { String dropDownSheetName= "隐藏sheet"; XSSFDataValidationHelper dropDownValidationHelper = createXSSFDataValidationHelper(workbook, dropDownSheetName, dropDownValue); DataValidationConstraint dropDOwnValidationConstraint = dropDownValidationHelper.createFormulaListConstraint(dropDownSheetName + "!$A$1:$A$" + dropDownValue.length); Sheet firstSheet = workbook.getSheet(mySheetNmme); CellRangeAddressList drowDownValueCoveringRowsAndCloumns = new CellRangeAddressList(1, firstSheet.getLastRowNum(), firstCol, lastCol); XSSFDataValidation dropDownValidation =(XSSFDataValidation)dropDownValidationHelper.createValidation(dropDOwnValidationConstraint, drowDownValueCoveringRowsAndCloumns); firstSheet.addValidationData(dropDownValidation); downLoadExcel(fileName, response, workbook); }} |
(3)导出结果示例:
2.EasyExcel的导出
官方文档的Demo写的很清晰，此处不在赘述：写Excel | Easy Excel (alibaba.com)
三.Excel的导入
1.EasyPoi
（1）实体类如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | public class testExcelDTO { /** * ID */ private Long id; /** * 项目编号 */ @Excel(name = "项目编号", orderNum = "1", width = 25) private String projectCode; /** * 项目名称 */ @Excel(name = "项目名称", orderNum = "2", width = 25) private String projectName; /** * 主持人工号 */ @Excel(name = "工号", orderNum = "3", width = 25) private String empNo; /** * 主持人姓名 */ @Excel(name = "姓名", orderNum = "4", width = 25) private String empName; /** * 角色 */ @Excel(name = "角色", replace = { "主持人_100001", "主要参与人_100002", "一般参与人_100003", "辅助参与人_100004" },orderNum = "5", width = 25) private Integer role; /** * 年度 */ private String year; /** * 季度 */ private String quarter; /** * 项目来源 */ private String source;} |
这个实体类和导入的时候类似的，需要通过注解@Excel对应好数据的映射关系
（2）代码实现如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | public DataImportResultBO testExcelImport(@NotNull @RequestParam("file") MultipartFile file, @RequestParam(value = "id") @Number(min=1,message = "ID必须大于0") Long id) throws Exception { String intekeyUrl = NacosGlobalConfiguration.getByKey("intekey-url"); String appCode = NacosGlobalConfiguration.getByKey("intekey-appCode"); String secretKey = NacosGlobalConfiguration.getByKey("intekey-secretKey"); InputStream inputStream = IntekeyUtils.DecryptFile(intekeyUrl,appCode,secretKey,file);//由于导入的wxcel文件是加密的，此处需要解密 ImportParams importParams = new ImportParams();// importParams.setKeyIndex(0); importParams.setNeedVerify(true); ExcelImportResult<testExcelDTO> excelImportResult = ExcelImportUtil .importExcelMore(inputStream, testExcelDTO.class, importParams);//easypoi提供的接口 List<testExcelDTO> list = excelImportResult.getList(); DataImportResultBO resultBO = new DataImportResultBO(); List<testExcelDTO> failList = excelImportResult.getFailList(); if (failList != null && failList.size() > 0) {//打印错误信息 int count = failList.size(); StringBuilder stringBuilder = new StringBuilder(); for (testExcelDTO dto : failList) { stringBuilder .append("第") .append(dto.getRowNum()) .append("行,") .append(dto.getErrorMsg()) .append(";"); } resultBO.setMsg(stringBuilder.toString()); resultBO.setErrorNumber(count); resultBO.setIsSuccess(false); } else { resultBO.setErrorNumber(0); } if(list.size()==0 && CollectionUtils.isEmpty(failList)){//对应解析excel失败的处理 final boolean[] isSuccess = {true}; isSuccess[0] = false; resultBO.setIsSuccess(isSuccess[0]); resultBO.setSuccessNumber(0); resultBO.setMsg("批量导入excel未解析到数据"); log.error("批量导入excel未解析到数据！"); return resultBO; }else {//业务逻辑处理 int successNum = 0; for (testExcelDTO hrIsCashSpecialApplyExcelBusiDTO : list) { testExcelDTO hrIsCashSpecialApplyBusiDTO = new HrIsCashSpecialApplyBusiDTO(); XXXXXXXX //业务逻辑 successNum++; } resultBO.setSuccessNumber(successNum); resultBO.setErrorNumber(list.size() - successNum); resultBO.setIsSuccess(true); return resultBO; } } |
其中返回的信息类定义如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | public class DataImportResultBO<E> implements Serializable { /** * 本次导入编码 */ private String importCode; /** * 导入成功条数 */ private Integer successNumber; /** * 失败条数 */ private Integer errorNumber; /** * 导入结果 */ private Boolean isSuccess; /** * 错误信息 */ private String msg; /** * 导入数据 */ List<E> result;} |
2.EasyExcel
参考官方文档：读Excel | Easy Excel (alibaba.com)
四.其他
1.EasyPoi导入、导出时不支持三级以上表头，但是也可以实现，只是相对复杂些，关于EasyPoi的多级表头的导入、导出可以参考：
(1) (35条消息) excel导入基于Easypoi一对多导入(实现合并单元格)_秃头老程序员的博客-CSDN博客_easypoi导入excel一对多
(2) (35条消息) 使用easypoi或者easyexcel多表头导出_山里的小蝌蚪的博客-CSDN博客_easypoi 导出表头
(3) easyExcel简单excel导出以及多sheet页导出 - 简书 (jianshu.com)
2.关于带下拉框的Excel的导出
参考代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /** * 自定义excel下拉框内容 * @param workbook * @param firstCol 需要设置下拉框的开始行 * @param lastCol 需要设置下拉框的开始列 * @param strings,下拉框选项 */ private static void selectList(Workbook workbook,int firstCol,int lastCol,String[] strings) { Sheet sheet = workbook.getSheetAt(0); // 生成下拉列表 // 只对(x，x)单元格有效 CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, firstCol, lastCol); // 生成下拉框内容 DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(strings); HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint); // 对sheet页生效 sheet.addValidationData(dataValidation); } |
