Connecting to Excel using JDBC 8

2019-07-25 10:34发布

问题:

I've written a code that will be querying data from Excel using odbc bridge. Now since in java8 there is no more support to odbc, I'm searching an alternative of the same. I've nearly 32 different programs of suce. i.e. querying from Excel Database. Below is a sample program.

private void getTheDetailedDataForRca(String userName, XSSFWorkbook workbook, XSSFSheet sheet) {
        // System.out.println("ph2");
        try {
            DecimalFormat df2 = new DecimalFormat("#.##");
            HashMap<String, HashMap<String, Double>> myArray;
            myArray = new HashMap<String, HashMap<String, Double>>();
            String user = "%" + userName + "%";

            String dburl = path.getDBUrl();

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            myConn = DriverManager.getConnection(dburl);
            String queryString = "select Error As Err, Sum(TotalErrors) as Errors from [Quality Sheet$] where Associate like ? group By Error";
            PreparedStatement ps = myConn.prepareStatement(queryString);
            ps.setString(1, user);
            ResultSet rs = ps.executeQuery();
            myArray.put(userName, new HashMap<String, Double>());

            while (rs.next()) {
                double inputValue = rs.getDouble("Errors");
                String fin = df2.format(inputValue);
                myArray.get(userName).put(rs.getString("Err"), Double.parseDouble(fin));
            }
            ps.close();
            myConn.close();
            printMapOne(myArray, userName, workbook, sheet);
        } catch (Exception e) {
            System.out.println(e + "\t" + "in finals sheet B2");
        }
    }

And my dburl is

public String getDBUrl() {
    return "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\\Users\\" + getSystemId
            + "\\Desktop\\Quality Sheets\\quality_template.xlsx;";
}

I checked it online and to my surprise I found nothing apart from suggesting to use POI. I tried with UCanAccess, but this supports only Access database. Can someone please let me know any alternate for odbc, so that I need not code the entire thing.

I've tried the solution given at Removal of JDBC ODBC bridge in java 8

I got the message as below in cmd.

C:\Users\u0138039\Desktop\sun>jar -cvf jdbc.jar sun
sun : no such file or directory
added manifest

But a jar is created in folder.

I pasted the same jdbc driver in C:\Program Files (x86)\Java\jdk1.8.0_101\jre\lib and copied jdbcodbc.dll to C:\Program Files (x86)\Java\jre1.8.0_101\bin, restarted and to my surprise I get the same java.sql.SQLException: No suitable driver found for jdbc:odbc Exception.

Thanks

回答1:

progress.com/jdbc/sequelink here is one more if someone is still looking for a trusted jdbc odbc bridge. https://www.progress.com/blogs/jdbc-odbc-bridge-replacement-yields-performance-boost this blog talks about how their bridge can yield a 310% performance boost.



标签: java excel jdbc