To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).

Program Flow

  1. Get input excel files and get workbook instances
  2. Get sheets from those workbooks
  3. Get rows from each sheets with same index and compare them
  4. Get columns from two selected above rows and compare them

Example

Observe below input files data. Here Row 3 - Column 2 is not matching so the two excel files are not equal. 

Expected Output 

Comparing Row 0
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 0 - Equal


Comparing Row 1
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 1 - Equal


Comparing Row 2
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 2 - Equal


Comparing Row 3
       Cell 0 - Equal
       Cell 1 - Equal
Row 3 - Not Equal


The two excel sheets are Not Equal
       Cell 2 - NOt Equal

Java Code

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CompareExcel {

    public static void main(String[] args) {
        try {
            // get input excel files
            FileInputStream excellFile1 = new FileInputStream(new File(
                    "C:\\sheet1.xlsx"));
            FileInputStream excellFile2 = new FileInputStream(new File(
                    "C:\\sheet2.xlsx"));

            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
            XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);

            // Get first/desired sheet from the workbook
            XSSFSheet sheet1 = workbook1.getSheetAt(0);
            XSSFSheet sheet2 = workbook2.getSheetAt(0);

            // Compare sheets
            if(compareTwoSheets(sheet1, sheet2)) {
                System.out.println("\n\nThe two excel sheets are Equal");
            } else {
                System.out.println("\n\nThe two excel sheets are Not Equal");
            }
            
            //close files
            excellFile1.close();
            excellFile2.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    
    // Compare Two Sheets
    public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
        int firstRow1 = sheet1.getFirstRowNum();
        int lastRow1 = sheet1.getLastRowNum();
        boolean equalSheets = true;
        for(int i=firstRow1; i <= lastRow1; i++) {
            
            System.out.println("\n\nComparing Row "+i);
            
            XSSFRow row1 = sheet1.getRow(i);
            XSSFRow row2 = sheet2.getRow(i);
            if(!compareTwoRows(row1, row2)) {
                equalSheets = false;
                System.out.println("Row "+i+" - Not Equal");
                break;
            } else {
                System.out.println("Row "+i+" - Equal");
            }
        }
        return equalSheets;
    }

    // Compare Two Rows
    public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
        if((row1 == null) && (row2 == null)) {
            return true;
        } else if((row1 == null) || (row2 == null)) {
            return false;
        }
        
        int firstCell1 = row1.getFirstCellNum();
        int lastCell1 = row1.getLastCellNum();
        boolean equalRows = true;
        
        // Compare all cells in a row
        for(int i=firstCell1; i <= lastCell1; i++) {
            XSSFCell cell1 = row1.getCell(i);
            XSSFCell cell2 = row2.getCell(i);
            if(!compareTwoCells(cell1, cell2)) {
                equalRows = false;
                System.err.println("       Cell "+i+" - NOt Equal");
                break;
            } else {
                System.out.println("       Cell "+i+" - Equal");
            }
        }
        return equalRows;
    }

    // Compare Two Cells
    public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
        if((cell1 == null) && (cell2 == null)) {
            return true;
        } else if((cell1 == null) || (cell2 == null)) {
            return false;
        }
        
        boolean equalCells = false;
        int type1 = cell1.getCellType();
        int type2 = cell2.getCellType();
        if (type1 == type2) {
            if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
                // Compare cells based on its type
                switch (cell1.getCellType()) {
                case HSSFCell.CELL_TYPE_FORMULA:
                    if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (cell1.getNumericCellValue() == cell2
                            .getNumericCellValue()) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    if (cell1.getStringCellValue().equals(cell2
                            .getStringCellValue())) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    if (cell1.getBooleanCellValue() == cell2
                            .getBooleanCellValue()) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                        equalCells = true;
                    }
                    break;
                default:
                    if (cell1.getStringCellValue().equals(
                            cell2.getStringCellValue())) {
                        equalCells = true;
                    }
                    break;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
        return equalCells;
    }
}

