Compare Two Excel Workbooks Using Apache POI

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

I hope you are aware of a normal excel workbook. An Excel Workbook is also an Excel File. An Excel Workbook consists of one or multiple sheets. Each sheet consists of rows and columns. Each intersection point of Row and Column is called a Cell. Here is the link o know basics about Excel.

Steps to compare excel workbooks

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 below:-

  1. Do workbooks have same number of sheets?
  2. Do workbooks have same name of sheets? You can relax order of sheets depending on requirements. In this post I am relaxing order of sheets but keeping same order to assert cell data.
  3. Do workbooks have same numbers of rows?
  4. Do workbooks have same numbers of columns/cells of corresponding rows? Corresponding rows mean each row may have different number of columns.

When above 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.

We can get sheet name using getSheetName(int sheetIndex) method.

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.");

If you want to check if sheets are in order in both workbook, remove sort() lines of code from above. If you are relaxing sheet order, remember for cell comparison, get sheet by name not by index.

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 columns 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 which 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. This we can check using DateUtil.isCellDateFormatted(Cell cell).

Complete Code

package ReadExcel;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.testng.Assert;

public class MSE_CompareExcelFiles {

	/*
	This method compares if both excel files have same number of sheets and same
	name of sheets
	*/
	public void verifyIfExcelFilesHaveSameNumberAndNameOfSheets(Workbook workbook1, Workbook workbook2) {
		System.out.println("Verifying if both work books have same number of sheets.............");
		// 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);
		// Printing number of sheets in each work book
		System.out.println("Sheets in first work book : "+sheetsInWorkbook1);
		System.out.println("Sheets in second work book : "+sheetsInWorkbook2);
		System.out.println("Both work books have same number of sheets.........................");
		
		// Verify if sheets have same name in both workbooks
		// Sheets may not be in same order in both excel. So I am relaxing order of sheets condition.
		// Change i as required.
		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.");
		System.out.println("Sheet Names in first work book : "+sheetsNameOfWb1);
		System.out.println("Sheet Names in second work book : "+sheetsNameOfWb2);
		System.out.println("Both work books have same name of sheets.........................");
	}

	// This method compares if both excel files have same number of rows and corresponding columns
	public void verifySheetsInExcelFilesHaveSameRowsAndColumns(Workbook workbook1, Workbook workbook2) {
		System.out.println(
				"Verifying if both work books have same number of rows and columns in all sheets.............");
		int sheetCounts = workbook1.getNumberOfSheets();
		for (int i = 0; i < sheetCounts; i++) {
			Sheet s1 = workbook1.getSheetAt(i);
			Sheet s2 = workbook2.getSheetAt(i);
			int rowsInSheet1 = s1.getPhysicalNumberOfRows();
			int rowsInSheet2 = s2.getPhysicalNumberOfRows();
			Assert.assertEquals(rowsInSheet1, rowsInSheet2, "Sheets have different count of rows..");

			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..");
			}
		}
	}

	public void verifyDataInExcelBookAllSheets(Workbook workbook1, Workbook workbook2) {
		System.out.println("Verifying if both work books have same data.............");
		// Since we have already verified that both work books have same number of sheets so iteration can be done against any workbook sheet count
		int sheetCounts = workbook1.getNumberOfSheets();
		// So we will iterate through sheet by sheet
		for (int i = 0; i < sheetCounts; i++) {
			// Get sheet at same index of both work books
			Sheet s1 = workbook1.getSheetAt(i);
			Sheet s2 = workbook2.getSheetAt(i);
			System.out.println("*********** Sheet Name : "+s1.getSheetName()+"*************");
			// Iterating through each row
			int rowCounts = s1.getPhysicalNumberOfRows();
			for (int j = 0; j < rowCounts; j++) {
				// Iterating through each cell
				int cellCounts = s1.getRow(j).getPhysicalNumberOfCells();
				for (int k = 0; k < cellCounts; k++) {
					// Getting individual cell
					Cell c1 = s1.getRow(j).getCell(k);
					Cell c2 = s2.getRow(j).getCell(k);
					// Since cell have types and need o use different methods
					if (c1.getCellType().equals(c2.getCellType())) {
						if (c1.getCellType() == CellType.STRING) {
							String v1 = c1.getStringCellValue();
							String v2 = c2.getStringCellValue();
							Assert.assertEquals(v1, v2, "Cell values are different.....");
							System.out.println("Its matched : "+ v1 + " === "+ v2);
						}
						if (c1.getCellType() == CellType.NUMERIC) {
							// If cell type is numeric, we need to check if data is of Date type
							if (DateUtil.isCellDateFormatted(c1) | DateUtil.isCellDateFormatted(c2)) {
								// Need to use DataFormatter to get data in given style otherwise it will come as time stamp
								DataFormatter df = new DataFormatter();
								String v1 = df.formatCellValue(c1);
								String v2 = df.formatCellValue(c2);
								Assert.assertEquals(v1, v2, "Cell values are different.....");
								System.out.println("Its matched : "+ v1 + " === "+ v2);
							} else {
								double v1 = c1.getNumericCellValue();
								double v2 = c2.getNumericCellValue();
								Assert.assertEquals(v1, v2, "Cell values are different.....");
								System.out.println("Its matched : "+ v1 + " === "+ v2);
							}
						}
						if (c1.getCellType() == CellType.BOOLEAN) {
							boolean v1 = c1.getBooleanCellValue();
							boolean v2 = c2.getBooleanCellValue();
							Assert.assertEquals(v1, v2, "Cell values are different.....");
							System.out.println("Its matched : "+ v1 + " === "+ v2);
						}
					} else
					{
						// If cell types are not same, exit comparison  
						Assert.fail("Non matching cell type.");
					}
				}
			}
		}
		System.out.println("Hurray! Both work books have same data.");
	}
}

