使用EasyExcel进行简单的导入、导出

发布于:2025-03-18 ⋅ 阅读:(16) ⋅ 点赞:(0)

准备

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);