Springboot项目查询mysql库导出Excel内容给前端,工具类

这里提供一个基于Spring Boot和Apache POI的工具类，可以方便地将MySQL数据库中的数据导出为Excel文件并返回给前端。

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ExcelUtil {
    // MySQL数据库连接信息
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/dbname";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";

    // 导出Excel文件
    public static void exportExcel(String sql, HttpServletResponse response) throws IOException, SQLException {
        // 1. 获取数据库连接
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

        // 2. 执行SQL查询
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        // 3. 获取查询结果列名和数据
        ResultSetMetaData meta = rs.getMetaData();
        int columnCount = meta.getColumnCount();
        List<String> columnNames = new ArrayList<>();
        List<List<Object>> data = new ArrayList<>();
        for (int i = 1; i <= columnCount; i++) {
            columnNames.add(meta.getColumnName(i));
        }
        while (rs.next()) {
            List<Object> rowData = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.add(rs.getObject(i));
            }
            data.add(rowData);
        }

        // 4. 创建Excel工作簿对象
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // 5. 设置列名样式
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.index);
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.BLUE.index);
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headerStyle.setFont(headerFont);

        // 6. 写入列名
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < columnCount; i++) {
            headerRow.createCell(i).setCellValue(columnNames.get(i));
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        // 7. 写入数据
        for (int i = 0; i < data.size(); i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnCount; j++) {
                row.createCell(j).setCellValue(data.get(i).get(j).toString());
            }
        }

        // 8. 将工作簿写入字节数组输出流
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        workbook.write(out);

        // 9. 设置HTTP响应头
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=data.xls");

        // 10. 将字节数组输出流写入响应流
        ServletOutputStream outputStream = response.getOutputStream();
        outputStream.write(out.toByteArray());

        // 11. 关闭资源
        outputStream.flush();
        outputStream.close();
        out.flush();
        out.close();
        rs.close();
        stmt.close();
        conn.close();
    }
}

使用时，只需要调用`ExcelUtil.exportExcel(sql, response)`方法即可，其中`sql`参数为要执行的SQL查询语句，`response`参数为HTTP响应对象。