admin Staff asked 5 years ago
There are two excel files. Compare that Data present in first excel is a Subset of the data in the 2nd Excel Sheet.
Excel 1 Data: two Columns
Name | Marks
A | 1
B | 2
C | 3
D | 4
Excel 2 Data: Two Columns
A | 1
B | 2
C | 3
D | 4
E | 5
F | 6
G | 7
Expected output and hint:
- Create a Reusable method which takes path of Excel file and returns the data in the data structure like 2 D array or dual hash map
- Then Compare data of Excel 1 with Data of Excel 2 by comparing returned data structures.
- It should be Dynamic i.e. no matter how many rows are there in both the excel it should handle at run time.
- Program should give exact failures. For example, if entry is not found than it should show “C” entry is not found. If “C” is found and its value is different than it should display entry found but value is mis-matched and it should display expected and actual.
1 Answers
Sarang Staff answered 5 years ago
This Code is to Compare Two Excel file data(.xls MS Office 2007 Version)
with unlimited number of entries in the Table and having provision to
Display error where the Two Entries having no same data
Excel File Table Discription :
Row First comprises of - Id, Name, Branch, Marks, City
Code Starts From Here
package snippets;
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import com.relevantcodes.extentreports.ExtentReports;
import com.relevantcodes.extentreports.ExtentTest;
import com.relevantcodes.extentreports.LogStatus;
public class ExcelCompare {
public static void main(String[] srgs) throws IOException {
ExtentReports extent = new ExtentReports("../Falcon/src/snippets/ExcelCompareExtentReport/report.html");
ExtentTest test = extent.startTest("Differnce Report","Below is the log of differnce found ");
FileInputStream fileInputStream1 = new FileInputStream("C:\\Vision\\eclipse-workspace\\Falcon\\src\\snippets\\ExcelSheetsToBeCompared\\Book11.xls");
HSSFWorkbook workbook1 = new HSSFWorkbook(fileInputStream1);
HSSFSheet worksheet1 = workbook1.getSheet("Sheet1");
int rowCount1= worksheet1.getPhysicalNumberOfRows();
FileInputStream fileInputStream2 = new FileInputStream("C:\\Vision\\eclipse-workspace\\Falcon\\\\src\\snippets\\ExcelSheetsToBeCompared\\Book22.xls");
HSSFWorkbook workbook2 = new HSSFWorkbook(fileInputStream2);
HSSFSheet worksheet2 = workbook2.getSheet("Sheet1");
int rowCount2= worksheet2.getPhysicalNumberOfRows();
if(rowCount1==rowCount2) {
for (int i = 1; i < rowCount1; i++) {
HSSFRow row1 = worksheet1.getRow(i);
HSSFRow row2 = worksheet2.getRow(i);
//------------------------------ comapring ID --------------------------
String idstr1 = "";
HSSFCell id1 = row1.getCell(0);
if (id1 != null) {
id1.setCellType(CellType.STRING);
idstr1 = id1.getStringCellValue();
}
String idstr2 = "";
HSSFCell id2 = row2.getCell(0);
if (id2 != null) {
id2.setCellType(CellType.STRING);
idstr2 = id2.getStringCellValue();
}
if(!idstr1.equals(idstr2))
{
System.out.println("[ERROR] :"+"Diference found for id (Sheet1) " + idstr1 + "| Sheet 1 id = " + idstr1+ " | Sheet 2 id = " + idstr2);
test.log(LogStatus.ERROR,"Diference for id (Sheet1) " + idstr1 + "| Sheet 1 id = " + idstr1+ "| Sheet 2 id = " + idstr2);
}
//------------------------------ End ID ---------------------------------
// ------------------------------ comapring Name --------------------------
String namestr1 = "";
HSSFCell name1 = row1.getCell(1);
if (name1 != null) {
name1.setCellType(CellType.STRING);
namestr1 = name1.getStringCellValue();
}
String namestr2 = "";
HSSFCell name2 = row2.getCell(1);
if (name2 != null) {
name2.setCellType(CellType.STRING);
namestr2 = name2.getStringCellValue();
}
if(!namestr1.equals(namestr2))
{
System.out.println("[ERROR] :"+"Diference found for id (Sheet1) " + idstr1 + " | Sheet 1 name = " + namestr1+ "| Sheet 2 name = " + namestr2);
test.log(LogStatus.ERROR,"Diference for id (Sheet1) " + idstr1 + " | Sheet 1 name = " + namestr1+ "| Sheet 2 name = " + namestr2);
}
//------------------------------ End Name Comparison---------------------------------
// ------------------------------ comapring branch --------------------------
String branchstr1 = "";
HSSFCell branch1 = row1.getCell(2);
if (branch1 != null) {
branch1.setCellType(CellType.STRING);
branchstr1 = branch1.getStringCellValue();
}
String branchstr2 = "";
HSSFCell branch2 = row2.getCell(2);
if (branch2 != null) {
branch2.setCellType(CellType.STRING);
branchstr2 = branch2.getStringCellValue();
}
if(!branchstr1.equals(branchstr2))
{
System.out.println("[ERROR] :"+"Diference found for id (Sheet1) " + idstr1 + "| Sheet 1 branch = " + branchstr1+ "| Sheet 2 branch = " + branchstr2);
test.log(LogStatus.ERROR,"Diference for id (Sheet1) " + idstr1 + "| Sheet 1 branch = " + branchstr1+ "| Sheet 2 branch = " + branchstr2);
}
//------------------------------ End branch Comparison---------------------------------
// ------------------------------ comapring marks --------------------------
String marksstr1 = "";
HSSFCell marks1 = row1.getCell(3);
if (marks1 != null) {
marks1.setCellType(CellType.STRING);
marksstr1 = marks1.getStringCellValue();
}
String marksstr2 = "";
HSSFCell marks2 = row2.getCell(3);
if (marks2 != null) {
marks2.setCellType(CellType.STRING);
marksstr2 = marks2.getStringCellValue();
}
if(!marksstr1.equals(marksstr2))
{
System.out.println("[ERROR] :"+"Diference found for id (Sheet1) " + idstr1 + " | Sheet 1 marks = " + marksstr1+ " | Sheet 2 marks = " + marksstr2);
test.log(LogStatus.ERROR,"Diference for id (Sheet) " + idstr1 + " | Sheet 1 marks = " + marksstr1+ " | Sheet 2 marks = " + marksstr2);
}
//------------------------------ End marks Comparison---------------------------------
// ------------------------------ comapring city --------------------------
String citystr1 = "";
HSSFCell city1 = row1.getCell(4);
if (city1 != null) {
city1.setCellType(CellType.STRING);
citystr1 = city1.getStringCellValue();
}
String citystr2 = "";
HSSFCell city2 = row2.getCell(4);
if (city2 != null) {
city2.setCellType(CellType.STRING);
citystr2 = city2.getStringCellValue();
}
if(!citystr1.equals(citystr2))
{
System.out.println("[ERROR] :"+"Diference found for id (Sheet1) " + idstr1 + " | Sheet 1 city = " + citystr1+ " | Sheet 2 city = " + citystr2);
test.log(LogStatus.ERROR,"Diference for id (Sheet1) " + idstr1 + " | Sheet 1 city = " + citystr1+ " | Sheet 2 city = " + citystr2);
}
//------------------------------ End city Comparison---------------------------------
System.out.println("[Processing] :"+"ID " + idstr1 + "=> Sheet 1 id = " + idstr1+ " Sheet 2 id = " + idstr2);
}
test.log(LogStatus.INFO,"Completed Successfully");
}
else {
test.log(LogStatus.ERROR,"Row count 1=" + rowCount1 + " Rocunt 2 = " + rowCount2);
System.out.println("Row count 1=" + rowCount1 + " Rocunt 2 = " + rowCount2);
}
extent.endTest(test);
extent.flush();
}
}