Discussion on How To Read / Write Excel Spreadsheet From Java

June 8, 2010: 4:00 am
Hi Angsuman and all visitors/readers! Your post is all about reading Excel spreadsheets and in one of your comments you wrote that you've only tested these two solutions on client supplied example files. You also wrote that "in my tests HSSF came out to be the clear leader and recommended solution". That's the point that many readers/commenters obviously missed! Most readers (as I got it from their comments) got to the conclusion that POI is the generally superior to JExcelAPI, which is not the case. POI is known to be a memory hog (even recent versions) and creating XLS files (from scratch) of 20k or more records (with 20-30 columns) can eat up several hundred MB of heap memory. This is not really an option in case of a webapp where several XLS generations can be running in parallel at the same time. JExcelAPI eats a lot less memory. On the other hand, POI is richer featurewise and maybe more robust. So the right conclusion is: there's no clearly "better" choice. Each has it's strengths and weaknesses. You've to consider your goal and pick the right solution accordingly. I'm afraid that there's no shortcut here: the developer has to test both methods for her/his particular application and see which one suits the particular requirements better.

May 19, 2010: 10:44 am
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); can't be execute in netbeans(and use POI 3.6 20091214)

May 7, 2010: 1:01 am
Hi Frdz, I am facing problem when i try to get the data from the excel file not able to accept the data from the excel file i am using an automation tool Eclipse to do this if anybody have worked or have solution plz let the know. Thanks in advance

April 26, 2010: 9:14 pm
how to take status of employees (30) on excel sheet and update into another main excel sheet. please send me the results

March 2, 2010: 6:43 am
Pls, how can i add two more interfaces(forms)to my java program that is connected to a Microsoft access database. The program has a form that is retrieving information from the MS Access database but my supervisor wants me to write code that would create a form to display flash/animation about the title of the project on which when clicked leads to the another form that displays password and username which leads to the database retrieval form. Pls help me urgently. Am a student and new learner

February 17, 2010: 6:49 am
iam desining a web site for a company iam stuck up at a point can any one tell me code where i should get details from mdb file where produts updates are stored as soon as the user enter the name of product it shoud get details from mdb and should get displayed as html output pls any one notify me by mail my mail id is sandeep_reddy@indiatimes.com

December 22, 2009: 4:15 am
hai, Can we read/write excel sheet using Connection class in sql package get connect with odbc bridge,similar to access.. is it possible?pls give me some samples Thank u n advance:-)

December 22, 2009: 4:10 am
HAI, cAN WE USE THE JDBC-ODBC CONNECTIVITY TO READ THE EXCEL DATA. GETcONNECTION(PATH OF XLS FILE) IS IT POSSIBLE??? PLS GIVE ME THE SAMPLES tHANK U IN ADVANCE

December 8, 2009: 8:57 am
I want read and write into another xls file. Please share me how to write excel. Thanks, ajay

November 29, 2009: 1:19 pm
Thank you very much for the crisp explanation.

October 24, 2009: 7:03 am
hi iam trying to write excel sheet using jxl hwo do i write ?

October 10, 2009: 7:18 am
I've lot dubts from creating excel sheet from java source code

August 12, 2009: 8:42 am
Sir, can u tell me how to connect Jakarta POI (HSSF) to java programm and how to buy it

August 8, 2009: 7:31 pm
thanks alot....my work made very easy,really nice way of explanation,null knowledge in java was able to understand the above example

August 6, 2009: 10:26 am
sir,i need to search a given searching value in excel spreadsheet using java.can it b done with this code?

August 5, 2009: 8:05 am
Thanks it's work fine.

July 20, 2009: 4:05 pm
which classes should be imported for using Jakarta (POI)

April 23, 2009: 1:28 pm
Hi, I got the logic for reading and saving of excel to database, but out of 5 rows if I delete 2 rows from excel file, then my logic fails because after reading 3 rows from 4th row onwards the value is coming as null and it is throwing null pointer exception. Can u please give me the reply for this question. Thanking you,

March 21, 2009: 12:34 pm
Have you used it yourself? How is it superior to the solution provided?

March 21, 2009: 7:21 am
try: https://sourceforge.net/projects/openxls

