准备
在pom.xml添加依赖
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
导入
controller
// 用户导入
@Operation(summary = "用户导入")
@PostMapping("/importUserExcel")
@ResponseBody
public Result importUserExcel(@RequestParam("file")MultipartFile file){
return userService.importUserExcel(file);
}
导入实体类
@Data
public class UserImportParam {
@ExcelProperty(value = "* 所属组织code", index = 0)
private String orgCode;
@ExcelProperty(value = "* 用户编号", index = 1)
private String code;
@ExcelProperty(value = "* 用户名", index = 2)
private String name;
@ExcelProperty(value = "* 邮箱", index = 3)
private String email;
@ExcelProperty(value = "* 手机号", index = 4)
private String phoneNumber;
@ExcelProperty(value = "* 性别", index = 5)
private String gender;
@ExcelProperty(value = "* 角色", index = 6)
private String isStaff;
@ExcelProperty(value = "简介", index = 7)
private String userInfo;
}
通过@ExcelProperty进行实体类与Excel文件里标题进行映射，如果只存在value，必须与Excel标题一一对应。如果两者都存在index会覆盖value的匹配逻辑时
数据解析监听
public class UserImportListener extends AnalysisEventListener<UserImportParam> {
private final UserServiceImpl userService;
// 构造器注入
public UserImportListener(UserServiceImpl userService) {
this.userService = userService;
}
private static final int BATCH_SIZE = 100;
private List<SysUser> cachedList = new ArrayList<>(BATCH_SIZE);
// 用于校验
@Override
public void invoke(UserImportParam data, AnalysisContext context) {
// 校验用户所属组织是否存在
if (StringUtils.isEmpty(data.getOrgCode())) {
throw new RuntimeException("所属组织不能为空");
}
// 生成实体类
SysUser user = new SysUser();
cachedList.add(user);
}
/**
* 解析后的操作
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!cachedList.isEmpty()) {
userService.saveBatch(cachedList);
}
}
}
service
@Transactional
public Result importUserExcel(MultipartFile file) {
try {
InputStream inputStream = file.getInputStream();
EasyExcel.read(inputStream, UserImportParam.class, new UserImportListener(this))
.sheet()
.doRead();
return Result.success("导入成功");
} catch (Exception e) {
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
return Result.error("导入失败");
}
}
导出
基础导出
import com.alibaba.excel.EasyExcel;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
public class EasyExcelUtil {
/**
* 导出 Excel 文件
*
* @param response HttpServletResponse 对象
* @param fileName 导出的文件名
* @param dataList 要导出的数据列表
* @param clazz 数据列表元素的类型
* @throws IOException 当写入输出流发生错误时抛出此异常
*/
public static <T> void exportExcel(HttpServletResponse response, String fileName, List<T> dataList, Class<T> clazz) throws IOException, IOException {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 导出数据到 Excel
EasyExcel.write(response.getOutputStream(), clazz).sheet("数据信息").doWrite(dataList);
}
}
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public void exportUserExcel(HttpServletResponse response) throws IOException {
// 从数据库查询所有用户信息
List<User> userList = userRepository.findAll();
// 调用工具类导出数据
EasyExcelUtil .exportExcel(response, "用户信息.xlsx", userList, User.class);
}
}
// 用户信息导出
@Operation(summary = "用户信息导出")
@PostMapping("/exportUserExcel")
public void exportUserExcel( HttpServletResponse response) throws IOException {
userService.exportUserExcel(response);
}
@Data
public class User{
@ExcelProperty("用户名")
private String name; // 对应数据库字段 name
@ExcelProperty("年龄")
private Integer age; // 对应数据库字段 age
@ExcelProperty("注册时间")
private LocalDateTime createTime; // 对应数据库字段 create_time
}
@ExcelProperty 的 value 可以设置导出的列头
动态列导出
/**
* 动态导出 Excel 文件
*
* @param response HttpServletResponse 对象
* @param fileName 导出的文件名
* @param dataList 要导出的数据列表
* @param fieldNames 要导出的字段名
* @param clazz 数据列表元素的类型
* @throws IOException 当写入输出流发生错误时抛出此异常
*/
public static <T> void exportExcel(HttpServletResponse response, String fileName, List<T> dataList, List<String> fieldNames, Class<T> clazz) throws Exception {
// 设置响应头
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 生成表头
List<List<String>> head = generateHead(fieldNames, clazz);
// 生成数据
List<List<Object>> data = generateData(dataList, fieldNames, clazz);
// 导出数据到 Excel
EasyExcel.write(response.getOutputStream()).head(head).sheet("数据信息").doWrite(data);
}
// 生成表头
private static List<List<String>> generateHead(List<String> fieldNames, Class<?> clazz) throws Exception {
List<List<String>> head = new ArrayList<>();
for (String fieldName : fieldNames) {
// 获取字段的注解
Field field = clazz.getDeclaredField(fieldName);
// 获取字段的注解
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
// 如果注解为空，则使用字段名作为列名
String columnName = annotation != null ? annotation.value()[0] : fieldName;
// 将列名添加到表头中
head.add(Collections.singletonList(columnName));
}
return head;
}
// 生产数据
private static List<List<Object>> generateData(List<?> dataList, List<String> fieldNames, Class<?> clazz) throws Exception {
List<List<Object>> data = new ArrayList<>();
for (Object item : dataList) {
List<Object> row = new ArrayList<>();
for (String fieldName : fieldNames) {
// 获取字段的值
Field field = clazz.getDeclaredField(fieldName);
// 设置字段可访问
field.setAccessible(true);
// 设置字段的值
row.add(field.get(item));
}
data.add(row);
}
return data;
}
// 动态导出
List<String> fieldsToExport = Arrays.asList("code", "name", "gender", "isStaff");
EasyExcelUtil.exportExcel(response, "用户信息.xlsx", userList, fieldsToExport, UserExportResult.class);