Quick Search for:  in language:    
article,shows,simple,ways,accessing,spread,sh
   Code/Articles » |  Newest/Best » |  Community » |  Jobs » |  Other » |  Goto » | 
CategoriesSearch Newest CodeCoding ContestCode of the DayAsk A ProJobsUpload
Java/ Javascript Stats

 Code: 220,465. lines
 Jobs: 89. postings

 How to support the site

 
Sponsored by:

 
You are in:
 

Does your code think in ink?
Login





Latest Code Ticker for Java/ Javascript.
Gobang
By Geniusbob Xu Qiang on 11/27


Click here to see a screenshot of this code!Salary
By Vikram Ivatury on 11/25

(Screen Shot)

Click here to see a screenshot of this code!A (part10) Powerful Swing Code to Maintain CD Database
By James Smith K on 11/25

(Screen Shot)

String Calculator
By MadokaCoder on 11/24


Chobi Dekha
By ShuvoRim on 11/23


Click here to see a screenshot of this code!A basic Client Server application II
By Ronald Holland on 11/23

(Screen Shot)

Bookmark image
By darren kurn on 11/22


myFT
By Owolabi Oyapero on 11/22


Click here to see a screenshot of this code!Simple Socket example
By Steven McElrea on 11/20

(Screen Shot)

Click here to put this ticker on your site!


Add this ticker to your desktop!


Daily Code Email
To join the 'Code of the Day' Mailing List click here!

Affiliate Sites



 
 
   

How to read Excel in java

Print
Email
 

Submitted on: 5/15/2001 3:54:37 PM
By: Manjunath P Reddy 
Level: Intermediate
User Rating: By 24 Users
Compatibility:Java (JDK 1.2)

Users have accessed this article 43556 times.
 
 
     This article shows a simple ways of accessing spread sheets (such as microsoft excel) in java

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.   
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.


This sample code was an outcome of a friend's requirement for reading an excel file dynamically. He had an EJB layer for a flight management system for read/writes to database. It so happened that the data also started coming from various departments in a spread sheet format. So either he had to import the data to his oracle database manually or re-design his EJB's for accomadating this new data input. So what i designed for him was a simple facade pattern classes run by a daemon process which makes use of the existing enterprise java beans which enabled him to treat the spreadsheet data as no different. The scope of the design is beyond this article.

So what i will illustrate in this article is a simple way of accessing spreadsheets as if they were a database. This article holds good for java running on windows-servers. The access itself is through a jdbc-odbc bridge.

okie..so here we go...

Open the odbc data administrator console and click on system dsn. select add and add the Microsoft Excel driver from the list of drivers and give a name to the dsn(say exceltest) and select the workbook.

Then all one needs is to connect through this dsn just like connecting to the database and accessing records.

Here's the sample code

import java.io.*;
import java.sql.*;
    public class ExcelReadTest{
        	public static void main(String[] args){
        		Connection connection = null;
            		try{
            			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            			Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );
            			Statement st = con.createStatement();
            			ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
            			ResultSetMetaData rsmd = rs.getMetaData();
            			int numberOfColumns = rsmd.getColumnCount();
                			while (rs.next()) {
                    				for (int i = 1; i <= numberOfColumns; i++) {
                    					if (i > 1) System.out.print(", ");
                    					String columnValue = rs.getString(i);
                    					System.out.print(columnValue);
                    				}
                    				System.out.println("");	
                    			}
                    			st.close();
                    			con.close();
                        		} catch(Exception ex) {
                        			System.err.print("Exception: ");
                        			System.err.println(ex.getMessage());
                        		}
                        	}
                    }


Answer to Comments:
Hey john ur comment is right and well recieved. The reason for the first row not being printed is that the jdbc-bridge assumes the first row to be akin to column names in the database. Hence the first available row is the name of the column...which explains why the third row is printed if the first row is missing.
Hope this helps and thanks for the comment

Standard sql queries like
                    Select column_name1,column_name2 from [Sheet1$] where column_name3 like '%bob%';
                    
can be used on the spreadsheet.
You can use the following snippet to print the column names(which is the first row of the spread sheet).
                        			for (int i = 1; i <= numberOfColumns; i++) {
                        				if (i > 1) System.out.print(", ");
                        				String columnName = rsmd.getColumnName(i);
                        				System.out.print(columnName);
                        			}
                        			System.out.println("");
                        


