Reading/Writing a Excel Sheet in Java
There are two good choices for reading & writing Microsoft Excel Spreadsheet files from Java, in a platform independent way, - jexcelapi and Jakarta POI (HSSF). Both of them provide nice interface to access Excel data structure and even generate new spreadsheet. I have done extensive tests with both of them for a high-profile project for a Fortune 500 company. Previously also I had successfully used HSSF for another high profile client. In the paragraphs below I present my conclusions and sample code for reading Excel spreadsheet from Java using both the libraries. Comparison of JExcelAPI with Jakarta-POI (HSSF)
1. JExcelAPI is clearly not suitable for important data. It fails to read several files. Even when it reads it fails on cells for unknown reasons. In short JExcelAPI isnt suitable for enterprise use.
2. HSSF is the POI Projects pure Java implementation of the Excel  97(-2002) file format. It is a mature product and was able to correctly and effortlessly read excel data generated from various sources, including non-MS Excel products like Open Office, and for various versions of Excel. It is very robust and well featured. Highly recommended.
3. Performance was never a consideration in our tests because a) data integrity is the single most important factor and b) there didnt appear to be any significant performance difference while running thetests; both of them were very fast. We didnt bother to time it for the above reasons.
How to read Excel Excel Spreadsheet from Java using Jakarta POI (HSSF)
try {

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

 HSSFRow row; HSSFCell cell;

int rows; // No of rows

rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns

int tmp = 0;

 // This trick ensures that we get the data properly even if it doesnt start from first few rows

for(int i = 0; i < 10 || i < rows; i++)

{

row = sheet.getRow(i);

if(row != null)

 { tmp = sheet.getRow(i).getPhysicalNumberOfCells();

if(tmp > cols) cols = tmp;

} }

for(int r = 0; r < rows; r++) {

row = sheet.getRow(r);

 if(row != null) { for(int c = 0; c < cols; c++) {

cell = row.getCell((short)c);

 if(cell != null)

{ // Your code here }

} } }

 } catch(Exception ioe) { ioe.printStackTrace(); }


Related Posts:




Sandeep Joshi
Mathematics, Technology and Programming are my passion. I am a part of Java Ecosystem and through this blog, I contribute to it. I am here to blog about my interests, views and experiences.
I am on Google+ and Facebook.
I feel proud to be listed as a "National Memory Record Holder" in the Limca Book of Records, 2009 and have attempted for an International Memory record in the Guiness Book of Records. I can remember the value of PI upto 10,000 digits after the decimal (3.1415.....). You can contact me on javagenious.com(At)gmal.com ; I would like to hear from you :)