Using JDBC to insert into MS Excel file

[Michael Zhou]

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);
testStmnt.executeUpdate();
******************************************************************************* ********

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

Thanks a lot!

Michael

[David Harkness]

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]

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!

Michael

[Avi Abrami]

Michael,
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:

http://tinyurl.com/4ssfq

Good Luck,
Avi.

[Hareesh Ram Chanchali]

hi

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 sourceforge.net

[Nitin Jawarkar]

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...

http://jakarta.apache.org/poi/hssf/quick-guide.html#NewSheet

Thanx

[Michael Zhou]

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.

Michael