# Lesen Excel via apache poi von excel Datei



## vector_ever (12. Juli 2013)

Hallo

ich versuchte Java code, um excel datei zu lesen, und so weit alles ok.

jetzt was ich möchte, dass die Zweite Spalte in Arraylist speichern um in Datenbank wie list[0], list[1] auf zurufen.

Mein Problem, ich weiß nicht genau wie ich genau die zweite Spalte bestimmen (wie kann man konkrete Spalte auswählen).

Außerdem möchte ich dazu die gespeicherte Daten in ArrayList in schleife passen und anzeigen


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

aber ich bekomme immer ein fehler 

```
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)
```

Die vollständige 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();
		}
	}
	}
```
was soll ich jetzt tun?


----------



## hendl (12. Juli 2013)

Hi 
Ich würde den Code ein wenig vereinfachen und kürzen 


```
package xls_read;

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:\\Noten.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");
								col.add(cell.getBooleanCellValue());
								break;
							case Cell.CELL_TYPE_NUMERIC:
								System.out.print(cell.getNumericCellValue()
										+ "\t\t");
								col.add(cell.getNumericCellValue());
								break;
							case Cell.CELL_TYPE_STRING:
								System.out.print(cell.getStringCellValue()
										+ "\t\t");
								col.add(cell.getStringCellValue());
								break;
							case Cell.CELL_TYPE_FORMULA:
								System.out.println(cell.getCellFormula());
								break;

							/**
							 * case Cell.CELL_TYPE_BLANK:
							 * System.out.println("BLANK"); break;
							 **/
							}

						}
					}
					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++) {
				System.out.println("coloum " + i + " : " + col.get(i));
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
```

Lg hendl


----------



## vector_ever (12. Juli 2013)

thanks for the quick response

but what i really need, how to add just the element of the second column to the Arraylist


----------



## hendl (12. Juli 2013)

Hi
Jetzt wird immer nur die 2 Spalte zur ArrayList hinzugefügt.

```
package xls_read;

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:\\Noten.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() < 9)) {
					{

						// For each row, iterate through each columns
						Iterator<Cell> cellIterator = row.cellIterator();
						while (cellIterator.hasNext()) {

							// Getting the cell contents
							Cell cell = cellIterator.next();
							if (cell.getColumnIndex() == 2)
								switch (cell.getCellType()) {
								case Cell.CELL_TYPE_BOOLEAN:
									col.add(cell.getBooleanCellValue());
									break;
								case Cell.CELL_TYPE_NUMERIC:
									col.add(cell.getNumericCellValue());
									break;
								case Cell.CELL_TYPE_STRING:
									col.add(cell.getStringCellValue());
									break;
								case Cell.CELL_TYPE_FORMULA:
									System.out.println(cell.getCellFormula());
									break;
								}

						}
					}
				}
			}

			file.close();

			// print the value of the cells which is stored in the the Arraylist
			for (int i = 0; i < col.size(); i++) {
				System.out.println("Row " + i + " : " + col.get(i));
			}

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
```

Lg hendl


----------



## vector_ever (12. Juli 2013)

vielen Dank ich auch so benutzt:


```
//store the values of the third Column
        Cell cell = row.getCell(2); 
       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());
           }
           }
```


----------