Other 3 submission(s) by this author

 

 
Report Bad Submission
Use this form to notify us if this entry should be deleted (i.e contains no code, is a virus, etc.).
Reason:
 
Your Vote!

What do you think of this article(in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor See Voting Log
 
Other User Comments
5/15/2001 7:41:59 PM:the ghost who walks
I was looking for an easy way to read from spreadsheets and this code served my purpose since my app was java based.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/16/2001 10:13:22 AM:John
Nice clean code....I have one question: Why does it start reading fron the 2nd row of data in the spreadsheet? Also if the first row is empty it will not read the data in the 2nd row and starts with the data in the 3rd row. Thanks
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/21/2001 3:07:37 PM:lisa
simple and an excellent piece!! My 5 cents to it
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/24/2001 7:59:17 AM:Valli
This is exactly what I was looking for. But my application runs on Linux server. How can this be done if my server is on RedHat6.2 Linux?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/24/2001 12:05:34 PM:Manjunath P Reddy
Valli...there are some type 3 jdbc-odbc bridges available in the free market which support linux versions. Check out http://industry.java.sun.com/solutions/p roducts/by_product/0,2348,all-2552-99,00 .html for more info one of them. And also...if u reach a dead end you can always run a daemon process on a winnt box which can do the updation to the database. Write to me if u have problems with easy soft's jdbc-odbc bridge.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/24/2001 12:36:20 PM:J2ee Observer
Excellent!!! hope the planet was full of such useful articles. thanx for sharing mangunuth
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/24/2001 12:49:38 PM:Tania Woods
It looks fine and pretty useful but i have 2Q.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/24/2001 12:50:33 PM:Tania Woods
1>since the name Sheet1 is default(which can be anything like billing, invoice, etc) so how do i get the name of the sheet dynamically and build a query. Or is it possible at all? 2>Since the name of the workbook is selected at design time what if a new excel sheet has to be read and then how do i configure at runtime ... pls help?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/26/2001 12:56:38 PM:Manjunath P Reddy
Tania..as u know the sheet name by default is sheet1..if someone changes this name currently the program cant handle this..im working on a piece of native code that will enable one to read dynamically the sheet names. The limitation of jdbc-odbc bridge makes it impossible to select the excel sheet at run time. But then one can design application to take care of this kind of problems. U can run a daemon process that looks into a folder and reads the excel into db and then archives the excel sheet into another folder and then keeps waiting for a new file to be read. So one can make sure that the file name is always same and then copy and archive it. Hope this helps. thanks
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/27/2001 12:18:44 AM:Tania Woods
Thanks for the help..and hope to see the promised stuff here soon..
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/15/2001 3:07:22 PM:chris
hey, i was wondering if u ever finished the app u made for your friend b/c my friend has asked me to make a similiar app so if u would, i would appreciate it if i could glance at urs
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/26/2001 2:15:15 PM:Kamran Aftab
Hi Manjunath: How would I access individual cells with the SQL statement. For example, if I wanted to access cell A1 on Sheet1? or cells A2-A6 on Sheet2? What would be the SQL statement for that? Where can I find this information? I tried looking up in excel, no help. Please reply by email. Thanks, Kamran, kaftab@husky.ca
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/26/2001 2:20:35 PM:Kamran Aftab
Hi Manjunath: How would you query for a specific cell. For example: cell A1 on sheet1, or cell A1-2 on sheet1. What would be the SQL statement in these cases. Is there any info available on this anywhere? I tried the help in excel, didn't get much. Please respond with an email. Thanks, Kamran (kaftab@husky.ca)
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/1/2001 10:03:55 PM:Ajay Tejwani
In my case, I have a column that has got both pure numeric(12345) and purely alphanumeric(testing) type of data. How can I get both the data? If alphanumeric data is the first data, it treats all the values as alphanumerc and returns null for numeric and vice versa. Your quick reply shall be appreciated. Thanks, Ajay (ajay_tejwani@yahoo.com)
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/1/2001 10:04:38 PM:Ajay Tejwani
In my case, I have acolumn that has got both pure numeric(12345) and purely alphanumeric(testing) type of data. How can I get both the data? If alphanumeric data is the first data, it treats all the values as alphanumerc and returns null for numeric and vice versa. Your quick reply shall be appreciated. Thanks, Ajay
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
9/24/2001 6:13:18 AM:chakradhar reddy
Hi, now iam working on excel with vba,my boss asked to work on excel and java,my request is that ,how could i connect excel with java please give a detailed answer....thanking you chakri
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/3/2001 3:13:58 AM:kumar
can i use all DDL Commands . will they work?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/3/2001 3:15:34 AM:kumar
hi manju how to delete all the rows from the excel sheet.if i use delete statement its giving me an error.can u pls help.tsk
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/4/2001 2:49:22 AM:dai
I also wonder why this program read from 2nd. How can I ahust it?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/22/2001 9:45:44 PM:Sudhanshu Umalkar
Is it possible to INSERT records into Excel spreadsheet dynamically through Java using method mentioned above i.e. using JDBC-ODBC bridge ? If yes, please reply at sudhanshu@mandarin-capital.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/4/2002 6:41:45 AM:sisir
Can u tell me how to read from excel sheet on Unix machine?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/4/2002 7:15:40 AM:Srikanth
The article is precise in what it does it's good
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/28/2002 3:11:44 AM:Ranjith
Manjunath, That's great. D'u have any suggestion for reading chart/images/objects from an excel worksheet? thanks, Ranjith
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/3/2002 12:19:00 AM:Radhika
How to read an Excel file using DSN-Less Connection. Also wnat to fetch the sheet names dynamically. Pls mail to me to ssdev90@yahoo.com Thanks in Advance..
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/3/2002 12:46:28 AM:Radhika
How to Read an Excel file using DSN-Less Connection and to fetch the sheet names dynamically. Please reply to me ASAP.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/3/2002 11:13:47 AM:Ravi
hi! there, i would like to know the process of getting the list of excel sheets in an excel file. I'm able to read the excel file using Jdbc-Odbc bridge driver and passing the sheet name as a static parameter. I would like to pass the sheet name as a dynamic value.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/12/2002 6:25:05 AM:Gilles Philippart
As an alternative solution, you could also use the POI library from the jakarta project (pure java access to Excel) : http://jakarta.apache.org/poi
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/12/2002 6:26:57 AM:Gilles Philippart
As an alternative, you could check this out: http://jakarta.apache.org/poi You now have access to Excel spreadsheets without using an jdbc-odbc bridge ;) Gilles Philippart
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/19/2002 10:45:11 PM:Hoyoung Hwang
Good~. But the code is not for excel. it is just program using odbc. if you parse excel file. you can call this, program for excel. and useful in any platform.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/22/2002 8:04:05 PM:Johnson Ng
Yes, POI is a better alternative, cause now u can access
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
8/23/2002 9:05:35 AM:Raj
hello, how can i write data to excel file at runtime in java or java script can anyone help? plz
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
9/10/2002 6:21:08 PM:
Hello, I am having trouble in defining the connection: Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" ); I get an error '[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified' Any help would be greatly appreciated. Thanks
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/3/2002 4:27:58 PM:
Hi, I am trying to upload an excel spreadsheet and tried to get it as a InputStream in server using request.getInputStream. I am getting the Stream, but this stream is not a proper OLE Stream and have lots of junk characters. How to do a proper upload of an XL Spreadsheet to the server. Any reply expected ASAP. Thanks.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
10/9/2002 7:58:16 PM:
Manjunath P Reddy is there anyway of opening a excel spread sheet from Java
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/6/2002 12:11:49 AM:
How to read and write an Excel file using DSN-Less Connection. Also want to fetch the sheet names dynamically. Pls mail to me to wiroajw@hotmail.com Thanks in Advance..
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/19/2002 4:48:49 PM:
Hi Manjunath, I'm facing the same problem as mentioned by Ajay Tewani...I can't seem to make the program read numbers and alphanumeric values from the same column...first few rows I have are numbers and as soon as the resulset traverses to a alphanumeric value, it returns a NULL. Any idea how to get around this... Thanks, Deepak
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/28/2002 1:03:30 AM:
Please tell me how to enter the sheet name.I get an error : could not find the object Enumeration 6 with the folling line of code ResultSet rs = st.executeQuery(
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/1/2003 4:17:40 AM:
Hi, I am looking for a java program to read a large size excel file with multiple worksheets in it. The test environment is Sun Solaris. I tried using JExcel but there was memory error while reading beyond 1000 rows of data. Does your program stand up to such rigorous testing
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/21/2003 5:11:18 PM:
Manjunath, If I have to write to a excel sheet from java, so how do i do?
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
2/25/2003 4:01:09 AM:
this code works fine if the excel sheet is in a given format.but what if the format is changed(for eg. if the sheet has got a heading in the first 2 rows
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/18/2003 12:41:10 PM:
i had this error: Exception: [Microsoft][Driver ODBC para Excel] 'Sheet1$' nÒo Ú um nome vßlido. C ertifique-se de que ele inclua somente caracteres ou pontuaþÒo vßlidos e de que nÒo seja longo demais. tadeudavid@petrobras.com.br
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
3/18/2003 12:46:29 PM:
i got this error C:\Temp>java excelreadtest Exception: [Microsoft][Driver ODBC para Excel] 'Sheet1$' nÒo Ú um nome vßlido. C ertifique-se de que ele inclua somente caracteres ou pontuaþÒo vßlidos e de que nÒo seja longo demais. thanks
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/5/2003 9:16:08 PM:
jovem, o erro "C:\Temp>java excelreadtest Exception: [Microsoft][Driver ODBC para Excel] 'Sheet1$' nÒo Ú um nome vßlido. C ertifique-se de que ele inclua somente caracteres ou pontuaþÒo vßlidos e de que nÒo seja longo demais", acontece porque em português 'sheet1$' existe como 'folha1$', provávelmente outros dados estarão incorrectos pq o código é para o excel em inglês.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/12/2003 3:18:23 AM:
'Sheet1' can be changed to any sheet presentin the workbook. I have a workbook with the sheets named as
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/12/2003 3:22:47 AM:
'Sheet1' can be changed to point to any sheet in the file. And it can be got randomly too. I have an excel file with sheets named "month-year". ResultSet rs = st.executeQuery( "Select * from [" + ReqMonth.getText() + "-" + ReqYear.getText() + "$]\"" ); I get the ReqMonth and ReqYear from the screen. Now the code snippet will display me the contents of the required sheet.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
4/21/2003 8:46:43 PM:
I am having the same problem as ajay_tejwani and Deepak. I can't seem to make the program read numbers and alphanumeric values from the same column...first few rows I have are numbers and as soon as the resulset traverses to a alphanumeric value, it returns a NULL. please help me regarding this.thanks in advance
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
5/7/2003 2:27:18 PM:
Open the odbc data administrator console and click on system dsn. select add and add the Microsoft Excel driver from the list of drivers and give a name to the dsn(say exceltest) and select the workbook Yours, above statement. Does it mean I have to configure my Jpad compiler's "odbc data administrator"? If so, where is it at? Thank you!!!!!!
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/20/2003 10:18:03 PM:
hi, my teacher ask me to convert a excel(may include chart) file to a web page. how can do for it. is it also work in linux platform.??? help. help. i have no idea about it ...please help
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
6/25/2003 7:22:33 AM:
Hi, Nice piece of code to read data from excel spread sheet. But I want to be able to insert an row of data to a excel spread sheet, how do I do this. I have modified the above and put in update query, When I try to run this code I get a very strange run time error message. I would appreciate any help on this. info@ilovemcr.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
7/8/2003 3:56:13 AM:
Hi,I would like to write the data to Excel from applet, The applet should perform 2 functions 1) open the windows excel application in windows system(Not within applet) and populate the excel with the data of the applet. Is it possible to do . my email id is jackiemani@yahoo.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/3/2003 2:11:39 AM:
its good artical.i've got some solution from it. i rate it 5 out of 10.
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
11/21/2003 7:20:07 AM:
Hi , is it possible to insert data into the spread sheet using this code JOCKMAHON@hotmail.com
Keep the Planet clean! If this comment was disrespectful, please report it:
Reason:

 
Add Your Feedback!
Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you.  If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.
 
Name:
Comment:

 

Categories | Articles and Tutorials | Advanced Search | Recommended Reading | Upload | Newest Code | Code of the Month | Code of the Day | All Time Hall of Fame | Coding Contest | Search for a job | Post a Job | Ask a Pro Discussion Forum | Live Chat | Feedback | Customize | Java/ Javascript Home | Site Home | Other Sites | About the Site | Feedback | Link to the Site | Awards | Advertising | Privacy

Copyright© 1997 by Exhedra Solutions, Inc. All Rights Reserved.  By using this site you agree to its Terms and Conditions.  Planet Source Code (tm) and the phrase "Dream It. Code It" (tm) are trademarks of Exhedra Solutions, Inc.