Two Laptop Bag
The moose likes Java in General and the fly likes how to write into excel sheet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Make it so: Java DB Connections & Transactions this week in the JDBC forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "how to write into excel sheet" Watch "how to write into excel sheet" New topic

how to write into excel sheet

sachin naphade

Joined: Mar 25, 2008
Posts: 8
I am reading excel file as a database by configuring it as the Db using Administrative tool,i made all the configuration there, I am able to read it without any problem but I want to write some data to excel sheet for that i used one update statement but when i compile the code it gives me syntax error in the update statement. The code llok like this

Connection con = DriverManager.getConnection("jdbc dbc:exceltest" );
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( "Select * from [Unreachable URLs$]" );
int statusCode=1;;
while ( {
String url = rs.getString(2);
String sql = "Insert into [Unreachable URLs$] (Status code)";
sql=sql +" values('" + statusCode +" ')";

where Unreachable URLs is the name of the spreadsheet

this spreadsheet has coloumn names as SrNo, URL,Description,Status code.
i want to insert data into Status code coloumn which has URL same as url which is read here.and the exception that I got is
Exception: [Microsoft][ODBC Excel Driver] Syntax error in UPDATE statement.

I also tried with
String sql = "Insert into [Unreachable URLs$] (Status code)where URL=url";
but still i am getting same exception.
Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42959
With SQL it's not a good idea to have spaces in attribute names, like "Status code". Try quoting it, like "('Status code')".
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 15046

This does not sound like it has anything to do with the SCJP exam. I'm moving this to a more appropriate forum.

Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Amit Suresh Pawar
Ranch Hand

Joined: Dec 09, 2008
Posts: 30

This exception is because of opening that file in read only mode.
So, do one of the below:

1. Start-> Settings->Control Panel->Administrative Tools->Data Sources (ODBC) Then select your driver & click on configure, Then click on Options & uncheck the Read-Only option
2. connection = DriverManager.getConnection( "jdbcdbcriver={Microsoft Excel Driver (*.xls)};DBQ=C://Java_Excel//tip_of_the_day.xls; readOnly= false");

It will work.

Amit S. Pawar
I agree. Here's the link:
subject: how to write into excel sheet
It's not a secret anymore!