一、依赖和Excel相关操作的类
使用的依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<!--hutool的工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.4.4</version>
</dependency>
<!--测试使用的-->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-engine</artifactId>
<version>5.5.2</version>
<scope>test</scope>
</dependency>
<!--logback日志-->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.2.3</version>
</dependency>
使用的Excel模型类
// 定义可以去查EasyExcel的文档
@Data
@ColumnWidth(value = 20)
@HeadRowHeight(value = 30)
@ContentRowHeight(value = 25)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
@HeadStyle(fillForegroundColor = 22)
@AllArgsConstructor
@NoArgsConstructor
public class Model {
@ExcelProperty("开始时间")
@DateTimeFormat("yyyy年MM月dd日 hh时mm分ss秒")
private LocalDateTime a1;
@ExcelProperty("结束日期")
private LocalDate a2;
@ExcelProperty("编号")
private String a3;
@ExcelProperty("数量")
private Integer a4;
}
封装好的导入和导出的方法,可以修改里面的file,集成springMvc实现web端的相关导入导出功能,里面相关的类会放在对应的测试那里
@Slf4j
@RequiredArgsConstructor
public class ExcelService {
// 注入的具有排序功能的handle(具体放在下面)
private final SortRowWriteHandler sortRowWriteHandler;
// localDate的转换器
private final LocalDateConverter localDateConverter;
/**
* 模板导入,根据下载的模板来进行导入,要求格式和模板class定义的一致
*/
public <T, R> List<R> fileConvertBean(File file, Class<T> template, Function<T, R> convert) {
TemplateImportEventListener<T, R> excelDataListener = new TemplateImportEventListener<>(convert);
parseExcel(file, template, 0, excelDataListener);
return excelDataListener.getResult();
}
/**
* excel转换成对应的实体对象
*
* @param file 文件对象,可以换成SpringMvc的文件对象,实现web端导入
* @param template 模板类
* @param req 自定义导入的请求对象,没有走模板导入,存在走自定义导入
* @param convert 转换函数,将model对象按照自己的规则转换成自己需要的实体对象
* @return 转换后的对应实体对象
*/
public <T, R> List<R> fileConvertBean(File file, Class<T> template, CustomImportReq req, Function<T, R> convert) {
if (req == null || CollectionUtil.isEmpty(req.getFieldColumns())) {
return fileConvertBean(file, template, convert);
}
CustomImportEventListener<T, R> eventListener = new CustomImportEventListener<>(req.getFieldColumns(), template, convert);
parseExcel(file, null, req.getSheetNo(), eventListener);
return eventListener.getResult();
}
/**
* 导出excel
*/
public void exportExcel(String exportName, Class<?> templateClass, List<?> data) {
exportExcel(exportName, templateClass, exportName, data, null);
}
/**
* 下载excel模板文件
*/
public void exportExcel(ExportTemplate exportTemplate) {
exportExcel(exportTemplate.exportName(), exportTemplate.templateClass(), exportTemplate.sheetName(), Collections.EMPTY_LIST, null);
}
/**
* 导出excel
*
* @param exportName 导出名字
* @param templateClass 模板class
* @param sheetName 工作表名字
* @param data 导出的数据
* @param includeColumnFiledNames 包含的要导出的列,根据这个排序导出(存在的时候进行排序)
*/
public void exportExcel(String exportName, Class<?> templateClass, String sheetName, List<?> data, List<String> includeColumnFiledNames) {
try {
// FileOutputStream使用时换成Servlet对应的输出流
// 例如:setHeader(fileName, ServletUtil.getHttpServletResponse()).getOutputStream(),即可实现web端导出
// setHeader设置请求头,可以参考EasyExcel的文档
ExcelWriterBuilder writerBuilder = EasyExcel.write(new FileOutputStream("D:\\test\\" + exportName + ".xlsx"), templateClass).registerConverter(localDateConverter);
if (CollUtil.isNotEmpty(includeColumnFiledNames)) {
// 存在包含的列时使用排序,将导出的文件按照指定的列进行排序
writerBuilder.includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(sortRowWriteHandler);
}
writerBuilder.sheet(sheetName).doWrite(data);
} catch (IOException e) {
log.error("exportExcel ioException", e);
throw new ExcelException("解析excel文件出错");
}
}
private void parseExcel(File file, Class<?> template, Integer sheetNo, AnalysisEventListener<?> excelDataListener) {
try {
InputStream inputStream = new FileInputStream(file);
EasyExcel.read(inputStream, template, excelDataListener).sheet(sheetNo).registerConverter(localDateConverter).doRead();
} catch (IOException e) {
log.error("parseExcel error", e);
throw new ExcelException("解析excel文件出错");
} catch (ExcelAnalysisException excelAnalysisException) {
throw (ExcelException) excelAnalysisException.getCause();
}
}
}
LocalDate的转换器:
// 自定义的类型转化
public class LocalDateConverter implements Converter<LocalDate> {
// 默认解析格式
private static final DateTimeFormatter DEFAULT_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return LocalDate.parse(cellData.getStringValue(), getDateTimeFormat(contentProperty));
}
@Override
public WriteCellData<?> convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<>(value.format(getDateTimeFormat(contentProperty)));
}
/**
* 获取日期转换格式
*
* @param contentProperty contentProperty
* @return 日期转换格式
*/
private DateTimeFormatter getDateTimeFormat(ExcelContentProperty contentProperty) {
// 这个会获取定义的类的字段上有没有DateTime的注解,有的话根据这个注解定义的格式解析,没有则是使用默认的
DateTimeFormatProperty dateTimeFormatProperty = contentProperty.getDateTimeFormatProperty();
String format = dateTimeFormatProperty == null ? null : dateTimeFormatProperty.getFormat();
return StrUtil.isBlank(format) ? DEFAULT_FORMAT : DateTimeFormatter.ofPattern(format);
}
}
自定义异常,用来处理一些解析出的错误,来统一抛出
public class ExcelException extends RuntimeException {
public ExcelException(String messageTemplate, Object... params) {
// 使用hutool的工具类,可以通过{}占位的形式来拼接字符串
super(StrUtil.format(messageTemplate, params));
}
}
// 简单的断言工具
public class ExcelExceptionAssert {
/**
* 判断不能为null
*/
public static void notNUll(Object target, String messageTemplate, Object... params) {
isFalse(target == null, messageTemplate, params);
}
/**
* 判断表达式是否为false
*
* @param expression 表达式
* @param messageTemplate 消息模板
* @param params 消息参数
*/
public static void isFalse(boolean expression, String messageTemplate, Object... params) {
if (expression) {
throw new ExcelException(messageTemplate, params);
}
}
}
二、Excel模板下载
针对需要通过excel进行批量导入数据，给用户提供一个导入模板,让用户按照模板来填写数据后进行导入。
测试代码:
// 操作excel的工具
private ExcelService excelService;
// 扩展excel的导入功能
private ExcelImportLogService excelImportLogService;
@BeforeEach
void setUp() {
// 这个是调整控制台输出日志的,通过设置这个可以让程序执行过程中减少日志
List<String> loggers = Arrays.asList(Logger.ROOT_LOGGER_NAME);
for (String log : loggers) {
Logger logger = (Logger) LoggerFactory.getLogger(log);
logger.setLevel(Level.INFO);
}
// 一些初始化 因为easyExcel没有提供LocalDate的转换,所以自己加入了一个LocalDateConverter
// 这些可以跟spring结合起来使用,注入对应的bean即可
excelService = new ExcelService(new SortRowWriteHandler(), new LocalDateConverter());
excelImportLogService = new ExcelImportLogService(excelService);
}
@Test
void templateDownload() {
// 下载模板文件
excelService.exportExcel(new ImportExcelTemplate());
}
导出效果:
实现代码:
// 这个需求可能会有多个地方会出现根据不同的模型类来下载导入的excel模板,所以设计了一个接口
// 这个可以结合Spring的注入来实现通过bean来下载不同的模板
// 例如:在实现类上让spring进行扫描
// @Component("TEST_TEMPLATE")public class ImportExcelTemplate implements ExportTemplate
// 然后在需要的地方注入private final Map<String, ExportTemplate> exportTemplateMap;通过不同的beanName,就可以获取对应的excel模板
public interface ExportTemplate {
/**
* 导出的模板的名字
*
* @return 模板名字
*/
String exportName();
/**
* excel sheet名字,默认导出名,需要区分在实现
*
* @return sheetName
*/
default String sheetName() {
return exportName();
}
/**
* 返回模板class
*
* @return 模板class
*/
Class<?> templateClass();
}
// 测试使用的 这里演示的都没有结合springMVC相关,正常业务都是通过web端,可以稍微改造就可以使用
public class ImportExcelTemplate implements ExportTemplate {
@Override
public String exportName() {
return "测试导入模板下载";
}
@Override
public Class<?> templateClass() {
return Model.class;
}
}
三、导出数据到excel
一种是满足按照固定的列导出excel,一种可以通过控制列和列顺序来导出excel(结合前端传入排序好的列和指定的列即可自定义导出)
@Test
void dataExport() {
// 正常导出数据
List<Model> models = Arrays.asList(new Model(LocalDateTime.now(), LocalDate.now(), "test", 1));
excelService.exportExcel("测试导出excel", Model.class, models);
// 自定义列排序导出
excelService.exportExcel("测试排序导出", Model.class, "测试排序导出", models,
// 导出的excel会根据这里传入的参数字段顺序进行导出,配合前端选择需要导出的列和列的顺序,即可实现排序导出
Arrays.asList("a2", "a1"));
}
实现效果:
排序导出的实现:通过注入easyexcel提供的handle来改变列的映射实现排序,包含列是easyexcel已经提供的功能
public class SortRowWriteHandler implements RowWriteHandler {
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
// 获取传入的包含的列(字段的名字list),将headMap的索引按照传入的列的顺序重新放入,即可实现排序
ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.getExcelWriteHeadProperty();
Map<Integer, Head> headMap = excelWriteHeadProperty.getHeadMap();
Collection<String> includeColumnFieldNames = writeSheetHolder.getIncludeColumnFieldNames();
// 将headMap中的字段名字对应Head
Map<String, Head> fieldNameHead = headMap.values().stream().collect(Collectors.toMap(Head::getFieldName, head -> head));
int index = 0;
for (String includeColumnFieldName : includeColumnFieldNames) {
// 按照includeColumnFieldNames中的顺序取出head重新覆盖
Head head = fieldNameHead.get(includeColumnFieldName);
if (head == null) {
continue;
}
headMap.put(index++, head);
}
}
}
}
四、模板导入和自定义导入
@Test
void dataImport() {
// 使用对应的模板导入数据(file换成springMvc的文件对象即可实现web端上传)
List<Model> models = excelService.fileConvertBean(new File("D:\\test\\测试导出excel.xlsx"), Model.class, model -> model);
System.out.println(models);
// 自定义导入 前端解析excel文件,获取excel列对应的标题后,用户根据选择每个字段的标题来实现导入功能
CustomImportReq customImportReq = new CustomImportReq();
// 选择的工作表的索引
customImportReq.setSheetNo(0);
// 导入的数据对应列
List<FieldColumnReq> fieldColumnReqs = new ArrayList<>();
// a1字段对应excel中第0列
FieldColumnReq req1 = new FieldColumnReq("a1", 0, "标题1");
// a2字段根据模板class上定义的名字自动查找对应的列
FieldColumnReq req2 = new FieldColumnReq("a2", -1, "结束日期");
fieldColumnReqs.add(req1);
fieldColumnReqs.add(req2);
customImportReq.setFieldColumns(fieldColumnReqs);
List<Model> models1 = excelService.fileConvertBean(new File("D:\\test\\自定义导入.xlsx"), Model.class, customImportReq, model -> model);
System.out.println(models1);
// 增加导入统计的功能(可以自行做日志)
excelImportLogService.fileConvertBean(new File("D:\\test\\测试导出excel.xlsx"), Model.class, model -> {
// 自行检查参数情况
ExcelExceptionAssert.notNUll(model.getA4(), "数量不能为空");
// 可以按规则将model对象转换成自己需要的实体对象
return model;
});
}
实现效果:
模板导入的excel(需要严格匹配模板类)
自定义导入的excel(对名字没有要求)：
执行结果:
4.1模板导入实现
这些功能的实现都为通过不同的监听器，来执行不同的数据解析操作
@Slf4j
public class TemplateImportEventListener<T, R> extends AnalysisEventListener<T> {
/**
* 返回结果
*/
private final List<R> result = new ArrayList<>();
/**
* 数据实体映射的转换器
*/
private final Function<T, R> convert;
/**
* 判断表格中是否存在数据,存在一种数据存在,但是数据校验不通过的情况,具体可以根据自己业务修改
*/
private boolean hasData;
@Override
public void invoke(T data, AnalysisContext context) {
// 这里都是自行修改
int maxSize = 10000;
if (result.size() >= maxSize) {
throw new ExcelException("超过最大导入数量10000");
}
// 这里设计可以允许返回空,比如数据校验不通过的情况,这中就属于存在数据,但是无法业务使用
R result = convert.apply(data);
if (result != null) {
this.result.add(result);
}
hasData = true;
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 数据全部执行完后会进来
if (CollectionUtil.isEmpty(result) && !hasData) {
throw new ExcelException("导入数据不能为空");
}
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
// 判断读取到的head和解析的模板是否一致
Map<Integer, Head> readHeadMap = context.readSheetHolder().excelReadHeadProperty().getHeadMap();
if (readHeadMap.size() != headMap.size()) {
throw new ExcelException("导入文件与模板不一致");
}
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
// 对异常统一处理
if (exception instanceof ExcelException) {
throw exception;
}
log.error("解析数据异常", exception);
throw new ExcelException("解析excel异常");
}
public List<R> getResult() {
// 获取解析结果
return result;
}
/**
* 构造实体转换的函数
*
* @param convert 模板实体转需要的实体
*/
public TemplateImportEventListener(Function<T, R> convert) {
this.convert = convert;
}
}
4.2自定义导入实现
思路:前端通过解析excel的头,做成下来选择,传给后端对应的索引,然后后端根据对应的索引去映射表格中的数据,自动查找则是根据提供的模板类上定义的名字来循环去判断,做一个模糊映射的操作.
// 这是传入的一个请求,多个数据就是前端传入对应的数据映射集合(我这里都调整成了本地file的形式,正常业务都是从前端来)
public class CustomImportReq {
/**
* 导入的工作表
*/
protected Integer sheetNo;
/**
* 导入数据对应的excel列
*/
protected List<FieldColumnReq> fieldColumns;
}
// 根据业务都可以自行调整代码
public class FieldColumnReq {
/**
* 对应的model字段名字
*/
private String fieldName;
/**
* 导入excel的第几列,-1代表根据字段自动查找
*/
private Integer column;
/**
* excel对应的标题名字,主要用作消息提示的
*/
private String columnHead;
}
实现映射的监听器:
@Slf4j
public class CustomImportEventListener<T, R> extends AnalysisEventListener<Map<Integer, String>> {
private final List<R> result;
/**
* 转换的数据模型
*/
private final Class<T> templateClass;
private final Function<T, R> convert;
/**
* 字段名对应的FieldExcelColumnReq,Column为-1代表自动匹配
*/
private final Map<String, FieldColumnReq> fieldExcelColumnMap;
/**
* excel列对应的字段名
*/
private final Map<String, String> excelColumnFieldMap;
private boolean hasData;
/**
* 字段名字对应中文标题,用作自动查找
*/
private Map<String, String> fieldChineseMap;
private final int AUTOMATIC_SEARCH_COLUMN = -1;
@Override
public void invoke(Map<Integer, String> data, AnalysisContext analysisContext) {
int maxSize = 10000;
if (result.size() >= maxSize) {
throw new ExcelException("超过最大导入数量10000");
}
Map<String, String> strDataMap = new HashMap<>();
// easyExcel解析出来的是数字列对应字符的map,hutool只支持string的key,所以做下转换
data.forEach((key, value) -> strDataMap.put(key.toString(), value));
// hutool提供的一个将map转换成bean的工具,通过设置别名即可映射
T bean = BeanUtil.toBean(strDataMap, templateClass, CopyOptions.create().setFieldMapping(excelColumnFieldMap));
R result = convert.apply(bean);
if (result != null) {
this.result.add(result);
}
hasData = true;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (CollectionUtil.isEmpty(result) && !hasData) {
throw new ExcelException("导入数据不能为空");
}
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
Set<Map.Entry<Integer, String>> headSet = headMap.entrySet();
for (Map.Entry<String, FieldColumnReq> entry : fieldExcelColumnMap.entrySet()) {
String field = entry.getKey();
FieldColumnReq fieldExcelColumn = entry.getValue();
Integer column = fieldExcelColumn.getColumn();
// 把索引为-1的查找到正确的索引位置
if (column == AUTOMATIC_SEARCH_COLUMN) {
String chineseFiled = fieldChineseMap.get(field);
// 通过判断字段上定义的中文名字去匹配解析出来的excel标题,通过contains的方式做到模糊匹配
for (Map.Entry<Integer, String> headEntry : headSet) {
String title = headEntry.getValue();
if (title.contains(chineseFiled)) {
column = headEntry.getKey();
break;
}
}
if (column == AUTOMATIC_SEARCH_COLUMN) {
throw new ExcelException("{}无法自动查找", chineseFiled);
}
}
// 将excel索引匹配正确的字段名
String oldField = excelColumnFieldMap.putIfAbsent(column.toString(), field);
// 这里业务是不让同一列对应同一个字段
if (oldField != null) {
FieldColumnReq oldFieldColumn = fieldExcelColumnMap.get(oldField);
throw new ExcelException("第{}列重复对应{}和{}", column + 1, oldFieldColumn.getColumnHead(),
fieldExcelColumn.getColumnHead());
}
}
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
if (exception instanceof ExcelException) {
throw exception;
}
log.error("解析异常", exception);
throw new ExcelException("数据解析异常,请检查");
}
public List<R> getResult() {
return result;
}
/**
* 初始化(自动查找的模板class不支持继承,需要继承的可以改造成循环遍历父类)
*
* @param fieldExcelColumns 字段名和excel列的信息
* @param templateClass 自动查找对应的列时用的模板类
*/
public CustomImportEventListener(List<FieldColumnReq> fieldExcelColumns, Class<T> templateClass, Function<T, R> convert) {
// 初始化字段名对应列
this.fieldExcelColumnMap = new HashMap<>();
for (FieldColumnReq fieldExcelColumn : fieldExcelColumns) {
Integer column = fieldExcelColumn.getColumn();
this.fieldExcelColumnMap.put(fieldExcelColumn.getFieldName(), fieldExcelColumn);
// 使用模板class初始化中文名对应的字段名,只有存在自动查找的时候才去初始化字段对应的中文名,如果没有自动查找的,就不需要初始了
if (this.fieldChineseMap == null && column == AUTOMATIC_SEARCH_COLUMN) {
initFieldChineseMap(templateClass);
}
}
this.templateClass = templateClass;
this.result = new ArrayList<>();
this.excelColumnFieldMap = new HashMap<>();
this.convert = convert;
}
/**
* 初始化自动查找的中文标题
*
* @param excelTemplateClass excel模板类
*/
private void initFieldChineseMap(Class<?> excelTemplateClass) {
this.fieldChineseMap = new HashMap<>();
// 将模板类上定义的中文名拿到(需要支持继承可以遍历父类)
for (Field field : excelTemplateClass.getDeclaredFields()) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
// 下面的操作是去除带*号和括号内的内容,根据自己业务可以调整,我这会有*商品(这是xxx)这种标题,匹配的时候只要商品两个字去匹配
String[] value = excelProperty.value();
if (StrUtil.isNotBlank(value[0])) {
// 自动查找需要去除模板类中*以及括号包含的内容
String formatStr = value[0];
int asteriskIndex = formatStr.indexOf("*");
int leftBracketIndex = formatStr.indexOf("(");
int substringStartIndex = 0;
int substringEndIndex = formatStr.length();
if (asteriskIndex != -1) {
substringStartIndex = asteriskIndex + 1;
}
if (leftBracketIndex != -1) {
substringEndIndex = leftBracketIndex;
}
fieldChineseMap.put(field.getName(), formatStr.substring(substringStartIndex, substringEndIndex));
}
}
}
}
}
4.3扩展导入(这里演示实现记录日志)
业务中可能会有需要对数据进行校验,记录导入日志等一些操作,可以通过扩展ExcelService的方法来进行实现,ExcelService主要只负责将excel数据解析成java对应的模型
@RequiredArgsConstructor
public class ExcelImportLogService {
private final ExcelService excelService;
/**
* 增加日志记录功能
*
* @param file 上传excel
* @param template 模板类
* @param convert 转换器
* @param customImportReq 自定义列导入
* @return 解析结果
*/
public <T, R> void fileConvertBean(File file, Class<T> template, Function<T, R> convert, CustomImportReq customImportReq) {
AtomicInteger successCount = new AtomicInteger();
AtomicInteger errorCount = new AtomicInteger();
// 通过将业务的转换函数包装,业务转换那里做数据校验,抛出指定的异常,即可统计,这里可以按照自己的业务,记录日志,存数据库等一些操作
Function<T, R> logRecordFunction = templateData -> {
R result = null;
try {
result = convert.apply(templateData);
successCount.incrementAndGet();
} catch (ExcelException excelException) {
// 自己制定校验出现的异常进行捕捉
System.out.println(excelException.getMessage());
errorCount.incrementAndGet();
}
return result;
};
System.out.println("=============================打印验证=================================");
List<R> result = excelService.fileConvertBean(file, template, customImportReq, logRecordFunction);
int error = errorCount.get();
int success = successCount.get();
int total = error + success;
System.out.println(StrUtil.format("error:{} success:{} total:{}", error, success, total));
System.out.println(result);
}
/**
* 不具备自定义导入功能,参数同上
*/
public <T, R> void fileConvertBean(File file, Class<T> template, Function<T, R> convert) {
fileConvertBean(file, template, convert, null);
}
}
以上功能只是切换成了本地file的形式,业务中要使用的时候可以换成mvc的MultipartFile和对应的servlet输出流
