aspose file tools*
The moose likes Java in General and the fly likes Reading XLS file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Reading XLS file" Watch "Reading XLS file" New topic
Author

Reading XLS file

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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
Ranch Hand

Joined: Jun 02, 2000
Posts: 244
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
Sheriff

Joined: Jan 07, 1999
Posts: 6920
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.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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
Ranch Hand

Joined: Jun 02, 2000
Posts: 244
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
Ranch Hand

Joined: Jun 23, 2000
Posts: 140
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
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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
Ranch Hand

Joined: Nov 05, 2000
Posts: 158

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


....
mohammed hguig
Greenhorn

Joined: May 14, 2001
Posts: 1
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
Ranch Hand

Joined: Apr 03, 2001
Posts: 178
Check out formula one for java. http://www.tidestone.com.
It is a tool that allows opening and manipulating excel files.
Prasenjit Chakraborty
Greenhorn

Joined: Dec 08, 2001
Posts: 1
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
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
IBM AlphaWorks has an ExcelAccessor bean suite.
- Peter
laxmikant shimpi
Greenhorn

Joined: Sep 18, 2001
Posts: 21
hi all,
can somebody really tell me how to create Excel DSN programmtically without creating it in the Control Panel

thanks in Advance,
Laxmikant


Sun Certified Java Programmer
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
Ranch Hand

Joined: Sep 09, 2001
Posts: 427
Use Jakarta POI
http://jakarta.apache.org/poi/
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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'.

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
Greenhorn

Joined: Jan 21, 2002
Posts: 4
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
Greenhorn

Joined: Sep 24, 2009
Posts: 1
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 = "jdbcdbcriver={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
Sheriff

Joined: Oct 27, 2005
Posts: 19760
    
  20

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.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Reading XLS file