如何将Spring Data JPA Repository中查询的结果保存为Excel文件

1. 修正Repository定义与映射结构 #

你当前的Repository存在两处问题：一是@Modifying注解仅用于增删改类DML操作，查询场景必须移除；二是查询返回的支付相关字段和User实体不匹配，需要新增对应的DTO类做结果映射。

• 新建报表DTO类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.ColumnWidth;
import lombok.Data;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Data
public class PaymentReportDTO {
    @ExcelProperty("支付记录ID")
    @ColumnWidth(15)
    private Long id;
    @ExcelProperty("创建时间")
    @ColumnWidth(20)
    private LocalDateTime createdDt;
    @ExcelProperty("操作人用户名")
    @ColumnWidth(15)
    private String username;
    @ExcelProperty("服务名称")
    @ColumnWidth(15)
    private String serviceName;
    @ExcelProperty("支付金额")
    @ColumnWidth(12)
    private BigDecimal amount;
    @ExcelProperty("支付状态")
    @ColumnWidth(12)
    private String status;
    @ExcelProperty("支付账户")
    @ColumnWidth(20)
    private String account;
    @ExcelProperty("外部流水ID")
    @ColumnWidth(20)
    private String externalId;
    @ExcelProperty("外部状态")
    @ColumnWidth(15)
    private String externalStatus;
}

• 调整Repository代码

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Query(value = "SELECT pp.id, pp.created_dt as createdDt, au.username, ss.name as serviceName, pp.amount, " +
            "REPLACE(pp.status, 'SUCCESS', 'Success') AS status, pp.account, " +
            "pp.external_id as externalId, COALESCE(pp.external_status, null, 'Indefined') AS externalStatus " +
            "FROM payments AS pp " +
            "INNER JOIN auth_user AS au ON au.id = pp.creator_id " +
            "INNER JOIN services AS ss ON ss.id = pp.service_id " +
            "WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date " +
            "AND ss.name = 'SomeName' AND pp.status = 'SUCCESS'", nativeQuery = true)
    List<PaymentReportDTO> queryDailyPaymentReport();
}

2. 引入Excel处理依赖 #

推荐使用EasyExcel处理Excel生成，内存占用低、配置简单，Maven依赖如下：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

3. 编写生成本地Excel的业务逻辑 #

import com.alibaba.excel.EasyExcel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;

@Service
public class PaymentReportService {
    @Autowired
    private UserRepository userRepository;

    // 本地文件保存路径，可配置到application.yml中
    @Value("${report.save-path:/data/report/daily_payment.xlsx}")
    private String savePath;

    @Transactional(readOnly = true)
    public void generateDailyReport() {
        // 查询报表数据
        List<PaymentReportDTO> reportData = userRepository.queryDailyPaymentReport();
        // 写入本地指定路径
        EasyExcel.write(savePath, PaymentReportDTO.class)
                .sheet("昨日成功支付报表")
                .doWrite(reportData);
    }
}

执行generateDailyReport方法后，Excel文件就会生成在你配置的savePath路径下，后续直接读取该文件作为邮件附件发送即可。

内容的提问来源于stack exchange，提问作者Alexander