This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java and have Dr. Raoul-Gabriel Urma & Richard Warburton on-line!
See this thread for details.
Win a flower (🌹) or copy of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java (📚) this week in the Agile and Other Processes 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • salvin francis
  • Frits Walraven
  • Piet Souris

how to write into excel sheet

 
sachin naphade
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
pollinator
Posts: 43011
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 16084
88
Android Scala IntelliJ IDE Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This does not sound like it has anything to do with the SCJP exam. I'm moving this to a more appropriate forum.
 
Amit Suresh Pawar
pioneer
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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( "jdbcdbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C://Java_Excel//tip_of_the_day.xls; readOnly= false");

It will work.
 
What do you have to say for yourself? Hmmm? Anything? And you call yourself a tiny ad.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!