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.
Dear User, We are grateful for your interest in the Kodingwindow Android app. We intend to soon make it available on the Google Play Store. Currently, Android versions 10 and up require manual apk installations. Would you like to get the app?