we can compare any
two excels of extension either xls or xlsx
import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Excelcomparison { ArrayList<String> listOfTerms =null; ArrayList<String> al =new ArrayList<String>(); ArrayList<String> finallist = new ArrayList<String>(); public Workbook checkFileFormat(String fileName){ Workbook hwb=null; FileInputStream checkFis=null; try{ checkFis=new FileInputStream(fileName); //Instantiate the Workbook using HSSFWorkbook hwb=(Workbook)new HSSFWorkbook(checkFis); Sheet sheet=hwb.getSheetAt(0); Iterator<Row> rows=sheet.rowIterator(); Iterator<Cell> cells=null; Row row=null; Cell cell=null; int check=0; //Read the file as HSSFWorkbook while(rows.hasNext()){ check++; row=(HSSFRow)rows.next(); cells=row.cellIterator(); while(cells.hasNext()){ cell=(HSSFCell)cells.next(); } if(check==2) break; } //Return HSSFWorkbook type object if there is no exception in reading the file using HSSFWorkbook return hwb; }catch(ClassCastException ce){ //Instantiate the Workbook using XSSFWorkbook in case of class cast exception Workbook xwb=null; try{ xwb=new XSSFWorkbook(checkFis); checkFis.close(); }catch(IOException e){ e.printStackTrace(); } return xwb; } catch(Exception e){ //Instantiate the Workbook using XSSFWorkbook in case of Exception while reading file through HSSFWorkbook Workbook xwb=null; try{ if(checkFis!=null) checkFis.close(); checkFis=null; checkFis=new FileInputStream(fileName); xwb=new XSSFWorkbook(checkFis); checkFis.close(); }catch(Exception ie){ return null; } return xwb; } } public void readExcel() { try { Workbook workbook = checkFileFormat("EXCEL1.xlsx"); listOfTerms =new ArrayList<String>(); Sheet sheet = workbook.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("here "+rows+" "+sheet.getSheetName()); for (int r = 0; r < rows; r++) { Row row = sheet.getRow(r); Cell cell =row.getCell((short)0); String cellValue = cell.getStringCellValue().trim(); listOfTerms.add(cellValue); } System.out.println(listOfTerms); Workbook workbook1 = checkFileFormat("EXCEL2.xls"); Sheet sheet1 = workbook1.getSheetAt(0); int rows1 = sheet1.getPhysicalNumberOfRows(); System.out.println("here "+rows1+" "+sheet.getSheetName()); for (int k = 0; k < rows1; k++) { Row row1 = sheet.getRow(k); Cell cell =row1.getCell((short)0); String cellValue2 = cell.getStringCellValue(); al.add(cellValue2); } System.out.println(al); for(int i=0; i<al.size(); i++) { if(!listOfTerms.contains(al.get(i))) { finallist.add(al.get(i)); } } for(int i=0; i<listOfTerms.size(); i++) { if(!al.contains(listOfTerms.get(i))) { finallist.add(listOfTerms.get(i)); } } System.out.println("Elements which are different: " +finallist); System.out.println(""); createSheet(); }catch(Exception e) { e.getStackTrace(); System.out.println(e.getMessage()); } } private void createSheet() { XSSFWorkbook bookout = new XSSFWorkbook(); try { int j=0; XSSFSheet sheet1= bookout.createSheet(); bookout.setSheetName(0,"Document Handles"); for(int m=0;m<finallist.size();m++) { XSSFRow row=sheet1.createRow(j++); row.createCell((short) 0).setCellValue(finallist.get(m)); } FileOutputStream fout = new FileOutputStream("OutputExcel.xlsx"); bookout.write(fout); fout.close(); System.out.println("file completed"); } catch(Exception e) { System.out.println(e.getMessage()); } } public static void main(String args[]){ Excelcomparison e = new Excelcomparison(); e.readExcel(); } }