we can compare any
two excels of extension either xls or xlsx
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 | 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(); }} |
