Java – How compare 2 different excel.xls sheets using apache POI and java

apacheapache-poiexceljavaselenium-webdriver

Problem statement: I want to fetch data from two different websites , and write this data into workbook Sheet 1 and sheet 2 am looking for solution to perform excel comparison, looking for expert guidance .

=======================================================

Excel sheet data

Excel sheet data

===================================

solution I need mismatched from these two sheets using Apache POI and java.

==================================================
Below is the code to read sheet 0 , i stuck with hoe to read second sheet1 and compare it.

import java.io.FileInputStream;

import jxl.Sheet;
import jxl.Workbook;

public class MyCCD {

public static void oldTurnover() throws Exception{

    //int turnoverRow = 0;
    String TickerName = null;
    int numOfTicker = 8;
    String SummaryColumn = null;
    float oldRevenue = 0;
    String description = "Ticker";

    Workbook wb = Workbook.getWorkbook(new FileInputStream("D:\\ssb.xls"));
    Sheet sh = wb.getSheet(0);

    int rows = sh.getRows();
    int cols = sh.getColumns();

    //to get the row of Ticker
    for(int i=0;i<rows;i++){
        //System.out.println(sh.getCell(0, i).getContents().toLowerCase());
        if(sh.getCell(0, i).getContents().toLowerCase().matches("ticker")){
            System.out.println("Ticker row:"+i);
            //turnoverRow = i;

            //Company Name
            for(int j=i;j<i+numOfTicker;j++){
                TickerName = sh.getCell(0, j+1).getContents();
                System.out.println("-----------"+TickerName+"-------------");

                for(int k=1;k<cols;k++){

                    //quarter
                    SummaryColumn = sh.getCell(k, i+1).getContents();
                    System.out.println("SummaryColumn: "+SummaryColumn);


                    //Estimated Revenue
                    oldRevenue = Float.parseFloat(sh.getCell(k, j+1).getContents());


                    float newRevenue = IHData(description, SummaryColumn);


                    if(oldRevenue != newRevenue){
                        System.out.println("SummaryColumn:"+SummaryColumn);
                        System.out.print  ("SheetOne:"+oldRevenue);
                        System.out.print  ("\t\t");
                        System.out.println("SheetTwo: "+newRevenue);
                    }
                }
            }               
        }
    }
}           

    public static float IHData(String description, String SummaryColumn) throws Exception
    {

Best Answer

Read your sheets data and compare the values.

First you need to read both sheets.

InputStream book1= new FileInputStream("book1.xlsx"));
XSSFWorkbook wb = new HSSFWorkbook(book1); 

XSSFSheet sheet1 = myWorkBook.getSheetAt(0)       // first sheet
Row row     = sheet1.getRow(0);        // first row
Cell cell   = row.getCell(0);
String value = cell.getStringCellValue();// use a loop to read all the cells in the rows.

Read the cell contents and put them in String or int whatever type and do the same for book2. Now compare the variables holding data from both sheets. Also please read this tutorial if you require additional reference

Related Topic