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
- Get input excel files and get workbook instances
- Get sheets from those workbooks
- Get rows from each sheets with same index and compare them
- 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; } }
This Example code is very usefully....Thanks
ReplyDeleteIs this working? I got this error - at CompareExcel.main(CompareExcel.java:20)
ReplyDeleteCaused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
You might have missed the XmlBeans jar file
DeleteCan you please provide me the steps, hot to execute the above code in eclipse
ReplyDeleteI have shared the eclipse project, download it and import to eclipse
DeleteHi, this same code workable eclipse?
DeleteDesari Srinivas, is this project available for reuse? That is, do I have permission to use it and/or edit it?
ReplyDeleteThanks
yes
DeleteThanks you Srinivas.... This code helped me a lot !!!!!!!
ReplyDeleteCan you please tell me how to return the entire row if there is a mismatch in a particular cell in that row ?
ReplyDeleteInstead of returning equalRows boolean value, return row1 and row2 in a arraylist
Deletehiiii 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...
ReplyDeletethanku u :-)
Hey, I am not able to recreate your issue, please contact me on facebook...share your project and required files in facebook messenger
Delete...my profile link --- https://www.facebook.com/srinivas.dasari1990
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".
ReplyDeleteso 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.
Hi
DeleteI have a similar requirement.
Can anyone help me please
How to compare files in csv format , file to file, sheet to sheet etc
ReplyDeleteHi 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".
ReplyDeleteso 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.
I`ll use it because I like
ReplyDeletecomparable 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.
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeletegetCellType is deprecated .How do I modify it?
ReplyDeleteim 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
ReplyDeleteCompare 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 ?
ReplyDeletehi srinivas, i have similar requirmenet but need some more add on,
ReplyDeleteLike 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.
Did you find slotuion can you please share please reply asap. have similar requirmenet but need some more add on,
DeleteLike 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
how to take comparison result in excel
ReplyDeleteHi Srinivas, can you please share the eclipse project ?
ReplyDeleteHi Srinivas, can you please share the eclipse project ?
ReplyDeletethe information you provide on this website has helped me tremendously. Thanks for all
ReplyDeleteof your time & work. Return Of Xander Cage Outfits
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.บาคาร่า
ReplyDeleteufa
ufabet
แทงบอล
แทงบอล
แทงบอล
pgslot ซึ่งเกมคาสิโนออนไลน์เกมนี้เป็นเกมที่เรียกว่าเกม สล็อตเอ็กซ์โอ คุณรู้จักเกมส์เอ็กซ์โอหรือไม่ 90% ต้องรู้จักเกมส์เอ็กซ์โออย่างแน่นอนเพราะในตอนนี้เด็กนั้นเราทุกคนมักที่จะเอาก็ได้ขึ้นมา สล็อต เล่นเกมส์เอ็กซ์โอกับเพื่อนเพื่อนแล้วคุณรู้หรือไม่ว่าในปัจจุบันนี้เกมส์เอ็กซ์โอนั้นกลายมาเป็นเกมซะลอสออนไลน์ที่ให้บริการด้วยเว็บคาสิโนออนไลน์คุณสามารถเดิมพันเกมส์เอ็กซ์โอกับเว็บคาสิโนออนไลน์ได้โดยที่จะทำให้คุณนั้นสามารถสร้างกำไรจากการเล่นเกมส์เดิมพันออนไลน์ได้เราแนะนำเกมส์ชนิดนี้ให้คุณได้รู้จักก็เพราะว่าเชื่อว่าทุก
ReplyDeleteAivivu chuyên vé máy bay, tham khảo
ReplyDeletevé máy bay đi Mỹ giá rẻ 2021
lịch bay từ mỹ về việt nam hôm nay
đăng ký bay từ canada về Việt Nam
vietnamairline từ nhật về việt nam
gia ve may bay vietjet tu han quoc ve viet nam
Vé máy bay từ Đài Loan về VN
khách sạn cách ly ở đà nẵng
Offered a bright, clear and clear concept ดูต่อที่นี่
ReplyDeleteWhat a blog, I really like to thank blog author for the wonderful post.
ReplyDeleteทางเข้า igoal
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
ReplyDeleteI think your blog will easily get the correct market because it's having the piece of content. so it can easily attract visitors.
ReplyDeleteIf you are looking to buy customised seed pencil. Get connected with us for more details.
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.
ReplyDeleteThank you for another amazing post, topics for dissertation in law this one is similar to that.
ReplyDeletei have found a lot of interesting topics in it, thanks for an amazing information Reading Psychology Essay Help will get amazing research.
ReplyDeleteEosin 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