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:
but some thing go wrong and i get the following errorfor (int i = 0; i < col.size(); i++){ String item = (String) col.get(i); System.out.println("coloum " + i + " : " + item); }
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??