easyExcel的读写及自定义格式

                                                         一:excel的读取

1  首先要定义读取对象

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class UserVO {
    /**
     * 用户id
     */
    @ExcelProperty(value = {"个人信息01", "用户id"}, index = 0,converter = IdConverter.class)
    private Integer id;

    /**
     * 用户名
     */
    @ExcelProperty(value = {"个人信息01", "用户名"}, index = 1,converter = StringConverter.class)
    private String name;
}

2 对象可定义 转换器便于做一些复杂操作（读取的数据有默认的格式需要格外注意）

String value = cellData.getStringValue();
cellData.getNumberValue();
cellData.getBooleanValue();   这种格式不对时是读取不到数据的

public class StringConverter implements Converter<String> {

   

    /**
     * Java 字段的数据类型-String
     */
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    /**
     * Excel文件中单元格的数据类型-String
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 读取 Excel 文件时调用，将 String 类型单元格的值转为 Integer 类型的 Java 字段
     */
    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        // 获取字符串类型单元格的值
        String value = cellData.getStringValue();
        return HalfAndFullConverseUtil.ToDBC(value);
    }
}

3 定义读取是的监听器，这样方便获取读取后的数据集合

@Slf4j
public class EasyExcelListener extends AnalysisEventListener<UserVO> {

    public List<UserVO> importList = new ArrayList<>();
    public Map<String, String> checkMap = new HashMap<>();
    //    public static ThreadLocal<List<UserVO>> readRecords=new ThreadLocal();
    int x = 0;
    String[] title1 = {"个人信息02", "个人信息02", "个人信息03"};
    String[] title2 = {"用户id", "用户名", "年龄", "性别", "地址", "创建时间", "工资"};

    //读取每一行时执行
    @Override
    public void invoke(UserVO data, AnalysisContext context) {
        val rowIndex = context.readSheetHolder().getRowIndex();
        System.err.println(rowIndex);

        log.info(Thread.currentThread().getName() + "解析到的一条数据: excelRow = {}", data);
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        importList.add(data);
    }
  
    //读取完全部数据执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 解析完所有excel行, 保存到数据库或进行业务处理
        log.info(Thread.currentThread().getName() + "解析的所有数据 list = {}", importList);

    }
    
    //读取表头行时执行
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info(Thread.currentThread().getName() + "表头数据 " + x + "excelHead= {}", headMap);

        log.info(Thread.currentThread().getName() + "校验 map = {}", checkMap);
        if (x == 0) {
            for (int i = 0; i < title1.length; i++) {
                if (!headMap.get(i).equals(title1[i])) {
                    checkMap.put("err", "1");
                    break;
                }
            }
        }
        if (1 == x) {
            for (int i = 0; i < title2.length; i++) {
                if (!headMap.get(i).equals(title2[i])) {
                    checkMap.put("err", "1");
                    break;
                }
            }
        }
        x++;
    }
}

4 读取就很简单了 

    public static void main(String[] args) throws IOException {
        EasyExcelListener lr1=new EasyExcelListener();
        // 读取文件路径
        // 这里需要指定用哪个 class 去读，然后读取第一个 sheet 文件流就会自动关闭
        EasyExcel.read("D:\\project\\write01.xlsx", UserVO.class, lr1).sheet("TEST").doRead();
        //也可定义一个public的方法去获取属性
        System.err.println(lr1.importList);
        System.err.println(lr1.checkMap);
    }

                                                        二：excel的写入

1 

    public static void main(String[] args) {
        //定义写入的数据集合
        List list=new ArrayList();
        UnitExcelDownVoOne record=new UnitExcelDownVoOne();
        record.setLevel("国家级示范校");
        list.add(record);
        //定义writer    定义写入对象   registerWriteHandler可定义多个
        ExcelWriter excelWriter = EasyExcel.write("d://test.xlsx").registerWriteHandler(new CustomSheetWriteHandler()).head(UnitExcelDownVoOne.class)
                .registerWriteHandler(new DateSheetWriteHandler()).registerWriteHandler(new CustomCellStyleStrategy()).build();
        //定义sheet
        WriteSheet writeSheet1 = EasyExcel.writerSheet(0,"11111").build();
        WriteSheet writeSheet2 = EasyExcel.writerSheet(1,"22222").build();

        excelWriter.write(list,writeSheet1);
        excelWriter.write(list,writeSheet2);
        //一定要finish否则文件损坏
        excelWriter.finish();
    }

