easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等) |
您所在的位置：网站首页 › excel怎么设置不可编辑 › easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等) |
easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等)
|
easyexcel官网文档：https://www.yuque.com/easyexcel/doc/easyexcel easyexcel {maven 版本} GitHub网址：https://github.com/alibaba/easyexcel com.alibaba easyexcel 2.1.4导出相关代码 controller： @PostMapping(value = "/test/export") public Object test(HttpServletResponse response) { try { //获取要导出的数据 List deviceData = mapper.findDeviceData(); //获取导出数据总条数 传入做校验格式使用 List 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.exportExcel2007Format(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; //排除该字段 @ExcelIgnore private String createUser; }导出相关工具类： EasyExcelParams：参数类，定义需要的一些数据字段。 @Setter @Getter @ToString public 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 mergeCellIndices; @Setter @Getter @ToString @NoArgsConstructor @AllArgsConstructor public 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 mergeCellIndices) { this.mergeCellIndices = mergeCellIndices; } }导出格式配置类： ExcelStyleConfig：导出样式配置类，需要继承（CellWriteHandler）写入handler类来重写里边的方法做逻辑处理，支持隐藏列，锁定列，表单保护密码，表单背景颜色，数据格式校验限制等，如果需要更多功能，请参照案例自行实现。 @Slf4j public class ExcelStyleConfig implements CellWriteHandler { /** * 需要锁定的列集合 */ private List columnList; /** * 样式类 */ private CellStyle cellStyle; /** * 隐藏索引数 */ private List hiddenIndices; /** * 限制那一列为数值型 开头结尾 */ private List columnNumList; /** * 限制哪一行为数值开头结尾集合 */ private List rowNumList; public ExcelStyleConfig(List columnList) { this.columnList = columnList; } public ExcelStyleConfig(List columnList, List columnNumList, List hiddenIndices, List rowNumList) { this.columnList = columnList; this.hiddenIndices = hiddenIndices; this.columnNumList = columnNumList; this.rowNumList = rowNumList; } public ExcelStyleConfig(List columnList, List columnNumList) { this.columnList = columnList; this.columnNumList = columnNumList; } public ExcelStyleConfig(List columnList, List columnNumList, List hiddenIndices) { this.columnList = columnList; this.columnNumList = columnNumList; this.hiddenIndices = hiddenIndices; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle(); } @Override public 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); } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } }导出工具类： EasyExcelUtil：封装一些方法导出数据到excel，导出自动关闭流，不需要手动去关闭，如需要扩展自行定义。 @Component public class EasyExcelUtil { private static final String EXCEL_SECRET_CODE = "intelligence-password"; private static final String EXCEL_VERSION = "1.0"; private EasyExcelUtil() { } /** * 导出2007版Excel */ public static void exportExcel2007Format(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 detailList = listener.getDetailList(); if (!detailList.isEmpty()) { //业务逻辑 } } catch (IOException e) { return ERROR; } return success(); }导入监听器： UploadStandardDeviceListener：使用easyexcel导入excel时需要自写一个监听器去实现 （AnalysisEventListener）类，泛型为你需要导入数据对应实体类，在 （invoke）方法中获取数据做相应的逻辑处理最终放入全局集合中进行保存使用。 @Slf4j @Setter @Getter @ToString @NoArgsConstructor public class UploadStandardDeviceListener extends AnalysisEventListener { private List detailList; @Autowired public UploadStandardDeviceListener(List detailList) { this.detailList = detailList; } @Override public void invoke(UploadVO data, AnalysisContext analysisContext) { detailList.add(data); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { } /** * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。 * @param exception 异常 * @param context 上下文 */ @Override public 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; //排除该字段 @ExcelIgnore private String createUser; }贴出最终结果：! 好啦，虽然过程踩到很多坑， 但是最终的结果还是很完美，如果对你有所帮助就点个赞吧！ |
【本文地址】
今日新闻 |
推荐新闻 |