40 comments:

  1. This Example code is very usefully....Thanks

    ReplyDelete
  2. Is this working? I got this error - at CompareExcel.main(CompareExcel.java:20)
    Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject

    ReplyDelete
    Replies
    1. You might have missed the XmlBeans jar file

      Delete
  3. Can you please provide me the steps, hot to execute the above code in eclipse

    ReplyDelete
    Replies
    1. I have shared the eclipse project, download it and import to eclipse

      Delete
    2. Hi, this same code workable eclipse?

      Delete
  4. Desari Srinivas, is this project available for reuse? That is, do I have permission to use it and/or edit it?
    Thanks

    ReplyDelete
  5. Thanks you Srinivas.... This code helped me a lot !!!!!!!

    ReplyDelete
  6. Can you please tell me how to return the entire row if there is a mismatch in a particular cell in that row ?

    ReplyDelete
    Replies
    1. Instead of returning equalRows boolean value, return row1 and row2 in a arraylist

      Delete
  7. hiiii Dasari u didnt answered of my post one error was coming and you also deleted it ...is some thing i did wrong so i apologiges...
    thanku u :-)

    ReplyDelete
    Replies
    1. Hey, I am not able to recreate your issue, please contact me on facebook...share your project and required files in facebook messenger
      ...my profile link --- https://www.facebook.com/srinivas.dasari1990

      Delete
  8. hi srinivas, i have a similar requirement with small change. Requirement is that "have to compare two rows from sheet1 and sheet2 but it should not be in order. Means if i say 1st row in sheet 1, presents in sheet2 as row 10 but both are equal".
    so here comparison logic would be take 1st row from sheet1 -> compare with all the rows in sheet2 one by one until its get matched. if matches then say row1 is matched or say failed. Can you help me here, Please. Thanks in advance.

    ReplyDelete
    Replies
    1. Hi
      I have a similar requirement.
      Can anyone help me please

      Delete
  9. How to compare files in csv format , file to file, sheet to sheet etc

    ReplyDelete
  10. Hi Srinivas, i have a similar requirement with small change. Requirement is that "have to compare two rows from sheet1 and sheet2 but it should not be in order. Means if i say 1st row in sheet 1, presents in sheet2 as row 10 but both are equal".
    so here comparison logic would be take 1st row from sheet1 -> compare with all the rows in sheet2 one by one until its get matched. if matches then say we need to change the Font of matched row to GREEN in anyone excel sheet and if it does not matches then change Font to RED in the same excel sheet.Can you help me here, Please. Thanks in advance.

    ReplyDelete
  11. I`ll use it because I like
    comparable vs comparator java https://explainjava.com/comparable-comparator-java/ and I'm used to working this way. The script needs to be compiled, and yes,
    I see some conditions. To understand the algorithm, I found several tutorials, perhaps this will be very useful for those who do not finally understand the sorting, and how I search for explanations.

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. getCellType is deprecated .How do I modify it?

    ReplyDelete
  15. im trying to implement extent report for the above code. im getting error when i put logstatu.PASS in else condition in comareTwoCells method.i m able to capture the failed report in extent report under if(!compareTwoCells(cell1, cell2) but not in else case- getting nullPointer Exception. if i remove logstatus.PASS statement from else block -- it works. Please help how to log the pass report here

    ReplyDelete
  16. Compare two excels files and if we find any differences in one excel sheet that difference or row should be added in a given excel path and after adding the differences in that excel it should get highlighted ? Is it possible to add automatically ?

    ReplyDelete
  17. hi srinivas, i have similar requirmenet but need some more add on,
    Like if any of the cell is not matched or mismatched , i need to Write into the one of the excel write that the cell is not matched in last of the row

    Please guide me.

    ReplyDelete
    Replies
    1. Did you find slotuion can you please share please reply asap. have similar requirmenet but need some more add on,
      Like if any of the cell is not matched or mismatched , i need to Write into the one of the excel write that the cell is not matched in last of the row

      Delete
  18. how to take comparison result in excel

    ReplyDelete
  19. Hi Srinivas, can you please share the eclipse project ?

    ReplyDelete
  20. Hi Srinivas, can you please share the eclipse project ?

    ReplyDelete
  21. the information you provide on this website has helped me tremendously. Thanks for all
    of your time & work. Return Of Xander Cage Outfits

    ReplyDelete
  22. Baccarat is money making plus it's spectacular availability. Optimal In your case it's being sold that you'll find pretty fascinating alternatives. And that is regarded as something that's very different And it's really a little something that's very happy to hit with Likely the most excellent, too, is a very positive choice. Furthermore, it's a really fascinating solution. It's the simplest way which could earn money. Superbly prepar The number of best-earning baccarat is the accessibility of making the most cash. Almost as possible is very ideal for you An alternative which may be guaranteed. To a wide variety of performance and supply And see outstanding benefits as well.บาคาร่า
    ufa
    ufabet
    แทงบอล
    แทงบอล
    แทงบอล

    ReplyDelete
  23. pgslot ซึ่งเกมคาสิโนออนไลน์เกมนี้เป็นเกมที่เรียกว่าเกม สล็อตเอ็กซ์โอ คุณรู้จักเกมส์เอ็กซ์โอหรือไม่ 90% ต้องรู้จักเกมส์เอ็กซ์โออย่างแน่นอนเพราะในตอนนี้เด็กนั้นเราทุกคนมักที่จะเอาก็ได้ขึ้นมา สล็อต เล่นเกมส์เอ็กซ์โอกับเพื่อนเพื่อนแล้วคุณรู้หรือไม่ว่าในปัจจุบันนี้เกมส์เอ็กซ์โอนั้นกลายมาเป็นเกมซะลอสออนไลน์ที่ให้บริการด้วยเว็บคาสิโนออนไลน์คุณสามารถเดิมพันเกมส์เอ็กซ์โอกับเว็บคาสิโนออนไลน์ได้โดยที่จะทำให้คุณนั้นสามารถสร้างกำไรจากการเล่นเกมส์เดิมพันออนไลน์ได้เราแนะนำเกมส์ชนิดนี้ให้คุณได้รู้จักก็เพราะว่าเชื่อว่าทุก

    ReplyDelete
  24. What a blog, I really like to thank blog author for the wonderful post.
    ทางเข้า igoal

    ReplyDelete
  25. What was the last experience that made you a stronger person?ThingsyoudoforbeautyWhen scrolling through social media, do you prefer posts from celebrities or from your best friends?INDIA'S BEST OFF SITE SEO PROVIDER CHEAPEST AND FASTEST OFF SITE SEO SERVICE IN INDIA infocompanion educatijhn

    ReplyDelete
  26. I think your blog will easily get the correct market because it's having the piece of content. so it can easily attract visitors.
    If you are looking to buy customised seed pencil. Get connected with us for more details.

    ReplyDelete
  27. Anyone suggest me what are the Do My Assignment in KSA for Psychology students. I have five-six assignments to proofread, and I’m willing to pay so it would be good amount. Also, since the number of assignments is good, I would like to know the websites that offer discounts. Let me know please.

    ReplyDelete
  28. Thank you for another amazing post, topics for dissertation in law this one is similar to that.

    ReplyDelete
  29. i have found a lot of interesting topics in it, thanks for an amazing information Reading Psychology Essay Help will get amazing research.

    ReplyDelete
  30. Eosin Y disodium salt, dye content 85%, indicator - Alfa Chemistry Catalysts offers a catalog of catalysts for a wide range of applications. Products listed on our website are either in stock or can be reconstituted within a reasonable time frame. In-stock products can be shipped within 3-5 business days of receipt of customer purchase order.

    ReplyDelete

Blogroll

Popular Posts