The new Excel framework realizes the import and export of complex tables, and the highlight display. Realize the functions that EasyExcel can't realize

Export database, export Excel table and highlight it

1. Highlight lines, customize the style of highlight lines according to each line of data

Insert picture description here


Sample code

/**
 * 高亮符合条件的行
 */
private static void rowHighLight(List<ExcelExportTemplateForQuarter> quarterList, Workbook workbook) throws Exception {
    /**
     * 某些行高亮展示
     */
    // 3种样式
    final List<CellStyle> cellStyleList = Arrays.asList(
            CellStyleEntity.builder().fontName("微软雅黑").fontSize(12).bgColor(9).build().getCellStyle(workbook),
            CellStyleEntity.builder().fontSize(12).bgColor(9).foregroundColor(13).build().getCellStyle(workbook),
            CellStyleEntity.builder().fontName("微软雅黑").fontSize(12).bgColor(10).build().getCellStyle(workbook)
    );
    // 函数式接口,返回样式的下标
    final Function<ExcelExportTemplateForQuarter,Integer> functional = (one) -> {
        if (one.getRiskNature().equals("技术违约") && (one.getYear() % 2 == 0 || one.getQuarter() == 3)) {
            return 1;
        } else {
            return 0;
        }
    };
    // 根据逻辑来使用函数时接口返回样式的下标,然后就会将样式注入进去
    ExcelImportExportUtils.filledListToSheetWithCellStyleByFunction(quarterList, cellStyleList, (one) -> {
        if (one.getRiskNature().equals("技术违约") && (one.getYear() % 2 == 0 || one.getQuarter() == 3)) {
            return 1;
        }else {
            return 0;
        }
    }, workbook.getSheetAt(0));
}

2. Highlight the cells that meet the conditions (this method can achieve all the highlighting of excel)

Insert picture description here


Sample code

/**
 * 指定标题下的单元格 部分高亮
 */
private static void titlePredicate(List<ExcelExportTemplateForQuarter> quarterList, Workbook workbook, Sheet sheet) throws Exception {
    final CellStyle cellStyle = CellStyleEntity.builder().fontName("微软雅黑").bold(true).fontSize(12).build().getCellStyle(workbook);
    final CellStyle cellStyle3 = CellStyleEntity.builder().fontSize(12).fontColor(14).foregroundColor(13).build().getCellStyle(workbook);
    final CellStyle cellStyle4 = CellStyleEntity.builder().fontSize(12).fontColor(10).bold(true).fontColor(14).foregroundColor(40).build().getCellStyle(workbook);
    final TitlePredicateList<ExcelExportTemplateForQuarter> predicateList = new TitlePredicateList<>();
    // 提供断言处理
    Predicate<ExcelExportTemplateForQuarter> predicate = (e) -> {
        String regex = ".*市";// 高亮市
        final Pattern pattern = Pattern.compile(regex);
        final Matcher matcher = pattern.matcher(e.getRegionCode().split(",")[0]);
        if (matcher.matches()) {
            return true;
        }
        return false;
    };
    Predicate<ExcelExportTemplateForQuarter> predicate2 = (e) -> {
        String regex = ".*市";// 高亮市
        final Pattern pattern = Pattern.compile(regex);
        final Matcher matcher = pattern.matcher(e.getRegionCode().split(",")[1]);
        if (matcher.matches()) {
            return true;
        }
        return false;
    };
    Predicate<ExcelExportTemplateForQuarter> predicate3 = (e) -> {
        if (e.getRiskNature().equals("管理失误违约")) {
            return true;
        }
        return false;
    };
    // 高亮时间,第3季度的背景色设置为蓝色,字体红色加粗
    Predicate<ExcelExportTemplateForQuarter> predicate4 = (e) -> {
        if (e.getQuarter() == 3) {
            return true;
        }
        return false;
    };
    // 表示标题("市州") 用那个样式(cellStyke), 依据断言(断言型接口返回布尔值) 使样式生效
    final List<TitleCellStylePredicate<ExcelExportTemplateForQuarter>> titlePredicateList = predicateList
            .add("市州", cellStyle, predicate)
            .add("区县", cellStyle, predicate2)
            .add("风险性质", cellStyle3, predicate3)
            .add("时间", cellStyle4, predicate4)
            .getTitlePredicateList();
    ExcelImportExportUtils.filledListToSheetWithCellStyleByBatchTitlePredicate(quarterList, titlePredicateList, sheet);
}

You may ask, isn’t there easyexcel? The stars of github 20k are awesome! And it's also Ali's open source project.

When I wrote my own Excel import and export framework, I didn't understand easyExcel. The starting point was different, saying that easyExcel has high performance.


After a few weeks of hard work, the first version was completed, without considering performance issues for the time being. If there are other people using it, there are many people who use it. Then consider optimization, or clone by yourself and then perform performance optimization.

Talk about the unrealized functions of easyExcel

1. In the export function design: the function that a field needs to be split into multiple cells is not implemented

For example, there is an entity field with a region information, the content is XX city XX district/county, this information is obtained through a multi-level dictionary.
For convenience, I separate them by commas XX市,XX区, and then I need to fill them in the, 市州and 区县two cells under the area respectively . EasyExcel did not fulfill my needs. As an extension, I also added a formatted output, which can fill in some auxiliary information before and after the content to be filled.

2. In the design of the import function: ①The content of a certain field is not considered as a combination of multiple cells, which is the reverse process. ②In addition, consider that the content of the field is part of the content of the cell. ③The problem of unit conversion is not considered

The case of ②: There are fields for year and quarter. The data of these two fields comes from the same cell content 时间, for example, there is one data 2020年第4季度.
The case of ③: For example, the total default scale unit is 亿, in the framework I designed, the unit and type conversion will be automatically converted.

In the process of designing the import, I thought of using a dictionary to map the cell content, but declarative annotations can’t inject objects other than basic types and String and enumeration. I wanted to inject a HashMap and then take out the cell content. , And then convert it through the dictionary, and then store it in the field, but it can’t be achieved. This mapping may have to be updated based on the dictionary in the database.


If you want to see the source code and experience the function, you can run the test case of the compilation number in the clone project.
Highlighted test case code