CODEWITHSUNDEEP
javaapache-poidata-formatters
Ajinkya kadam
Ajinkya kadam

Reputation: 602

DataFormatter is not reading blank cell and left shifting data in excel in java

DataFormatter is not reading blank cell and left shifting data wherever the cell is blank in excel in java.

Please help on this so that the blank column can be read as a empty string and the next cell data in row should not be left shifted

   Workbook  wb = new XSSFWorkbook (Inputexcelfile);            
            //Get first/desired sheet from the workbook
            Sheet  ws = wb.getSheetAt(0);
            
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = ws.iterator();
            int roWnum=0;
           
         
         
            while (rowIterator.hasNext()) 
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                int cellnum=0;
                
               
                
                while (cellIterator.hasNext()) 
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                   DataFormatter fmt = new DataFormatter();
                    String valueAsSeenInExcel = fmt.formatCellValue(cell).trim();
                    System.out.print(valueAsSeenInExcel + "\t");
                    
                 
                   }
}

Upvotes: 0

Views: 50

Answers (1)

DuncG
DuncG

Reputation: 15086

When a cell is blank, you don't see a corresponding Cell in the row iteration. So if cell A1+A3 are set and A2 is blank, you get 2 Cells back not three. Change the print to show the row and cell index will confirm:

String valueAsSeenInExcel = fmt.formatCellValue(cell).trim();
System.out.format("[%d, %d] valueAsSeenInExcel %s%n", cell.getRowIndex(), cell.getColumnIndex(), valueAsSeenInExcel);

=> Should print 2 cells for A1/A3, and nothing for A2

[0, 0] valueAsSeenInExcel {value of A1}
[0, 2] valueAsSeenInExcel {value of A3}

You can use the difference in value of cell.getColumnIndex() to know how many blank cells were skipped over since previous column.

Upvotes: 1

Related Questions