easyExcel自定义单元格

• 配置
• • 依赖
  • 对象
• 写excel
• • 导出excel方法
  • 实现CellWriteHandler接口
• 读excel

配置

依赖

 <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.11</version>
        </dependency>

对象

@Data
@Accessors(chain = true)
@HeadFontStyle(fontName = "等线", fontHeightInPoints = 10)
public class ApproveList {

    @ExcelProperty(value = "approveResult",index = 0)
    private String approveResult;

    private String remark;

    private String id;

    private String pn;

    private String detail;
 }

写excel

导出excel方法

public void download(HttpServletResponse response,ApproveList approveList){
       String fileName =
                URLEncoder.encode(
                        ("ApproveList"  + dateFormat.format(new Date())), "UTF-8");

        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        
        //设置approveResult为下拉选择框，设置选择框的枚举值
        approveList.getApproveResult();
        HashMap<Integer, String[]> dropDownMap = new HashMap<>();
        dropDownMap.put(0,new String[]{"Approve","Reject"});

        //设置哪些列不可编辑，字段索引
        List<Integer> columnIndex = new ArrayList<>();
        for (int i = 2; i < 4; i++) {
            columnIndex.add(i);
        }

        try {
            EasyExcel.write(response.getOutputStream(), ApproveList.class).
                    registerWriteHandler(new CustomCellWriteHandler())
                    .registerWriteHandler(new ContentCellWriteHandler(columnIndex,dropDownMap))
                    .sheet("ApproveList").doWrite(approveList);
        } catch (IOException e) {
            e.printStackTrace();
        }
}

实现CellWriteHandler接口

public class ContentCellWriteHandler implements CellWriteHandler {

    private List<Integer> columnIndexs;
    private Short colorIndex;
    private HashMap<Integer,String> annotationsMap;
    private HashMap<Integer,String[]> dropDownMap;

    public ContentCellWriteHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer,String> annotationsMap){
        this.columnIndexs=columnIndexs;
        this.colorIndex=colorIndex;
        this.annotationsMap=annotationsMap;
    }

    public ContentCellWriteHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer,String> annotationsMap, HashMap<Integer,String[]> dropDownMap){
        this.columnIndexs=columnIndexs;
        this.colorIndex=colorIndex;
        this.annotationsMap=annotationsMap;
        this.dropDownMap=dropDownMap;
    }

    public ContentCellWriteHandler(List<Integer> columnIndexs, HashMap<Integer,String[]> dropDownMap){
        this.columnIndexs=columnIndexs;
        this.dropDownMap=dropDownMap;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
        if (!isHead){
            Sheet sheet = writeSheetHolder.getSheet();

            if (CollectionUtils.isNotEmpty(columnIndexs)){
                Workbook workbook = sheet.getWorkbook();
//                CellStyle cellStyle1 = StyleUtil.buildHeadCellStyle(workbook, writeFont);
                /* 这里要重新create不锁定的列样式 */
                CellStyle unlockCellStyle = workbook.createCellStyle();
                unlockCellStyle.setLocked(false);//默认是锁定状态；将所有单元格设置为：未锁定；然后再对需要上锁的单元格单独锁定
                cell.setCellStyle(unlockCellStyle);
                if (colorIndex != null && columnIndexs.contains(cell.getColumnIndex())){
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    WriteFont writeFont = new WriteFont();
                    writeFont.setColor(colorIndex);
                    writeCellStyle.setWriteFont(writeFont);
                }
                /* 这里可以根据需要进行判断;我这就将columnIndexs列上锁了 */
                if (cell.getColumnIndex() != 1 && columnIndexs.contains(cell.getColumnIndex())){
                    // 设置表单保护密码
                    writeSheetHolder.getSheet().protectSheet("password");
                    // 设置锁定单元格
//                    CellStyle cellLockStyle = StyleUtil.buildHeadCellStyle(workbook, writeFont);
                    /*  这里要重新create列锁定的列样式，*/
                    CellStyle cellLockStyle = workbook.createCellStyle();
                    cellLockStyle.setLocked(true);
                    cellLockStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellLockStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                    cellLockStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellLockStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                    cellLockStyle.setBorderBottom(BorderStyle.THIN);
                    cellLockStyle.setBorderTop(BorderStyle.THIN);
                    cellLockStyle.setBorderLeft(BorderStyle.THIN);
                    cellLockStyle.setBorderRight(BorderStyle.THIN);
                    cellLockStyle.setWrapText(true);
                    cell.setCellStyle(cellLockStyle);
                }

            }
            if (annotationsMap != null && annotationsMap.containsKey(cell.getColumnIndex())){
                String context = annotationsMap.get(cell.getColumnIndex());
                Comment cellComment =sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
                cellComment.setString(new XSSFRichTextString(context));
                cell.setCellComment(cellComment);
            }
            //设置下拉框
            if (dropDownMap !=null && dropDownMap.containsKey(cell.getColumnIndex())){
                String[] datas = dropDownMap.get(cell.getColumnIndex());
                DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
                DataValidationConstraint dvConstraint = dataValidationHelper.createExplicitListConstraint(datas);
                CellRangeAddressList addressList = null;
                DataValidation validation = null;
                for (int i = 1; i < 1000; i++) {
                    addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
                    validation = dataValidationHelper.createValidation(
                            dvConstraint, addressList);
                    sheet.addValidationData(validation);
                }
            }
        }
    }
}

• cellLockStyle.setLocked(true);设置为不可编辑，但导出后发现所有单元格均不可编辑：
  • 因为createCell();创建单元格后，单元格默认是锁定状态；
  • protectSheet(“密码”);保护工作表是保护所有锁定的单元格；

导出效果：

读excel

对象中设置@Accessors(chain = true)，easyExcel能读取行数据，但是解析不了，也就是映射不了相应的类。