Skip to main content

store certain column in ArrayList from excel file

No replies
vector_ever
Offline
Joined: 2013-07-12
Points: 0

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

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 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 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??

AttachmentSize
poi-3.9.jar1.78 MB
hi.xls25.5 KB