How to compare two excel sheets data in selenium

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<String> sheetsNameOfWb1 = new ArrayList<>(); List<String> 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<Row> rowInSheet1 = s1.rowIterator(); Iterator<Row> 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<String> sheetsNameOfWb1 = new ArrayList<>(); List<String> 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<Row> rowInSheet1 = s1.rowIterator(); Iterator<Row> 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:-

ExcelFilesWithThreeSheetsDownload

ExcelFilesWithTwoSheets-Data-ActualDownload

ExcelFilesWithTwoSheets-Data-MatchDownload

ExcelFilesWithTwoSheets-Data-MismatchDownload

ExcelFilesWithTwoSheets-Extra-ColumnsDownload

ExcelFilesWithTwoSheets-Extra-RowDownload

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