Compare Two Excel Files in Java

Comparing two or multiple excel workbooks is very common requirement specially for automated test scenarios. In this section, we will learn how to compare two excel workbooks or to verify if two workbooks have same data set.

Comparing Excel Files

We must not start comparing excel sheets at cell level or cell data directly. We should first check for basic conditions to be true then proceed for cell comparison.

Basic ordered conditions to be checked first are as follows:

Do workbooks have same number of sheets?
We can change sheet order as needed. I am changing sheet order while keeping the same order for cell data.
Do workbooks have same numbers of rows?
Do workbooks have same numbers of columns/cells of corresponding rows? Corresponding rows mean each row may have different number of columns.

When above conditions are checked are passed then we should go for cell data comparison. If either check fails then we should not proceed further.

Check If Both Excel Workbooks Have Same Number of Sheets

To get number of sheets in a workbook, we need to use getNumberOfSheets() of Workbook interface provided by Apache POI. Workbook interface is implemented by major classes like XSSFWorkbook , HSSFWorkbook.

// Get total sheets count from first excel file
int sheetsInWorkbook1 = workbook1.getNumberOfSheets();
// Get total sheets count from second excel file
int sheetsInWorkbook2 = workbook2.getNumberOfSheets();
// Compare if both excel files have same number of sheets
Assert.assertEquals(sheetsInWorkbook1, sheetsInWorkbook2,"Excel work books have different number of sheets. \n " + "Sheets in work book 1 : "+sheetsInWorkbook1 +"\n "+ "Number of sheets in work book 2 : "+sheetsInWorkbook2);

Check If Both Excel Workbooks Have Sheets of Same Name

Here we have two scenarios.

First - Just verify if sheet names are same irrespective of sheet orders.

Second - Verify sheet names are same and appears in same order.

System.out.println("Verifying if both work books have same name of sheets.............");
List sheetsNameOfWb1 = new ArrayList<>();
List sheetsNameOfWb2 = new ArrayList<>();
// Since we have already verified that both work books have same number of sheets so iteration can be done against any workbook sheet count
for (int i = 0; i < sheetsInWorkbook1; i++) {
// Retrieving sheet names from both work books and adding to different lists
sheetsNameOfWb1.add(workbook1.getSheetName(i));
sheetsNameOfWb2.add(workbook2.getSheetName(i));
}
// Since I am relaxing same sequence of sheets. 
Collections.sort(sheetsNameOfWb1);
Collections.sort(sheetsNameOfWb2);
Assert.assertEquals(sheetsNameOfWb1, sheetsNameOfWb2, "Provided excel work books have different name of sheets.");

Check If Both Workbooks Have Same Number of Rows In All Sheets

We need to check if number of rows in each sheet of workbooks are same. If sheet1 of workbook 1 has 10 rows and sheet2 has 15 rows then workbook 2 should also have 10 rows in sheet1 and 15 rows in sheet2.

To get number of rows in a sheet we need to use getPhysicalNumberOfRows() method of Sheet interface. This method returns the number of physically defined rows (NOT the number of rows in the sheet). If we have 10 rows in a sheet, each row will be indexed or numbered staring from zero. Same we need to do for all sheets. Below is an example code for one sheet.

int rowsInSheet1 = s1.getPhysicalNumberOfRows();
int rowsInSheet2 = s2.getPhysicalNumberOfRows();
Assert.assertEquals(rowsInSheet1, rowsInSheet2, "Sheets have different count of rows..");

Check If Both Workbooks Have Same Column for Row

A row can have different number of columns. So we need to check for each row in all sheets. To iterate through rows, we can use rowIterator() method of Sheet interface that returns an iterator of the physical rows. During iteration of rows, we need to get physical number of cells. Same logic need to do for all sheets.

Iterator rowInSheet1 = s1.rowIterator();
Iterator rowInSheet2 = s2.rowIterator();
while (rowInSheet1.hasNext()) {
	int cellCounts1 = rowInSheet1.next().getPhysicalNumberOfCells();
	int cellCounts2 = rowInSheet2.next().getPhysicalNumberOfCells();
	Assert.assertEquals(cellCounts1, cellCounts2, "Sheets have different count of columns..");
}

Now we need to check for cell data and assert.

Each cell has a type like String, Numeric, Boolean etc. To get value as per type, different method needs to be used. For example, to get data from a Numeric cell type, we need to use getNumericCellValue() and getStringCellValue() for String cell type.

If a cell consists Date, we do not have any such type for Cell. A Cell with date falls under Numeric type. We need to check if cell is date formatted. We can check using DateUtil.isCellDateFormatted(Cell cell).

CompareExcelFiles.java

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public class CompareExcelFiles {
    public static void main(String[] args) throws IOException {
        File excelFile1 = new File("file1.xls");
        File excelFile2 = new File("file2.xls");
        Workbook workbook1 = new HSSFWorkbook(excelFile1);
        Workbook workbook2 = new HSSFWorkbook(excelFile2);
        // Compare the number of sheets
        int numSheets1 = workbook1.getNumberOfSheets();
        int numSheets2 = workbook2.getNumberOfSheets();
        if (numSheets1 != numSheets2) {
            System.out.println("The two files have different number of sheets");
            return;
        }
        for (int i = 0; i < numSheets1; i++) {
            Sheet sheet1 = workbook1.getSheetAt(i);
            Sheet sheet2 = workbook2.getSheetAt(i);
            // Compare the number of rows
            int numRows1 = sheet1.getPhysicalNumberOfRows();
            int numRows2 = sheet2.getPhysicalNumberOfRows();
            if (numRows1 != numRows2) {
                System.out.println("The two sheets have different number of rows");
                continue;
            }
            // Compare the cell values
            for (int j = 0; j < numRows1; j++) {
                for (int k = 0; k < sheet1.getRow(j).getPhysicalNumberOfCells(); k++) {
                    Cell cell1 = sheet1.getRow(j).getCell(k);
                    Cell cell2 = sheet2.getRow(j).getCell(k);
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        System.out.println("The cell values at (" + j + ", " + k + ") are the same");
                    } else {
                        System.out.println("The cell values at (" + j + ", " + k + ") are different");
                    }
                }
            }
        }
        workbook1.close();
        workbook2.close();
    }
}

Output:

The cell values at (0, 0) are the same
The cell values at (0, 1) are the same
The cell values at (0, 2) are the same
The cell values at (0, 3) are the same
The cell values at (1, 0) are the same
The cell values at (1, 1) are the same
The cell values at (1, 2) are the same
The cell values at (1, 3) are the same
The cell values at (2, 0) are the same
The cell values at (2, 1) are the same
The cell values at (2, 2) are the same
The cell values at (2, 3) are the same
The cell values at (3, 0) are the same
The cell values at (3, 1) are the same
The cell values at (3, 2) are the same
The cell values at (3, 3) are the same