Thread: store certain column in ArrayList from excel file

Hello every body

I tried to write java code to read excel file, and so far was ok

Now i have two problems:

1-i want to store the data elements of the second column in the excel file in ArrayList to call it later with database list[0], list[1]... .

the problem hier is how to determine the column number, i can store and add the elements of the whole excel file or for elements of certain rows (first row or second or the first three rows ...ect.) but i want do it for certain column!!

2- the seconed Problem is, that i want to pass the Arraylist to loop and print the element out to display it
XML Code:

for (int i = 0; i < col.size(); i++){
               String item = (String) col.get(i);
               System.out.println("coloum " + i + " : " + item);
            }

but some thing go wrong and i get the following error

Exception in thread "main" java.lang.ClassCastException: org.apache.poi.hssf.usermodel.HSSFRichTextString cannot be cast to java.lang.String
    at Reader.main(Reader.java:97)

the Code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
 
 
 
public class Reader {
    public static void main(String[] args) {
 
        ArrayList  col = new ArrayList();
    try {
 
        FileInputStream file = new FileInputStream(new File("d:\\hi.xls"));
 
        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);
 
        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);
 
        //Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()) {
            Row row = rowIterator.next();
 
            //display from the third row until 5th
            if(row.getRowNum()>2 && (row.getRowNum()<5)){
            {
 
 
 
            //For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) {
 
                //Getting the cell contents
                Cell cell = cellIterator.next();
 
                switch(cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
 
                   /** case Cell.CELL_TYPE_BLANK:
                        System.out.println("BLANK");
                        break;
                        **/
                }
 
                //add the values of the cell to the Arraylist 
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) 
                {
                System.out.print(cell.getNumericCellValue());
                col.add(cell.getNumericCellValue());
                } 
                else if (cell.getCellType() == Cell.CELL_TYPE_STRING) 
                {
                System.out.print(cell.getRichStringCellValue());
                col.add(cell.getRichStringCellValue());
                } 
                else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) 
                {
                System.out.print(cell.getBooleanCellValue());
                col.add(cell.getBooleanCellValue());
                }
            }
 
            }
            }
            System.out.println("");
 
        }
 
        file.close();
 
        //print the value of the cells which is stored in the the Arraylist
        System.out.println("");
        for (int i = 0; i < col.size(); i++){
               String item = (String) col.get(i);
               System.out.println("coloum " + i + " : " + item);
            }
 
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    }

so now how can i fix it??

Hello vector_ever,
I'm also a kind of junior, but I'll try to help. (I cant try your code since I'm running in a JDK14 enviroment right now .. sory for that)
1. You have done:

ArrayList col= new ArrayList();

2.then:

col.add(cell.getSOME_TYPE_OF_VALUE());

3.then:

for (int i = 0; i < col.size(); i++){
 String item = (String) col.get(i);
 System.out.println("coloum " + i + " : " + item);
}

please try to: do this change:

Object item = col.get(i);

I see that you have there a generic ArrayList with three Object Types. Boolean, Some_Kind_Of_String and a Numeric(?).
And this will help with the Boolean object too.

That's one thing.
Another Ideea:
instead of:

Cell cell = cellIterator.next();

try this:

HSSFCell cell = cellIterator.next();

then do this - where needed:

col.add(cell.getRichStringCellValue().getString());

Hope it will somehow help you.

Hello khrypt,

thank you very much

Object item = col.get(i);

was right decision, so it works.

now stay the another part of the problem, how to store the every element just from the certain column, for example second column??

Sorry vector_ever, it seems I don't understand your question/need.
You already read the file.
You can retrieve data from rows, from columns...
Please explain this:

how to store the every element just from the certain column, for example second column

What is the problem? the: second column ?

the problem was that i want to add just the elements of the second column to the arraylist

any way i have found the solution

//store the values of the third Column
	         Cell cell = row.getCell(1); 
	 	    	   if(cell != null){
	            //add the values of the cell to the Arraylist 
	            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) 
	            {
	            col.add(cell.getNumericCellValue());
	            } 
	            else if (cell.getCellType() == Cell.CELL_TYPE_STRING) 
	            {
	            col.add(cell.getRichStringCellValue().getString());
	            } 
	            else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) 
	            {
	            col.add(cell.getBooleanCellValue());
	            }
	            }

. Ah, as I initially thought, the index was your problem...
row.getCell(0) - first column
row.getCell(1) - second column.
anyway, cheers.

thanks you again