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