Sample Excel Files:-

Unit Tests

package ReadExcel;

import java.io.File;
import java.io.IOException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.testng.annotations.Test;

public class UnitTests {

	@Test
	public void differentNumberOfSheets() throws EncryptedDocumentException, IOException
	{
		String userDir = System.getProperty("user.dir");
		Workbook wb1 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithThreeSheets.xlsx"));
		Workbook wb2 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets - Data Actual.xlsx"));
		MSE_CompareExcelFiles mse_CompareExcelFiles = new MSE_CompareExcelFiles();
		mse_CompareExcelFiles.verifyIfExcelFilesHaveSameNumberAndNameOfSheets(wb1, wb2);
		
	}
	
	@Test
	public void sameNumberOfSheets() throws EncryptedDocumentException, IOException
	{
		String userDir = System.getProperty("user.dir");
		Workbook wb1 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets - Data Actual.xlsx"));
		Workbook wb2 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets- Data Match.xlsx"));
		MSE_CompareExcelFiles mse_CompareExcelFiles = new MSE_CompareExcelFiles();
		mse_CompareExcelFiles.verifyIfExcelFilesHaveSameNumberAndNameOfSheets(wb1, wb2);
	}
	
	
	@Test
	public void differentNumberOfRows() throws EncryptedDocumentException, IOException
	{
		String userDir = System.getProperty("user.dir");
		Workbook wb1 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets - Data Actual.xlsx"));
		Workbook wb2 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets- Extra Row.xlsx"));
		MSE_CompareExcelFiles mse_CompareExcelFiles = new MSE_CompareExcelFiles();
		mse_CompareExcelFiles.verifySheetsInExcelFilesHaveSameRowsAndColumns(wb1, wb2);
	}
	
	@Test
	public void differentNumberOfColumns() throws EncryptedDocumentException, IOException
	{
		String userDir = System.getProperty("user.dir");
		Workbook wb1 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets - Data Actual.xlsx"));
		Workbook wb2 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets- Extra Columns.xlsx"));
		MSE_CompareExcelFiles mse_CompareExcelFiles = new MSE_CompareExcelFiles();
		mse_CompareExcelFiles.verifySheetsInExcelFilesHaveSameRowsAndColumns(wb1, wb2);
	}
	
	@Test
	public void sameContentOfExcelFiles() throws EncryptedDocumentException, IOException {
		String userDir = System.getProperty("user.dir");
		Workbook wb1 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets - Data Actual.xlsx"));
		Workbook wb2 = WorkbookFactory.create(new File(userDir+"\\src\\test\\resources\\excelFiles\\CompareExcel\\ExcelFilesWithTwoSheets- Data Match.xlsx"));
		MSE_CompareExcelFiles mse_CompareExcelFiles = new MSE_CompareExcelFiles();
		mse_CompareExcelFiles.verifyIfExcelFilesHaveSameNumberAndNameOfSheets(wb1, wb2);
		mse_CompareExcelFiles.verifySheetsInExcelFilesHaveSameRowsAndColumns(wb1, wb2);
		mse_CompareExcelFiles.verifyDataInExcelBookAllSheets(wb1, wb2);
	
	}
	
	
}

