Java:无法使用 JDBC ODBC 更新 Excel
我可以很好地读取行/列,但无法更新、插入或删除。
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);}
此代码在到达 rs.updateRow();
时失败并显示以下错误:
java.sql.SQLException: [Microsoft][ODBC Excel 驱动程序]错误 行
注意:有人说这是因为 READONLY 未设置为 false 或 0,但我已经完成了,并且 Excel 文件也未设置为只读
我按照步骤应用更新行ResultSet 对象位于此处:http://download.oracle。 com/javase/tutorial/jdbc/basics/retrieve.html
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
您可能应该考虑使用 Apache POI 进行 Excel 集成。
May you should to consider the Apache POI for excel integration.
我建议您使用 Apache POI http://poi.apache.org/ 和这里的一些代码:http://onjava.com/pub/a/onjava/2003/04 /16/poi_excel.html
这是代码:
它成功更新单元格,或者您可以根据您的情况更改此代码(更新行)。
HSSF 适用于 Excel '97(-2007) 文件格式,XSSF 适用于 Excel 2007 OOXML (.xlsx) 。 (poi.apache.org/spreadsheet/index.html)我认为更新时应该不会有任何问题
如果您有任何问题请写信给我
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:
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
可以更新、插入和删除。使用,
stmt.executeUpdate("query")
而不是stmt.executeQuery("query")
。It is possible to Update, Insert and Delete. Use,
stmt.executeUpdate("query")
instead ofstmt.executeQuery("query")
.resultSet 不是通过 JDBC 进行更新的典型方法。 (通常使用插入、更新语句。)
您发布的教程链接中有一段解释默认结果集是只读的。它说:
默认的 ResultSet 并发性是 CONCUR_READ_ONLY。
注意:并非所有 JDBCD 驱动程序和数据库都支持并发。如果驱动程序支持指定的并发级别,则 DatabaseMetaData.supportsResultSetConcurrency 方法返回 true,否则返回 false。
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.
我可以使用 JDBC 更新 excel 文件,您可以使用下面的代码,此代码更新 D:/Test.xls 中的文件并使用“Test”更新 Col1,其中 Col2 是“Testing”:
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' :