Java: unable to update Excel using JDBC ODBC

Question

I can read rows / columns perfectly, but I cannot update, insert, or delete.

try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String myDB = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=myExcelFile.xls;" + "DriverID=22;READONLY=false"; con = DriverManager.getConnection(myDB, username, password); stmt = con.createStatement(); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery("SELECT * FROM [users$]"); while (rs.next()) { String str = rs.getString("username"); System.out.println(str); rs.updateString("username", str + "UPDATED"); rs.updateRow(); } rs.close(); stmt.close(); con.close(); }catch(Exception e){System.out.println(e);} 

This code crashes when it reaches rs.updateRow(); and displays this error:

java.sql.SQLException: [Microsoft] [Excel ODBC driver] Error in line

Note. Some people say this because READONLY is not set to false or 0, but I already did this, and the Excel file is also not set to read only

I have completed the following steps to apply the Update strings in ResultSet objects here: http://download.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

Answer 1

Maybe you should consider Apache POI for integration with excel.

Answer 2

I suggest you use the Apache POI http://poi.apache.org/ and some codes here: http://onjava.com/pub/a/onjava/2003/04/16/poi_excel.html

Here is the code:

 FileInputStream myInput = new FileInputStream(inputFile); XSSFWorkbook wb = new XSSFWorkbook(myInput); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(0); XSSFCell cell = row.getCell(1); cell.setCellValue(123); sheet.getRow(37).getCell(13). setCellValue("USD"); 

It successfully updates the cell, or you can change this code to your situation (update line).

HSSF is for the Excel '97 (-2007) file format, and XSSF is for Excel 2007 OOXML (.xlsx). (poi.apache.org/spreadsheet/index.html) I think there will be no problems updating when updating

If you have any questions, write me

Answer 3

You can update, insert and delete. Using stmt.executeUpdate("query") instead of stmt.executeQuery("query") .

Answer 4

The result set is not a typical upgrade tool through JDBC. (Usually, inserts, update statements are used.)

There is a link in the link below that explains that the default result set is read-only. It says:

The default value of ResultSet concurrency is CONCUR_READ_ONLY. Note. Not all JDBD drivers and databases support concurrency. The DatabaseMetaData.supportsResultSetConcurrency method returns true if the specified concurrency level is supported by the driver and false otherwise.

Answer 5

I can update the excel file using JDBC, you can use the code below, this code updates the file in D: /Test.xls and Updates Col1 using "Test", where Col2 is "Testing":

  java.sql.Statement stmt=null; PreparedStatement ps=null; Connection con=null; con = java.sql.DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=D:/Test.xls;ReadOnly=False;"); ps=con.prepareStatement("Update [Sheet1$] Set Col1='Test' Where Col2='Testing'); ps.executeUpdate();