2

I am using this code to rewrite xlsm files using Apache POI. I have to rewrite data on Sheet1 from resultset, this code creates copy of template xlsm file and does all the processing.

But when I open the created xlsm file it shows me this message:

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.

Here is my code, please suggest what I should do.

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();
  }
}
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Rikhi Sahu
  • 655
  • 1
  • 7
  • 19

1 Answers1

1

Done....... 

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();
  }
}
Rikhi Sahu
  • 655
  • 1
  • 7
  • 19