本文主要介绍利用easyExcel做的excel导入导出工具类，导出时进行合并单元格，导入时进行各种校验（字符串，数字，日期，字段唯一）提示成功数据以及错误信息，显示导入失败数据的行数，错误原因等。手把手教学
1.导入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.lkx</groupId>
<artifactId>excel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excel</name>
<description>excel导入导出测试案例</description>
<properties>
<java.version>8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--json转换-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.83</version>
</dependency>
<!--校验工具类-->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<!--swagger-->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<!-- hutool工具类-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.20</version>
</dependency>
<!-- easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
<exclusions>
<exclusion>
<artifactId>slf4j-api</artifactId>
<groupId>org.slf4j</groupId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
2.自定义excel监听器
该监听器在读取excel表格时，会将excel读取到一个Map集合中，key是属性的下标，value是字段值，最后将Map放到List中
package com.lkx.excel.util;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* <p>导入监听器
*/
public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(NoModelDataListener.class);
List<Map<Integer, String>> list = new ArrayList<>();
@Override
public void invoke(Map<Integer, String> data, AnalysisContext context) {
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("所有数据解析完成！");
}
public List<Map<Integer, String>> getList() {
return list;
}
public void setList(List<Map<Integer, String>> list) {
this.list = list;
}
}
3.合并单元格处理器
package com.lkx.excel.util.handler;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMergeCellHandler implements CellWriteHandler {
// 需要合并的列，从0开始算
private int[] mergeColIndex;
// 从指定的行开始合并，从0开始算
private int mergeRowIndex;
/**
* 当前单元格向上合并
*
* @param writeSheetHolder 表格处理句柄
* @param cell 当前单元格
* @param currRowIndex 当前行
* @param currColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int currRowIndex, int currColIndex) {
// 获取当前单元格数值
Object currData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
// 获取当前单元格正上方的单元格对象
Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
// 获取当前单元格正上方的单元格的数值
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数值与其正上方单元格的数值比较
if (preData.equals(currData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
// 当前单元格的正上方单元格是否是已合并单元格
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress address = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (address.isInRange(currRowIndex - 1, currColIndex)) {
sheet.removeMergedRegion(i);
address.setLastRow(currRowIndex);
sheet.addMergedRegion(address);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
/**
* 在单元格上的所有操作完成后调用，遍历每一个单元格，判断是否需要向上合并
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
// 获取当前单元格行下标
int currRowIndex = cell.getRowIndex();
// 获取当前单元格列下标
int currColIndex = cell.getColumnIndex();
// 判断是否大于指定行下标，如果大于则判断列是否也在指定的需要的合并单元列集合中
if (currRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColIndex.length; i++) {
if (currColIndex == mergeColIndex[i]) {
/**
* 获取列表数据的唯一标识。不同集合的数据即使数值相同也不合并
* 注意：我这里的唯一标识为客户编号（Customer.userCode），在第一列，即下标为0。大家需要结合业务逻辑来做修改
*/
// 获取当前单元格所在的行数据的唯一标识
Object currCode = cell.getRow().getCell(0).getStringCellValue();
// 获取当前单元格的正上方的单元格所在的行数据的唯一标识
Object preCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
// 判断两条数据的是否是同一集合，只有同一集合的数据才能合并单元格
if(preCode.equals(currCode)){
// 如果都符合条件，则向上合并单元格
mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
break;
}
}
}
}
}
}
4.Excel读取处理器
package com.lkx.excel.util.handler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
public class MySheetWriteHandler implements com.alibaba.excel.write.handler.SheetWriteHandler {
private String date;
private int lastCol;
//标题名
private String titleName;
public MySheetWriteHandler(String date, int lastCol, String titleName){
this.date=date;
this.lastCol=lastCol;
this.titleName=titleName;
}
public MySheetWriteHandler() {
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 1280);
Cell cell = row1.createCell(0);
//设置标题
cell.setCellValue(StringUtils.isNotBlank(date) ?
titleName+ "("+ date +")" : titleName);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 500);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCol));
}
}
5.属性赋值工具类
package com.lkx.excel.util;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import org.springframework.beans.BeanUtils;
/**
* @Author: lkx
* @Date: 2024/6/29/12:38
* @Description:
*/
public class BeanUtilExt extends BeanUtils {
public static <k> k copyBeanSuper(Object source,Class<k> target) {
String s = JSON.toJSONString(source);
return JSON.parseObject(s,target);
}
}
6.Excel通用工具类
package com.lkx.excel.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import lombok.extern.slf4j.Slf4j;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import static java.util.stream.Collectors.toList;
@Slf4j
public class ExcelCommonUtil {
/**
* 不创建对象的都
*
* @param excelInputStream
* @param fileName
* @return
*/
public static List<Map<Integer, String>> readExcel(InputStream excelInputStream,
String fileName,
Integer headRowNumber) {
NoModelDataListener excelListener = new NoModelDataListener();
ExcelReader excelReader = getReader(excelInputStream, fileName, excelListener,headRowNumber);
return getExcelMap(excelListener,excelReader);
}
/**
*通过监听器将数据读取到List里的Map中
*/
public static List<Map<Integer, String>> getExcelMap(NoModelDataListener excelListener,ExcelReader excelReader){
if (excelReader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
for (ReadSheet readSheet : readSheetList) {
excelReader.read(readSheet);
}
return excelListener.getList();
}
/**
* 获取ExcelReader
*
* @param excelListener
*/
private static ExcelReader getReader(InputStream inputStream, String filename, AnalysisEventListener excelListener
,Integer index) {
try {
if (filename == null ||
(!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
return null;
}
ExcelReader excelReader = EasyExcel.read(inputStream, excelListener)
.headRowNumber(index)
.build();
inputStream.close();
return excelReader;
} catch (Exception e) {
StackTraceElement[] stackTrace = e.getStackTrace();
String exceptionMsg = "错误类名: "+ stackTrace[0].getClassName()
+",错误方法名：" +stackTrace[0].getMethodName()
+",错误行号："+stackTrace[0].getLineNumber()
+",异常原因"+ e.toString();
log.error("exceptionMsg = {}",exceptionMsg);
}
return null;
}
/**
* 通过反射获取class属性
**/
public static Field[] getAllFields(Class<?> clazz) {
List<Field> fieldList = new ArrayList<>();
while (clazz != null) {
fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
clazz = clazz.getSuperclass();
}
Field[] fields = new Field[fieldList.size()];
return fieldList.toArray(fields);
}
}
7.文件导入结果集封装
package com.lkx.excel.util;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
* @Author: lkx
* @Description: 文件导入返回结果封装
*/
@Data
@ApiModel(description = "Excel文件导入返回结果")
public class ExcelImportResult<T> {
@ApiModelProperty("导入错误详情")
private List<String> error;
@ApiModelProperty("导入错误条数")
private Integer errorNum;
@ApiModelProperty("导入成功的数据")
private List<T> successData;
}
8.Excel导入导出工具类
package com.lkx.excel.util;
import cn.hutool.core.convert.Convert;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.google.common.collect.Lists;
import com.lkx.excel.util.handler.ExcelMergeCellHandler;
import com.lkx.excel.util.handler.MySheetWriteHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
/**
* @author: lkx
* @Description:
*/
@Slf4j
public class ExcelUtil {
/**
* 导入excel并校验格式
* @param excelInputStream 文件输入流
* @param fileName 文件名
* @param clazz excel类对象
* @param notNullList 不为空字段下标集合
* @param numberList 必须为数字字段的下标集合
* @param dateList 日期格式校验 必须为 yyyy-MM-dd 格式，需要更改验证格式更改正则表达式即可
* @param distinctMap 需要字段不重复集合 K-V = 字段下标-数据库中该字段的集合
* @param headRowNumber
* @param <T>
* @return
*/
public static <T> ExcelImportResult<T> indexReadExcel(InputStream excelInputStream,
String fileName,
Class<T> clazz,
List<Integer> notNullList,
List<Integer> numberList,
List<Integer> dateList,
Map<Integer, List<String>> distinctMap,
Integer headRowNumber) {
List<Map<Integer, String>> maps = ExcelCommonUtil.readExcel(excelInputStream, fileName ,headRowNumber);
return getReadExcelEntityFxfx(clazz, notNullList,numberList,dateList,distinctMap, maps);//所有相关功能自动补齐工厂
}
public static <T> ExcelImportResult<T> getReadExcelEntityFxfx(Class<T> clazz,
List<Integer> notNullList,
List<Integer> numberList,
List<Integer> dateList,
Map<Integer, List<String>> distinctMap,
List<Map<Integer, String>> maps) {
//常规实现excel导入
ExcelImportResult<T> readExcelEntity = new ExcelImportResult<>();
// 导入成功的数据集合
List<T> successData = new ArrayList<>();
// 错误信息集合
List<String> errorList = new ArrayList<>();
// excel实体类属性
Field[] fields = ExcelCommonUtil.getAllFields(clazz);
// 第二行开始读取数据 第一行为表格标题
int row = 2;
// 错误条数
int num = 0;
for (Map<Integer, String> integerStringMap : maps) {
Map<String, Object> objectMap = new HashMap<>();
// 当前行数据校验是否通过标识
boolean flag = true;
try {
// 遍历excel实体类属性
for (Field field : fields) {
// 实体类必须有@ExcelProperty注解
if (!field.isAnnotationPresent(ExcelProperty.class)) {
continue;
}
// 获取注解中的配置
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
// 根据下标从读取到的信息中获取表格中的实际值
String value = integerStringMap.get(annotation.index());
//不为空校验
if (notNullList.contains(annotation.index()) && StringUtils.isBlank(value)) {
errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不能为空！");
flag = false;
}
//数字检验
if (numberList.contains(annotation.index()) && StringUtils.isNotBlank(value) && !NumberUtils.isCreatable(value)) {
errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不是一个数字！");
flag = false;
}
// 值重复校验
if (distinctMap != null && !distinctMap.isEmpty()) {
Set<Integer> keySet = distinctMap.keySet();
if (keySet.contains(annotation.index())) {
List<String> list = distinctMap.get(annotation.index());
if (list.contains(value)) {
errorList.add("第" + row + "行的[" + annotation.value()[0] + "]值重复！");
flag = false;
} else {
if (flag) {
list.add(value);
distinctMap.put(annotation.index(),list);
}
}
}
}
// 日期格式校验
if (!CollectionUtils.isEmpty(dateList)) {
if (dateList.contains(annotation.index())) {
String str = "([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]|[0-9][1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))";
if (!value.matches(str)) {
errorList.add("第" + row + "行的[" + annotation.value()[0] + "]不是一个日期格式！");
flag = false;
}
}
}
// 全部验证通过，将属性字段放入map中
if (flag) {
objectMap.put(field.getName(), value);
}
}
// 当且仅当，一整条数据的flag为true时，才算导入成功
if (flag) {
T t = BeanUtilExt.copyBeanSuper(objectMap, clazz);
successData.add(t);
} else {
num++;
}
} catch (Exception e) {
log.error("解析数据对象出现错误！", e);
}
row++;
}
readExcelEntity.setSuccessData(successData);
readExcelEntity.setError(errorList);
readExcelEntity.setErrorNum(num);
return readExcelEntity;
}
/**
* 通用自定义标题头和合并导出
*
* @param response response
* @param list 数据集合
* @param fileName 文件名
* @param sheetName sheet名
* @param clazz 导出类
* @param mergeRowIndex 合并开始的行
* @param mergeColumnIndex 合并的列
* @param mySheetWriteHandler 自定义处理器
*/
public static <T> void writeExcelRow(HttpServletResponse response,
List<T> list,
String fileName,
String sheetName,
Class<T> clazz,
int mergeRowIndex,
int[] mergeColumnIndex, MySheetWriteHandler mySheetWriteHandler) {
OutputStream outputStream = getOutputStream(response, fileName);
WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
ExcelWriter writer = EasyExcel.write(outputStream, clazz).needHead(true)
.relativeHeadRowIndex(1)
.registerWriteHandler(new ExcelMergeCellHandler(mergeColumnIndex, mergeRowIndex))
.registerWriteHandler(mySheetWriteHandler)
.build();
writer.write(list, sheet);
writer.finish();
}
// 验证excel格式
public static final boolean checkExtension(String extension) {
return Lists.newArrayList("xls", "xlsx", "XLS", "XLSX").contains(extension);
}
/**
* 导出时生成OutputStream
*/
private static OutputStream getOutputStream(HttpServletResponse response, String fileName) {
//创建本地文件
fileName = fileName + ".xlsx";
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
response.setHeader("myExcel", "202");
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
9.swagger配置类
package com.lkx.excel.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
import java.util.Collections;
/**
* @author lkx
* @date:
*/
@Configuration
@EnableSwagger2
public class SwaggerConfig implements WebMvcConfigurer {
/**
* api接口包扫描路径
*/
private static final String SWAGGER_SCAN_BASE_PACKAGE = "com.lkx.excel";
/**
* 版本号
*/
private static final String VERSION = "1.0.0";
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.protocols(Collections.singleton("http"))
.apiInfo(apiInfo())
.select()
.apis(RequestHandlerSelectors.basePackage(SWAGGER_SCAN_BASE_PACKAGE))
// 可以根据url路径设置哪些请求加入文档，忽略哪些请求
.paths(PathSelectors.any())
.build();
}
private ApiInfo apiInfo() {
String title = "excel - " + "测试" + "模块";
return new ApiInfoBuilder()
//设置文档的标题
.title(title)
// 设置文档的描述
.description(title)
// 设置文档的版本信息-> 1.0.0 Version information
.version(VERSION)
.build();
}
/**
* 防止@EnableMvc把默认的静态资源路径覆盖了，手动设置的方式
*
* @param registry
*/
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler("/static/**").addResourceLocations("classpath:/static/");
registry.addResourceHandler("swagger-ui.html")
.addResourceLocations("classpath:/META-INF/resources/");
registry.addResourceHandler("doc.html")
.addResourceLocations("classpath:/META-INF/resources/");
registry.addResourceHandler("/webjars/**")
.addResourceLocations("classpath:/META-INF/resources/webjars/");
registry.addResourceHandler("doc.html")
.addResourceLocations("classpath:/META-INF/resources/");
}
/**
* 配置servlet处理
*/
@Override
public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
configurer.enable();
}
}
10.模拟学生excel实体类
package com.lkx.excel.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 学生信息
*/
@Data
@ColumnWidth(20)
@NoArgsConstructor
@AllArgsConstructor
public class StudentInfoExcel {
@ApiModelProperty("姓名")
@ExcelProperty(index = 0,value = "姓名")
private String name;
@ApiModelProperty("班级")
@ExcelProperty(index = 1,value = "班级")
private String className;
@ApiModelProperty("学号")
@ExcelProperty(index = 2,value = "学号")
private String stuNo;
@ApiModelProperty("籍贯")
@ExcelProperty(index = 3,value = "籍贯")
private String nativePlace;
@ApiModelProperty("民族")
@ExcelProperty(index = 4,value = "民族")
private String nation;
@ApiModelProperty("性别")
@ExcelProperty(index = 5,value = "性别")
private String sex;
@ApiModelProperty("年龄")
@ExcelProperty(index = 6,value = "年龄")
private Integer age;
@ApiModelProperty("家庭住址")
@ExcelProperty(index = 7,value = "家庭住址")
private String address;
}
11.controller测试
package com.lkx.excel.controller;
import com.lkx.excel.entity.StudentInfoExcel;
import com.lkx.excel.util.ExcelImportResult;
import com.lkx.excel.util.ExcelUtil;
import com.lkx.excel.util.handler.MySheetWriteHandler;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
/**
* @Author: lkx
* @Date: 2024/6/29/14:56
* @Description:
*/
@RestController
@Slf4j
@RequestMapping(value = "/test")
@Api(tags = "excel测试")
public class TestController {
@ApiModelProperty("导出excel")
@PostMapping("/exportExcel")
public void export(HttpServletResponse response) {
List<StudentInfoExcel> list = new ArrayList<>();
StudentInfoExcel studentInfoExcel1 = new StudentInfoExcel("小明", "高三一班", "202401001", "四川成都",
"汉族", "男", 17, "花果山水帘洞1号");
StudentInfoExcel studentInfoExcel2 = new StudentInfoExcel("小红", "高三二班", "202402001", "四川乐山",
"汉族", "女", 18, "乐山市乐山大佛");
list.add(studentInfoExcel1);
list.add(studentInfoExcel2);
ExcelUtil.writeExcelRow(response, list, "学生信息表",
"学生信息表", StudentInfoExcel.class, 1, new int[]{0, 1, 2, 3, 4, 5, 6, 7},
new MySheetWriteHandler(null, 7, "学生信息表"));
}
/**
* 对比两个表数据之差
* @param file1
* @param file2
* @return
*/
@PostMapping("/importStudentInfoExcel")
@ApiOperation("导入环保设施基础信息")
public ExcelImportResult<StudentInfoExcel> importStudentInfoExcel(MultipartFile file) throws Exception {
String fileName = file.getOriginalFilename();
// 文件名称（不含后缀名）
String type = fileName.indexOf(".") != -1 ? (fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length())) : null;
if (!ExcelUtil.checkExtension(type)) {
throw new Exception("文件格式错误！");
}
ExcelImportResult<StudentInfoExcel> result = new ExcelImportResult<>();
// 假设数据库中已经有一些学号数据了
HashMap<Integer, List<String>> distinctMap = new HashMap<>();
ArrayList<String> list = new ArrayList<>();
list.add("202401001");
list.add("202401002");
list.add("202401003");
distinctMap.put(2,list);
try {
result = ExcelUtil.indexReadExcel(file.getInputStream(),
fileName,
StudentInfoExcel.class,
new ArrayList<>(Arrays.asList(0,1, 2, 3,4,5,7)),
new ArrayList<>(Arrays.asList(6)),
null,
distinctMap,
2);
} catch (IOException e) {
log.error("导入出错：{}", e.getMessage());
}
return result;
}
}
12.启动类
package com.lkx.excel;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @Author: lkx
* @Date: 2024/6/29/14:41
* @Description:
*/
@SpringBootApplication
public class ExcelApplication {
public static void main(String[] args) {
SpringApplication.run(ExcelApplication.class,args);
}
}
总结
这里主要是通过自定义监听器 NoModelDataListener 读取数据到map中，然后通过MySheetWriteHandler处理sheet格式，用反射获取实体类属性，然后遍历获取到的数据，再遍历反射时获取到的属性，只要使用的注解@ExcelProperty的，下标在验证集合当中的，进行格式校验。
