Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2867
  • Last Modified:

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



0
royalcyber
Asked:
royalcyber
1 Solution
 
rama_krishna580Commented:
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
0
 
royalcyberAuthor Commented:
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
0
 
phuocnhCommented:
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();
     }  
    }
  }
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
royalcyberAuthor Commented:
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


0
 
phuocnhCommented:
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
0
 
royalcyberAuthor Commented:
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
0
 
phuocnhCommented:
Which IDE are you using?
0
 
royalcyberAuthor Commented:
Jbuilder 2005
0
 
phuocnhCommented:
If you are using JBuilder please check
Project menu > Project properties >Classpath tab
Can you find poi-2.5.1-final-20040804.jar listed here?
If not, you select "Required library" tab and select one library listed here, click on "Edit" button , then click on "Add" button, browse to poi-2.5.1-final-20040804.jar.
Click "OK" to close the property winows.
Recompile your code.
Goodluck to you.
0
 
royalcyberAuthor Commented:
Thank you Phuonch it worked
0
 
phuocnhCommented:
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.
 
0
 
phuocnhCommented:
Yeah. Congratulation on you!
0
 
royalcyberAuthor Commented:
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;

    }
0
 
phuocnhCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now