Java EasyExcel 随记

JAR

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

入口

EasyExcel.write(response.getOutputStream(), 导出实体类.class).sheet("导出EXCEL名称")
                    .registerWriteHandler(new CustomSheetWriteHandler(subpackageWorkerRegisterService, export.size(), export.size() + 3, export.size() + 10, 0, 18)).doWrite(export);

描述

• CustomSheetWriteHandler : 新建类名称
• sheetNumber : 导出列表数据长度
• firstRow : 起始行
• lastRow : 截止行
• firstCol : 起始列
• lastCol : 截止列

HEAD注解

@HeadRowHeight(36)
@HeadFontStyle(fontName = "宋体",fontHeightInPoints = 16)//表头字体样式
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,verticalAlignment = VerticalAlignment.CENTER)//内容样式,垂直水平居中

字段注解

@HeadFontStyle(color= 10)
@ColumnWidth(16)
@ExcelProperty(value = "*是否农民工", index = 3)

描述

• HeadFontStyle : 当前字段名称样式
• ColumnWidth : 当前字段宽度
• ExcelProperty ：value-字段名称 index-Excel中从左到右索引
• color ：字段颜色 (10:红色)

下拉框

新建类实现方法(implements SheetWriteHandler)

@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

}

@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    //定义一个map key是需要添加下拉框的列的index value是下拉框数据
    Map<Integer, String[]> mapDropDown = new HashMap<>(3);
    //是否农民工
    String[] migrantWorker = {"是", "否"};

    //用工类别
    String[] employmentCategory = {"合同工", "劳务派遣"};

    //银行卡开户行
    List<String> dictListName = subpackageWorkerRegisterService.getDictListName();
    String[] openingBankCode = null;
    if (null != dictListName && 0 < dictListName.size()) {
        openingBankCode = new String[dictListName.size()];
        for (int i = 0; i < dictListName.size(); i++) {
            String name = dictListName.get(i);
            openingBankCode[i] = name;
        }
    } else {
        openingBankCode = new String[]{"东莞银行", "中信银行", "中国银行"};
    }

    //下拉选在Excel中对应的列
    mapDropDown.put(3, migrantWorker);
    mapDropDown.put(4, employmentCategory);
    mapDropDown.put(5, openingBankCode);

    //获取工作簿
    Sheet sheet = writeSheetHolder.getSheet();

    ///开始设置下拉框
    DataValidationHelper helper = sheet.getDataValidationHelper();
    //设置下拉框
    for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
        /*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**/
        //sheetNumber:导出列表数据长度
        CellRangeAddressList addressList = new CellRangeAddressList(1, sheetNumber, entry.getKey(), entry.getKey());
        /*设置下拉框数据**/
        DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        sheet.addValidationData(dataValidation);
    }
}

合并单元格并设置值

新建类实现方法(implements CellWriteHandler)

@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 aBoolean) {
    //当前行
    int curRowIndex = cell.getRowIndex();
    //当前列
    int curColIndex = cell.getColumnIndex();

    //合并单元格并设置样式和内容
    if (curRowIndex == sheetNumber) {
        if (0 == curColIndex) {
            Sheet sheet = cell.getSheet();
            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列),行和列都是从0开始计数，且起始结束都会合并
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.addMergedRegion(region);

            Workbook workbook = cell.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setWrapText(true);
            cellStyle.setLocked(true);//设置锁定
            cellStyle.setAlignment(HorizontalAlignment.LEFT);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setBold(true);
            font.setColor(IndexedColors.RED.getIndex());
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);

            Row row = sheet.createRow(firstRow);
            Cell cell2 = row.createCell(0);

            cell2.setCellStyle(cellStyle);
            cell2.setCellValue("备注信息如下:\r\n" + "1、有'*'标注的为必填项\r\n" + "2、编辑红色标题EXCEL内容\r\n" + "3、只更新红色标题内容信息\r\n" + "4、用工类别:合同工、劳务派遣\r\n" + "5、银行卡开户行:若下拉无对应银行名称向客服反馈\r\n" + "6、标题为红色内容会覆盖现有数据\r\n");
        }
    }
}