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();
}
}