SpringBatch自定义Excel文件写入器

一:场景需求
工作中需要对数据库中的数据进行报表导出,所以用到了poi包里的excel工具.为了适配springbatch的reader–>processor–>writer模式,决定将excel写入的方式改造成类似org.springframework.batch.item.file.FlatFileItemWriter的方式,可以很好地利用springbatch的一些特性(比如分片读取并写入以提高效率).更优雅的实现Excel的数据写入;
实现功能:
使用mybatis读取一千条数据,经processor对每一条数据处理后,用ExcelItemWriter写入到Excel中.
二:代码实现
主要类
ExcelAggregator 聚集器:处理每一行数据的处理接口
ExcelLineAggregator 聚集器行处理器实现类
ExcelCellStyleCallback 单元格格式回调接口
ExcelHeaderCallback 表格头信息回调接口
ExcelItemWriter 主要的Excel写入器

1.ExcelAggregator

public interface ExcelAggregator<T> {
   
    Object[] aggregate(T item);
}

2.ExcelLineAggregator

/**
 * @author: m
 * @date: 2020/12/02
 * @Description: Excel的行处理器
 */
public class ExcelLineAggregator<T> implements ExcelAggregator<T> {
   
    private FieldExtractor<T> fieldExtractor = new PassThroughFieldExtractor<T>();

    /**
     * Public setter for the field extractor responsible for splitting an input
     * object up into an array of objects. Defaults to
     * {@link PassThroughFieldExtractor}.
     *
     * @param fieldExtractor The field extractor to set
     */
    public void setFieldExtractor(FieldExtractor<T> fieldExtractor) {
   
        this.fieldExtractor = fieldExtractor;
    }

    /**
     * Extract fields from the given item using the {@link FieldExtractor} and
     * then aggregate them. Any null field returned by the extractor will be
     * replaced by an empty String. Null items are not allowed.
     *
     * @see org.springframework.batch.item.file.transform.LineAggregator#aggregate(java.lang.Object)
     */
    @Override
    public Object[] aggregate(T item) {
   
        Assert.notNull(item);
        Object[] fields = this.fieldExtractor.extract(item);
        return fields;
        //
        // Replace nulls with empty strings
        //
        // Object[] args = new Object[fields.length];
        // for (int i = 0; i < fields.length; i++) {
   
        //     if (fields[i] == null) {
   
        //         args[i] = "";
        //     }
        //     else {
   
        //         args[i] = fields[i];
        //     }
        // }

    }
}

3.ExcelCellStyleCallback

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;

public interface ExcelCellStyleCallback {
   
    CellStyle getBorderCellStyle(Workbook workbook);
}

3.1 AllBorderCellStyle 单元格格式实现类

import com.loan.batch.item.ExcelCellStyleCallback;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.context.annotation.Configuration;

/**
 * @author: ma
 * @date: 2020/12/30
 * @Description: 对内容单元格设置全边框
 */
@Configuration
public class AllBorderCellStyle implements ExcelCellStyleCallback {
   
    @Override
    public CellStyle getBorderCellStyle(Workbook workbook) {
   
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置字体
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 11);
        cellStyle.setFont(font);
        return cellStyle;
    }
}

4.ExcelHeaderCallback 表头回调接口

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;

public interface ExcelHeaderCallback {
   

    String[] getHeaderArray();

    void writeHeader(XSSFWorkbook workbook) throws IOException;
}

4.1 CreditInfoHeaderWriter 表头信息写入实现类

import com.loan.batch.item.ExcelHeaderCallback;
import com.loan.util.Constants;
import com.loan.util.ExcelUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.context.annotation.Configuration;
import java.io.IOException;

@Configuration
public class CreditInfoHeaderWriter implements ExcelHeaderCallback {
   

    @Override
    public String[] getHeaderArray() {
   
        String[] strings = new String[]{
   };
        strings = Constants.CREDIT_INFO_REPORT_NAMES_MAP.values().toArray(strings);
        return strings;
    }
    @Override
    public void writeHeader(XSSFWorkbook workbook) throws IOException {
   
        String[] strings = getHeaderArray();
        //公共二级表头
        String oneHeader = "明细报表";
        ExcelUtil.createReportHeader(workbook, strings, oneHeader);
    }
}

4.2 CREDIT_INFO_REPORT_NAMES_MAP 表头信息及对应的英文字段(取值)

	/**
     * 额度信息报表表头映射
     */
    Map<String, String> CREDIT_INFO_REPORT_NAMES_MAP = new LinkedHashMap() {
   {
   
        put("id", "序号");
        put("custNo", "客户号");
        put("custName", "客户名称");
        put("creditNo", "授信编号");
        put("grtType", "主要担保方式");
        put("creditSts", "额度状态");
        put("creditAmt", "单户授信金额");
        put("sumLoanAmt", "累计出账金额");
        put("sumLoanBalance", "出账余额");
        put("sumOverdueBalance", "逾期金额");
        put("actualRate", "执行利率(%)");
        put("maxRiskClassify", "五级分类");
        put("startDate", "授信起始日");
        put("endDate", "授信到期日");
        put("maxPayoffDate", "最后1笔结清日期");
        put("maxPayoutEndDate", "最后一笔到期日");
        put