How to fill Microsoft Excel Sheet from JAVA via ODBC


2004-07-06 05:10:57 PM
jbuilder22
Hi
I tried to fill an Microsoft Excel mapped as an ODBC source with JAVA:
CREATE TABLE [Sheet1$] (APPLGRP char(1), DBNAME char(1), TSCNT
char(1), IXCNT char(1), ALOCGB char(1), USEDGB char(1), FREEGB
char(1), USEDPCT char(1), AUSEDPCT char(1), DSCNT char(1))
However I can only create one field. If I want to create more than one
field then the following error message appears:
java.sql.SQLException: [Microsoft][ODBC Excel Driver] Too many fields
defined.
So what I want is to fill in a title line (title for every column) and
then to add data records:
A B C D E F G
------- ------ ----- ----- ------ ------ ------
APPLGRP DBNAME TSCNT IXCNT ALOCGB USEDGB FREEGB ...
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7
The source:
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
void connect_actionPerformed(ActionEvent e) {
String url_excel = "jdbc:odbc:EXCEL";
String sqlcmd = "";
int i;
String elements = "";
Connection con_excel = null;
Statement stmt_excel = null;
String strSQL = null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con_excel = DriverManager.getConnection(url_excel,"","");
stmt_excel = con_excel.createStatement();
sqlcmd = "CREATE TABLE [Sheet1$] (APPLGRP char(1), DBNAME
char(1), TSCNT char(1), IXCNT char(1), ALOCGB char(1), USEDGB char(1),
FREEGB char(1), USEDPCT char(1), AUSEDPCT char(1), DSCNT char(1))";
System.out.println(sqlcmd);
i = stmt_excel.executeUpdate(sqlcmd);
stmt_excel.close();
}
catch (Exception e2)
{
System.out.println(e2.toString());
}
}
Can anyone help how this can be done and how to get rid of the error
message?
Thanks a lot,
regards
Werner Nussbaumer