依赖版本
//easyExcle
api("com.alibaba:easyexcel:3.3.2")
EasyExcelUtil Excel工具类
package com.cogo.b2c.admin.service.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.handler.WriteHandler;
import org.apache.poi.ss.formula.functions.T;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* @author zhaozj
* @date 2022年07月22日 14:32
* @Description: 注：如果方法中无以下参数，则使用默认值
* Integer sheetNo 默认0读取第一张sheet
* Integer headRowNum 默认1不读取首行，从第二行开始读取
* 返回值return：List<Map<Integer,String>>: Integer:列数 String:列数对应的value
*/
public class EasyExcelUtil {
/**
* 同步无模型读（默认读取sheet0,从第2行开始读）
* @param filePath excel文件的绝对路径
*/
public static List<Map<Integer, String>> syncRead(String filePath){
return EasyExcelFactory.read(filePath).sheet().doReadSync();
}
/**
* 同步无模型读（自定义读取sheetX，从第2行开始读）
* @param filePath excel文件的绝对路径
* @param sheetNo sheet页号，从0开始
*/
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo){
return EasyExcelFactory.read(filePath).sheet(sheetNo).doReadSync();
}
/**
* 同步无模型读（指定sheet和表头占的行数）
* @param filePath
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<Map<Integer, String>> syncRead(String filePath, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
/**
* 同步无模型读（指定sheet和表头占的行数）
* @param inputStream
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<Map<Integer, String>> syncRead(InputStream inputStream, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
/**
* 同步无模型读（指定sheet和表头占的行数）
* @param file
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<Map<Integer, String>> syncRead(File file, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).doReadSync();
}
//====================================================无JAVA模型读取excel数据===============================================================
//====================================================将excel数据同步到JAVA模型属性里===============================================================
/**
* 同步按模型读（默认读取sheet0,从第2行开始读）
* @param filePath
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
*/
public static List<T> syncReadModel(String filePath, Class clazz){
return EasyExcelFactory.read(filePath).sheet().head(clazz).doReadSync();
}
/**
* 同步按模型读（默认表头占一行，从第2行开始读）
* @param filePath
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
*/
public static List<T> syncReadModel(String filePath, Class clazz, Integer sheetNo){
return EasyExcelFactory.read(filePath).sheet(sheetNo).head(clazz).doReadSync();
}
/**
* 同步按模型读（指定sheet和表头占的行数）
* @param inputStream
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<T> syncReadModel(InputStream inputStream, Class clazz, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(inputStream).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 同步按模型读（指定sheet和表头占的行数）
* @param file
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<T> syncReadModel(File file, Class clazz, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(file).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 同步按模型读（指定sheet和表头占的行数）
* @param filePath
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static List<T> syncReadModel(String filePath, Class clazz, Integer sheetNo, Integer headRowNum){
return EasyExcelFactory.read(filePath).sheet(sheetNo).headRowNumber(headRowNum).head(clazz).doReadSync();
}
/**
* 异步无模型读（默认读取sheet0,从第2行开始读）
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param filePath 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener){
EasyExcelFactory.read(filePath, excelListener).sheet().doRead();
}
/**
* 异步无模型读（默认表头占一行，从第2行开始读）
* @param filePath 表头占的行数，从0开始（如果要连表头一起读出来则传0）
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param sheetNo sheet页号，从0开始
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener, Integer sheetNo){
EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).doRead();
}
/**
* 异步无模型读（指定sheet和表头占的行数）
* @param inputStream
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncRead(InputStream inputStream, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(inputStream, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 异步无模型读（指定sheet和表头占的行数）
* @param file
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncRead(File file, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(file, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 异步无模型读（指定sheet和表头占的行数）
* @param filePath
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
* @return
*/
public static void asyncRead(String filePath, AnalysisEventListener<T> excelListener, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(filePath, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 异步按模型读取（默认读取sheet0,从第2行开始读）
* @param filePath
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz){
EasyExcelFactory.read(filePath, clazz, excelListener).sheet().doRead();
}
/**
* 异步按模型读取（默认表头占一行，从第2行开始读）
* @param filePath
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo){
EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).doRead();
}
/**
* 异步按模型读取
* @param inputStream
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncReadModel(InputStream inputStream, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(inputStream, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 异步按模型读取
* @param file
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncReadModel(File file, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(file, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 异步按模型读取
* @param filePath
* @param excelListener 监听器，在监听器中可以处理行数据LinkedHashMap，表头数据，异常处理等
* @param clazz 模型的类类型（excel数据会按该类型转换成对象）
* @param sheetNo sheet页号，从0开始
* @param headRowNum 表头占的行数，从0开始（如果要连表头一起读出来则传0）
*/
public static void asyncReadModel(String filePath, AnalysisEventListener<T> excelListener, Class clazz, Integer sheetNo, Integer headRowNum){
EasyExcelFactory.read(filePath, clazz, excelListener).sheet(sheetNo).headRowNumber(headRowNum).doRead();
}
/**
* 无模板写文件
* @param filePath
* @param head 表头数据
* @param data 表内容数据
*/
public static void write(String filePath, List<List<String>> head, List<List<Object>> data){
EasyExcel.write(filePath).head(head).sheet().doWrite(data);
}
/**
* 无模板写文件
* @param filePath
* @param head 表头数据
* @param data 表内容数据
* @param sheetNo sheet页号，从0开始
* @param sheetName sheet名称
*/
public static void write(String filePath, List<List<String>> head, List<List<Object>> data, Integer sheetNo, String sheetName){
EasyExcel.write(filePath).head(head).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 根据excel模板文件写入文件
* @param filePath
* @param templateFileName
* @param headClazz
* @param data
*/
public static void writeTemplate(String filePath, String templateFileName, Class headClazz, List data){
EasyExcel.write(filePath, headClazz).withTemplate(templateFileName).sheet().doWrite(data);
}
/**
* 根据excel模板文件写入文件
* @param filePath
* @param templateFileName
* @param data
*/
public static void writeTemplate(String filePath, String templateFileName, List data){
EasyExcel.write(filePath).withTemplate(templateFileName).sheet().doWrite(data);
}
/**
* 按模板写文件
* @param filePath
* @param headClazz 表头模板
* @param data 数据
*/
public static void write(String filePath, Class headClazz, List data){
EasyExcel.write(filePath, headClazz).sheet().doWrite(data);
}
/**
* 按模板写文件
* @param filePath
* @param headClazz 表头模板
* @param data 数据
* @param sheetNo sheet页号，从0开始
* @param sheetName sheet名称
*/
public static void write(String filePath, Class headClazz, List data, Integer sheetNo, String sheetName){
EasyExcel.write(filePath, headClazz).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板写文件
* @param filePath
* @param headClazz 表头模板
* @param data 数据
* @param writeHandler 自定义的处理器，比如设置table样式，设置超链接、单元格下拉框等等功能都可以通过这个实现（需要注册多个则自己通过链式去调用）
* @param sheetNo sheet页号，从0开始
* @param sheetName sheet名称
*/
public static void write(String filePath, Class headClazz, List data, WriteHandler writeHandler, Integer sheetNo, String sheetName){
EasyExcel.write(filePath, headClazz).registerWriteHandler(writeHandler).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板写文件（包含某些字段）
* @param filePath
* @param headClazz 表头模板
* @param data 数据
* @param includeCols 包含字段集合，根据字段名称显示
* @param sheetNo sheet页号，从0开始
* @param sheetName sheet名称
*/
public static void writeInclude(String filePath, Class headClazz, List data, Set<String> includeCols, Integer sheetNo, String sheetName){
EasyExcel.write(filePath, headClazz).includeColumnFiledNames(includeCols).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 按模板写文件（排除某些字段）
* @param filePath
* @param headClazz 表头模板
* @param data 数据
* @param excludeCols 过滤排除的字段，根据字段名称过滤
* @param sheetNo sheet页号，从0开始
* @param sheetName sheet名称
*/
public static void writeExclude(String filePath, Class headClazz, List data, Set<String> excludeCols, Integer sheetNo, String sheetName){
EasyExcel.write(filePath, headClazz).excludeColumnFiledNames(excludeCols).sheet(sheetNo, sheetName).doWrite(data);
}
/**
* 多个sheet页的数据链式写入
* ExcelUtil.writeWithSheets(outputStream)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
* @param outputStream
*/
public static EasyExcelWriterFactory writeWithSheets(OutputStream outputStream){
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(outputStream);
return excelWriter;
}
/**
* 多个sheet页的数据链式写入
* ExcelUtil.writeWithSheets(file)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
* @param file
*/
public static EasyExcelWriterFactory writeWithSheets(File file){
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(file);
return excelWriter;
}
/**
* 多个sheet页的数据链式写入
* ExcelUtil.writeWithSheets(filePath)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
* @param filePath
*/
public static EasyExcelWriterFactory writeWithSheets(String filePath){
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(filePath);
return excelWriter;
}
/**
* 多个sheet页的数据链式写入（失败了会返回一个有部分数据的Excel）
* ExcelUtil.writeWithSheets(response, exportFileName)
* .writeModel(ExcelModel.class, excelModelList, "sheetName1")
* .write(headData, data,"sheetName2")
* .finish();
* @param response
* @param exportFileName 导出的文件名称
*/
public static EasyExcelWriterFactory writeWithSheetsWeb(HttpServletResponse response, String exportFileName) throws IOException{
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
String fileName = URLEncoder.encode(exportFileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcelWriterFactory excelWriter = new EasyExcelWriterFactory(response.getOutputStream());
return excelWriter;
}
}
DefaultExcelListener` 异步监听对Excel操作
package com.cogo.b2c.admin.service.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* @author zhaozj
* @date 2022年07月22日 14:34
* @Description: excel读取监听器
*/
@Slf4j
public class DefaultExcelListener<T> extends AnalysisEventListener<T> {
private final List<T> rows = new ArrayList();
/**
* 读取excel数据前操作(只有不读取表头数据时才会触发此方法)
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("======================================================");
log.info("解析第一行数据:{}"+ JSON.toJSONString(headMap));
log.info("======================================================");
}
/**
* 读取excel数据操作
* @param object
* @param context
*/
@Override
public void invoke(T object, AnalysisContext context) {
rows.add(object);
log.info("list容量"+rows.size()+"---"+object);
/** 数据量不是特别大，可以不需要打开
// 实际数据量比较大时，rows里的数据可以存到一定量之后进行批量处理（比如存到数据库），
// 然后清空列表，以防止内存占用过多造成OOM
if(rows.size() >= 500){
log.info("存入数据库ing");
try {
Thread.sleep(3000);
} catch (InterruptedException e) {
e.printStackTrace();
}
rows.clear();
}
*/
}
/**
* 读取完excel数据后的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("成功读取【"+ rows.size()+"】条数据");
}
/**
* 在读取excel异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
}
}
/**
* @return 返回读取excel总数据
*/
public List<T> getRows() {
return rows;
}
}
EasyExcelWriterFactory` 链式导出多个sheet的Excel
package com.cogo.b2c.admin.service.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import java.io.File;
import java.io.OutputStream;
import java.util.List;
/**
* @author zhaozj
* @date 2022年07月22日 14:35
* @Description: 链式添加sheet表
*/
public class EasyExcelWriterFactory {
private int sheetNo = 0;
private ExcelWriter excelWriter = null;
public EasyExcelWriterFactory(OutputStream outputStream) {
excelWriter = EasyExcel.write(outputStream).build();
}
public EasyExcelWriterFactory(File file) {
excelWriter = EasyExcel.write(file).build();
}
public EasyExcelWriterFactory(String filePath) {
excelWriter = EasyExcel.write(filePath).build();
}
/**
* 链式模板表头写入
* @param headClazz 表头格式
* @param data 数据 List<ExcelModel> 或者List<List<Object>>
* @return
*/
public EasyExcelWriterFactory writeModel(Class headClazz, List data, String sheetName){
excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(headClazz).build());
return this;
}
/**
* 链式自定义表头写入
* @param head
* @param data 数据 List<ExcelModel> 或者List<List<Object>>
* @param sheetName
* @return
*/
public EasyExcelWriterFactory write(List<List<String>> head, List data, String sheetName){
excelWriter.write(data, EasyExcel.writerSheet(this.sheetNo++, sheetName).head(head).build());
return this;
}
/**
* 使用此类结束后，一定要关闭流
*/
public void finish() {
excelWriter.finish();
}
}
列子
EasyExcelUtil.writeWithSheetsWeb(response, "B2B-收款记录导出").
writeModel(B2bCollectOrderExcelBody.class, b2bCollectOrderExcelBodies, "收款列表").finish();
类对象
package com.cogo.trade.bto.vo.settleExchange;
import com.alibaba.excel.annotation.ExcelIgnore; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import lombok.Data;
import java.math.BigDecimal;
@Data public class PushExportYibaoVo extends BaseRowModel {
/**
* 订单号
* 取交易订单的「订单编号」
*/
@ExcelProperty(index = 0, value = {"Version", "订单号"})
private String orderNo;
@ExcelProperty(index = 1, value = {"psp_1.2", "订单金额"})
private BigDecimal orderAmt;
@ExcelProperty(index = 2, value = {"","订单币种"})
private String orderCurrencyCode;
/**
* 订单时间
* 格式：yyyy-MM-dd hh:mm:ss
*/
@ExcelProperty(index = 3, value = {"","订单时间"})
private String purchaseDate;
/**
* 取关联贸易订单对应的申报业务明细的「申报主体名称」
*/
@ExcelProperty(index = 4, value = {"","收款方名称"})
private String payeeName;
/**
* 取关联贸易订单对应的申报业务明细的「申报主体证件号码」的9-17位
*/
@ExcelProperty(index = 5, value = {"","收款方证件号"})
private String idCardNo;
@ExcelProperty(index = 6, value = {"","收款方银行卡号"})
private String cardNo;
@ExcelProperty(index = 7, value = {"","平台会员编号"})
private String platformVipNo;
@ExcelProperty(index = 8, value = {"","收款方联系方式"})
private String payeeContact;
@ExcelProperty(index = 9, value = {"","付款方名称"})
private String payName;
@ExcelProperty(index = 10, value = {"","付款方银行卡号"})
private String payBankCardNo;
@ExcelProperty(index = 11, value = {"","付款方银行名称"})
private String payBankName;
@ExcelProperty(index = 12, value = {"","商品/服务名称"})
private String productName;
@ExcelProperty(index = 13, value = {"","商品/服务类别"})
private String productType;
@ExcelProperty(index = 14, value = {"", "数量"})
private String num;
@ExcelProperty(index = 15, value = {"", "贸易类型"})
private String tradeType;
@ExcelProperty(index = 16, value = {"","汇款附言"})
private String postscript;
@ExcelProperty(index = 17, value = {"","物流公司名称"})
private String logisticsCompany;
@ExcelProperty(index = 18, value = {"","物流单号"})
private String logisticsNo;
@ExcelProperty(index = 19, value = {"","平台名称"})
private String platformName;
@ExcelProperty(index = 20, value = {"","卖家ID"})
private String buyerId;
@ExcelProperty(index = 21, value = {"","业务网址"})
private String webUrl;
@ExcelProperty(index = 22, value = {"","交易详情"})
private String tradeDesc;
@ExcelProperty(index = 23, value = {"","付款唯一标识号"})
private String payNo;
/**
* 外部店铺ID
*/
@ExcelIgnore
private String extShopId;
}
列子
代码
package com.cogo.collect.excel.handler;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @ClassName CustomCellWriteHandler
* @Description 合并单元格处理
* @Author LYY
* @Date 2024/5/15 15:44
* @Version 1.0
**/
@Data
public class BalanceCellWriteHandler implements CellWriteHandler {
/**
* -余额账单汇总-结束行
*/
private Integer overAllEndRow;
/**
* 余额账单明细-结束行
*/
private Integer detailEndRow;
public static final int fix_row = 5;
/**
* 构造方法
*
* @param overAllListSize 余额账单汇总行数
* @param detailListSize 余额账单明细行数
*/
public BalanceCellWriteHandler(Integer overAllListSize, Integer detailListSize) {
this.overAllEndRow = fix_row + overAllListSize;
this.detailEndRow = fix_row + detailListSize;
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Sheet sheet = context.getWriteSheetHolder().getSheet();
int rowIndex = context.getRowIndex();
int columnIndex = context.getColumnIndex();
//设置列宽
if (columnIndex == 0) {
sheet.setColumnWidth(columnIndex, 20 * 256);
} else {
sheet.setColumnWidth(columnIndex, 14 * 256);
}
// 固定合并第5行的前4个单元格
if (rowIndex == 4 && columnIndex == 0) {
sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, 4));
// 设置单元格内容居中
cellCenterSetting(context);
}
// 动态合并某一行的前4个单元格
if (rowIndex == overAllEndRow && columnIndex == 0) {
// 设置单元格内容居中
cellCenterSetting(context);
sheet.addMergedRegion(new CellRangeAddress(overAllEndRow, overAllEndRow, 0, 4));
}
}
/**
* 设置格式
* @param context
*/
public void cellCenterSetting(CellWriteHandlerContext context) {
Cell cell = context.getCell();
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
}
}
package com.cogo.collect.excel.handler;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.cogo.collect.consts.CollectServiceConstants;
import com.cogo.collect.excel.dto.BalanceDetailExcelDTO;
import com.cogo.collect.excel.dto.BalanceOverAllExcelDTO;
import lombok.Data;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.lang.reflect.Field;
/**
* @ClassName CustomCellWriteHandler
* @Description 合并单元格处理
* @Author LYY
* @Date 2024/5/15 15:44
* @Version 1.0
**/
@Data
public class BalanceSheetWriteHandler implements SheetWriteHandler {
private final Class sheet1Class = BalanceOverAllExcelDTO.class;
private final Class sheet2Class = BalanceDetailExcelDTO.class;
/**
* 汇总表头行数
*/
private Integer headRow;
/**
* 构造方法
*/
public BalanceSheetWriteHandler(Integer headRow) {
this.headRow = headRow;
}
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
// 在Sheet创建后插入表头到第5行
Sheet sheet = context.getWriteSheetHolder().getSheet();
Class clazz = null;
if (CollectServiceConstants.BALANCE_OVERALL_SHEET.equals(sheet.getSheetName())) {
clazz = sheet1Class;
} else if (CollectServiceConstants.BALANCE_DETAIL_SHEET.equals(sheet.getSheetName())) {
clazz = sheet2Class;
}
// 在第5行写入表头
Row headerRow = sheet.createRow(headRow);
Field[] fields = clazz.getDeclaredFields();
int cellIndex = 0;
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
String headerName = excelProperty.value()[0];
Cell headerCell = headerRow.createCell(cellIndex++);
headerCell.setCellValue(headerName);
}
}
}
}
生成方法
/**
* @param billResp
* @param jhMno
* @param kycName
* @param currencyList
*/
public String createExcel(AccountDayBillResp billResp, String jhMno, String kycName, List<String> currencyList,
String fileName, String startDateString, String endDateString, String documentNo) throws IOException {
ByteArrayOutputStream outputStream = null;
ByteArrayInputStream inputStream = null;
try {
log.info("balanceStatementBusinessService 开始导出excel");
fileName += FileTypeEnum.XLSX.getSuffix();
//数据处理
List<BalanceOverAllExcelDTO> overAllList = new ArrayList<>();
List<BalanceDetailExcelDTO> detailList = new ArrayList<>();
//处理支付核心数据
handleAccountDayBillResp(overAllList, detailList, billResp, currencyList);
int overAllSize = CollectionUtils.isNotEmpty(overAllList) ? overAllList.size() + 1 : 0;
int detailSize = CollectionUtils.isNotEmpty(detailList) ? detailList.size() + 1 : 0;
//处理表头
BalanceCellWriteHandler balanceCellWriteHandler = new BalanceCellWriteHandler(overAllSize, detailSize);
BalanceSheetWriteHandler balanceSheetWriteHandler = new BalanceSheetWriteHandler(BalanceCellWriteHandler.fix_row);
outputStream = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream)
.needHead(false)
.registerWriteHandler(balanceSheetWriteHandler)
.registerWriteHandler(balanceCellWriteHandler)
.build();
WriteSheet writeSheet1 = EasyExcel.writerSheet(CollectServiceConstants.BALANCE_OVERALL_SHEET)
.build();
WriteSheet writeSheet2 = EasyExcel.writerSheet(CollectServiceConstants.BALANCE_DETAIL_SHEET)
.build();
String currentTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
//sheet1 头+汇总数据+尾
excelWriter.write(createBalanceOverAllData(kycName, startDateString, endDateString, currentTime), writeSheet1);
excelWriter.write(overAllList, writeSheet1);
excelWriter.write(createBalanceOverAllEndData(), writeSheet1);
//sheet2 头+汇总明细数据+尾
excelWriter.write(createBalanceDetailData(kycName, startDateString, endDateString, currentTime), writeSheet2);
excelWriter.write(detailList, writeSheet2);
excelWriter.write(createBalanceDetailEndData(), writeSheet2);
excelWriter.finish();
inputStream = new ByteArrayInputStream(outputStream.toByteArray());
OssResMessage ossResMessage = ossSdkUtil.uploadDocumentStream(inputStream, fileName);
log.info("余额账单导出excel成功,jhMno={}", jhMno);
//调用下载中心更新
cashoutCommonService.updateTask(documentNo, "FINNISH", ossResMessage.getPublicCdnHttp());
return ossResMessage.getPublicCdnHttp();
} catch (Exception e) {
log.error("余额账单导出excel失败", e);
String message = String.format("余额账单导出excel失败,jhMno=%s", jhMno);
throw ExceptionHelper.metric(CoGoCollectExceptionEnum.SYSTEM_EXCEPTION.getCode(), message);
} finally {
// 关闭资源的逻辑
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
log.error("关闭outputStream时发生异常", e);
}
}
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
log.error("关闭inputStream时发生异常", e);
}
}
}
/**
* 构建header
*
* @param kycName
* @param startDate
* @param endDate
* @param currentTime
* @return
*/
private List<List<String>> createBalanceOverAllData(String kycName, String startDate, String endDate, String currentTime) {
List<List<String>> dataList = new ArrayList<>();
// 添加第一行：余额账单汇总
List<String> row1 = new ArrayList<>();
row1.add("余额账单汇总");
dataList.add(row1);
// 添加第二行：KYC商户名称
List<String> row2 = new ArrayList<>();
row2.add(kycName);
dataList.add(row2);
// 添加第三行：起始日期
List<String> row3 = new ArrayList<>();
row3.add("起始日期");
row3.add(startDate);
row3.add("结束日期");
row3.add(endDate);
dataList.add(row3);
// 添加第四行：下载时间
List<String> row4 = new ArrayList<>();
row4.add("下载时间");
row4.add(currentTime);
dataList.add(row4);
// 添加第五行：----余额账单汇总----
List<String> row5 = new ArrayList<>();
row5.add("------------余额账单汇总------------");
dataList.add(row5);
return dataList;
}
对象
package com.cogo.collect.excel.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.validator.constraints.Length;
import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.math.BigDecimal;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class BalanceOverAllExcelDTO implements Serializable {
@ExcelProperty("币种")
@ApiModelProperty("币种")
private String currency;
@ExcelProperty("期初余额")
@ApiModelProperty("期初余额")
private BigDecimal startBalance;
@ExcelProperty("收入")
@ApiModelProperty("期间累计收入金额")
private BigDecimal incomeTotalBalance;
@ExcelProperty("支出")
@ApiModelProperty("期间累计支出金额")
private BigDecimal outTotalBalance;
@ExcelProperty("期末余额")
@ApiModelProperty("期末余额")
private BigDecimal endBalance;
}
package com.cogo.collect.excel.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.validator.constraints.Length;
import javax.validation.constraints.NotBlank;
import java.io.Serializable;
import java.math.BigDecimal;
/**
* @ClassName B2cOrderImportDto
* @Author LYY
* @Date 2023/9/26 11:33
* @Version 1.0
**/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class BalanceDetailExcelDTO implements Serializable {
@ExcelProperty("日期")
@ApiModelProperty("日期")
private String bookkeepingDate;
@ExcelProperty("币种")
@ApiModelProperty("币种")
private String currency;
@ExcelProperty("期初余额")
@ApiModelProperty("期初余额")
private BigDecimal startBalance;
@ExcelProperty("收入")
@ApiModelProperty("期间累计收入金额")
private BigDecimal incomeTotalBalance;
@ExcelProperty("支出")
@ApiModelProperty("期间累计支出金额")
private BigDecimal outTotalBalance;
@ExcelProperty("期末余额")
@ApiModelProperty("期末余额")
private BigDecimal endBalance;
}
