package com.sl;
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;
}
}
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;
}
}
5 comments:
Do you have this code in C#??
Hi,
I am getting below error while execution
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
Type mismatch: cannot convert from CellType to int
Type mismatch: cannot convert from CellType to int
CELL_TYPE_FORMULA cannot be resolved or is not a field
CELL_TYPE_NUMERIC cannot be resolved or is not a field
CELL_TYPE_STRING cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BOOLEAN cannot be resolved or is not a field
CELL_TYPE_ERROR cannot be resolved or is not a field
at com.sl.CompareExcel.compareTwoCells(CompareExcel.java:105)
at com.sl.CompareExcel.compareTwoRows(CompareExcel.java:85)
at com.sl.CompareExcel.compareTwoSheets(CompareExcel.java:58)
at com.sl.CompareExcel.main(CompareExcel.java:30)
Hi,
i am also getting same error :
Exception in thread "main" java.lang.Error: Unresolved compilation problems:
Type mismatch: cannot convert from CellType to int
Type mismatch: cannot convert from CellType to int
CELL_TYPE_FORMULA cannot be resolved or is not a field
CELL_TYPE_NUMERIC cannot be resolved or is not a field
CELL_TYPE_STRING cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BOOLEAN cannot be resolved or is not a field
CELL_TYPE_ERROR cannot be resolved or is not a field
at com.sl.CompareExcel.compareTwoCells(CompareExcel.java:105)
at com.sl.CompareExcel.compareTwoRows(CompareExcel.java:85)
at com.sl.CompareExcel.compareTwoSheets(CompareExcel.java:58)
at com.sl.CompareExcel.main(CompareExcel.java:30)
Hi, I am also getting same error, can any one please suggest how to resolve this issue.
java.lang.Error: Unresolved compilation problems:
Type mismatch: cannot convert from CellType to int
Type mismatch: cannot convert from CellType to int
CELL_TYPE_FORMULA cannot be resolved or is not a field
CELL_TYPE_NUMERIC cannot be resolved or is not a field
CELL_TYPE_STRING cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BLANK cannot be resolved or is not a field
CELL_TYPE_BOOLEAN cannot be resolved or is not a field
CELL_TYPE_ERROR cannot be resolved or is not a field
package TestCases;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.SystemOutLogger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
//import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Assert;
import org.testng.annotations.Test;
public class CompareExcel {
@Test
//THIS METHOD WILL COMPARE IF WHORKPSACE HAS EQUAL NUMBER OF ROWS AND CELLS
public void CompareRowAndCells() throws IOException
{
//int i=0,j=0;
FileInputStream inputStream=new FileInputStream("/Users/xxx/Desktop/1ABC/TestDataTest.xlsx");
XSSFWorkbook wb1=new XSSFWorkbook(inputStream);
XSSFSheet sheet1=wb1.getSheet("Sheet1");
int RowCount1= sheet1.getLastRowNum()-sheet1.getFirstRowNum();
FileInputStream inputStream2=new FileInputStream("/Users/xxx/Desktop/1ABC/TestDataTest2.xlsx");
XSSFWorkbook wb2=new XSSFWorkbook(inputStream2);
XSSFSheet sheet2=wb2.getSheet("Sheet1");
int RowCount2= sheet2.getLastRowNum()-sheet2.getFirstRowNum();
//Compares if both excel have equal now of rows
Assert.assertEquals(RowCount1, RowCount2,"Not sames");
//compares if both excel file has same Row Number
Iterator rowInSheet1 =sheet1.rowIterator();
Iterator rowInSheet2 =sheet2.rowIterator();
while(rowInSheet1.hasNext()) {
int cellcounts1 =rowInSheet1.next().getPhysicalNumberOfCells();
int cellcounts2 =rowInSheet2.next().getPhysicalNumberOfCells();
Assert.assertEquals(cellcounts1, cellcounts2,"Not same");
for(int i=0;i Numeric 1->String 4->Boolean
int type2= c2.getCellType();
if(type1==type2)
{
if(type1==1)
{
if(c1.getStringCellValue().equals(c2.getStringCellValue())) {
System.out.println(c1.getStringCellValue()+", "+c2.getStringCellValue()+"-->"+" Match");
}}
if(type1==0)
{
if(c1.getNumericCellValue()==c2.getNumericCellValue())
{
System.out.println(c1.getNumericCellValue() +", "+c2.getNumericCellValue()+" -->"+"Match");
}
else {System.out.println("NOT MATCHED");}
}
if(type1==4)
{
if(c1.getBooleanCellValue()==c2.getBooleanCellValue())
{
System.out.println(c1.getBooleanCellValue()+", "+c2.getBooleanCellValue()+"--> "+"Match");
}
else
{
System.out.println(c1.getBooleanCellValue()+", "+ c2.getBooleanCellValue()+"--> "+"Not Matched");
}
}
}}}}}}
Post a Comment