Win a copy of Beginning Java 17 Fundamentals: Object-Oriented Programming in Java 17 this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

Using JDBC to insert into MS Excel file

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic