Solved

Does Excel Have to be running to insert data into it from JAVA?

Posted on 2001-06-19
3
133 Views
Last Modified: 2010-03-31
I can load an Excel spreadsheet, but it only works if I have the Spreadsheet open in Excel.  I would like this to load data at night from an Database table.   Any ideas or links anyone can point me to?
0
Comment
Question by:RedBane
  • 2
3 Comments
 
LVL 1

Accepted Solution

by:
wgilster earned 50 total points
ID: 6207950
Use the ODBCJDBC driver. Here is an example

package classes;
import java.sql.*;

public class TestServer
{
  static
  {
      try  {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      }
      catch (Exception e) {
          System.err.println(e);
      }
  }

  public static void main(String args[]) {
      Connection conn=null;
      Statement stmt=null;
      String sql="";
      try {
          conn=DriverManager.getConnection("jdbc:odbc:excel","","");
          stmt=conn.createStatement();
          sql="INSERT INTO [Sheet1$] (FIELD_NAME1 FIELD_NAME2 FIELD_NAME3) VALUES (100,'String Value',200)";
          stmt.executeUpdate(sql);

      }
      catch (Exception e){
          System.err.println(e);
      }
      finally {
          try{
              stmt.close();
              conn.close();
              stmt=null;
              conn=null;
          }
          catch(Exception e){}
      }
  }
}
0
 
LVL 1

Expert Comment

by:wgilster
ID: 6210462
With my example above all you need to do is create is a DSN named "excel", and your ready to insert all the data you want.  If your importing data from a database just open the database the same way and import each resultset into Excel.
0
 

Author Comment

by:RedBane
ID: 6211595
Thanks for the help.

Its working now with the below statement.  I had to set up a data range in Excel.  I can not get it to work by putting in the column names in the first part of the Insert statement.

sqlProd = "INSERT INTO \"ProductWS$\" VALUES ("+ProdID+",\'Desc\',\'TransType\')";

Thanks
Rob
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now