java - 如何使用 Apache POI 重写 .xlsm 文件？

Question

我正在使用此代码使用 Apache POI 重写 xlsm 文件。我必须从结果集中重写 Sheet1 上的数据，此代码创建模板 xlsm 文件的副本并进行所有处理。

但是当我打开创建的 xlsm 文件时，它会显示以下消息:

We found a problem with some content in 'FileName.xlsm'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, Click Yes.

这是我的代码，请建议我应该做什么。

public void dbConnect(String driver_connect_string, String db_connect_string, String db_userid, String db_password){
  try{ 
    Class.forName(driver_connect_string);
    Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password);
    System.out.println("connected");
    Statement statement = conn.createStatement();
    Properties propq = new Properties();    
    FileInputStream fisq = new FileInputStream("query.properties");
    propq.load(fisq);
    String queryString = propq.getProperty("myQueryString");
    ResultSet rs = statement.executeQuery(queryString);

    Properties propf2 = new Properties();   
    FileInputStream fisf2 = new FileInputStream("file.properties");
    propf2.load(fisf2);

    OPCPackage pkg = OPCPackage.open(new File(propf2.getProperty("sourceFile")));
    XSSFWorkbook wb_template;
    wb_template = new XSSFWorkbook(pkg);
    System.out.println("package loaded");
    SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); 
    wb.setCompressTempFiles(true);

     Sheet  sheet = wb.getSheetAt(0); 
     Row rowhead = sheet.createRow((short) 0);
         rowhead.createCell((short) 0).setCellValue("EmpId");
         rowhead.createCell((short) 1).setCellValue("EmaName");
         rowhead.createCell((short) 2).setCellValue("Department");
         rowhead.createCell((short) 3).setCellValue("Job Title");
         rowhead.createCell((short) 4).setCellValue("DOB");
         int index = 1;
         while (rs.next()) {
              Row row = sheet.createRow((short) index);
              row.createCell((short) 0).setCellValue(rs.getString(1));
              row.createCell((short) 1).setCellValue(rs.getString(2));
              row.createCell((short) 2).setCellValue(rs.getString(3));
              row.createCell((short) 3).setCellValue(rs.getString(4));
              row.createCell((short) 4).setCellValue(rs.getString(5));
              index++;
          }


         FileOutputStream out = new FileOutputStream(new File(propf2.getProperty("destFile")));
         System.out.println("XLSM created Successfully");
         wb.write(out);
         out.close();  
    }catch(Exception e){
      e.printStackTrace();
  }
}

Answer 1

完成…………

public void dbConnect(String driver_connect_string, String db_connect_string, String db_userid, String db_password){
 try{ 
  Class.forName(driver_connect_string);
  Connection conn = DriverManager.getConnection(db_connect_string, db_userid, db_password);
  System.out.println("connected");
  Statement statement = conn.createStatement();
  Properties propq = new Properties();    
  FileInputStream fisq = new FileInputStream("query.properties");
  propq.load(fisq);
  String queryString = propq.getProperty("myQueryString");
  ResultSet rs = statement.executeQuery(queryString);

  Properties propf2 = new Properties();   
  FileInputStream fisf2 = new FileInputStream("file.properties");
  propf2.load(fisf2);

  OPCPackage pkg = OPCPackage.open(new File(propf2.getProperty("sourceFile")));
  XSSFWorkbook wb_template;
  wb_template = new XSSFWorkbook(pkg);
  System.out.println("package loaded");


  Sheet  sheet = wb_template.getSheetAt(0);

  Row rowhead = sheet.createRow(0);
     rowhead.createCell(0).setCellValue("EmpId");
     rowhead.createCell(1).setCellValue("EmaName");
     rowhead.createCell(2).setCellValue("Department");
     rowhead.createCell(3).setCellValue("Job Title");
     rowhead.createCell(4).setCellValue("DOB");
     int index = 1;
     while (rs.next()) {
          Row row = sheet.createRow((short) index);
          row.createCell(0).setCellValue(rs.getString(1));
          row.createCell(1).setCellValue(rs.getString(2));
          row.createCell(2).setCellValue(rs.getString(3));
          row.createCell(3).setCellValue(rs.getString(4));
          row.createCell(4).setCellValue(rs.getString(5));
          index++;
      }


     FileOutputStream out = new FileOutputStream(new File(propf2.getProperty("destFile")));
     System.out.println("XLSM created Successfully");
     wb.write(out);
     out.close();  
  }catch(Exception e){
    e.printStackTrace();
  }
}