aspose file tools*
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 Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "how to write into excel sheet" Watch "how to write into excel sheet" New topic
Author

how to write into excel sheet

sachin naphade
Greenhorn

Joined: Mar 25, 2008
Posts: 8
Hi
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

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:exceltest" );
Statement st = con.createStatement();
ResultSet rs = st.executeQuery( "Select * from [Unreachable URLs$]" );
int statusCode=1;
rs.next();
while (rs.next()) {
String url = rs.getString(2);
String sql = "Insert into [Unreachable URLs$] (Status code)";
sql=sql +" values('" + statusCode +" ')";
st.executeUpdate(sql);
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
Marshal

Joined: Mar 22, 2005
Posts: 42286
    
  64
With SQL it's not a good idea to have spaces in attribute names, like "Status code". Try quoting it, like "('Status code')".


Ping & DNS - my free Android networking tools app
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14270
    
  21

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

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
SCJP,SCWCD,OCA
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to write into excel sheet