This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC 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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Using JDBC to insert into MS Excel file" Watch "Using JDBC to insert into MS Excel file" New topic
Author

Using JDBC to insert into MS Excel file

Michael Zhou
Greenhorn

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

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

Thanks a lot!

Michael
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
Greenhorn

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!

Michael
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

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
Ranch Hand

Joined: Jan 31, 2005
Posts: 110
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


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

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

Thanx


Cheers<br />Nitin
Michael Zhou
Greenhorn

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.

Michael
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Using JDBC to insert into MS Excel file
 
Similar Threads
Insert Data into .csv File thru JDBC Program
how to insert a file object in excel sheet using java
How to upload oracle data to excel
Need to develop a Quality checking software Urgent!!!
Help: Inserting non-english(Hebrew) characters in MS SQL SERVER 2005