0

I am trying to write my ResultSet into an excel workbook. to do that I am using poi-ooxml-3.17 lib. But I am unable to create Object of XSSFWorkbook. My control not going after below line. also I am not getting any exception

XSSFWorkbook workbook = new XSSFWorkbook();

any workaround for this issue. below are the jar I am using in my application.

enter image description here

Below is my code:

package javaapplication1;

import com.mysql.jdbc.Statement;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.swing.JOptionPane;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JavaApplication1 {

    public static void main(String[] args){

        try{

        Class.forName("com.mysql.jdbc.Driver");
      Connection connect = DriverManager.getConnection( 
         "jdbc:mysql://localhost:3306/database" , 
         "root" , 
         "pass"
      );

      Statement statement = (Statement) connect.createStatement();
      ResultSet resultSet = statement.executeQuery("SELECT col1,col2, col3 FROM table");

      System.out.println("Before");

      XSSFWorkbook workbook = new XSSFWorkbook(); 
         System.out.println("After");
      XSSFSheet spreadsheet = workbook.createSheet("table_data");

      XSSFRow row = spreadsheet.createRow(1);
      XSSFCell cell;
      cell = row.createCell(1);
      cell.setCellValue("COL1");
      cell = row.createCell(2);
      cell.setCellValue("COL2");
      cell = row.createCell(3);
      cell.setCellValue("COL3");

      int i = 2;

      while(resultSet.next()) {
         row = spreadsheet.createRow(i);
         cell = row.createCell(1);
         cell.setCellValue(resultSet.getString("col1"));
         cell = row.createCell(2);
         cell.setCellValue(resultSet.getString("col2"));
         cell = row.createCell(3);
         cell.setCellValue(resultSet.getString("col3"));

         i++;
      }

      FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("exceldatabase.xlsx written successfully");
   }
        catch(Exception ex)
        {
            JOptionPane.showMessageDialog(null, ex);
        }
    }

}

Thank You..

ansh
  • 573
  • 3
  • 9
  • 26
  • 1
    can you fix your classpath not mix poi versions - having 2 different poi-ooxml jars with different version numbers is a bad idea? -- could you also use the latest release - POI 4.0.0 – PJ Fanning Sep 26 '18 at 20:11
  • Hi Fanning, I have tried with latest version as well Still facing same issue. – ansh Sep 27 '18 at 06:26
  • Hi Fanning, I have added my code as well also updated my JARs. – ansh Sep 27 '18 at 06:32
  • 1
    If the call is stuck on the constructor call. you can go and debug inside the constructor XSSFWorkbook(). It does operations like assigning of packages, creation of workbook etc. – teeman12 Sep 27 '18 at 07:03
  • 1
    [Don't mix POI jars from different versions](https://poi.apache.org/help/faq.html#faq-N10204). When `poi` version 4.0.0 is used, then also `poi-ooxml` of that version **must** be used. And do also updating the further dependencies as descrided in [Component Map](https://poi.apache.org/components/#components). – Axel Richter Sep 28 '18 at 05:41

0 Answers0