CODEWITHSUNDEEP
javaexceljdbcodbc
evilReiko
evilReiko

Reputation: 20473

Java: cannot update Excel using JDBC ODBC

I can read rows/columns just fine, but I can't 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 fails when it reached rs.updateRow(); and displays this error:

java.sql.SQLException: [Microsoft][ODBC Excel Driver]Error in row

Note: Some people say it's because of READONLY is not set to false or 0, but I've done it already, and the Excel file is also not set to read-only

I followed the steps to apply Updating Rows in ResultSet Objects in here: http://download.oracle.com/javase/tutorial/jdbc/basics/retrieving.html

Upvotes: 4

Views: 5946

Answers (5)

Sreekanth Simhadri
Sreekanth Simhadri

Reputation: 1

I am able update excel file using JDBC, you can use below code, this code updates a file in D:/Test.xls and Updates Col1 with '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();

Upvotes: 0

Shafeer Hussain
Shafeer Hussain

Reputation: 11

It is possible to Update, Insert and Delete. Use, stmt.executeUpdate("query") instead of stmt.executeQuery("query").

Upvotes: 1

user467871
user467871

Reputation:

I suggest you to use 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 succesfully update the cell or you can change this code to your situation (update row).

HSSF is for Excel '97(-2007) file format and XSSF is for Excel 2007 OOXML (.xlsx) . (poi.apache.org/spreadsheet/index.html) I think there shouln't be any problem while updating

If you have any question please write to me

Upvotes: 2

John John
John John

Reputation: 4575

May you should to consider the Apache POI for excel integration.

Upvotes: 2

jzd
jzd

Reputation: 23629

A resultSet is not the typical means for updating through JDBC. (Normally insert, update statements are used.)

There is a paragraph in the tutorial link that you posted that explains the default result set is read only. It says:

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

Upvotes: 0

Related Questions