2 定义下拉列表数据

public class CustomSheetWriteHandler 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[] unitIdentity = {"职教集团成员单位","拟合作单位","合作单位"};

        //地区
        String[] area = {"国内","国外"};

        //等级下拉选
        String[] level = {"国家级示范校","国家级骨干校","省级示范校","省级骨干校","其他"};

        //年份下拉选
        String[] joinYear = {"1990年","1991年","1992年","1993年","1994年","1995年","1996年","1997年","1998年",
                "1999年","2000年","2001年","2002年","2003年","2004年","2005年","2006年","2007年","2008年","2009年","2010年","2011年","2012年",
                "2013年","2014年","2015年","2016年","2017年","2018年","2019年","2020年","2021年","2022年"};

        //下拉选在Excel中对应的列
        mapDropDown.put(0,unitIdentity);
        mapDropDown.put(1,joinYear);
        mapDropDown.put(4,area);
        mapDropDown.put(10,level);

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

3 自定义样式(未写入数据的行)

public class DateSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        //获取工作部
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        //获取sheet
        Sheet sheet = writeSheetHolder.getSheet();
        //创建一个样式
        CellStyle cellStyle = workbook.createCellStyle();
        //workbook创建一个时间格式  然后获取时间输入格式
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
        //某列开始
        sheet.setDefaultColumnStyle(18,cellStyle);

    }
}

针对填充数据的行----表头样式也在这设置

@Data
@NoArgsConstructor
public class CustomCellStyleStrategy extends AbstractVerticalCellStyleStrategy {

    /**
     * 操作列
     */
    private List<Integer> columnIndexes;

    public CustomCellStyleStrategy( List<Integer> columnIndexes ) {
        this.columnIndexes = columnIndexes;
    }


    @Override
    protected WriteCellStyle contentCellStyle(CellWriteHandlerContext context) {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        if(context.getRow().getRowNum()>=1&&context.getColumnIndex()==18){
            DataFormatData forma=new DataFormatData();
            forma.setFormat("yyyy-MM-dd");
            headWriteCellStyle.setDataFormatData(forma);
        }
        return headWriteCellStyle;
    }

    @Override
    protected WriteCellStyle headCellStyle(Head head) {

        // 获取样式实例
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 获取字体实例
        WriteFont headWriteFont = new WriteFont();
        // 设置字体样式
        headWriteFont.setFontName("宋体");
        // 设置字体大小
        headWriteFont.setFontHeightInPoints((short)20);
        // 边框
        headWriteFont.setBold(true);
//        // 设置表头单元格必填时为红色
//        if (columnIndexes.contains(head.getColumnIndex())) {
//            headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//        }
//        //非必填时为蓝色
//        else{
//            headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
//        }
        headWriteCellStyle.setWriteFont(headWriteFont);

        return headWriteCellStyle;
    }
}

或者

public class WriteContentStyle extends AbstractCellStyleStrategy {

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        super.setHeadCellStyle(cell, head, relativeRowIndex);
    }

    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {
        DataFormat dataFormat = context.getWriteWorkbookHolder().getWorkbook().createDataFormat();
        WriteCellStyle writeCellStyle=new WriteCellStyle();
        DataFormatData format=new DataFormatData();
        format.setIndex(dataFormat.getFormat("yyyy/MM/dd"));
        writeCellStyle.setDataFormatData(format);
        //3行16列时合并样式
        if(context.getRowIndex()==3&&context.getColumnIndex()==16){
            WriteCellStyle.merge(writeCellStyle,context.getFirstCellData().getOrCreateStyle());
        }
        
    }
}