准备
在pom.xml添加依赖
<!-- EasyExcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>
导入
controller
// 用户导入@Operation(summary = "用户导入")@PostMapping("/importUserExcel")@ResponseBodypublic 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);// 用于校验@Overridepublic void invoke(UserImportParam data, AnalysisContext context) {// 校验用户所属组织是否存在if (StringUtils.isEmpty(data.getOrgCode())) {throw new RuntimeException("所属组织不能为空");}// 生成实体类SysUser user = new SysUser();cachedList.add(user);}/*** 解析后的操作*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {if (!cachedList.isEmpty()) {userService.saveBatch(cachedList);}}
}
service
@Transactionalpublic 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);// 导出数据到 ExcelEasyExcel.write(response.getOutputStream(), clazz).sheet("数据信息").doWrite(dataList);}
}
@Service
public class UserService {@Autowiredprivate 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);// 导出数据到 ExcelEasyExcel.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);