aspose file tools
The moose likes JDBC and Relational Databases and the fly likes Using JDBC to insert into MS Excel file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Using JDBC to insert into MS Excel file" Watch "Using JDBC to insert into MS Excel file" New topic

Using JDBC to insert into MS Excel file

Michael Zhou

Joined: Jan 27, 2005
Posts: 12
Hi all experts,
I am developing a program which uses the JDBC to insert records into MS *.xls files. It worked fine for reading. But when inserting: it shows the exception:

java.sql.SQLException: [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

I was so confused, because I already used PreparedStatement.executeUpdate() method, the following is my code:
******************************************************************************* ********

String insertStmnt = "INSERT INTO [test1$] values(' ', " + "'" + result +"','')";

PreparedStatement testStmnt = testConn.prepareStatement(insertStmnt);
******************************************************************************* ********

Any one has some suggestion?
I am so worried! Coz the project is due soon!

Thanks a lot!

David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
I haven't used JDBC to access Excel files, but the only thing that I can see that would certainly cause a problem is if the result String contained a single-quote itself, breaking your quoting. For example, "This doesn't work" would cause confusion since the ' in doesn't would terminate the quoting for result.

Otherwise, my only recommendation is to verify that your syntax is correct. What does [test1$] represent: a worksheet, cell, range, name? If it's a cell, can you insert three values into a single cell? Again, I know Excel pretty well (though it's been a while since I've done serious macros) and JDBC, but I've never put them together.

It's probably a bit late, but there are also a couple Open Source APIs for reading and writing Excel workbooks directly. One is an Apache project (POI I think), and another was just mentioned today, Java Excel API.
Michael Zhou

Joined: Jan 27, 2005
Posts: 12
Hi David,
Thanks a lot for the quick reply!!!
The [tablename$] is the special syntax for representing an Excel work sheet ( a table otherwise in JDBC), I was trying to be very careful when dealing with the double or single quotes stuff. It seems that the problem is something about that I have to use update JDBC statement instead of mere query statement. However, that is my biggest confusion, becuase I already used the PreparedStatement.executeUpdate() method.
Anyway, if it still does not work out, I will check out the Java Excel API link you gave me!
Thanks again for your help!

Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

In case you are unaware, there are several JavaWorld articles dealing with Excel and JDBC. Perhaps one (or more) of them will be of help?

Here are the results of a search I did at the JavaWorld Web site:

Good Luck,
Hareesh Ram Chanchali
Ranch Hand

Joined: Jan 31, 2005
Posts: 110

have u worked about getting the data from excel sheet

if so how can we know the date format of the data in the excel sheet

I am using JXL package from

Hareesh Ram Chanchali
SCJP 5.0, IBM Certified Solution Designer
Nitin Jawarkar
Ranch Hand

Joined: Dec 18, 2004
Posts: 79
Hello Michael

I have solved recently same problem of excel file and jdbc but as you asked to insert data in excel file, then i some confused becouse i have create fresh excel file using java code and for this i used apache's POI package, if you are using the same then this package will help you to insert data into existing file as well as create fresh excelfile. You will get more detail of this package here...


Cheers<br />Nitin
Michael Zhou

Joined: Jan 27, 2005
Posts: 12
Hi guys,
Thanks a lot for all of your replies!
I finally just gave up rolling around with JDBC to deal with MS Excel. I shifted to apache POI HSSF packages, it turned out to be satisfactory. However, when I was trying to write Japanese Characters to the *.xls file, the result was all chaotic characters. In addition, it's not that the .xls file does not support Japanese, I can well paste and write Japanese characters into it. It seems the HSSF package is not so well writing characters other than standard ASCIIs.
So what I fianlly did was to use the HSSF to write to .cvs files, which are *.xls format compatible.

rocket mass heater kickstarter
subject: Using JDBC to insert into MS Excel file