Ouput

[RemoteTestNG] detected TestNG version 7.0.1
Verifying if both work books have same number of rows and columns in all sheets.............
Verifying if both work books have same number of rows and columns in all sheets.............
Verifying if both work books have same number of sheets.............
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithTwoSheets - Data Actual.xlsx
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithTwoSheets - Data Actual.xlsx
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithThreeSheets.xlsx
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithTwoSheets- Extra Row.xlsx
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithTwoSheets - Data Actual.xlsx
Cleaning up unclosed ZipFile for archive C:\Users\amomahaj\eclipse-workspace\MSE_ApachePOI\src\test\resources\excelFiles\CompareExcel\ExcelFilesWithTwoSheets- Extra Columns.xlsx
Verifying if both work books have same number of sheets.............
Sheets in first work book : 2
Sheets in second work book : 2
Both work books have same number of sheets.........................
Verifying if both work books have same name of sheets.............
Sheet Names in first work book : [Sheet1, Sheet2]
Sheet Names in second work book : [Sheet1, Sheet2]
Both work books have same name of sheets.........................
Verifying if both work books have same number of rows and columns in all sheets.............
Verifying if both work books have same data.............
*********** Sheet Name : Sheet1*************
Its matched : OrderDate === OrderDate
Its matched : Region === Region
Its matched : Rep === Rep
Its matched : Item === Item
Its matched : Units === Units
Its matched : UnitCost === UnitCost
Its matched : Total === Total
Its matched : 1/6/19 === 1/6/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Pencil === Pencil
Its matched : 95.0 === 95.0
Its matched : 1.99 === 1.99
Its matched : 189.05 === 189.05
Its matched : 1/23/19 === 1/23/19
Its matched : Central === Central
Its matched : Kivell === Kivell
Its matched : Binder === Binder
Its matched : 50.0 === 50.0
Its matched : 19.99 === 19.99
Its matched : 999.5 === 999.5
Its matched : 2/9/19 === 2/9/19
Its matched : Central === Central
Its matched : Jardine === Jardine
Its matched : Pencil === Pencil
Its matched : 36.0 === 36.0
Its matched : 4.99 === 4.99
Its matched : 179.64 === 179.64
Its matched : 2/26/19 === 2/26/19
Its matched : Central === Central
Its matched : Gill === Gill
Its matched : Pen === Pen
Its matched : 27.0 === 27.0
Its matched : 19.99 === 19.99
Its matched : 539.73 === 539.73
Its matched : 3/15/19 === 3/15/19
Its matched : West === West
Its matched : Sorvino === Sorvino
Its matched : Pencil === Pencil
Its matched : 56.0 === 56.0
Its matched : 2.99 === 2.99
Its matched : 167.44 === 167.44
Its matched : 4/1/19 === 4/1/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Binder === Binder
Its matched : 60.0 === 60.0
Its matched : 4.99 === 4.99
Its matched : 299.4 === 299.4
Its matched : 4/18/19 === 4/18/19
Its matched : Central === Central
Its matched : Andrews === Andrews
Its matched : Pencil === Pencil
Its matched : 75.0 === 75.0
Its matched : 1.99 === 1.99
Its matched : 149.25 === 149.25
Its matched : 5/5/19 === 5/5/19
Its matched : Central === Central
Its matched : Jardine === Jardine
Its matched : Pencil === Pencil
Its matched : 90.0 === 90.0
Its matched : 4.99 === 4.99
Its matched : 449.1 === 449.1
Its matched : 5/22/19 === 5/22/19
Its matched : West === West
Its matched : Thompson === Thompson
Its matched : Pencil === Pencil
Its matched : 32.0 === 32.0
Its matched : 1.99 === 1.99
Its matched : 63.68 === 63.68
Its matched : 6/8/19 === 6/8/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Binder === Binder
Its matched : 60.0 === 60.0
Its matched : 8.99 === 8.99
Its matched : 539.4 === 539.4
Its matched : 6/25/19 === 6/25/19
Its matched : Central === Central
Its matched : Morgan === Morgan
Its matched : Pencil === Pencil
Its matched : 90.0 === 90.0
Its matched : 4.99 === 4.99
Its matched : 449.1 === 449.1
Its matched : 7/12/19 === 7/12/19
Its matched : East === East
Its matched : Howard === Howard
Its matched : Binder === Binder
Its matched : 29.0 === 29.0
Its matched : 1.99 === 1.99
Its matched : 57.71 === 57.71
Its matched : 7/29/19 === 7/29/19
Its matched : East === East
Its matched : Parent === Parent
Its matched : Binder === Binder
Its matched : 81.0 === 81.0
Its matched : 19.99 === 19.99
Its matched : 1619.19 === 1619.19
Its matched : 7/29/19 === 7/29/19
Its matched : East === East
Its matched : Parent === Parent
Its matched : true === true
Its matched : 81.0 === 81.0
Its matched : 19.99 === 19.99
Its matched : 1619.19 === 1619.19
*********** Sheet Name : Sheet2*************
Its matched : OrderDate === OrderDate
Its matched : Region === Region
Its matched : Rep === Rep
Its matched : Item === Item
Its matched : Units === Units
Its matched : UnitCost === UnitCost
Its matched : Total === Total
Its matched : 1/6/19 === 1/6/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Pencil === Pencil
Its matched : 95.0 === 95.0
Its matched : 1.99 === 1.99
Its matched : 189.05 === 189.05
Its matched : 1/23/19 === 1/23/19
Its matched : Central === Central
Its matched : Kivell === Kivell
Its matched : Binder === Binder
Its matched : 50.0 === 50.0
Its matched : 19.99 === 19.99
Its matched : 999.5 === 999.5
Its matched : 2/9/19 === 2/9/19
Its matched : Central === Central
Its matched : Jardine === Jardine
Its matched : Pencil === Pencil
Its matched : 36.0 === 36.0
Its matched : 4.99 === 4.99
Its matched : 179.64 === 179.64
Its matched : 2/26/19 === 2/26/19
Its matched : Central === Central
Its matched : Gill === Gill
Its matched : Pen === Pen
Its matched : 27.0 === 27.0
Its matched : 19.99 === 19.99
Its matched : 539.73 === 539.73
Its matched : 3/15/19 === 3/15/19
Its matched : West === West
Its matched : Sorvino === Sorvino
Its matched : Pencil === Pencil
Its matched : 56.0 === 56.0
Its matched : 2.99 === 2.99
Its matched : 167.44 === 167.44
Its matched : 4/1/19 === 4/1/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Binder === Binder
Its matched : 60.0 === 60.0
Its matched : 4.99 === 4.99
Its matched : 299.4 === 299.4
Its matched : 4/18/19 === 4/18/19
Its matched : Central === Central
Its matched : Andrews === Andrews
Its matched : Pencil === Pencil
Its matched : 75.0 === 75.0
Its matched : 1.99 === 1.99
Its matched : 149.25 === 149.25
Its matched : 5/5/19 === 5/5/19
Its matched : Central === Central
Its matched : Jardine === Jardine
Its matched : Pencil === Pencil
Its matched : 90.0 === 90.0
Its matched : 4.99 === 4.99
Its matched : 449.1 === 449.1
Its matched : 5/22/19 === 5/22/19
Its matched : West === West
Its matched : Thompson === Thompson
Its matched : Pencil === Pencil
Its matched : 32.0 === 32.0
Its matched : 1.99 === 1.99
Its matched : 63.68 === 63.68
Its matched : 6/8/19 === 6/8/19
Its matched : East === East
Its matched : Jones === Jones
Its matched : Binder === Binder
Its matched : 60.0 === 60.0
Its matched : 8.99 === 8.99
Its matched : 539.4 === 539.4
Its matched : 6/25/19 === 6/25/19
Its matched : Central === Central
Its matched : Morgan === Morgan
Its matched : Pencil === Pencil
Its matched : 90.0 === 90.0
Its matched : 4.99 === 4.99
Its matched : 449.1 === 449.1
Its matched : 7/12/19 === 7/12/19
Its matched : East === East
Its matched : Howard === Howard
Its matched : Binder === Binder
Its matched : 29.0 === 29.0
Its matched : 1.99 === 1.99
Its matched : 57.71 === 57.71
Its matched : 7/29/19 === 7/29/19
Its matched : East === East
Its matched : Parent === Parent
Its matched : Binder === Binder
Its matched : 81.0 === 81.0
Its matched : 19.99 === 19.99
Its matched : 1619.19 === 1619.19
Hurray! Both work books have same data.
Verifying if both work books have same number of sheets.............
Sheets in first work book : 2
Sheets in second work book : 2
Both work books have same number of sheets.........................
Verifying if both work books have same name of sheets.............
Sheet Names in first work book : [Sheet1, Sheet2]
Sheet Names in second work book : [Sheet1, Sheet2]
Both work books have same name of sheets.........................
PASSED: sameContentOfExcelFiles
PASSED: sameNumberOfSheets
FAILED: differentNumberOfColumns
java.lang.AssertionError: Sheets have different count of columns.. expected [8] but found [7]
	at org.testng.Assert.fail(Assert.java:97)
	at org.testng.Assert.assertEqualsImpl(Assert.java:136)
	at org.testng.Assert.assertEquals(Assert.java:118)
	at org.testng.Assert.assertEquals(Assert.java:839)
	at ReadExcel.MSE_CompareExcelFiles.verifySheetsInExcelFilesHaveSameRowsAndColumns(MSE_CompareExcelFiles.java:77)
	at ReadExcel.UnitTests.differentNumberOfColumns(UnitTests.java:51)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:134)
	at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:597)
	at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:173)
	at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
	at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:816)
	at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:146)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at org.testng.TestRunner.privateRun(TestRunner.java:766)
	at org.testng.TestRunner.run(TestRunner.java:587)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
	at org.testng.SuiteRunner.run(SuiteRunner.java:286)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1187)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1109)
	at org.testng.TestNG.runSuites(TestNG.java:1039)
	at org.testng.TestNG.run(TestNG.java:1007)
	at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
	at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)

