Reading XLS file

[Anonymous]

Hi,
How do I open a xls file, read its contents and print it on the screen(or any file for that matter).
Code snippets are most welcome !!

cheers
Lakshmi.

[Paul Smiley]

Do you mean a Microsoft Excel spreadsheet .xls? If so, it is a binary file that can only be interpreted by Excel or another program that understands the format. If not Excel, please explain what kind of file you mean!

[Frank Carver]

I have seen a site with some Java code to read Excel spreadsheets, but I can't seem to find it at the moment. Keep looking on the net. If I find it again I'll mention it here.

[Anonymous]

Hi Paul,
Yes it is an Excel spread sheet. Let me be a bit more clear in framing the query. I have to read the data from the Excel sheet and place the data in DataBase.
Thanks in advance
-lakshmi


[This message has been edited by Lakshmi (edited June 16, 2000).]

[Paul Smiley]

Any file can be read if you know how to interpret the data on it. Frank says he's seen a Java reader somewhere on the web for Excel - I would check Microsoft's developer site first, then search the web. Let us know if you find out how to interpret the file, and we can step you through reading it and writing to the database.
Another option is to open the spreadsheet in Excel and save it as a comma (or whatever) delimited file. Then you can write a small parser to step through the delimited file and extract your data there.

[Serge Plourde]

Another solution may be to use an OLE object, if you can do
so with Java. Can we?
You must know Excel commands though, in order to go through
the Excel workbook/worksheets.
Here is some code extract of a program that I did in another
language (Visual dBase):
Function NewExcelInstance
local oExcel
oExcel = null
try
oExcel := new oleAutoclient("excel.application")
catch ( Exception e )
msgbox( 'Can not start Excel OLE automation!')
endtry
return oExcel
...

Once you get a hold on the Excel OLE object, you can do whatever
with it, provided you know Excel "vb macro" language.
Good luck.

[Anonymous]

Hi,
I got the solution for the query which i posted. With the following code we can read a XLS file. Considering the File as the Database with a single Table and the table name is the Name of the File. And the contents in the First row as the columns names, we read the file as if we are querying a Table.
1) First step before executing the code is to create a DSN for the XLS file.. In the Eg below the DSN I have created is "XLDSN".
2) TEST is the name of the XLS File. and Mind u the "$" sysmbol is mandatory after the file name in the select statement.
3) rs.getObject("subject").. In this statement subject is one of the columns in the first row of the XLS file.
Cheers,
Lakshmi.
/**************************************/
import java.sql.*;
import java.lang.*;
public class ReadXLS
{
public static void main(String args[])
{
Statement stmt=null;
Connection conn=null;
String sql="";
ResultSet rs=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc : odbc : XLDSN","","");
stmt=conn.createStatement();
sql="select * from [TEST$]";
rs=stmt.executeQuery(sql);
while(rs.next())
{
System.out.println("The Subject name : " +
rs.getObject("subject"));
}
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch (Exception e)
{
System.err.println(e);
}
}
[This message has been edited by Lakshmi (edited July 18, 2000).]

[Sahir Shah]

Thanks Lakshmi,
I was looking for something similar. This would help me a great deal.

[mohammed hguig]

Hi,
i read your answer to the problem of reading an excel file with java, but i don't know how to create a DSN for XLS files ???
when i execute your code, i have sqlexception ???
how can i do this?
thank's.

Originally posted by Lakshmi:
Hi,
I got the solution for the query which i posted. With the following code we can read a XLS file. Considering the File as the Database with a single Table and the table name is the Name of the File. And the contents in the First row as the columns names, we read the file as if we are querying a Table.
1) First step before executing the code is to create a DSN for the XLS file.. In the Eg below the DSN I have created is "XLDSN".
2) TEST is the name of the XLS File. and Mind u the "$" sysmbol is mandatory after the file name in the select statement.
3) rs.getObject("subject").. In this statement subject is one of the columns in the first row of the XLS file.
Cheers,
Lakshmi.
/**************************************/
import java.sql.*;
import java.lang.*;
public class ReadXLS
{
public static void main(String args[])
{
Statement stmt=null;
Connection conn=null;
String sql="";
ResultSet rs=null;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc : odbc : XLDSN","","");
stmt=conn.createStatement();
sql="select * from [TEST$]";
rs=stmt.executeQuery(sql);
while(rs.next())
{
System.out.println("The Subject name : " +
rs.getObject("subject"));
}
rs.close();
stmt.close();
conn.close();
rs=null;
stmt=null;
conn=null;
}
catch (Exception e)
{
System.err.println(e);
}
}
[This message has been edited by Lakshmi (edited July 18, 2000).]

