Solved

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

Posted on 2001-06-19
3
135 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

932 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

7 Experts available now in Live!

Get 1:1 Help Now