一:场景需求
工作中需要对数据库中的数据进行报表导出,所以用到了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("creditTerm", "授信期限(月)");
put("custOrg", "所属机构");
}};
5.ExcelItemWriter
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.item.*;
import org.springframework.batch.item.file.FlatFileFooterCallback;
import org.springframework.batch.item.file.ResourceAwareItemWriterItemStream;
import org.springframework.batch.item.file.transform.LineAggregator;
import org.springframework.batch.item.support.AbstractItemStreamItemWriter;
import org.springframework.batch.item.util.FileUtils;
import org.springframework.batch.support.transaction.TransactionAwareBufferedWriter;
import org.springframework.core.io.Resource;
import org.springframework.util.Assert;
import org.springframework.util.ClassUtils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.Writer;
import java.nio.channels.FileChannel;
import java.util.List;
/**
* @author: ma
* @date: 2020/12/02
* @Description: Excel表头的操作及row数据的写入, todo 暂未考虑多线程问题
*/
public class ExcelItemWriter<T> extends AbstractItemStreamItemWriter<T> implements ResourceAwareItemWriterItemStream<T>, ItemWriter<T> {
protected static final Logger logger = LoggerFactory.getLogger(ExcelItemWriter.class);
private OutputState state = null;
private FlatFileFooterCallback footerCallback;
private static final String XSSF_XLSX = "xlsx";
private ExcelHeaderCallback excelHeaderCallback;
private ExcelLineAggregator<T> excelLineAggregator;
private static final boolean DEFAULT_TRANSACTIONAL = true;
private static final String WRITTEN_STATISTICS_NAME = "written";
private static final String RESTART_DATA_NAME = "current.count";
private boolean shouldDeleteIfExists = true;
/**
* 如果生成的文件为空的话,就删除掉(解释:write方法 一行数据都没有写入)
*/
private boolean shouldDeleteIfEmpty = false;
private XSSFWorkbook workbook;
private boolean transactional = DEFAULT_TRANSACTIONAL;
private Resource resource;
private String encoding = OutputState.DEFAULT_CHARSET;
@Deprecated
private boolean append = false;
private volatile Sheet currentSheet;
private volatile int lastRowNum;
/**
* 序列号是否自动增长
*/
private boolean seriAutoIncre = false;
// 自增序列号的默认列位置
private int colPosition = 0;
// 自增序列号的默认值
private int serialNumber = 1;
/**
* 自定义内容单元格格式回调类
*/
private ExcelCellStyleCallback excelCellStyleCallback;
/**
* 自定义row单元格格式
*/
private CellStyle cellStyle;
public ExcelItemWriter() {
this.setExecutionContextName(ClassUtils.getShortName(ExcelItemWriter.class));
}
public void setCellStyleCustum1(ExcelCellStyleCallback excelCellStyleCallback) {
this.excelCellStyleCallback = excelCellStyleCallback;
}
public void setSerialNumberAutoIncre(boolean autoIncre, int colPosition) {
this.colPosition = colPosition;
this.seriAutoIncre = autoIncre;
}
/**
* Initialize the reader. This method may be called multiple times before
* close is called.
*
* @see ItemStream#open(ExecutionContext)
*/
@Override
public void open(ExecutionContext executionContext) throws ItemStreamException {
super.open(executionContext);
Assert.notNull(resource, "The resource must be set");
if (!getOutputState().isInitialized()) {
doOpen(executionContext);
}
}
/**
* @author: 马振波
* @date: 2020/12/02
* @Description: Excel应该在这里进行初始化
*/
private void doOpen(ExecutionContext executionContext) throws ItemStreamException {
OutputState outputState = getOutputState();
// 如果含有这个key的话,说明是重启行为
if (executionContext.containsKey(getExecutionContextKey(RESTART_DATA_NAME))) {
outputState.restoreFrom(executionContext);
}
try {
outputState.initializeBufferedWriter();
} catch (IOException ioe) {
throw new ItemStreamException("Failed to initialize writer", ioe);
}
if (outputState.lastMarkedByteOffsetPosition == 0 && !outputState.appending) {
if (excelHeaderCallback != null) {
try {
// 1.在doOpen时,判断自动增长的序列号的位置是否合法
int length = excelHeaderCallback.getHeaderArray().length;
if (colPosition > (length - 1)) {
throw new RuntimeException("increment-colPosition Illegal--");
}
// 2.判断Excel 的后缀是否为xlsx
if (!XSSF_XLSX.equals(FilenameUtils.getExtension(resource.getFilename()))) {
throw new RuntimeException("filename Illegal is not xlsx--");
}
excelHeaderCallback.writeHeader(workbook);
// 默认为第 0 个sheet
currentSheet = workbook.getSheetAt(0);
} catch (IOException e) {
throw new ItemStreamException("Could not write headers. The file may be corrupt.", e);
}
}
}
}
/**
* Public setter for the {@link LineAggregator}. This will be used to
* translate the item into a line for output.
*
* @param excelLineAggregator the {@link LineAggregator} to set
*/
public void setLineAggregator(ExcelLineAggregator<T> excelLineAggregator) {
this.excelLineAggregator = excelLineAggregator;
}
/**
* Flag to indicate that the target file should be deleted if it already
* exists, otherwise it will be created. Defaults to true, so no appending
* except on restart. If set to false and {@link #setAppendAllowed(boolean)
* appendAllowed} is also false then there will be an exception when the
* stream is opened to prevent existing data being potentially corrupted.
*
* @param shouldDeleteIfExists the flag value to set
*/
public void setShouldDeleteIfExists(boolean shouldDeleteIfExists) {
this.shouldDeleteIfExists = shouldDeleteIfExists;
}
/**
* Flag to indicate that the target file should be appended if it already
* exists. If this flag is set then the flag
* {@link #setShouldDeleteIfExists(boolean) shouldDeleteIfExists} is
* automatically set to false, so that flag should not be set explicitly.
* Defaults value is false.
*
* @param append the flag value to set
* 文件如果存在的话就继续追加(未实现)
*/
@Deprecated
public void setAppendAllowed(boolean append) {
this.shouldDeleteIfExists = false;
}
/**
* Flag to indicate that the target file should be deleted if no lines have
* been written (other than header and footer) on close. Defaults to false.
*
* @param shouldDeleteIfEmpty the flag value to set
*/
public void setShouldDeleteIfEmpty(boolean shouldDeleteIfEmpty) {
this.shouldDeleteIfEmpty = shouldDeleteIfEmpty;
}
/**
* headerCallback will be called before writing the first item to file.
* Newline will be automatically appended after the header is written.
*/
public void setHeaderCallback(ExcelHeaderCallback headerCallback) {
this.excelHeaderCallback = headerCallback;
}
/**
* footerCallback will be called after writing the last item to file, but
* before the file is closed.
*/
public void setFooterCallback(FlatFileFooterCallback footerCallback) {
this.footerCallback = footerCallback;
}
/**
* Writes out a string followed by a "new line", where the format of the new
* line separator is determined by the underlying operating system. If the
* input is not a String and a converter is available the converter will be
* applied and then this method recursively called with the result. If the
* input is an array or collection each value will be written to a separate
* line (recursively calling this method for each value). If no converter is
* supplied the input object's toString method will be used.<br>
*
* @param items list of items to be written to output stream
* @throws Exception if the transformer or file output fail,
* WriterNotOpenException if the writer has not been initialized.
* 加了 synchronized
*/
@Override
public synchronized void write(List<? extends T> items) throws Exception {
if (!getOutputState().isInitialized()) {
throw new WriterNotOpenException("Writer must be open before it can be written to");
}
if (logger.isDebugEnabled()) {
logger.debug("Writing to flat file with " + items.size() + " items.");
}
// 考虑多sheet问题 Excel2007以上版本的xlsx格式文件可以支持104,8576行数据(根据目前的数据量评估,不需要用到多sheet)
// int numberOfSheets = workbook.getNumberOfSheets();
try {
state.write(items);
} catch (IOException e) {
throw new WriteFailedException("Could not write data. The file may be corrupt.", e);
}
state.linesWritten += items.size();
}
/**
* @see ItemStream#close()
* fixme footerCallback未实现功能
*/
@Override
public void close() {
super.close();
if (state != null) {
try {
if (footerCallback != null && state.outputBufferedWriter != null) {
footerCallback.writeFooter(state.outputBufferedWriter);
state.outputBufferedWriter.flush();
}
} catch (IOException e) {
throw new ItemStreamException("Failed to write footer before closing", e);
} finally {
state.close();
if (state.linesWritten == 0 && shouldDeleteIfEmpty) {
try {
resource.getFile().delete();
} catch (IOException e) {
throw new ItemStreamException("Failed to delete empty file on close", e);
}
}
state = null;
}
}
}
// Returns object representing state.
private OutputState getOutputState() {
if (state == null) {
File file;
try {
file = resource.getFile();
} catch (IOException e) {
throw new ItemStreamException("Could not convert resource to file: [" + resource + "]", e);
}
Assert.state(!file.exists() || file.canWrite(), "Resource is not writable: [" + resource + "]");
state = new OutputState();
state.setDeleteIfExists(shouldDeleteIfExists);
state.setAppendAllowed(append);
state.setEncoding(encoding);
}
return state;
}
@Override
public void setResource(Resource resource) {
this.resource = resource;
}
/**
* Encapsulates the runtime state of the writer. All state changing
* operations on the writer go through this class.
*/
private class OutputState {
// default encoding for writing to output files - set to UTF-8.
private static final String DEFAULT_CHARSET = "UTF-8";
// The bufferedWriter over the file channel that is actually written
@Deprecated
Writer outputBufferedWriter;
@Deprecated
FileChannel fileChannel;
// this represents the charset encoding (if any is needed) for the
// output file
String encoding = DEFAULT_CHARSET;
boolean restarted = false;
long lastMarkedByteOffsetPosition = 0;
long linesWritten = 0;
boolean shouldDeleteIfExists = true;
boolean initialized = false;
private FileOutputStream os;
private boolean append = false;
private boolean appending = false;
/**
* Return the byte offset position of the cursor in the output file as a
* long integer.
*/
public long position() throws IOException {
long pos = 0;
if (fileChannel == null) {
return 0;
}
outputBufferedWriter.flush();
pos = fileChannel.position();
if (transactional) {
pos += ((TransactionAwareBufferedWriter) outputBufferedWriter).getBufferSize();
}
return pos;
}
/**
* @param append
*/
public void setAppendAllowed(boolean append) {
this.append = append;
}
/**
* @param executionContext
*/
public void restoreFrom(ExecutionContext executionContext) {
lastMarkedByteOffsetPosition = executionContext.getLong(getExecutionContextKey(RESTART_DATA_NAME));
linesWritten = executionContext.getLong(getExecutionContextKey(WRITTEN_STATISTICS_NAME));
if (shouldDeleteIfEmpty && linesWritten == 0) {
// previous execution deleted the output file because no items were written
restarted = false;
lastMarkedByteOffsetPosition = 0;
} else {
restarted = true;
}
}
/**
* @param shouldDeleteIfExists
*/
public void setDeleteIfExists(boolean shouldDeleteIfExists) {
this.shouldDeleteIfExists = shouldDeleteIfExists;
}
/**
* @param encoding
*/
public void setEncoding(String encoding) {
this.encoding = encoding;
}
/**
* Close the open resource and reset counters.
*/
public void close() {
initialized = false;
restarted = false;
// 在此将文件写入
try (FileOutputStream fileOutputStream = new FileOutputStream(resource.getFile())) {
logger.info("==>>将处理好的Excel写入到文件中[{}]<<==", resource.getFile().getAbsolutePath());
workbook.write(fileOutputStream);
} catch (IOException ioe) {
throw new ItemStreamException("Unable to close the the ItemWriter", ioe);
} finally {
if (!transactional) {
closeStream();
}
}
}
private void closeStream() {
try {
if (fileChannel != null) {
fileChannel.close();
}
} catch (IOException ioe) {
throw new ItemStreamException("Unable to close the the ItemWriter", ioe);
} finally {
try {
if (os != null) {
os.close();
}
} catch (IOException ioe) {
throw new ItemStreamException("Unable to close the the ItemWriter", ioe);
}
}
}
/**
* @param line
* @throws IOException
*/
/**
* @author: 马振波
* @date: 2020/12/02
* @Description: 从这里写入excel的row
*/
public void write(List<? extends T> items) throws IOException {
if (!initialized) {
initializeBufferedWriter();
}
// todo 应该从这个开始excel row的写入
// 1.获取最新的行数
lastRowNum = currentSheet.getLastRowNum();
// 2.为什么代码写的这么重复,为了少N次判断!
if (seriAutoIncre) {
// todo 加边框线在此处加
for (int i = 0; i < items.size(); i++) {
// 2.创建行记录
Row row = currentSheet.createRow(++lastRowNum);
// 3.对每一行待解析数据进行解析
Object[] values = excelLineAggregator.aggregate(items.get(i));
// 4.创建cell
for (int j = 0; j < values.length; j++) {
// 因为在外层加了syn 关键字,所以在此处不做线程安全处理
Cell cell = row.createCell(j);
// 5.对cell添加样式
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
if (j == colPosition) {
cell.setCellValue(serialNumber++);
} else {
Object o = values[j];
cell.setCellValue(o != null ? o.toString() : null);
}
}
}
} else {
for (int i = 0; i < items.size(); i++) {
// 2.创建行记录
Row row = currentSheet.createRow(++lastRowNum);
// 3.对每一行待解析数据进行解析
Object[] values = excelLineAggregator.aggregate(items.get(i));
// 4.创建cell
for (int j = 0; j < values.length; j++) {
Object o = values[j];
// 因为在外层加了syn 关键字,所以在此处不做线程安全处理
row.createCell(j).setCellValue(o != null ? o.toString() : null);
}
}
}
/*
* todo 考虑每一千条进行写入到文件中,此时文件已生成好,表头已经生成好
* 5.将文件追加到Excel中 todo poi不方便实现
*/
}
/**
* Truncate the output at the last known good point.
*
* @throws IOException
*/
@Deprecated
public void truncate() throws IOException {
fileChannel.truncate(lastMarkedByteOffsetPosition);
fileChannel.position(lastMarkedByteOffsetPosition);
}
/**
* Creates the buffered writer for the output file channel based on
* configuration information.
* 初始化Excel处理类
*
* @throws IOException
*/
private void initializeBufferedWriter() throws IOException {
workbook = new XSSFWorkbook();
if (excelCellStyleCallback != null) {
cellStyle = excelCellStyleCallback.getBorderCellStyle(workbook);
}
XSSFCellStyle cellStyle = workbook.createCellStyle();
XSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
// todo 默认创建sheet
workbook.createSheet();
File file = resource.getFile();
// 文件是否需要删除/创建 判断操作
FileUtils.setUpOutputFile(file, restarted, append, shouldDeleteIfExists);
// os = new FileOutputStream(file.getAbsolutePath(), true);
// fileChannel = os.getChannel();
// outputBufferedWriter = getBufferedWriter(fileChannel, encoding);
// outputBufferedWriter.flush();
if (append) {
if (file.length() > 0) {
appending = true;
}
}
initialized = true;
}
public boolean isInitialized() {
return initialized;
}
/**
* Checks (on setState) to make sure that the current output file's size
* is not smaller than the last saved commit point. If it is, then the
* file has been damaged in some way and whole task must be started over
* again from the beginning.
*
* @throws IOException if there is an IO problem
*/
private void checkFileSize() throws IOException {
long size = -1;
outputBufferedWriter.flush();
size = fileChannel.size();
if (size < lastMarkedByteOffsetPosition) {
throw new ItemStreamException("Current file size is smaller than size at last commit");
}
}
}
}
三:使用方法
直接从step开始
step总览
@Bean
public Step ledgerGhdStep() {
// 1.step名字
return stepBuilderFactory.get("ledgerGhdStep")
// 2.每1000条处理一次
.<LedgerReport, LedgerReport>chunk(1000)
// 3.数据读取器
.reader(ledgerGhdItemReader(null))
// 4.数据处理器
.processor(ledgerProcessor)
// 5.数据写入器
.writer(ledgerGhdWriter(null))
// 6.如果step已完成,是否允许重启
.allowStartIfComplete(true)
// 7.step监听器
.listener(stepStatefulListener)
.build();
}
1️⃣.reader(读取器):
@Bean
@StepScope
public ItemReader<LedgerReport> ledgerGhdItemReader(@Value("#{jobParameters['predateAfterSwitch_yyyy-MM-dd']}") String preDate) {
Map<String, String> params = new HashMap<>();
params.put("prdCode", Constants.PRD_CODE_3000);
params.put("reconciliationDate", preDate);
ledgerGhdReader.setQueryId("com.loan.job.mapper.LoanDuebillMapper.select4BizLedger");
ledgerGhdReader.setParameterValues(params);
ledgerGhdReader.setPageSize(1000);
return ledgerGhdReader;
}
1️⃣.1️⃣.ledgerGhdReader(Mybatis数据库读取器):
@StepScope
@Configuration
class LedgerGhdReaderImpl<T> extends MyBatisPagingItemReader {
}
2️⃣.processor(数据处理器):
@StepScope
@Configuration
public class LedgerProcessor implements ItemProcessor<LedgerReport, LedgerReport> {
@Override
public LedgerReport process(LedgerReport ledgerReport) {
ledgerReport.setDuebillNo(ledgerReport.getDuebillNo());
ledgerReport.setOverdueDate(countOverDate(ledgerReport));
ledgerReport.setPrdName(Constants.PRD_TO_NAME.get(ledgerReport.getPrdCode()));
ledgerReport.setCustType(Constants.CUST_TYPE_MAP.get(ledgerReport.getCustType()));
ledgerReport.setGrtType(Constants.STD_GRT_TYPE_MAP.get(ledgerReport.getGrtType()));
ledgerReport.setRepayMode(Constants.REPAY_MODE_MAP.get(ledgerReport.getRepayMode()));
ledgerReport.setRiskClassify(Constants.STD_FIVE_CLASS_MAP.get(ledgerReport.getRiskClassify()));
return ledgerReport;
}
}
3️⃣.writer(Excel写入器)
@Bean
@StepScope
public ExcelItemWriter<LedgerReport> ledgerGhdWriter(@Value("#{jobParameters['ledgerReportXlsx']}") String filePath) {
// 1.设置属性
BeanWrapperFieldExtractor<LedgerReport> fieldExtractor = new BeanWrapperFieldExtractor();
// 属性提取的key
String[] names = new String[]{};
names = Constants.LEDGER_REPORT_NAMES_MAP.keySet().toArray(names);
fieldExtractor.setNames(names);
// 2.行聚合器
ExcelLineAggregator<LedgerReport> lineAggregator = new ExcelLineAggregator();
lineAggregator.setFieldExtractor(fieldExtractor);
// 3.文件写入
FileSystemResource resource = new FileSystemResource(new File(filePath));
ExcelItemWriter<LedgerReport> itemWriter = new ExcelItemWriter();
// 4.自动增长列 序号
itemWriter.setSerialNumberAutoIncre(true, 0);
itemWriter.setResource(resource);
// 5.设置cellStyle
itemWriter.setCellStyleCustum1(allBorderCellStyle);
// 文件已存在就删除
itemWriter.setShouldDeleteIfExists(true);
itemWriter.setHeaderCallback(creditInfoHeaderWriter);
itemWriter.setLineAggregator(lineAggregator);
ExecutionContext executionContext = new ExecutionContext();
// 在重启job时会用到这个状态
itemWriter.open(executionContext);
itemWriter.update(executionContext);
return itemWriter;
}