[Aleksey Matiychenko]

Check out formula one for java. http://www.tidestone.com.
It is a tool that allows opening and manipulating excel files.

[Prasenjit Chakraborty]

Lakshmi..
I went through the code for reading a excel file through java..
Can you just let me know if it works out equally well on unix server. his is so becuse right now.. we are working on NT server but the final production server is Unix. Nt is only the development environment.

[Peter den Haan]

IBM AlphaWorks has an ExcelAccessor bean suite.
- Peter

[laxmikant shimpi]

hi all,
can somebody really tell me how to create Excel DSN programmtically without creating it in the Control Panel

thanks in Advance,
Laxmikant

[Peter den Haan]

You can't. Not without JNI.
In addition to (perhaps even in preference to) IBM's ExcelAccessor, look at Apache Jakarta POI. Instead of a clunky COM bridge it uses native Java code.
- Peter

[Sean Sullivan]

Use Jakarta POI
http://jakarta.apache.org/poi/

[Jamie Robertson]

This code works for me. The only limitation is that you need to be using Windows, and have the excel driver ( not an issue if your computer comes installed with it ).
The .xls file can be downloaded from JavaWorld( you only need the excel file part of the download ) and this code I provided ( similar to the java downloaded except with a dsnless connection ) will work right out of the box if you save the excel file to 'C:\temp\qa.xls'.

String fullConnectionString = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\temp\\qa.xls";
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
Connection sconn = DriverManager.getConnection ( fullConnectionString );
Statement stmnt = sconn.createStatement();
String query = "select URL from [qas$] where Month='March' and Year=2000;";
ResultSet rs = stmnt.executeQuery( query );
System.out.println( "Found the following URLs for March 2000:" );
while( rs.next() )
{
System.out.println( rs.getString( "URL" ) );
}

Just a note, spacing in the dsnless connection url is imperative! Do not change the spacing anywhere in it. Just cut and paste, changing only the directory and filename to the .xls file
good luck,
Jamie
[ September 04, 2002: Message edited by: Jamie Robertson ]

[lakshmi jonnala]

Hi all,
I am the real lakshmi. I never posted this topic. SOmebody hacked my id, logged in and posting new topics. Did anyboby experienced this problem??? I want to cancel my login and freeze my id so that no boby can use it. How can i do that? This is ridiculous.....
thanks.

[Sudhish Mathuria]

hi all

I am using above code in RFT which is build on eclipse. I am getting below exception. Please suggest some solution:

exception_name = java.sql.SQLException
exception_message = [Microsoft][ODBC Excel Driver] Syntax error (missing operator) in query expression 'Testcase ID='TC1' and Row ID='2''.
script_name = Script5
script_id = Script5.java
line_number = 44
exception_stack = at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6969)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7126)

My code is below:

String fullConnectionString = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\\TC\\Login.xls";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection con = DriverManager.getConnection(fullConnectionString);
Statement state = con.createStatement();
String query = "select loginName from [Login$] where TestcaseID='TC1' and RowID='2';";
ResultSet rs = state.executeQuery(query);
System.out.println( "Found the following URLs for March 2000:" );
while(rs.next())
{
System.out.println( rs.getString("loginName") );
}

[Rob Spoor]

Please don't wake the zombies. This thread was 7 years old, you should have created a new thread instead.

Also, post real code / real error messages. Your exception mentions "Row ID", but your code has it without the space, as "RowID". Which one is it? Likewise for Testcase ID / TestcaseID.

Something tells me that it is "Row ID", because that would match the type of error. The SQL parser sees "Row" as a field name, and an operator (=, like, etc) should follow. Instead, "ID='2'" follows.
If your column or table names contain spaces, or have the same name as keywords, you should escape them so they will be treated as columns / tables. In MS SQL Server, Access and Excel you do that by enclosing the name in [ and ]: "[Row ID]='2'".


Also, this thread is from 2000, when Apache POI (probably from 2001 or 2002) and JExcelAPI (from October 2003) didn't exist, or at least didn't have much popularity yet. You should really try these out for reading Excel files.