2后端JAVA：下载数据库数据到EXCEL表格？代码

这是运用kimi，cursor写出来的后端基础增删改查代码心得系列。

将从如何用cursor写出代码，修改cursor写出来的代码，连接数据库，上传下载代码等等方面展开。

第一集： 

后端JAVA：Cursor与kimi如何结合？Cursor写出的代码出现哪些bug？-CSDN博客

下载代码：

/**
     * 下载数据库数据
     * 总情况：将数据库中的合同信息导出为Excel文件
     * @param response HTTP响应对象
     * @throws Exception 可能抛出的异常
     */
    @GetMapping("/download")
    @ApiOperation("下载数据库数据")
    public void download(HttpServletResponse response) throws Exception {
        // 查询多表数据
        List<DownloadDTO> list = contractsService.getContractsDetails();

        // 在内存操作，写到浏览器
        ExcelWriter writer = ExcelUtil.getWriter(true);
        // 自定义标题别名
        writer.addHeaderAlias("contractId", "序号");
        writer.addHeaderAlias("campus", "所在校区");
        writer.addHeaderAlias("address", "坐落地址");
        writer.addHeaderAlias("propertyName", "房产名称（铺位号）");
        writer.addHeaderAlias("tenantName", "租户");
        writer.addHeaderAlias("contactNumber", "联系电话");
        writer.addHeaderAlias("tenantInfo", "租户信息");
        writer.addHeaderAlias("area", "房屋面积（平方米）");
        writer.addHeaderAlias("contractDuration", "合同年限");
        writer.addHeaderAlias("contractCode", "合同编码");
        writer.addHeaderAlias("totalContractAmount", "合同总金额");
        writer.addHeaderAlias("contractStartDate", "合同期起");
        writer.addHeaderAlias("contractEndDate", "合同期止");
        writer.addHeaderAlias("yearOfRentReceivable", "应收租金年份");
        writer.addHeaderAlias("rentalPeriodStart", "本次应收租金期起");
        writer.addHeaderAlias("rentalPeriodEnd", "本次应收租金期止");
        writer.addHeaderAlias("currentRentReceived", "本次应收租金");
        writer.addHeaderAlias("currentRentDue", "本次实收租金");
        writer.addHeaderAlias("currentRentUnpaid", "本次未收租金");
        //writer.addHeaderAlias("currentRentReceived", "本次应收已收租金");
        writer.addHeaderAlias("rentReceivedDate", "收取租金时间");
        writer.addHeaderAlias("remarks", "备注");
        writer.addHeaderAlias("rentSubmittedToSchool", "已上交学校租金");
        writer.addHeaderAlias("rentNotSubmittedToSchool", "未上交学校租金");
        writer.addHeaderAlias("rentSubmissionDate", "租金上交学校时间");
        writer.addHeaderAlias("requiredDeposit", "应交押金");
        writer.addHeaderAlias("paidDeposit", "已交押金");
        writer.addHeaderAlias("depositEntryDate", "押金进账日期");

        // 写入数据
        writer.write(list, true);

        // 设置content-type
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        String fileName = URLEncoder.encode("合同信息", "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream, true);
        outputStream.close();
        writer.close();
    }

这里要注意的事项是：

1.writer.addHeaderAlias("contractId", "序号");   先写contractId，再写序号

2.要有对应的实体类

package com.ams;

import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.time.LocalDate;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DownloadDTO {
    @TableId
    private Integer contractId;

//    private Integer propertyId;
//    private Integer tenantId;
    private String contractCode;
    private LocalDate contractStartDate;
    private LocalDate contractEndDate;
    private Long contractDuration;
    private BigDecimal requiredDeposit;
    private BigDecimal paidDeposit;
    private LocalDate depositEntryDate;
    private BigDecimal totalContractAmount;

    private String campus;
    private String address;
    private String propertyName;
    private BigDecimal area;

    private LocalDate rentalPeriodStart;
    private LocalDate rentalPeriodEnd;
    private BigDecimal currentRentDue;
    private BigDecimal currentRentUnpaid;
    private LocalDate rentReceivedDate;
    private String remarks;
    private BigDecimal rentSubmittedToSchool;
    private BigDecimal rentNotSubmittedToSchool;
    private LocalDate rentSubmissionDate;
    private String yearOfRentReceivable;
    private BigDecimal currentRentReceived;
    private String tenantName;
    private String contactNumber;
    private String tenantInfo;

} 

3.对应的ServiceImpl，Service层

/**
     * 查询合同详情信息
     * 总情况：查询合同详情信息，用于导出
     * @return 查询结果
     */
    List<DownloadDTO> getContractsDetails();

/**
     * 查询合同详情信息
     * 总情况：查询合同详情信息，用于导出
     * @return 查询结果
     */
    @Override
    public List<DownloadDTO> getContractsDetails() {
        return contractsMapper.findContractsDetails(); // 调用多表查询
    }

4.对应的mapper层，mapper

 /**
     * 查询合同详情信息
     * 总情况：查询合同详情信息，用于导出
     * @return 查询结果
     */
    List<DownloadDTO> findContractsDetails();

<select id="findContractsDetails" resultType="com.ams.DownloadDTO">
        SELECT c.contractCode, c.contractStartDate, c.contractEndDate, c.contractDuration,
               c.requiredDeposit, c.paidDeposit, c.depositEntryDate, c.totalContractAmount,c.contractID,
               p.campus, p.address, p.propertyName, p.area,
               r.rentalPeriodStart, r.rentalPeriodEnd, r.currentRentDue,
               r.currentRentUnpaid, r.rentReceivedDate, r.remarks,
               r.currentRentReceived,r.rentSubmissionDate,r.rentSubmittedToSchool,
               r.rentNotSubmittedToSchool,r.yearOfRentReceivable,
               t.tenantName, t.contactNumber, t.tenantInfo
        FROM contracts c
                 JOIN rentals r ON c.rentalsId = r.rentalId
                 JOIN tenants t ON c.tenantId = t.tenantId
                 JOIN properties p ON c.propertyId = p.propertyId
    </select>

 这里用到的是多表联查。

 5.用到的pom，这里要注意，父pom和子pom都要引用

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.7.20</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

6.效果如图

 以及下面的数据库信息（这里不作展示）