How to add a gradle ODBC driver to connect with xlsb files

35 Views Asked by At

I tried connecting with an xlsb file as a Database to support a legacy process. (Since the Apache POI for xlsb files is not supported well) Below is my code:

        try{
        Connection xlsbConnection=DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ="+filePath+";");
        Statement xlsbStatement=xlsbConnection.createStatement();
        ResultSet xlsbResultSet=xlsbStatement.executeQuery("Select * from ["+sheetName+"$]");
        while (xlsbResultSet.next()) {
            ResultSetMetaData xlsbRsmd = xlsbResultSet.getMetaData();
            int numberOfColumns = xlsbRsmd.getColumnCount();

            for (int i = 1; i <= numberOfColumns; i++) {
                if (i > 1)
                    System.out.print(", ");
                String columnValue = xlsbResultSet.getString(i);
                System.out.print(columnValue);
            }
            System.out.println("");
        }

        xlsbConnection.close();
    }catch(Exception e){
        System.out.print(e.toString());
    }

After running the above code, I am getting an exception that

java.sql.SQLException: No suitable driver found for jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\Remaining file path;

Please let me know where I should install the driver to make the odbc. I can see the 64 bit driver in the User DSN my ODBC Driver manager (64 bit).

enter image description here

0

There are 0 best solutions below