Fill Microsoft Excel Sheet from JAVA via ODBC

[Werner Nussbaumer]

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 use 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 = "jdbcdbc: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

 

[Stefan Poehn]

Hi

I tried to fill an Microsoft Excel mapped as an ODBC source with JAVA:

[...]
Can anyone help how this can be done and how to get rid of the error
message?

It is much easier using the HSSF package to read and write Xl-Files. We have
used it in one of our projects and found out that it is fast and does its
job properly.

http://jakarta.apache.org/poi/hssf/index.html