easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等)

最新推荐文章于 2024-05-16 08:33:51 发布

置顶我是一切问题的根源

最新推荐文章于 2024-05-16 08:33:51 发布

阅读量1.1w

点赞数 15

本文链接：https://blog.csdn.net/weixin_44990626/article/details/107389814

版权

easyexcel官网文档：https://www.yuque.com/easyexcel/doc/easyexcel
easyexcel {maven 版本}
GitHub网址：https://github.com/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.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<MergeCellIndex> 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<MergeCellIndex> mergeCellIndices) {
        this.mergeCellIndices = mergeCellIndices;
    }
}

导出格式配置类：
ExcelStyleConfig：导出样式配置类，需要继承（CellWriteHandler）写入handler类来重写里边的方法做逻辑处理，支持隐藏列，锁定列，表单保护密码，表单背景颜色，数据格式校验限制等，如果需要更多功能，请参照案例自行实现。

@Slf4j
public 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;
    }
    @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<CellData> 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<UploadVO> 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<UploadVO> {
    private List<UploadVO> detailList;
    @Autowired
    public UploadStandardDeviceListener(List<UploadVO> 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;
}

贴出最终结果：! 锁定单元格提示表头合并
输入数值类型超范围错误提示
输入字符类型错误提示

好啦，虽然过程踩到很多坑，但是最终的结果还是很完美，如果对你有所帮助就点个赞吧！

我是一切问题的根源

关注

15
点赞
踩
68

收藏

觉得还不错? 一键收藏
3
评论
easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等)

加粗样式你可以找到更多关于的信息 LaTeX 数学表达式here.新的甘特图功能，丰富你的文章Mon 06Mon 13Mon 20已完成进行中计划一计划二现有任务Adding GANTT diagram functionality to mermaid关于甘特图语法，参考这儿,UML 图表可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：
复制链接

扫一扫

easyExcel导入导出(列锁定单元格、表头合并、导出类型限制、锁定单元格增加底色、设置密码、隐藏列等)

“相关推荐”对你有帮助么？