Read Excel file containing multiple values in single column -Java

Question

I'm reading Excel file using Apache POI. My Excel table structure is like this

|2000s| 2001, 2003, 2008, 2009|

so for right hand side data, I require it to assign to 2000s

Till now I've implemented this way:

List<Class> list = new ArrayList<Class>();
        File file = new File(file_path);
        FileInputStream fis = new FileInputStream(file);

        //Create an instance of workbook which refers to an excel file
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        //This selects the 1st sheet 
        XSSFSheet sheet =  wb.getSheetAt(0);

        //Iterate through each row one by one
        Iterator<Row> itr = sheet.iterator();
        String newName = null; 
        String oldName = null;


        while(itr.hasNext()){
            Row nextRow = itr.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();

                newName =  nextRow.getCell(0).toString();

                if(nextRow.getCell(1).toString().contains(",")){
                    StringTokenizer st = new StringTokenizer(nextRow.getCell(1).toString(),",");
                    while(st.hasMoreTokens()){
                        oldName = st.nextToken();
                    }
                }
                else{
                     oldName = nextRow.getCell(1).toString();
                }
            }

            System.out.println();
        }   

When I compile, it throws me "Null pointer Exception" at nextRow.getCell(1) line.

I don't understand how do I map all comma values to 2000s.

This is working perfectly fine for normal data(without comma).

Answer 1

Comma values have been handled

I'm posting answer so somebody can get help from here.

What I've done is- added String Tokenizer class and if there's comma in the cell, it breaks the value with the comma delimiter.

Lets have a look at the code below

 while(itr.hasNext()){
            Row nextRow = itr.next();
            // For each row, iterate through all the columns
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            while (cellIterator.hasNext()) 
            {
               Cell cell = cellIterator.next();

                newName =  nextRow.getCell(0).toString();

                if(nextRow.getCell(1).toString().contains(",")){
                    StringTokenizer st = new StringTokenizer(nextRow.getCell(1).toString(),",");
                    while(st.hasMoreTokens()){
                        oldName = st.nextToken();
                    }
               }
                else{
                     oldName = nextRow.getCell(1).toString();
                    }
            }
            System.out.println();
        }

Here newName gets the value of 1st col.(2000s) and oldName gets the tokens based on ',' delimiter- In this case 2001, 2003, 2008, 2009

for all these values of oldName, newName 2000s would be mapped.

UPDATE: Reason I was getting 'Null Pointer Exception' there, because some cells at 2nd column(nextRow.getCell(1)) are null.

So whenever iterator reaches to the null cell, it throws Null Pointer Exception. Here you need to assign Missing Cell Policy

by

Cell cell2 = row.getCell(j,org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);

(It just treats null values as blank)

This way you can also resolve Null pointer exception in Excel while reading from Null values