阿里的easyExcel

一.easyExcel导出bigDecimal类型数据并设置千分号显示

1.实体属性

 @ExcelProperty(value = "实际认款金额", index = 10 )
    BigDecimal clamAmount;

2.实现抽象类AbstractCellStyleStrategy

我本来是重写了CellWriteHandler，但是没生效

翻源码时发现，AbstractCellStyleStrategy也实现了CellWriteHandler，并且也重写了我本来重写的

afterCellDispose方法，所以肯定是他把我覆盖了，里边经过了其他的处理，才导致我的不生效

所以我就直接重写了AbstractCellStyleStrategy，去重写AbstractCellStyleStrategy中的afterCellDispose方法，完美解决问题

public class CustomCellWriteHandler extends AbstractCellStyleStrategy {

    //金额
    List<String> amountFields = Arrays.asList("clamAmount","overseasCharge","overseasTaxAmount","factoringFee","cashShort","nonBusinessIncome",
            "receivableAmount","invoiceAmount","receiptAmount","clamedAmount","lastClamedAmount");
    //百分比
    List<String> termsRateList = Arrays.asList("termsRate");
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
       if(!isHead){
           String fieldName = head.getFieldName();
           Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();
           CellStyle cellStyle = workbook.createCellStyle();
           if(amountFields.contains(fieldName)){
                //金额
               short amountFormat = HSSFDataFormat.getBuiltinFormat("#,##0.00");
               cellStyle.setDataFormat(amountFormat);
               cell.setCellStyle(cellStyle);
           }else if(termsRateList.contains(fieldName)){
               //百分比
               short rateFormat = HSSFDataFormat.getBuiltinFormat("0.00%");
               cellStyle.setDataFormat(rateFormat);
               cell.setCellStyle(cellStyle);
           }
       }
    }
}

3.调用

wirteHandler就是传入刚重写的AbstractCellStyleStrategy 

 WriteSheet writeSheet = EasyExcel
                        .writerSheet(sheetNo, sheetNameMap.get(m.getKey()))
                        .head(m.getKey())
                        .registerWriteHandler(writeHandler).build();

4.测试结果

满足两个条件 1.千分号保留两位小数  2.可以进行加和计算

二.时间转换

 直接用easyExcel的@DateTimeFormat注解搞定，不用繁琐的转换了

    @ExcelProperty(value = "到款日期", index = 8)
    @DateTimeFormat(value = DateUtil.PATTERN_YYYYMMDD)
    Date receiptTime;

三.追加

The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook

用上边那个方法，数据少没事，数据多了就报错了

修改如下  每个类型new一个，这样就避免new了多次报错

    //金额
    List<String> amountFields = Arrays.asList("clamAmount","overseasCharge","overseasTaxAmount","factoringFee","cashShort","nonBusinessIncome",
            "receivableAmount","invoiceAmount","receiptAmount","clamedAmount","lastClamedAmount","refundAmount");
    //百分比
    List<String> termsRateList = Arrays.asList("termsRate");

    private CellStyle amountCellStyle;

    private CellStyle rateCellStyle;

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
       if(!isHead){
           String fieldName = head.getFieldName();
           Workbook workbook = writeSheetHolder.getParentWriteWorkbookHolder().getWorkbook();
           if(amountFields.contains(fieldName)){
               if(Objects.isNull(amountCellStyle)){
                   amountCellStyle = workbook.createCellStyle();
               }
                //金额
               short amountFormat = HSSFDataFormat.getBuiltinFormat("#,##0.00");
               amountCellStyle.setDataFormat(amountFormat);
               cell.setCellStyle(amountCellStyle);
           }else if(termsRateList.contains(fieldName)){
               if(Objects.isNull(rateCellStyle)){
                   rateCellStyle = workbook.createCellStyle();
               }
               //百分比
               short rateFormat = HSSFDataFormat.getBuiltinFormat("0.00%");
               rateCellStyle.setDataFormat(rateFormat);
               cell.setCellStyle(rateCellStyle);
           }
       }
    }

 重点代码

    private CellStyle amountCellStyle;

    private CellStyle rateCellStyle;

    if(Objects.isNull(amountCellStyle)){
                   amountCellStyle = workbook.createCellStyle();
               }

    if(Objects.isNull(rateCellStyle)){
                   rateCellStyle = workbook.createCellStyle();
               }