Require help on Java Logic for Reading Excel file and populating to HashMap

Question

I am reading an excel data file from Java using apache POI API and populating an HashMap collection with excel Headers as Key and specified Row Data as Value of the Map. All Headers are always present but data corresponding to some headers may or may not be present.

Below is my code logic: First I populate all the headers to an ArrayList. Then I Iterate through the cells of the specified excel data row and I add the header value from the ArrayList, populated previously, and Data cell value from the row as key-value to an HashMap.

Below is my code:

ArrayList<String> headerList = new ArrayList<String>();
Map<String, String> dataMap = new LinkedHashMap<String, String>();
DateFormat df = new SimpleDateFormat("dd/MM/yyyy");

// Create object of XSSFWorkbook to get hold of excel file
FileInputStream fis = new FileInputStream(System.getProperty("user.dir") + "\\Resources\\TestData.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fis);
try
{
int noOfSheets = workbook.getNumberOfSheets();
for(int i=0; i<noOfSheets; i++)
    {
        
        if(workbook.getSheetName(i).equalsIgnoreCase(workSheet))
        {
            //Get access to sheet
            XSSFSheet sheet = workbook.getSheetAt(i);
            
            //Get access to all rows of sheet
            Iterator<Row> rows = sheet.iterator();
            
            Row headerRow = rows.next();
            Iterator<Cell> headerCells = headerRow.cellIterator();
            while(headerCells.hasNext())
            {
                headerList.add(headerCells.next().getStringCellValue());
            }
            
            // Get access to specific row
            while(rows.hasNext())
            {
                Row dataRow = rows.next();
                
                if(dataRow.getCell(0).getStringCellValue().equalsIgnoreCase(testCase))
                {
                    int j = 0;
                    //Get access to collection of cells of the identified rows
                    Iterator<Cell> dataCells = dataRow.cellIterator();
                    
                    //loop through all the cells of the row and add cell data to arraylist.
                    while(dataCells.hasNext())
                    {
                        Cell dataCell = dataCells.next();
                                                    
                        if(dataCell.getCellType()==CellType.STRING)
                        {   
                            //arrList.add(dataCell.getStringCellValue());
                            dataMap.put(headerList.get(j), dataCell.getStringCellValue());
                        }
                        else if(dataCell.getCellType()==CellType.NUMERIC)
                        {   
                            if(DateUtil.isCellDateFormatted(dataCell))
                            {
                                //arrList.add(df.format(dataCell.getDateCellValue()));
                                dataMap.put(headerList.get(j), df.format(dataCell.getDateCellValue()));
                            }
                            else
                            {
                                //arrList.add(NumberToTextConverter.toText(dataCell.getNumericCellValue()));
                                dataMap.put(headerList.get(j), NumberToTextConverter.toText(dataCell.getNumericCellValue()));
                            }
                        }
                        else if(dataCell.getCellType()==CellType.BOOLEAN)
                        {   
                            //arrList.add(Boolean.toString(dataCell.getBooleanCellValue()));
                            dataMap.put(headerList.get(j), Boolean.toString(dataCell.getBooleanCellValue()));
                        }
                        else
                            dataMap.put(headerList.get(j), null);
                        j++;
                    }
                }
            }
        }
    }

If there is no data in any cell then I do not want the corresponding header to be added in the Map. But when I iterate through the Data cells(dataCells.hasNext()), then the iterator does not return me null for that blank cell, instead it totally skips the Blank cell. So all headers are added but those cells where is no data are not added hence there is mismatch of Header-Data key-values.

Example: If data cell of Column 5 is blank then, "Value" of the column 5 header is mapped with the value of Data Column 6 as "Key-Value" in the HashMap. What I want is column 5 header should be skipped being added to the Map when column 5 data is blank. How do I resolve this logical mismatch issue? Excel file screenshot Debug scenario screenshot

Answer 1

Problem resolved using by calling getColumnIndex()