Firstly, you need to download the following jar files. Once downloaded, you can import it by right-clicking on the project -> Properties -> Java Build Path -> Libraries -> Add External JARs... 1. Dom4J 2. POI OOXML Schemas 3. POI OOXML 4. Apache POI 5. XmlBeans
Lastly, you need to create the two Excel files with data in it. Please refer the screenshot in output section.
How to compare two Excel sheets using Apache POI
KW.java
importjava.io.File;importjava.io.FileInputStream;importorg.apache.poi.hssf.usermodel.HSSFCell;importorg.apache.poi.xssf.usermodel.XSSFCell;importorg.apache.poi.xssf.usermodel.XSSFRow;importorg.apache.poi.xssf.usermodel.XSSFSheet;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;classKW{publicstaticvoidmain(String[]args){try{FileInputStreamexcellFile1=newFileInputStream(newFile("D:\\ExcelComparison\\Workbook-1.xlsx"));FileInputStreamexcellFile2=newFileInputStream(newFile("D:\\ExcelComparison\\Workbook-2.xlsx"));XSSFWorkbookworkbook1=newXSSFWorkbook(excellFile1);XSSFWorkbookworkbook2=newXSSFWorkbook(excellFile2);XSSFSheetsheet1=workbook1.getSheetAt(0);XSSFSheetsheet2=workbook2.getSheetAt(0);if(compareTwoSheets(sheet1,sheet2)){System.out.println("\nTwo Excelsheets are Equal");}else{System.out.println("\nTwo Excelsheets are Not Equal");}excellFile1.close();excellFile2.close();}catch(Exceptione){e.printStackTrace();}}publicstaticbooleancompareTwoSheets(XSSFSheetsheet1,XSSFSheetsheet2){intfirstRow1=sheet1.getFirstRowNum();intlastRow1=sheet1.getLastRowNum();booleanequalSheets=true;for(inti=firstRow1;i<=lastRow1;i++){System.out.print("___________________________");System.out.println("\nComparing Row "+i);System.out.println("___________________________");XSSFRowrow1=sheet1.getRow(i);XSSFRowrow2=sheet2.getRow(i);if(!compareTwoRows(row1,row2)){equalSheets=false;System.out.println(" Row "+i+" | Not Equal");}else{System.out.println(" Row "+i+" | Equal");}}returnequalSheets;}publicstaticbooleancompareTwoRows(XSSFRowrow1,XSSFRowrow2){if((row1==null)&&(row2==null)){returntrue;}elseif((row1==null)||(row2==null)){returnfalse;}intfirstCell1=row1.getFirstCellNum();intlastCell1=row1.getLastCellNum();booleanequalRows=true;for(inti=firstCell1;i<=lastCell1;i++){XSSFCellcell1=row1.getCell(i);XSSFCellcell2=row2.getCell(i);if(!compareTwoCells(cell1,cell2)){equalRows=false;System.err.println("Cell "+i+" | Not Equal");}else{System.out.println("Cell "+i+" | Equal");}}returnequalRows;}publicstaticbooleancompareTwoCells(XSSFCellcell1,XSSFCellcell2){if((cell1==null)&&(cell2==null)){returntrue;}elseif((cell1==null)||(cell2==null)){returnfalse;}booleanequalCells=false;inttype1=cell1.getCellType();inttype2=cell2.getCellType();if(type1==type2){if(cell1.getCellStyle().equals(cell2.getCellStyle())){switch(cell1.getCellType()){caseHSSFCell.CELL_TYPE_FORMULA:if(cell1.getCellFormula().equals(cell2.getCellFormula())){equalCells=true;}break;caseHSSFCell.CELL_TYPE_NUMERIC:if(cell1.getNumericCellValue()==cell2.getNumericCellValue()){equalCells=true;}break;caseHSSFCell.CELL_TYPE_STRING:if(cell1.getStringCellValue().equals(cell2.getStringCellValue())){equalCells=true;}break;caseHSSFCell.CELL_TYPE_BLANK:if(cell2.getCellType()==HSSFCell.CELL_TYPE_BLANK){equalCells=true;}break;caseHSSFCell.CELL_TYPE_BOOLEAN:if(cell1.getBooleanCellValue()==cell2.getBooleanCellValue()){equalCells=true;}break;caseHSSFCell.CELL_TYPE_ERROR:if(cell1.getErrorCellValue()==cell2.getErrorCellValue()){equalCells=true;}break;default:if(cell1.getStringCellValue().equals(cell2.getStringCellValue())){equalCells=true;}break;}}else{returnfalse;}}else{returnfalse;}returnequalCells;}}
Dear User, Thank you for visitng KodingWindow. If you are interested in technical articles, latest technologies, and our journey further, please follow us on LinkedIn.