Java 数据库数据导出到 Excel 文件并下载

可以使用 Apache POI 库来实现将数据库信息下载到具有标题的 Excel 中，并使用 Servlet 将 Excel 文件提示在浏览器上。以下是一个简单的示例代码：

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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;

public class DownloadServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String fileName = 'database_info.xlsx';
        response.setContentType('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        response.setHeader('Content-disposition', 'attachment; filename=' + fileName);

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet('Database Info');

        // 设置标题
        Row headerRow = sheet.createRow(0);
        String[] headers = {'ID', 'Name', 'Email'};
        for (int i = 0; i < headers.length; i++) {
            Cell headerCell = headerRow.createCell(i);
            headerCell.setCellValue(headers[i]);
        }

        // 从数据库中获取数据
        try {
            Class.forName('com.mysql.jdbc.Driver');
            Connection connection = DriverManager.getConnection('jdbc:mysql://localhost:3306/mydb', 'username', 'password');
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery('SELECT * FROM users');

            // 填充数据
            int rowNum = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(resultSet.getInt('id'));
                row.createCell(1).setCellValue(resultSet.getString('name'));
                row.createCell(2).setCellValue(resultSet.getString('email'));
            }

            // 设置列宽和日期格式
            sheet.setColumnWidth(0, 256 * 4);
            sheet.setColumnWidth(1, 256 * 20);
            sheet.setColumnWidth(2, 256 * 30);
            CellStyle dateCellStyle = workbook.createCellStyle();
            DataFormat dataFormat = workbook.createDataFormat();
            dateCellStyle.setDataFormat(dataFormat.getFormat('yyyy-MM-dd'));
            sheet.setDefaultColumnStyle(3, dateCellStyle);

            // 输出Excel文件
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

在Web应用程序中，您可以将此Servlet映射到一个URL，并在需要下载数据库信息的地方创建一个链接或按钮来触发下载。例如：

<a href='/download'>Download Database Info</a>

当用户单击链接时，将调用DownloadServlet，该Servlet将从数据库中检索信息并将其写入Excel文件，然后将Excel文件发送到浏览器以下载。