Java and Excel
[201 byte] By [l_marceloa] at [2007-9-19] 
there's a couple of posts regarding java/jdbc/excel in these forums, just invoke a search.
aschorlea at 2007-7-8 >
Try this code:
import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.*;
/**
*
* Name: Excel.java
*
* Purpose: To demonstrate how to use ODBC and Excel to create
* a table, insert data into it, and select it back out.
*
* Version: Developed using JDK 1.3, but also works with JDK
* 1.2.2
*
* Instructions:
*
* 1) Create a new Excel spreadsheet
*
* 2) Create a new ODBC data source that points to this
* spreadsheet
*
* a) Go to Control Panel
* b) Open "ODBC Data sources (32-bit) (wording may be
* slightly different for different platforms)
* c) Under "User DSN" tab, press "Add" button
* d) Select the "Microsoft Excel Driver (*.xls)" and
* press "Finish" button
* e) Enter "Data Source Name" of "TestExcel"
* f) Press "Select Workbook" button
* g) Locate and select the spreadsheet you created in
* Step 1
* h) Unselect the "Read Only" checkbox
* i) Press "Ok" button
*
* 3) Compile and run Excel.java
*
* 4) Open Excel spreadsheet and you will find a newly
* created sheet, GOOD_DAY, with three rows of data.
*
* Notes:
* If you want to select data from a spreadsheet that was
* NOT created via JDBC-ODBC (i.e. you entered data manually
* into a spreadsheet and want to select it out), you must
* reference the sheet name as "[sheetname$]".
*
* When you create the table and insert the data using
* Java, you must reference the sheet name as "sheetname".
*
* Also, do not have the spreadsheet open when you are
* running the program. You can get locking conflicts.
*
*
*/
public class Excel{
public Excel(){
setDefaults();
}
private static void message(String pMessage){
System.out.println(pMessage);
}
private void setDefaults(){
setDriver("sun.jdbc.odbc.JdbcOdbcDriver");
setUrl("jdbc:odbc");
// ODBC data source named "TestExcel" defined from Control Panel
setDataSource("TestExcel");
setTableName("GREAT_CARS");
}
public void openDatabase(){
String file = "E:/projects/jbproject/edelapreport/classes/Excel.xls";
//use this without setting an ODBC Data Source
String lConnectStr = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + file + ";DriverID=22;READONLY=false";
//use this in the case of an existing DSN
//String lConnectStr = getUrl()+":"+getDataSource();
try {
Class.forName(getDriver());
gConnection = DriverManager.getConnection(lConnectStr);
}catch (Exception e) {
message("Error connecting to DB: " + e.getMessage());
}
}
private void closeDatabase(){
try{
getConnection().close();
}catch (Exception e){
message("closeDatabase(): "+e.getMessage());
}
}
private void createTable(){
message("createTable() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.execute("CREATE TABLE "+getTableName()+" ("
+" ID INTEGER" +" ,NAME VARCHAR" +")");
}catch (Exception e){
message("createTable(): "+e.getMessage());
}
message("createTable() end");
}
private void doInsert(){
message("doInsert() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (10,'Audi')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (20,'Mercedes')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (30,'BMW')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (40,'Audi A6')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (50,'Audi S6')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (60,'Audi 80 Quattro')");
lStat.close();
}catch(Exception e){
message("doInsert(): "+e.getMessage());
}
message("doInsert() end");
}
private void dropTable(){
message("dropTable() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.execute("DROP TABLE " + getTableName());
lStat.close();
}catch(Exception e){
message("dropTable(): "+e.getMessage());
}
message("dropTable() end");
}
private void doQuery(){
message("doQuery() begin");
try {
Statement lStat = getConnection().createStatement();
ResultSet lRes = lStat.executeQuery("SELECT * FROM "+getTableName());
ResultSetMetaData lMeta = lRes.getMetaData();
// print out the column headers separated by commas
for (int i = 1; i <= lMeta.getColumnCount(); ++i){
if (i > 1){
System.out.print(", ");
}
String lValue = lMeta.getColumnName(i);
System.out.print(lValue);
}
System.out.println("");
// print out the data separated by commas
while (lRes.next()){
for (int i=1; i<=lMeta.getColumnCount(); ++i){
if (i > 1){
System.out.print(", ");
}
String lValue = lRes.getString(i);
System.out.print(lValue);
}
System.out.println("");
}
lRes.close();
lStat.close();
}catch (Exception e){
message("doQuery(): "+e.getMessage());
}
message("doQuery() end");
}
private void run(){
openDatabase();
createTable();
doInsert();
doQuery();
//dropTable();
closeDatabase();
}
public static void main(String args[]){
message("main() begin");
Excel lExcel = new Excel();
lExcel.run();
message("main() end");
System.exit(0);
}
public void setTableName (String pValue){
gTableName = pValue;
}
public String getTableName(){
return(gTableName);
}
public void setSql(String pValue){
gSql = pValue;
}
public String getSql(){
return(gSql);
}
public Connection getConnection(){
return(gConnection);
}
public String getDataSource(){
return(gDataSource);
}
public void setDataSource(String pValue){
gDataSource = pValue;
}
public void setDriver(String pValue){
gDriver = pValue;
}
public void setUrl(String pValue){
gUrl = pValue;
}
public String getDriver (){
return (gDriver);
}
public String getUrl (){
return (gUrl);
}
private Connection gConnection = null;
private String gDataSource = null;
private String gTableName = null;
private String gSql = null;
private String gDriver = null;
private String gUrl = null;
}
/**
* Connection to Access without existing DNS
* Connection con = null;
* Statement st = null;
* String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
* String path = "c:/jdk1.3/jswdk-1.0.1/examples/jsp/list/base/bd.mdb";
* String fullConnectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path;
* Class.forName (driver);
* con = DriverManager.getConnection (fullConnectionString);
* st = con.createStatement ();
*/
//Example for SQLServer:
//db = DriverManager.getConnection("jdbc:odbc:Driver={SQL Server};Server=MyServerName;Database=MyDataBase","","");
//Example for Excel:
//db = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/temp/test2.xls;DriverID=22;READONLY=false","","");
jquattroa at 2007-7-8 >
Depending on how fancy you want to get, you can also look at http://jakarta.apache.org/poi . Here is what that open source project has to say about itself:
You'd use POIFS if you had a document written in OLE 2 Compound Document Format, probably written using MFC, that you needed to read in Java. Alternatively, you'd use POI to write OLE 2 Compound Document Format if you needed to inter-operate with software running on the Windows platform. We are not just bragging when we say that POIFS is the most complete and correct port of this file format to date!
You'd use HSSF if you needed to read or write an XLS (Excel) file using Java. You can also read and modify spreadsheets using this API, although right now writing is more mature.
Hope that helps,
Markus
medale94a at 2007-7-8 >
Also you can take a look at jXLS http://jxls.sourceforge.net
leonatea at 2007-7-8 >
Dear Friend,plz tell me the flow of ur code. I feel very hard to understand . plz help me ...........................
Pandiana at 2007-7-8 >
Dear Friends ..................
Some times i will get this error " [ Micro Soft ] [ ODBC Excel Driver ] the Micro Soft jet Data Base engine could not find the object .
Make Sure this object is existand that u spell it correctly................. "
while connecting to excel . see my code ...............
import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.*;
/**
*
* Name: Excel.java
*
* Purpose: To demonstrate how to use ODBC and Excel to create
* a table, insert data into it, and select it back out.
*
* Version: Developed using JDK 1.3, but also works with JDK
* 1.2.2
*
* Instructions:
*
* 1) Create a new Excel spreadsheet
*
* 2) Create a new ODBC data source that points to this
* spreadsheet
*
* a) Go to Control Panel
* b) Open "ODBC Data sources (32-bit) (wording may be
* slightly different for different platforms)
* c) Under "User DSN" tab, press "Add" button
* d) Select the "Microsoft Excel Driver (*.xls)" and
* press "Finish" button
* e) Enter "Data Source Name" of "TestExcel"
* f) Press "Select Workbook" button
* g) Locate and select the spreadsheet you created in
* Step 1
* h) Unselect the "Read Only" checkbox
* i) Press "Ok" button
*
* 3) Compile and run Excel.java
*
* 4) Open Excel spreadsheet and you will find a newly
* created sheet, GOOD_DAY, with three rows of data.
*
* Notes:
* If you want to select data from a spreadsheet that was
* NOT created via JDBC-ODBC (i.e. you entered data manually
* into a spreadsheet and want to select it out), you must
* reference the sheet name as "[sheetname$]".
*
* When you create the table and insert the data using
* Java, you must reference the sheet name as "sheetname".
*
* Also, do not have the spreadsheet open when you are
* running the program. You can get locking conflicts.
*
*
*/
public class Excel{
public Excel(){
setDefaults();
}
private static void message(String pMessage){
System.out.println(pMessage);
}
private void setDefaults(){
setDriver("sun.jdbc.odbc.JdbcOdbcDriver");
setUrl("jdbc:odbc");
// ODBC data source named "TestExcel" defined from Control Panel
setDataSource("TestExcel");
setTableName("GREAT_CARS");
}
public void openDatabase(){
String file = "E:/projects/jbproject/edelapreport/classes/Excel.xls";
//use this without setting an ODBC Data Source
String lConnectStr = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + file + ";DriverID=22;READONLY=false";
//use this in the case of an existing DSN
//String lConnectStr = getUrl()+":"+getDataSource();
try {
Class.forName(getDriver());
gConnection = DriverManager.getConnection(lConnectStr);
}catch (Exception e) {
message("Error connecting to DB: " + e.getMessage());
}
}
private void closeDatabase(){
try{
getConnection().close();
}catch (Exception e){
message("closeDatabase(): "+e.getMessage());
}
}
private void createTable(){
message("createTable() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.execute("CREATE TABLE "+getTableName()+" ("
+" ID INTEGER" +" ,NAME VARCHAR" +")");
}catch (Exception e){
message("createTable(): "+e.getMessage());
}
message("createTable() end");
}
private void doInsert(){
message("doInsert() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (10,'Audi')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (20,'Mercedes')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (30,'BMW')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (40,'Audi A6')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (50,'Audi S6')");
lStat.executeUpdate("INSERT INTO " +getTableName()+" VALUES (60,'Audi 80 Quattro')");
lStat.close();
}catch(Exception e){
message("doInsert(): "+e.getMessage());
}
message("doInsert() end");
}
private void dropTable(){
message("dropTable() begin");
Statement lStat = null;
try {
lStat = getConnection().createStatement();
lStat.execute("DROP TABLE " + getTableName());
lStat.close();
}catch(Exception e){
message("dropTable(): "+e.getMessage());
}
message("dropTable() end");
}
private void doQuery(){
message("doQuery() begin");
try {
Statement lStat = getConnection().createStatement();
ResultSet lRes = lStat.executeQuery("SELECT * FROM "+getTableName());
ResultSetMetaData lMeta = lRes.getMetaData();
// print out the column headers separated by commas
for (int i = 1; i <= lMeta.getColumnCount(); ++i){
if (i > 1){
System.out.print(", ");
}
String lValue = lMeta.getColumnName(i);
System.out.print(lValue);
}
System.out.println("");
// print out the data separated by commas
while (lRes.next()){
for (int i=1; i<=lMeta.getColumnCount(); ++i){
if (i > 1){
System.out.print(", ");
}
String lValue = lRes.getString(i);
System.out.print(lValue);
}
System.out.println("");
}
lRes.close();
lStat.close();
}catch (Exception e){
message("doQuery(): "+e.getMessage());
}
message("doQuery() end");
}
private void run(){
openDatabase();
createTable();
doInsert();
doQuery();
//dropTable();
closeDatabase();
}
public static void main(String args[]){
message("main() begin");
Excel lExcel = new Excel();
lExcel.run();
message("main() end");
System.exit(0);
}
public void setTableName (String pValue){
gTableName = pValue;
}
public String getTableName(){
return(gTableName);
}
public void setSql(String pValue){
gSql = pValue;
}
public String getSql(){
return(gSql);
}
public Connection getConnection(){
return(gConnection);
}
public String getDataSource(){
return(gDataSource);
}
public void setDataSource(String pValue){
gDataSource = pValue;
}
public void setDriver(String pValue){
gDriver = pValue;
}
public void setUrl(String pValue){
gUrl = pValue;
}
public String getDriver (){
return (gDriver);
}
public String getUrl (){
return (gUrl);
}
private Connection gConnection = null;
private String gDataSource = null;
private String gTableName = null;
private String gSql = null;
private String gDriver = null;
private String gUrl = null;
}
/**
* Connection to Access without existing DNS
* Connection con = null;
* Statement st = null;
* String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
* String path = "c:/jdk1.3/jswdk-1.0.1/examples/jsp/list/base/bd.mdb";
* String fullConnectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=" + path;
* Class.forName (driver);
* con = DriverManager.getConnection (fullConnectionString);
* st = con.createStatement ();
*/
//Example for SQLServer:
//db = DriverManager.getConnection("jdbc:odbc:Driver={SQL Server};Server=MyServerName;Database=MyDataBase","","");
//Example for Excel:
//db = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=c:/temp/test2.xls;DriverID=22;READONLY=false","","");
Pandiana at 2007-7-8 >
Core Hot Topic
- Load class inside customized classloader
- RFC: Proposing a high level iteration facility based on Collections
- command line interface?
- Save objects to disk or save Vector of objects ?
- java.lang.VerifyError in code generated by Prototype 1.2
- Problem Casting Arrays.
- the code dosent work.........
- Computer Hardware Identification
Core
Site Classified
- Archived Forums
- Enterprise & Remote Computing
- Java Essentials
- Desktop
- Core
- Solaris Operating System
- Security
- Database Connectivity
- Java Mobility Forums
- Web & Directory Servers
- Development Tools
- Application & Integration Servers
- Java HotSpot Virtual Machine
- Other Topics
- E-Mail, Calendar, & Collaboration
- Developer Tools
- General
- Administration Tools
- Sun Hardware
- Storage Forums
- Java Enterprise System
- StarOffice
- Open Source Technologies
- BigAdmin
- Real-Time
- Java Coding