FAILED: differentNumberOfRows
java.lang.AssertionError: Sheets have different count of rows.. expected [16] but found [15]
	at org.testng.Assert.fail(Assert.java:97)
	at org.testng.Assert.assertEqualsImpl(Assert.java:136)
	at org.testng.Assert.assertEquals(Assert.java:118)
	at org.testng.Assert.assertEquals(Assert.java:839)
	at ReadExcel.MSE_CompareExcelFiles.verifySheetsInExcelFilesHaveSameRowsAndColumns(MSE_CompareExcelFiles.java:70)
	at ReadExcel.UnitTests.differentNumberOfRows(UnitTests.java:41)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:134)
	at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:597)
	at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:173)
	at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
	at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:816)
	at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:146)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at org.testng.TestRunner.privateRun(TestRunner.java:766)
	at org.testng.TestRunner.run(TestRunner.java:587)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
	at org.testng.SuiteRunner.run(SuiteRunner.java:286)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1187)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1109)
	at org.testng.TestNG.runSuites(TestNG.java:1039)
	at org.testng.TestNG.run(TestNG.java:1007)
	at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
	at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)

FAILED: differentNumberOfSheets
java.lang.AssertionError: Excel work books have different number of sheets. 
 Sheets in work book 1 : 3
 Number of sheets in work book 2 : 2 expected [2] but found [3]
	at org.testng.Assert.fail(Assert.java:97)
	at org.testng.Assert.assertEqualsImpl(Assert.java:136)
	at org.testng.Assert.assertEquals(Assert.java:118)
	at org.testng.Assert.assertEquals(Assert.java:839)
	at ReadExcel.MSE_CompareExcelFiles.verifyIfExcelFilesHaveSameNumberAndNameOfSheets(MSE_CompareExcelFiles.java:30)
	at ReadExcel.UnitTests.differentNumberOfSheets(UnitTests.java:19)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:134)
	at org.testng.internal.TestInvoker.invokeMethod(TestInvoker.java:597)
	at org.testng.internal.TestInvoker.invokeTestMethod(TestInvoker.java:173)
	at org.testng.internal.MethodRunner.runInSequence(MethodRunner.java:46)
	at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:816)
	at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:146)
	at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
	at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at org.testng.TestRunner.privateRun(TestRunner.java:766)
	at org.testng.TestRunner.run(TestRunner.java:587)
	at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
	at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
	at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
	at org.testng.SuiteRunner.run(SuiteRunner.java:286)
	at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
	at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
	at org.testng.TestNG.runSuitesSequentially(TestNG.java:1187)
	at org.testng.TestNG.runSuitesLocally(TestNG.java:1109)
	at org.testng.TestNG.runSuites(TestNG.java:1039)
	at org.testng.TestNG.run(TestNG.java:1007)
	at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
	at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
	at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)


