asked on

how to read excel file from a java application using HSSF POI

I have a webpage where users can upload their excel files. The files will be uploaded at the following directory
C:\svn_new\Tomcat\webapps\chapters\attachments\IREM

The first row of the excel file contains column headings such as first_name, last_name, address1, etc

Now what I need is to read line by line of data(using while loop) from the excel file. How can I be able to do that

i got the following code

// Open spreadsheet

POIFSFileSystem fs = new POIFSFileSystem(InputStream iostr);
HSSFWorkbook wb = new HSSFWorkbook(fs);
private HSSFSheet sheet = null;
sheet = wb.getSheet("Product Data");

Now what should I pass to the getSheet method and how can I read the headings and the data line by line from excel, please help
Aslo do I need to import any files?

Any help will be greatly appreciated

rama_krishna580

Hi,

Have a look at jexcel. This is a java api for reading/writting excel files.
You can find it at http://www.andykhan.com/jexcelapi/index.html

Also have a look at POI project by apache. http://jakarta.apache.org/poi

Although I found jexcel better, both in performance and memory management.

R.K

royalcyber

ASKER

thank you rama

But my question was actually how to use while loop to get data from excel using Jakarta POI HSSF. Please let me know if you have any sample code of reading data

Thanks again

Nguyen Huu Phuoc

I think you are using Jakarta POI.
It is OK.
I suppose that your workbook name is workbook.xls
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;
import java.util.*;
public class Untitled1 {
public static void main(String[] args)
{
POIFSFileSystem fs;
HSSFWorkbook wb;
try
{
fs = new POIFSFileSystem(new FileInputStream("workbook.xls"));
wb = new HSSFWorkbook(fs);
}catch (IOException ie)
{
ie.printStackTrace();
return;
}
HSSFSheet sheet = wb.getSheet("SheetName");
int lr = sheet.getLastRowNum();
int fr = sheet.getFirstRowNum()+1;
/* I want to demo two methods:
1. Use index
2. Use iteration.
*/
for (int j=fr;j<=lr;j++)/*you can use iteration here*/
{
HSSFRow row = sheet.getRow(j);
for (Iterator it = row.cellIterator();it.hasNext();)/*you can use index here*/
{
System.out.print(((HSSFCell)it.next()).getStringCellValue() + ' ');
}
System.out.println();
}
}
}

hoomanv

these links may be helpful on how to use Jakarta POI
http://www.javaworld.com/javaworld/jw-03-2004/jw-0322-poi.html
http://www.onjava.com/pub/a/onjava/2003/04/16/poi_excel.html?page=1

royalcyber

ASKER

Thank you very much guys.

One more question, when i import the following files

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

It gives an error that it couldn't find the package, please if you can let me know if I need to download anything and from where

Thanks

Nguyen Huu Phuoc

Has the poi package been placed in your classpath when you compiled your code?
I downloaded poi-bin-2.5.1-final-20040804.tar.gz from
http://mirror.candidhosting.com/pub/apache/jakarta/poi/release/bin/poi-bin-2.5.1-final-20040804.tar.gz
Ungzipped and untared this file I have poi-2.5.1-final-20040804.jar file, placed it in my classpath, compiled my code I posted it above successfully. I tested it many times.
Phuoc H. Nguyen

royalcyber

ASKER

phuonch, I transferred the poi-2.5.1-final-20040804.jar file to my Web-INF/lib folder and compiled the whole application but it is still giving the same error

Any suggestion

Thanks

Nguyen Huu Phuoc

Which IDE are you using?

royalcyber

ASKER

Jbuilder 2005

ASKER CERTIFIED SOLUTION

Nguyen Huu Phuoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

royalcyber

ASKER

Thank you Phuonch it worked

Nguyen Huu Phuoc

If your "Required library" tab doesn't contain any library, you must click on "Add" butto to make a new one.
When you click on "Add" button, "Select one or more libraries" dialog appears. You click on "New" button, "New Library Wizard" appears.
Name: <Enter anything you like, take example poilibrary>
Location:Project.
Library paths: Click on "Add" button to browse to your poi-2.5.1-final-20040804.jar file.
Click on "OK" button to close "New Library Wizard" dialog.
Click on "OK" button to close "Select one or more libraries" dialog.
NOw you can see poilibrary appear in your "Required library" tab.
Recompile your code.

Nguyen Huu Phuoc

Yeah. Congratulation on you!

royalcyber

ASKER

Phuonch

I am actually using the following method and pass the File inputStream, but the problem is that it doesn't get the data in sequence, it gets all the dtata but randomly.

Please if you can help me in this

public static String xlsTextStripper(FileInputStream fis) {

String content = null;

try {

StringBuffer sb = new StringBuffer();

HSSFWorkbook workbook = new HSSFWorkbook(fis);

int numOfSheets = workbook.getNumberOfSheets();

for (int i = 0; i < numOfSheets; i++) {

HSSFSheet sheet = workbook.getSheetAt(i);

Iterator rowIterator = sheet.rowIterator();

while (rowIterator.hasNext()) {

HSSFRow row = (HSSFRow) rowIterator.next();

Iterator cellIterator = row.cellIterator();

while (cellIterator.hasNext()) {

HSSFCell cell = (HSSFCell) cellIterator.next();

String cellStringValue = null;

if (cell.getCellType() == 4) {

boolean booleanValue = cell.getBooleanCellValue();

cellStringValue = Boolean.toString(booleanValue);

} else if (cell.getCellType() == 0) {

double doubleValue = cell.getNumericCellValue();

cellStringValue = Double.toString(doubleValue);

} else if (cell.getCellType() == 1) {

cellStringValue = cell.getStringCellValue();

}

if (cellStringValue != null) {

sb.append(cellStringValue);

sb.append("\t");

}

}

sb.append("\n");

}

}

content = sb.toString();

} catch (Exception e) {

e.printStackTrace();

}

return content;

}

Nguyen Huu Phuoc

If you want get it in order you use index instead of using iteration.

Iterator rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
=>
int lr = sheet.getLastRowNum();
int fr = sheet.getFirstRowNum()+1;
for (int j=fr;j<=lr;j++)
{
HSSFRow row = sheet.getRow(j);
Similar to cell in the row. You should use index.
Phuoc H. Nguyen