Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 7 of 7

Thread: store certain column in ArrayList from excel file

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

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


  2. #2
    Junior Member
    Join Date
    Jul 2013
    Location
    Bucharest
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

    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.

  3. #3
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

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

  4. #4
    Junior Member
    Join Date
    Jul 2013
    Location
    Bucharest
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

    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 ?

  5. #5
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

    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());
    	            }
    	            }

  6. #6
    Junior Member
    Join Date
    Jul 2013
    Location
    Bucharest
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

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

  7. #7
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Re: store certain column in ArrayList from excel file

    thanks you again

Similar Threads

  1. Replies: 7
    Last Post: May 28th, 2013, 10:03 AM
  2. How to store 10 inputs in an arraylist?
    By Bray92 in forum What's Wrong With My Code?
    Replies: 4
    Last Post: March 20th, 2011, 11:22 PM
  3. insert(embed) a file object (.txt file) in MS excel sheet using java.
    By jyoti.dce in forum What's Wrong With My Code?
    Replies: 1
    Last Post: August 12th, 2010, 09:16 AM
  4. Excel Column Letter Referencing
    By aussiemcgr in forum What's Wrong With My Code?
    Replies: 3
    Last Post: July 21st, 2010, 04:47 PM
  5. How can i store ArrayList objects in Access database
    By frankycool in forum JDBC & Databases
    Replies: 0
    Last Post: November 4th, 2009, 12:44 AM