使用POI导出SQL结果生成的Excel文件损坏问题求助

之前直连3306端口时，生成Excel文件正常；切换为SSH隧道连接MySQL后，生成的.xlsx文件打开时报错：

Excel无法打开文件，因为文件格式或文件扩展名无效。请验证文件未损坏且文件扩展名与文件格式匹配。

控制台无报错信息，相关代码及依赖版本如下：

生成Excel的核心代码 #

package process;

import java.sql.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;


public class WriteMySQLToExcel {
   public static void main(String[] args) throws SQLException, IOException {

         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/wp_dashort", "dashort", "gt9wkk6r1TPnkgrY");
         
         String mark_complete = "SELECT DISTINCT wpgc.user_id, wpgc.timestamp\r\n"
                    + "FROM wp_grassblade_completions as wpgc \r\n" + "  INNER JOIN \r\n"
                    + "  wp_usermeta ON wpgc.user_id = wp_usermeta.user_id\r\n"
                    + "  WHERE wpgc.user_id NOT IN (SELECT atc_reporting.user_id FROM atc_reporting WHERE course = 'SECURITY') AND wpgc.timestamp BETWEEN (CURRENT_DATE -INTERVAL 30 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AND content_id IN (1575, 642, 1580) \r\n"
                    + "GROUP BY wpgc.user_id\r\n" + "order BY wpgc.timestamp, wp_usermeta.user_id";
         
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(mark_complete);
         
         XSSFWorkbook workbook = new XSSFWorkbook();
         XSSFSheet sheet = workbook.createSheet("Query Results");
         // Create header row
         XSSFRow headerRow = sheet.createRow(0);
         for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
           headerRow.createCell(i - 1).setCellValue(rs.getMetaData().getColumnName(i));
         }

         int rowNum = 1;
         while (rs.next()) {
           XSSFRow row = sheet.createRow(rowNum++);
           for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
             row.createCell(i - 1).setCellValue(rs.getString(i));
           }
         }
  
         FileOutputStream fileOut = new FileOutputStream("D://testexpdata.xlsx");
         workbook.write(fileOut);
         fileOut.close();
         workbook.close();
         
         rs.close();
         stmt.close();
         conn.close();
      }
   }

SSH连接代码 #

public class SQLConnection {
private static Connection connection = null;
private static Session session = null;

private static void connectToServer(String dataBaseName) throws SQLException {
    dataBaseName="wp_dashort";
    connectSSH();
    connectToDataBase(dataBaseName);

}

static void connectSSH() throws SQLException {
    String sshHost = "shortguy.ssh.wpengine.net";
    String sshuser = "shortguy";
    String dbuserName = "shortguy";
    String dbpassword = "gt9wgrYpassword";
    String SshKeyFilepath = "c://users//ed25519eclipse";

    int localPort = 3307; // any free port can be used
    String remoteHost = "127.0.0.1";
    int remotePort = 3306;
    String localSSHUrl = "localhost";
    /***************/
    String driverName = "com.mysql.cj.jdbc.Driver";

    try {
        java.util.Properties config = new java.util.Properties();
        JSch jsch = new JSch();
        session = jsch.getSession(sshuser, sshHost, 22);
        jsch.addIdentity(SshKeyFilepath);
        config.put("StrictHostKeyChecking", "no");
        config.put("ConnectionAttempts", "3");
        session.setConfig(config);
        session.connect();

        System.out.println("SSH Connected");

        Class.forName(driverName).newInstance();

        int assinged_port = session.setPortForwardingL(localPort, remoteHost, remotePort);

        System.out.println("localhost:" + assinged_port + " -> " + remoteHost + ":" + remotePort);
        System.out.println("Port Forwarded");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

依赖版本 #

• POI 5.2.3
• Apache Maven 3.8.7
• MySQL Connector 8.0.32
• XMLBeans 5.1.1

• 检查查询结果是否为空：SSH连接后可能因权限、数据范围变化导致查询返回空结果集，部分Excel版本会因文件内容过少（只有表头或无内容）判定为无效文件。可在写入Excel前添加判断：

  if (!rs.isBeforeFirst()) {
      System.out.println("查询结果为空");
      XSSFRow emptyRow = sheet.createRow(1);
      emptyRow.createCell(0).setCellValue("无查询结果");
  }
  

• 调整资源关闭顺序：当前先关闭Excel资源再关闭数据库连接，建议调换顺序，先释放数据库资源，再处理Excel写入，避免连接占用导致写入不完整：

  // 先关闭数据库资源
  rs.close();
  stmt.close();
  conn.close();
  
  // 再写入并关闭Excel
  FileOutputStream fileOut = new FileOutputStream("D://testexpdata.xlsx");
  workbook.write(fileOut);
  fileOut.flush(); // 确保缓冲区内容全部写入
  fileOut.close();
  workbook.close();
  

• 验证SSH隧道有效性：用Navicat等工具测试localhost:3307是否能正常连接数据库，确认隧道转发端口工作正常，避免因隧道未建立导致查询异常。

• 捕获Excel写入异常：当前代码仅抛出顶层异常，添加try-catch包裹Excel写入逻辑，输出详细错误信息：

  try {
      FileOutputStream fileOut = new FileOutputStream("D://testexpdata.xlsx");
      workbook.write(fileOut);
      fileOut.flush();
      fileOut.close();
      workbook.close();
  } catch (IOException e) {
      e.printStackTrace();
      System.err.println("Excel写入异常：" + e.getMessage());
  }
  

• 排查依赖冲突：确认Maven依赖中无POI相关包冲突，比如是否同时引入了HSSF（.xls）和XSSF（.xlsx）依赖，导致文件格式混乱。

内容的提问来源于stack exchange，提问作者David S.