File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
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: 42965
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: 15094

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!