March 18, 2009: 5:04 pm
How to fetch columns from excel sheet with out using DSN

March 13, 2009: 11:17 pm
Check these other open source solutions out: openxls java spreadsheet SDK: https://sourceforge.net/projects/openxls sheetster programmable web spreadsheet: https://sheetster.com

March 5, 2009: 1:52 pm
Hi Thanks I am a leymann in java programing and i need to fetch the specified data from the excel sheet and i need to convert into csv format. Can you please suggest me which is the best way to achive this. Thanks in Advance

February 9, 2009: 3:31 pm
How to Link Excel to Java codes?

January 23, 2009: 2:56 am
Hi, is POIFSFileSystem located in some .jar file? I need to analyze a chart and write data to an excel document, given each y axes value will be written to a separate sheet. When i tried to run the code above im getting errors for almost all the classes used here.please let me know where can i find all these classes? Thanks

January 16, 2009: 8:01 pm
Hi, I meant to find a web-based spredsheet Java Application that can be installed on one's own server. Privacy is the issue. I know there are many commercial web-based spreadsheet software. Thanks.

January 16, 2009: 7:33 pm
Hi, Is there web based Java tool for Excel similar to POI HSSF ? ( I had a feeling it's a wishful thinking, just asking ..) Thank you in advance.

October 31, 2008: 1:22 pm
Hi, I am trying to read and excel file and find some particular string in it. It is happeneing extremely slow and mu application gets timed out. Is there a fast method to Search for a String in excel using POI? Thanks in advance

October 27, 2008: 10:58 pm
Hi, I am trying to read data from excel sheets using jxl but with large size files like more than 7 mb giving out of memory error.Suggestions are most welcome .It is urgent.Please help

June 10, 2008: 3:32 am
hello everybody, i have a small problem and need your help on it i need to access my .mpp (Microsoft Project) File From java.....How can i do that? knowing that using ready made components is not allowed.

June 2, 2008: 12:34 am
Mohamed, I don't know. I haven't tried it.

June 1, 2008: 11:24 am
but how to read excel sheets carying non english language as arabic ?

May 20, 2008: 3:07 pm
what do i import to use the JExcelAPI?

April 26, 2008: 2:02 am
Hi, Sorry that I am putting question of Jxl in POI forum. I am getting error "java.lang.OutOfMemoryError: Java heap space" while reading file with 10000 records and 95 columns. size of file is 14M. I am testing my application through JProfiler. Is there any restriction of file size or problem while reading throught jxl? Can anybody help me. Thanks

April 18, 2008: 4:18 am
I tried POI , with Excel 2003, it's writing fine , but when I'm trying to open it corrupts the whole Excel file. So I tried with JXl, here writing & opening is o.k, But it's not appending new sheet's. it's always replaces old sheet with newly created sheet.Please any one can help me.

April 16, 2008: 12:40 pm
I have used POI with thousands of complex spreadsheets without any issues at all.

April 16, 2008: 12:37 pm
I would guess that it is the problem of the spreadsheet.

April 15, 2008: 8:37 am
I have one excel file which is 51MB size. i tried to open using POI but fails. I saved the file using MS Excel with different file name and opened it with POI, it works. Is it the file problem or with POI? Please suggest.

April 6, 2008: 7:11 am
ur's mail id please, by which i mail u regardings my problems in programing

March 6, 2008: 6:43 am
how to read upload Excel sheet in tamil font in java code I would like to upload Excel sheet in tamil font using java code help me

February 6, 2008: 5:41 pm
POI HSSF was not usable for me mainly because it didn't know how to write out number values with the correct format - so things which were percentages in the spreadsheet like "83%" might come out of POI as "0.834444" which doesn't work with the rest of the pipeline.

February 4, 2008: 7:04 am
Thanks Angsuman for this introduction which helped me choose POI. I had some difficulty understanding the difference between physical and logical rows and cells and I found out that your code is not working when there are empty rows. You mention in already in your "gotcha's" but it's actually the other way around getPhysicalNumberOfRows() may be LESS than the actual rows (retrieved by getLastRowNum() ). In your example you mix up the getPhysicalNumberOfRows() and the sheet.getRow(int) which returns the logical row in the sheet which might be a different one if you have empty rows. The same goes for columns.

February 4, 2008: 1:17 am
I used the same code. In the biginning i have only 1000 rows, 26 columns then it was working properly. Here the proble cames, now i hav 3000 rows, 29 colomns; at first, my program had worked properly but its taken 1.26 minuts to finish up and i cheacked ie. taking more time in executing this single line "HSSFWorkbook wb = new HSSFWorkbook(fs);". and at the next time i got this Exception in thread "main" java.lang.OutOfMemoryError: Java heap space. finally i executed the same code in some other system, again the same problem... plz help me to solve this problem.. Thanks in Adwnc Nandu.

January 24, 2008: 3:47 am
I want to write the data in Excel File . (i.e) I want to append the excel file . how can i do that? The original file\'s soft copy is in one subfolder of my project. I want to overwrite that file . Pls help . I use jxl jar file in my project . Thanks in Advance Ganesh

January 18, 2008: 2:45 am
thx for the info!

November 14, 2007: 3:40 am
Thanku angs.. I did use ur code to read an excel file and got too exceptions as ArrayIndexOutOfBound and 2 more.. If u can jus help me to write whats in my acess database onto an excel file i'll be thankful.. Will be glad if I get some code on it!!!

November 13, 2007: 12:43 pm
Erwin said> have you tried reading charts from an excel file? No.

November 13, 2007: 12:41 pm
You should use Java to transfer data from MS Access to MS Excel. Use the above tutorial to read / write from MS Excel. Read the tutorial to read / write from Microsoft Access database using Java.

November 13, 2007: 6:13 am
Hi.. I need to write data from a microsoft access database onto an excel sheet and any changes or updations in the database should also be reflected on the excel sheet.. Can anyone help?? Same with importing data from excel to database! Thanks..

November 3, 2007: 7:32 am
HI.. i want to automate the scripting,, so i want the to read the data from excel sheet & with that date it has to write it in to the another text files,, so i can i have source code for this single case..

October 31, 2007: 1:42 pm
You have given example for reading the excel file. Do you have code snippet to write data to excel template with POI?

October 25, 2007: 8:31 am
Senthil, Use Jakarta-POI (HSSF) for that.

October 25, 2007: 6:23 am
I want to know how to append the spreadsheet using java .Please let me know soon . thanks

October 23, 2007: 7:26 pm
very helpful script! i have another question, though. have you tried reading charts from an excel file? do you have a sample code for that? thank you in advance!

September 1, 2007: 6:13 am
@Neelam, The best I can suggest is look into the Excel dll files for their interface. I haven't read Excel speradsheet in C / C++. @John, I have been using it on client supplied Excel files. I don't know their versions as I use Open Office or Gnumeric to open them on Linux.

August 31, 2007: 1:42 pm
Did you test POI for newer versions of Excel than 2002? Will POI read an Excel 2005 spreadsheet with just basic data?

August 21, 2007: 8:22 am
plz tell me how i can fetch the data from excel sheet in c code::::::plzzzzz.......... thanks:::::

August 14, 2007: 3:56 pm
En que idioma esta esto

July 26, 2007: 1:11 am
There is another choice. Jxcell spreadsheet component

July 20, 2007: 4:29 am
How did you conclude JExcel API is not suitable for Enterprise? Can you list the problems faced using JExcel API and how did you overcome them in Jakarta POI?

June 20, 2007: 7:23 pm
Sin ningun lugar a dudas poi es un excelente framework para el manejo de hojas de calculo, lo uso desde hace bastante tiempo y nunca tuve mayoer problemas.

June 4, 2007: 12:16 pm
Interesting comparison of ExcelAPI with Jakarta-POI. Very useful, thanks for the article.

May 31, 2007: 9:50 pm
please tell me how to create a dropdownlist in excel with POI. Thanks alot.

May 26, 2007: 5:41 pm
jxls is simply a wrapper on top of poi to make it easier to create complex excel reports. It is not a substitute for poi-hssf or jexcelapi.

May 25, 2007: 2:12 am
What do you think of jxls (https://jxls.sourceforge.net) ?

May 24, 2007: 1:32 pm
Thanks, 4 the script. Peter :-)