Compare Two Excel Files in JavaComparing 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 FilesWe 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:
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 SheetsTo 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. Check If Both Excel Workbooks Have Sheets of Same NameHere 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. Check If Both Workbooks Have Same Number of Rows In All SheetsWe 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. Check If Both Workbooks Have Same Column for RowA 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. 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 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 Note: In the above program, assumed that file1.xls and file2.xls contain two sheets with the same structure and values.
Next TopicConsecutive Prime Sum Program in Java
