springboot3导出数据库数据到excel

一、导入依赖

         <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.3.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>6.1.13</version>
        </dependency>

二、实体类

package com.guide.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;
import org.apache.poi.ss.usermodel.Sheet;

/**
 * @author cxy
 * @create 2024-10-11 09:41:43
 */

@Data
@TableName("workpiece")
public class Workpiece {
    @TableId(value = "id", type = IdType.AUTO)
    @Schema(description = "id")
    private Integer id;

    @TableField(value = "vehicleModel")
    @Schema(description = "车型")
    private String vehicleModel;

    @TableField(value = "twoNum")
    @Schema(description = "轴二起始Num")
    private Integer twoNum;

    @TableField(value = "threeNum")
    @Schema(description = "轴三起始Num")
    private Integer threeNum;

    @TableField(value = "imgUrl")
    @Schema(description = "图片路径")
    private String imgUrl;

    public Workpiece(int id, String vehicleModel, int twoNum, int threeNum, String imgUrl) {
        this.id = id;
        this.vehicleModel = vehicleModel;
        this.twoNum = twoNum;
        this.threeNum = threeNum;
        this.imgUrl = imgUrl;
    }

    public Workpiece() {
    }
}

三、controller层 

package com.guide.controller;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.guide.entity.Workpiece;
import com.guide.result.Result;
import com.guide.service.WorkpieceService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.media.Schema;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author cxy
 * @create 2024-10-12 10:12:02
 */

@Tag(name = "excel")
@RequestMapping("/excel")
@RestController
@CrossOrigin(origins = "*")
@Slf4j
@RequiredArgsConstructor
public class ExcelController {
    private final WorkpieceService service;
    private final JdbcTemplate jdbcTemplate;

    @PostMapping("/downWorkpiece")
    @Schema(description = "工件信息导出excel")
    public Result downWorkpiece(HttpServletResponse response) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data");

        // 创建表头
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("id");
        header.createCell(1).setCellValue("车型");
        header.createCell(2).setCellValue("轴二起始Num");
        header.createCell(3).setCellValue("轴三起始Num");
        header.createCell(4).setCellValue("图片路径");

        // 查询数据库并填充数据
        List<Workpiece> dataList = jdbcTemplate.query(
                "SELECT id,vehicleModel,twoNum,threeNum,imgUrl FROM workpiece",
                (ResultSet rs, int rowNum) -> new Workpiece(rs.getInt("id"), rs.getString("vehicleModel")
                        , rs.getInt("twoNum"), rs.getInt("threeNum"), rs.getString("imgUrl"))
        );

        // 填充数据到Excel表格
        int rowNum = 1;
        for (Workpiece data : dataList) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(data.getId());
            row.createCell(1).setCellValue(data.getVehicleModel());
            row.createCell(2).setCellValue(data.getTwoNum());
            row.createCell(3).setCellValue(data.getThreeNum());
            row.createCell(4).setCellValue(data.getImgUrl());
        }

        // 设置响应头
        response.setHeader("Content-Disposition", "attachment; filename=\"data.xlsx\"");
        response.setStatus(HttpServletResponse.SC_OK);

        // 写入响应流并下载文件
        ServletOutputStream outStream = response.getOutputStream();
        workbook.write(outStream);
        outStream.flush();
        workbook.close();
        return Result.success();
    }

}