===============================================
    Default test
    Tests run: 5, Failures: 3, Skips: 0
===============================================


===============================================
Default suite
Total tests run: 5, Passes: 2, Failures: 3, Skips: 0
===============================================


Note :-

  1. I have not covered for all Cell type. You can add as per your requirements.
  2. Formatting of cells like fonts, styles etc is not covered.

You can download/clone above sample project from here.

If you have any doubt, feel free to comment below.
If you like my posts, please like, comment, share and subscribe.
#ThanksForReading
#HappyLearning

Find all Selenium related post here, all API manual and automation related posts here and find frequently asked Java Programs here.

Many other topics you can navigate through menu.

4 thoughts on “Compare Two Excel Workbooks Using Apache POI

  1. package TestCases;

    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.util.Iterator;

    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.util.SystemOutLogger;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellType;
    //import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.testng.Assert;
    import org.testng.annotations.Test;

    public class CompareExcel {
    @Test
    //THIS METHOD WILL COMPARE IF WORKPSACE HAS EQUAL NUMBER OF ROWS AND CELLS
    public void CompareRowAndCells() throws IOException
    {
    //int i=0,j=0;
    FileInputStream inputStream=new FileInputStream(“/Users/xxx/Desktop/1ABC/TestDataTest.xlsx”);
    XSSFWorkbook wb1=new XSSFWorkbook(inputStream);
    XSSFSheet sheet1=wb1.getSheet(“Sheet1”);
    int RowCount1= sheet1.getLastRowNum()-sheet1.getFirstRowNum();

    FileInputStream inputStream2=new FileInputStream(“/Users/xxx/Desktop/1ABC/TestDataTest2.xlsx”);
    XSSFWorkbook wb2=new XSSFWorkbook(inputStream2);
    XSSFSheet sheet2=wb2.getSheet(“Sheet1″);

    int RowCount2= sheet2.getLastRowNum()-sheet2.getFirstRowNum();
    //Compares if both excel have equal now of rows
    Assert.assertEquals(RowCount1, RowCount2,”Not sames”);

    //compares if both excel file has same Row Number
    Iterator rowInSheet1 =sheet1.rowIterator();
    Iterator rowInSheet2 =sheet2.rowIterator();
    while(rowInSheet1.hasNext()) {
    int cellcounts1 =rowInSheet1.next().getPhysicalNumberOfCells();
    int cellcounts2 =rowInSheet2.next().getPhysicalNumberOfCells();
    Assert.assertEquals(cellcounts1, cellcounts2,”Not same”);

    for(int i=0;i<RowCount1;i++) {
    for(int j=0;j Numeric 1->String 4->Boolean
    int type2= c2.getCellType();

    if(type1==type2)
    {
    if(type1==1)
    {
    if(c1.getStringCellValue().equals(c2.getStringCellValue())) {
    System.out.println(c1.getStringCellValue()+”, “+c2.getStringCellValue()+”–>”+” Match”);
    }}
    if(type1==0)
    {
    if(c1.getNumericCellValue()==c2.getNumericCellValue())
    {
    System.out.println(c1.getNumericCellValue() +”, “+c2.getNumericCellValue()+” –>”+”Match”);
    }
    else {System.out.println(“NOT MATCHED”);}
    }
    if(type1==4)
    {
    if(c1.getBooleanCellValue()==c2.getBooleanCellValue())
    {
    System.out.println(c1.getBooleanCellValue()+”, “+c2.getBooleanCellValue()+”–> “+”Match”);
    }
    else
    {
    System.out.println(c1.getBooleanCellValue()+”, “+ c2.getBooleanCellValue()+”–> “+”Not Matched”);
    }
    }

    }}}}}}

Leave a Reply

Your email address will not be published. Required fields are marked *