How to compare equality of two rows in Excel file in Java?

Question

Is there any built in function that can be used to easily compare two rows in an Excel file. I am using Apache POI.

        try {

            Workbook wb1 = WorkbookFactory.create(new File(file1Path));
            Sheet sheet1 = wb1.getSheetAt(0);

            Workbook wb2 = WorkbookFactory.create(new File(file2Path));
            Sheet sheet2 = wb2.getSheetAt(0);

            for (Row myrow : sheet1) {
                if (myrow.getRowNum() == 0) {
                    // add entire row to a sheet for 'found in file 1 but not file 2' -> sheet 0
                    write(myrow, output_filename_path, dstSheetNumberInOutputFile);
                    continue;
                }
                // look for this key in the other sheet
                for (Row otherRow : sheet2) {
                    if (rowsAreEqual(myrow, otherRow)) {
                        write(myrow, output_filename_path, dstSheetNumberInOutputFile);
                        break;
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

So I have an if statement that has the function rowsAreEqual() needs to compare equality of the two rows. How can I make the rowsAreEqual() function?

I tried this but hasn't worked:

    private static boolean rowsAreEqual(Row myrow, Row otherRow) {
        int equalCount = 0;
        System.out.println("last cell num: " + myrow.getLastCellNum());
        for(int i=0; i < myrow.getLastCellNum(); i++){
            if(myrow.getCell(i) == otherRow.getCell(i)){
                equalCount++;
                System.out.println("Cells are the same: " + myrow.getCell(i) + " && " + otherRow.getCell(i));
            }
        }

        if(equalCount == myrow.getLastCellNum()){
            return true;
        }
        return false;
    }

Each `Row` in apache poi contains `Cell` which can be accessed by index or can be iterated over. You can write a small utility to iterate over these cell values. Pls refer https://stackoverflow.com/a/16656068/7803797 — Chaitanya, Jul 25 '21 at 17:05
What about compare numbers of cells in rows and if they are different return false. Else, if are equals, iterate over them and call something like this: `row1.getCell(i).getStringCellValue().equals(row2.getCell(i).getStringCellValue())`? — Matt, Jul 25 '21 at 17:10

score 0 · Accepted Answer · answered Jul 25 '21 at 17:23

I figured it out like this. I had to make an extra function called getCellContentsAsString() so that I could compare the cells correctly:

    private static boolean rowsAreEqual(Row myrow, Row otherRow) {
        int equalCount = 0;
        System.out.println("last cell num: " + myrow.getLastCellNum());
        for(int i=0; i < myrow.getLastCellNum(); i++){
            System.out.println(myrow.getCell(i) + " is the cell content in file1");
            System.out.println(otherRow.getCell(i) + " is the cell content in file2");
            Cell c1 = myrow.getCell(i);
            Cell c2 = otherRow.getCell(i);
            
            String s1 = getCellContentAsString(c1);
            String s2 = getCellContentAsString(c2);
            
            if(s1.equals(s2)){
                equalCount++;
                System.out.println("Cells are the same: " + myrow.getCell(i) + " && " + otherRow.getCell(i));
            }
        }

        if(equalCount == myrow.getLastCellNum()){
            return true;
        }
        return false;
    }

    private static String getCellContentAsString(Cell cell) {
        String data = null;
        if(cell.getCellType()==CellType.STRING) {
            data = cell.getStringCellValue(); 
        }
        else if(cell.getCellType()==CellType.NUMERIC) {
            data = String.valueOf(cell.getNumericCellValue());
        }
        return data;
    }

hc_dev · Answer 2 · 2021-07-25T21:38:24.717

You could abstract to comparing a range of cells. This would allow you comparing either rows or columns, or individual regions (e.g. A1:Z256) - a so called Range.

So the object to compare - to be equal in contents - is CellRange.

In Java the boolean equals() method of any object relies on their int hashCode() function. In a utility-class called RangeContentsUtils you could implement both:

public class RangeContentsUtils {

  public static boolean contentsEquals(CellRange rangeA, CellRange rangeB) {
    if (rangeA == null || rangeB == null) {
        return false;
    }
    if (rangeA.size() != rangeB.size()) {
        return false;
    }
    // alternative would be:
    // return stringContentsOf(rangeA).hashCode() == stringContentsOf(rangeB).hashCode();

    return contentsToString(rangeA).equals(contentsToString(rangeB));
  }

  public static String contentsToString(CellRange range) {
    StringBuilder sb = new StringBuilder();
    
    Iterator<Cell> cellIterator = range.iterator();
    while(cellIterator.hasNext()) {
      Cell cell = cellIterator.next();
      switch(cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
          sb.append(cell.getBooleanCellValue());
          break;
        case Cell.CELL_TYPE_NUMERIC:
          sb.append(cell.getNumericCellValue());
          break;
        case Cell.CELL_TYPE_STRING:
          sb.append(cell.getStringCellValue());
          break;
      }
      sb.append("\0"); // use NUL char as cell-separator 
    }

    return sb.toString();
  }

}

Now your row-comparison could use this utility class.

See also: Compare specific row or column of xls sheet using java and Apache POI

How to compare equality of two rows in Excel file in Java?

2 Answers2