how to read all cell value using Apache POI?

Question

I want to read all cell value from excel sheet using Apache POI and store it into one dimensional string array .Is is possible to read data from excel row by row and extract value from each cell?

Please help me to solve this problem.

Thanks

Answer 1

import java.io.FileInputStream;
import java.util.Iterator;
import java.util.Vector;

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;

public class ReadExcelFile {

public static void main( String [] args ) {

    String fileName="C:\\temp\\testPOI.xls";
    //Read an Excel File and Store in a Vector
    Vector dataHolder=readExcelFile(fileName);
    //Print the data read
    printCellDataToConsole(dataHolder);
}
public static Vector readExcelFile(String fileName)
{
    /** --Define a Vector
        --Holds Vectors Of Cells
     */
    Vector cellVectorHolder = new Vector();

    try{
    /** Creating Input Stream**/
    //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName );
    FileInputStream myInput = new FileInputStream(fileName);

    /** Create a POIFSFileSystem object**/
    POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);

    /** Create a workbook using the File System**/
     HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);

     /** Get the first sheet from workbook**/
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);

    /** We now need something to iterate through the cells.**/
      Iterator rowIter = mySheet.rowIterator();

      while(rowIter.hasNext()){
          HSSFRow myRow = (HSSFRow) rowIter.next();
          Iterator cellIter = myRow.cellIterator();
          Vector cellStoreVector=new Vector();
          while(cellIter.hasNext()){
              HSSFCell myCell = (HSSFCell) cellIter.next();
              cellStoreVector.addElement(myCell);
          }
          cellVectorHolder.addElement(cellStoreVector);
      }
    }catch (Exception e){e.printStackTrace(); }
    return cellVectorHolder;
}

private static void printCellDataToConsole(Vector dataHolder) {

    for (int i=0;i<dataHolder.size(); i++){
               Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
        for (int j=0; j < cellStoreVector.size();j++){
            HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
            String stringCellValue = myCell.toString();
            System.out.print(stringCellValue+"\t");
        }
        System.out.println();
    }
}

}

Answer 2

There's a document on Javaworld, It's POI-Fect showing you how this is achieved.

Example:

String fileName = "C:/MyExcelFile.xls"; // file we are interested in
POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(fileName));
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workbook.getSheetAt(0); //Get first Excel Sheet

//Iterate throw each row.
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
    HSSFRow row = (HSSFRow) rows.next();

    Iterator<Cell> cells = row.cellIterator();
    while (cells.hasNext()) {
        HSSFCell cell = (HSSFCell) cells.next();

        //Must do this, you need to get value based on the cell type
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                System.out.println(cell.getNumericCellValue());
            break;
            case HSSFCell.CELL_TYPE_STRING:
                System.out.println(cell.getStringCellValue());
            break;
            default: break;
        }
    }
}