skip to main | skip to sidebar

Java Programs and Examples with Output

Pages

▼
 
  • RSS
  • Twitter
Thursday, November 1, 2012

Excel Comparator xls or xlsx in java

Posted by Raju Gupta at 10:30 AM – 0 comments
 

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

Leave a Reply

Newer Post Older Post
Subscribe to: Post Comments ( Atom )
  • Popular
  • Recent
  • Archives
Powered by Blogger.
 
 
 
© 2011 Java Programs and Examples with Output | Designs by Web2feel & Fab Themes

Bloggerized by DheTemplate.com - Main Blogger