Results 1 to 9 of 9
  1. #1
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default store certain column in ArrayList

    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
    XML Code:
    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
    XML 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??

  2. #2
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,030
    Rep Power
    6

    Default Re: store certain column in ArrayList

    Quote Originally Posted by vector_ever View Post
    Hello every body

    2- the seconed Problem is, that i want to pass the Arraylist to loop and print the element out to display it
    Java Code:
    for (int i = 0; i < col.size(); i++){
    	    	   String item = (String) col.get(i);
    XML Code:
    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)
    Apparently that col.get(i) does not return a String object but a HSSFRichTextString object, so the cast can't work. The exception tells you exactly that. You'll have to investigate the HSSFRichTextString class to get a String representation from it.

    PS: don't use HTML tags but CODE tags to format code.

  3. #3
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: store certain column in ArrayList

    ok, i know that cast dosn't wor
    so any idea for the solution??

  4. #4
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,030
    Rep Power
    6

    Default Re: store certain column in ArrayList

    ... read the POI documentation? Check the POI javadocs? Get informed.

  5. #5
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: store certain column in ArrayList

    in the Loop of the ArrayList, i will use
    Java Code:
    Object item = col.get(i);
    instead of
    String item = (String) col.get(i);
    now still the main problem of how to add the values of the certain column( 2nd column) to the array list, i don't want to add the whole elements of the excel file to the arraylist just certain columns (one or more).
    any help?

  6. #6
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: store certain column in ArrayList

    i found the solution

    use the following method:
    Java Code:
    //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());
               }
               }

  7. #7
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,030
    Rep Power
    6

    Default Re: store certain column in ArrayList

    Good job. But that will give you a list of objects of different types (Double, String, Boolean). Is that really what you want, or did you want only String objects?

  8. #8
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: store certain column in ArrayList

    i prefer to get only String, but i don't know how to do it

  9. #9
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,030
    Rep Power
    6

    Default Re: store certain column in ArrayList

    But not knowing is not a problem - you solve it by finding out. You will want to convert those different types to a String then. That seems like something that is easy to figure out with a bit of googling and perhaps checking the javadocs of the different classes you have (Double, Integer, Boolean).

    Of course it is never as easy as you say it is. For example a number is something very flexible in how you display it or want to display it; its also nothing standard, it changes from country to country. Do you want to show it with 10 fraction positions, or do you want to round it down to 2 fractions only? What separator character do you want to use? Do you want a number to print like '5.22' or '5,22'. Perhaps groups are important too so you want to display a number like '1.000.000,23'. Maybe it is a currency and you want to display the currency symbol also?

    Seems like a number does not only need conversion but also formatting.

    Google away.

Similar Threads

  1. Replies: 3
    Last Post: 11-01-2011, 07:58 PM
  2. How to Store ArrayList into an Object?
    By TheGame in forum New To Java
    Replies: 6
    Last Post: 10-10-2010, 10:38 PM
  3. How can i store ArrayList objects in Access database
    By frankycool in forum Advanced Java
    Replies: 3
    Last Post: 11-04-2009, 06:55 AM
  4. Replies: 3
    Last Post: 03-28-2009, 05:02 AM
  5. how to store an ArrayList<string> into cookies?
    By perplexingtrax in forum Java Servlet
    Replies: 7
    Last Post: 01-12-2009, 12:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •