easyexcel官网文档：/easyexcel/doc/easyexcel
easyexcel {maven 版本}
GitHub网址：/alibaba/easyexcel
<!-- easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.4</version></dependency>
导出相关代码
controller：
@PostMapping(value = "/test/export")public Object test(HttpServletResponse response) {try {//获取要导出的数据List<TestVo> deviceData = mapper.findDeviceData();//获取导出数据总条数 传入做校验格式使用List<Integer> collect = deviceData.stream().map(TestVo::getId).collect(Collectors.toList());//设置excel名称以及sheet名称，是否需要表头EasyExcelParams params = new EasyExcelParams("模板", "模板", true, deviceData, TestVo.class, response);//设置样式 校验格式params.setStyleConfig(new ExcelStyleConfig(Lists.newArrayList(0, 1, 2, 3, 4, 5,6), Lists.newArrayList(8, 20), Lists.newArrayList( 7, 22), collect));//导出数据EasyExcelUtil.exportExcelFormat(params);} catch (IOException e) {return error;}return success();}
导出实体类：
@Setter@Getter@ToString@ContentRowHeight(20)@HeadRowHeight(20)@ColumnWidth(25) public class TestVo{//表头名称@ExcelProperty(value = "id")private String id;//表头合并 名称@ExcelProperty(value = {"数量", "值"})private Integer value;//排除该字段@ExcelIgnoreprivate String createUser;}
导出相关工具类：
EasyExcelParams：参数类，定义需要的一些数据字段。
@Setter@Getter@ToStringpublic class EasyExcelParams {/*** 文件名*/private String fileName;/*** sheet名*/private String sheetName;/*** 是否需要表头*/private Boolean needHead;/*** 导出数据*/private List data;/*** 数据模型类型*/private Class dataModelClazz;/*** 响应*/private HttpServletResponse response;/*** 单元格样式*/private ExcelStyleConfig styleConfig;/*** 合并索引数*/private List<MergeCellIndex> mergeCellIndices;@Setter@Getter@ToString@NoArgsConstructor@AllArgsConstructorpublic static class MergeCellIndex {/*** 开始行*/private Integer firstRowIndex;/*** 结束行*/private Integer lastRowIndex;/*** 开始列*/private Integer firstColumnIndex;/*** 结束列*/private Integer lastColumnIndex;}/*** 不合并和不锁定构造*/public EasyExcelParams(String fileName, String sheetName, Boolean needHead, List data, Class dataModelClazz, HttpServletResponse response) {this.fileName = fileName;this.sheetName = sheetName;this.needHead = needHead;this.data = data;this.dataModelClazz = dataModelClazz;this.response = response;}/*** 对于非空字典判空*/public boolean isValid() {return ObjectUtils.allNotNull(fileName, data, response, dataModelClazz);}public void setStyleConfig(ExcelStyleConfig styleConfig) {this.styleConfig = styleConfig;}public void setMergeCellIndices(List<MergeCellIndex> mergeCellIndices) {this.mergeCellIndices = mergeCellIndices;}}
导出格式配置类：
ExcelStyleConfig：导出样式配置类，需要继承（CellWriteHandler）写入handler类来重写里边的方法做逻辑处理，支持隐藏列，锁定列，表单保护密码，表单背景颜色，数据格式校验限制等，如果需要更多功能，请参照案例自行实现。
@Slf4jpublic class ExcelStyleConfig implements CellWriteHandler {/*** 需要锁定的列集合*/private List<Integer> columnList;/*** 样式类*/private CellStyle cellStyle;/*** 隐藏索引数*/private List<Integer> hiddenIndices;/*** 限制那一列为数值型 开头结尾*/private List<Integer> columnNumList;/*** 限制哪一行为数值开头结尾集合*/private List<Integer> rowNumList;public ExcelStyleConfig(List<Integer> columnList) {this.columnList = columnList;}public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices, List<Integer> rowNumList) {this.columnList = columnList;this.hiddenIndices = hiddenIndices;this.columnNumList = columnNumList;this.rowNumList = rowNumList;}public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList) {this.columnList = columnList;this.columnNumList = columnNumList;}public ExcelStyleConfig(List<Integer> columnList, List<Integer> columnNumList, List<Integer> hiddenIndices) {this.columnList = columnList;this.columnNumList = columnNumList;this.hiddenIndices = hiddenIndices;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {Sheet sheet = writeSheetHolder.getSheet();//设置冻结某行某列sheet.createFreezePane(COL_SPLIT, ROW_SPLIT);// 下边框cellStyle.setBorderBottom(BorderStyle.THIN);// 左边框cellStyle.setBorderLeft(BorderStyle.THIN);// 上边框cellStyle.setBorderTop(BorderStyle.THIN);// 右边框cellStyle.setBorderRight(BorderStyle.THIN);// 水平对齐方式cellStyle.setAlignment(HorizontalAlignment.CENTER);// 垂直对齐方式cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setLocked(false);if (!CollectionUtils.isEmpty(hiddenIndices) && hiddenIndices.contains(cell.getColumnIndex())) {// 设置隐藏列writeSheetHolder.getSheet().setColumnHidden(cell.getColumnIndex(), true);}if (!CollectionUtils.isEmpty(columnList) && columnList.contains(cell.getColumnIndex())) {// 设置表单保护密码writeSheetHolder.getSheet().protectSheet("password");// 设置锁定单元格cellStyle.setLocked(true);//设置背景颜色cellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());}if (!CollectionUtils.isEmpty(columnNumList) && columnNumList.contains(cell.getColumnIndex())) {// --- 数据有效性 只允许输入整数 ---DataValidationHelper helper = sheet.getDataValidationHelper();//校验数值格式 只能输入整数0 -xx 范围内DataValidationConstraint constraintNum = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.INTEGER,DataValidationConstraint.OperatorType.BETWEEN, "0", "100000000");CellRangeAddressList regionNumber = new CellRangeAddressList(BigInteger.ONE.intValue(), rowNumList.get(rowNumList.size() - 1), columnNumList.get(0), columnNumList.get(columnNumList.size() - 1));DataValidation validationNum = helper.createValidation(constraintNum, regionNumber);//输入错误提示validationNum.createErrorBox("输入值错误", "请输入0-100000000之间的数字");validationNum.setShowErrorBox(true);sheet.addValidationData(validationNum);}// 填充单元格样式cell.setCellStyle(cellStyle);}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}}
导出工具类：
EasyExcelUtil：封装一些方法导出数据到excel，导出自动关闭流，不需要手动去关闭，如需要扩展自行定义。
@Componentpublic class EasyExcelUtil {private static final String EXCEL_SECRET_CODE = "intelligence-password";private static final String EXCEL_VERSION = "1.0";private EasyExcelUtil() {}/*** 导出版Excel*/public static void exportExcelFormat(EasyExcelParams params) throws IOException {exportExcel(params);}/*** 导出Excel实现*/private static void exportExcel(EasyExcelParams params) throws IOException {Validate.isTrue(params.isValid(), "参数错误!");prepareResponds(params.getFileName(), params.getResponse());ServletOutputStream outputStream = params.getResponse().getOutputStream();ExcelWriterBuilder builder = new ExcelWriterBuilder();builder.sheet(params.getSheetName());builder.head(params.getDataModelClazz());builder.file(outputStream);builder.excelType(ExcelTypeEnum.XLSX);builder.needHead(true);builder.registerWriteHandler(params.getStyleConfig());WriteSheet sheet = new WriteSheet();sheet.setSheetName(params.getSheetName());sheet.setSheetNo(1);ExcelWriter writer = builder.build();writer.write(params.getData(), sheet);if (!CollectionUtils.isEmpty(params.getMergeCellIndices())) {for (EasyExcelParams.MergeCellIndex mergeCellIndex : params.getMergeCellIndices()) {writer.merge(mergeCellIndex.getFirstRowIndex(), mergeCellIndex.getLastRowIndex(), mergeCellIndex.getFirstColumnIndex(), mergeCellIndex.getLastColumnIndex());}}writer.finish();outputStream.close();}/*** 将文件输出到浏览器(导出)*/private static void prepareResponds(String fileName, HttpServletResponse response) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");fileName = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename*=utf-8'zh_cn'" + fileName + ExcelTypeEnum.XLSX.getValue());}/*** 校验导入文件是否是Excel格式*/public static boolean checkExcelStyle(MultipartFile file) {String filename = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf('.'));return filename.equals(ExcelTypeEnum.XLSX.getValue());}/*** 检查Excel的密码*/public static boolean checkExcelPassword(String secretCode) {return EXCEL_SECRET_CODE.equals(secretCode);}/*** 检查Excel的密码*/public static boolean checkExcelVersion(String version) {return EXCEL_VERSION.equals(version);}}
===========================================
导入相关代码：
controller：MultipartFile作为参数传入，如果有业务还可以传递需要参数。
@PostMapping(value = "/test/upload")public Object standardUpload(@RequestParam("file") MultipartFile file, @RequestParam("id") String id) {try {UploadStandardDeviceListener listener = new UploadStandardDeviceListener(Lists.newArrayList());//导入数据EasyExcel.read(file.getInputStream(), UploadVO.class, listener).sheet().doRead();List<UploadVO> detailList = listener.getDetailList();if (!detailList.isEmpty()) {//业务逻辑}} catch (IOException e) {return ERROR;}return success();}
导入监听器：
UploadStandardDeviceListener：使用easyexcel导入excel时需要自写一个监听器去实现 （AnalysisEventListener）类，泛型为你需要导入数据对应实体类，在 （invoke）方法中获取数据做相应的逻辑处理最终放入全局集合中进行保存使用。
@Slf4j@Setter@Getter@ToString@NoArgsConstructorpublic class UploadStandardDeviceListener extends AnalysisEventListener<UploadVO> {private List<UploadVO> detailList;@Autowiredpublic UploadStandardDeviceListener(List<UploadVO> detailList) {this.detailList = detailList;}@Overridepublic void invoke(UploadVO data, AnalysisContext analysisContext) {detailList.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}/*** 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。* @param exception 异常* @param context 上下文*/@Overridepublic void onException(Exception exception, AnalysisContext context) {log.error("解析失败,但是继续解析下一行:{}", exception.getMessage());//如果是某一个单元格的转换异常,获取具体行号//如果要获取头的信息 配合 invokeHeadMap 使用if (exception instanceof ExcelDataConvertException) {ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;log.error("第{}行,第{}列请输入数值类型", excelDataConvertException.getRowIndex(),excelDataConvertException.getColumnIndex());}}}
导入实体类：
UploadStandardVO：因为有合并表头的需求，所以在导入的时候需要拿下标索引去取值，否则获取不到。数值类型要使用 int 去接收，如果是Integer类型去接收如果用户不填会出现空指针。
@Setter@Getter@ToString@ContentRowHeight(20)@HeadRowHeight(20)@ColumnWidth(25) public class UploadStandardVO {//表头名称@ExcelProperty(value = "id")private String id;//表头合并 索引下标@ExcelProperty(index = 19)private int equipmentOne;//排除该字段@ExcelIgnoreprivate String createUser;}
贴出最终结果：!
好啦，虽然过程踩到很多坑， 但是最终的结果还是很完美，如果对你有所帮助就点个赞吧！
easyExcel导入导出(列锁定单元格 表头合并 导出类型限制 锁定单元格增加底色 设置密码 隐藏列等)
