org.apache.poi.ss.examples.ExcelComparator Maven / Gradle / Ivy
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.ss.examples;
import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
/**
* Utility to compare Excel File Contents cell by cell for all sheets.
*
* This utility will be used to compare Excel File Contents cell by cell for all sheets programmatically.
*
* Below are the list of Attribute comparison supported in this version.
*
*
* - Cell Alignment
* - Cell Border Attributes
* - Cell Data
* - Cell Data-Type
* - Cell Fill Color
* - Cell Fill pattern
* - Cell Font Attributes
* - Cell Font Family
* - Cell Font Size
* - Cell Protection
* - Name of the sheets
* - Number of Columns
* - Number of Rows
* - Number of Sheet
*
*
* (Some of the above attribute comparison only work for *.xlsx format currently. In future it can be enhanced.)
*
* Usage:
*
*
* {@code
* Workbook wb1 = WorkbookFactory.create(new File("workBook1.xls"));
* Workbook wb2 = WorkbookFactory.create(new File("workBook2.xls"));
* List listOfDifferences = ExcelComparator.compare(wb1, wb2);
* for (String differences : listOfDifferences)
* System.out.println(differences);
* System.out.println("DifferenceFound = "+ excelFileDifference.isDifferenceFound);
* }
*
*/
public class ExcelComparator {
private static final String CELL_DATA_DOES_NOT_MATCH = "Cell Data does not Match ::";
private static final String CELL_FONT_ATTRIBUTES_DOES_NOT_MATCH = "Cell Font Attributes does not Match ::";
private static class Locator {
Workbook workbook;
Sheet sheet;
Row row;
Cell cell;
}
List listOfDifferences = new ArrayList();
public static void main(String args[]) throws Exception {
if (args.length != 2 || !(new File(args[0]).exists()) || !(new File(args[1]).exists())) {
System.err.println("java -cp "+ExcelComparator.class.getCanonicalName()+" compare(Workbook wb1, Workbook wb2) {
Locator loc1 = new Locator();
Locator loc2 = new Locator();
loc1.workbook = wb1;
loc2.workbook = wb2;
ExcelComparator excelComparator = new ExcelComparator();
excelComparator.compareNumberOfSheets(loc1, loc2 );
excelComparator.compareSheetNames(loc1, loc2);
excelComparator.compareSheetData(loc1, loc2);
return excelComparator.listOfDifferences;
}
/**
* Compare data in all sheets.
*
* @param workbook1 the workbook1
* @param workbook2 the workbook2
* @param listOfDifferences the list of differences
* @throws ExcelCompareException the excel compare exception
*/
private void compareDataInAllSheets(Locator loc1, Locator loc2) {
for (int i = 0; i < loc1.workbook.getNumberOfSheets(); i++) {
if (loc2.workbook.getNumberOfSheets() <= i) return;
loc1.sheet = loc1.workbook.getSheetAt(i);
loc2.sheet = loc2.workbook.getSheetAt(i);
compareDataInSheet(loc1, loc2);
}
}
private void compareDataInSheet(Locator loc1, Locator loc2) {
for (int j = 0; j < loc1.sheet.getPhysicalNumberOfRows(); j++) {
if (loc2.sheet.getPhysicalNumberOfRows() <= j) return;
loc1.row = loc1.sheet.getRow(j);
loc2.row = loc2.sheet.getRow(j);
if ((loc1.row == null) || (loc2.row == null)) {
continue;
}
compareDataInRow(loc1, loc2);
}
}
private void compareDataInRow(Locator loc1, Locator loc2) {
for (int k = 0; k < loc1.row.getLastCellNum(); k++) {
if (loc2.row.getPhysicalNumberOfCells() <= k) return;
loc1.cell = loc1.row.getCell(k);
loc2.cell = loc2.row.getCell(k);
if ((loc1.cell == null) || (loc2.cell == null)) {
continue;
}
compareDataInCell(loc1, loc2);
}
}
private void compareDataInCell(Locator loc1, Locator loc2) {
if (isCellTypeMatches(loc1, loc2)) {
final CellType loc1cellType = loc1.cell.getCellTypeEnum();
switch(loc1cellType) {
case BLANK:
case STRING:
case ERROR:
isCellContentMatches(loc1,loc2);
break;
case BOOLEAN:
isCellContentMatchesForBoolean(loc1,loc2);
break;
case FORMULA:
isCellContentMatchesForFormula(loc1,loc2);
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(loc1.cell)) {
isCellContentMatchesForDate(loc1,loc2);
} else {
isCellContentMatchesForNumeric(loc1,loc2);
}
break;
default:
throw new IllegalStateException("Unexpected cell type: " + loc1cellType);
}
}
isCellFillPatternMatches(loc1,loc2);
isCellAlignmentMatches(loc1,loc2);
isCellHiddenMatches(loc1,loc2);
isCellLockedMatches(loc1,loc2);
isCellFontFamilyMatches(loc1,loc2);
isCellFontSizeMatches(loc1,loc2);
isCellFontBoldMatches(loc1,loc2);
isCellUnderLineMatches(loc1,loc2);
isCellFontItalicsMatches(loc1,loc2);
isCellBorderMatches(loc1,loc2,'t');
isCellBorderMatches(loc1,loc2,'l');
isCellBorderMatches(loc1,loc2,'b');
isCellBorderMatches(loc1,loc2,'r');
isCellFillBackGroundMatches(loc1,loc2);
}
/**
* Compare number of columns in sheets.
*/
private void compareNumberOfColumnsInSheets(Locator loc1, Locator loc2) {
for (int i = 0; i < loc1.workbook.getNumberOfSheets(); i++) {
if (loc2.workbook.getNumberOfSheets() <= i) return;
loc1.sheet = loc1.workbook.getSheetAt(i);
loc2.sheet = loc2.workbook.getSheetAt(i);
Iterator ri1 = loc1.sheet.rowIterator();
Iterator ri2 = loc2.sheet.rowIterator();
int num1 = (ri1.hasNext()) ? ri1.next().getPhysicalNumberOfCells() : 0;
int num2 = (ri2.hasNext()) ? ri2.next().getPhysicalNumberOfCells() : 0;
if (num1 != num2) {
String str = String.format(Locale.ROOT, "%s\nworkbook1 -> %s [%d] != workbook2 -> %s [%d]",
"Number Of Columns does not Match ::",
loc1.sheet.getSheetName(), num1,
loc2.sheet.getSheetName(), num2
);
listOfDifferences.add(str);
}
}
}
/**
* Compare number of rows in sheets.
*/
private void compareNumberOfRowsInSheets(Locator loc1, Locator loc2) {
for (int i = 0; i < loc1.workbook.getNumberOfSheets(); i++) {
if (loc2.workbook.getNumberOfSheets() <= i) return;
loc1.sheet = loc1.workbook.getSheetAt(i);
loc2.sheet = loc2.workbook.getSheetAt(i);
int num1 = loc1.sheet.getPhysicalNumberOfRows();
int num2 = loc2.sheet.getPhysicalNumberOfRows();
if (num1 != num2) {
String str = String.format(Locale.ROOT, "%s\nworkbook1 -> %s [%d] != workbook2 -> %s [%d]",
"Number Of Rows does not Match ::",
loc1.sheet.getSheetName(), num1,
loc2.sheet.getSheetName(), num2
);
listOfDifferences.add(str);
}
}
}
/**
* Compare number of sheets.
*/
private void compareNumberOfSheets(Locator loc1, Locator loc2) {
int num1 = loc1.workbook.getNumberOfSheets();
int num2 = loc2.workbook.getNumberOfSheets();
if (num1 != num2) {
String str = String.format(Locale.ROOT, "%s\nworkbook1 [%d] != workbook2 [%d]",
"Number of Sheets do not match ::",
num1, num2
);
listOfDifferences.add(str);
}
}
/**
* Compare sheet data.
*
* @param workbook1
* the workbook1
* @param workbook2
* the workbook2
* @param listOfDifferences
*
* @throws ExcelCompareException
* the excel compare exception
*/
private void compareSheetData(Locator loc1, Locator loc2) {
compareNumberOfRowsInSheets(loc1, loc2);
compareNumberOfColumnsInSheets(loc1, loc2);
compareDataInAllSheets(loc1, loc2);
}
/**
* Compare sheet names.
*/
private void compareSheetNames(Locator loc1, Locator loc2) {
for (int i = 0; i < loc1.workbook.getNumberOfSheets(); i++) {
String name1 = loc1.workbook.getSheetName(i);
String name2 = (loc2.workbook.getNumberOfSheets() > i) ? loc2.workbook.getSheetName(i) : "";
if (!name1.equals(name2)) {
String str = String.format(Locale.ROOT, "%s\nworkbook1 -> %s [%d] != workbook2 -> %s [%d]",
"Name of the sheets do not match ::", name1, i+1, name2, i+1
);
listOfDifferences.add(str);
}
}
}
/**
* Formats the message.
*/
private void addMessage(Locator loc1, Locator loc2, String messageStart, String value1, String value2) {
String str =
String.format(Locale.ROOT, "%s\nworkbook1 -> %s -> %s [%s] != workbook2 -> %s -> %s [%s]",
messageStart,
loc1.sheet.getSheetName(), new CellReference(loc1.cell).formatAsString(), value1,
loc2.sheet.getSheetName(), new CellReference(loc2.cell).formatAsString(), value2
);
listOfDifferences.add(str);
}
/**
* Checks if cell alignment matches.
*/
private void isCellAlignmentMatches(Locator loc1, Locator loc2) {
// TODO: check for NPE
short align1 = loc1.cell.getCellStyle().getAlignment();
short align2 = loc2.cell.getCellStyle().getAlignment();
if (align1 != align2) {
addMessage(loc1, loc2,
"Cell Alignment does not Match ::",
Short.toString(align1),
Short.toString(align2)
);
}
}
/**
* Checks if cell border bottom matches.
*/
private void isCellBorderMatches(Locator loc1, Locator loc2, char borderSide) {
if (!(loc1.cell instanceof XSSFCell)) return;
XSSFCellStyle style1 = ((XSSFCell)loc1.cell).getCellStyle();
XSSFCellStyle style2 = ((XSSFCell)loc2.cell).getCellStyle();
boolean b1, b2;
String borderName;
switch (borderSide) {
case 't': default:
b1 = style1.getBorderTopEnum() == BorderStyle.THIN;
b2 = style2.getBorderTopEnum() == BorderStyle.THIN;
borderName = "TOP";
break;
case 'b':
b1 = style1.getBorderBottomEnum() == BorderStyle.THIN;
b2 = style2.getBorderBottomEnum() == BorderStyle.THIN;
borderName = "BOTTOM";
break;
case 'l':
b1 = style1.getBorderLeftEnum() == BorderStyle.THIN;
b2 = style2.getBorderLeftEnum() == BorderStyle.THIN;
borderName = "LEFT";
break;
case 'r':
b1 = style1.getBorderRightEnum() == BorderStyle.THIN;
b2 = style2.getBorderRightEnum() == BorderStyle.THIN;
borderName = "RIGHT";
break;
}
if (b1 != b2) {
addMessage(loc1, loc2,
"Cell Border Attributes does not Match ::",
(b1 ? "" : "NOT ")+borderName+" BORDER",
(b2 ? "" : "NOT ")+borderName+" BORDER"
);
}
}
/**
* Checks if cell content matches.
*/
private void isCellContentMatches(Locator loc1, Locator loc2) {
// TODO: check for null and non-rich-text cells
String str1 = loc1.cell.getRichStringCellValue().getString();
String str2 = loc2.cell.getRichStringCellValue().getString();
if (!str1.equals(str2)) {
addMessage(loc1,loc2,CELL_DATA_DOES_NOT_MATCH,str1,str2);
}
}
/**
* Checks if cell content matches for boolean.
*/
private void isCellContentMatchesForBoolean(Locator loc1, Locator loc2) {
boolean b1 = loc1.cell.getBooleanCellValue();
boolean b2 = loc2.cell.getBooleanCellValue();
if (b1 != b2) {
addMessage(loc1,loc2,CELL_DATA_DOES_NOT_MATCH,Boolean.toString(b1),Boolean.toString(b2));
}
}
/**
* Checks if cell content matches for date.
*/
private void isCellContentMatchesForDate(Locator loc1, Locator loc2) {
Date date1 = loc1.cell.getDateCellValue();
Date date2 = loc2.cell.getDateCellValue();
if (!date1.equals(date2)) {
addMessage(loc1, loc2, CELL_DATA_DOES_NOT_MATCH, date1.toGMTString(), date2.toGMTString());
}
}
/**
* Checks if cell content matches for formula.
*/
private void isCellContentMatchesForFormula(Locator loc1, Locator loc2) {
// TODO: actually evaluate the formula / NPE checks
String form1 = loc1.cell.getCellFormula();
String form2 = loc2.cell.getCellFormula();
if (!form1.equals(form2)) {
addMessage(loc1, loc2, CELL_DATA_DOES_NOT_MATCH, form1, form2);
}
}
/**
* Checks if cell content matches for numeric.
*/
private void isCellContentMatchesForNumeric(Locator loc1, Locator loc2) {
// TODO: Check for NaN
double num1 = loc1.cell.getNumericCellValue();
double num2 = loc2.cell.getNumericCellValue();
if (num1 != num2) {
addMessage(loc1, loc2, CELL_DATA_DOES_NOT_MATCH, Double.toString(num1), Double.toString(num2));
}
}
private String getCellFillBackground(Locator loc) {
Color col = loc.cell.getCellStyle().getFillForegroundColorColor();
return (col instanceof XSSFColor) ? ((XSSFColor)col).getARGBHex() : "NO COLOR";
}
/**
* Checks if cell file back ground matches.
*/
private void isCellFillBackGroundMatches(Locator loc1, Locator loc2) {
String col1 = getCellFillBackground(loc1);
String col2 = getCellFillBackground(loc2);
if (!col1.equals(col2)) {
addMessage(loc1, loc2, "Cell Fill Color does not Match ::", col1, col2);
}
}
/**
* Checks if cell fill pattern matches.
*/
private void isCellFillPatternMatches(Locator loc1, Locator loc2) {
// TOOO: Check for NPE
short fill1 = loc1.cell.getCellStyle().getFillPattern();
short fill2 = loc2.cell.getCellStyle().getFillPattern();
if (fill1 != fill2) {
addMessage(loc1, loc2,
"Cell Fill pattern does not Match ::",
Short.toString(fill1),
Short.toString(fill2)
);
}
}
/**
* Checks if cell font bold matches.
*/
private void isCellFontBoldMatches(Locator loc1, Locator loc2) {
if (!(loc1.cell instanceof XSSFCell)) return;
boolean b1 = ((XSSFCell)loc1.cell).getCellStyle().getFont().getBold();
boolean b2 = ((XSSFCell)loc2.cell).getCellStyle().getFont().getBold();
if (b1 != b2) {
addMessage(loc1, loc2,
CELL_FONT_ATTRIBUTES_DOES_NOT_MATCH,
(b1 ? "" : "NOT ")+"BOLD",
(b2 ? "" : "NOT ")+"BOLD"
);
}
}
/**
* Checks if cell font family matches.
*/
private void isCellFontFamilyMatches(Locator loc1, Locator loc2) {
// TODO: Check for NPEs
if (!(loc1.cell instanceof XSSFCell)) return;
String family1 = ((XSSFCell)loc1.cell).getCellStyle().getFont().getFontName();
String family2 = ((XSSFCell)loc2.cell).getCellStyle().getFont().getFontName();
if (!family1.equals(family2)) {
addMessage(loc1, loc2, "Cell Font Family does not Match ::", family1, family2);
}
}
/**
* Checks if cell font italics matches.
*/
private void isCellFontItalicsMatches(Locator loc1, Locator loc2) {
if (!(loc1.cell instanceof XSSFCell)) return;
boolean b1 = ((XSSFCell)loc1.cell).getCellStyle().getFont().getItalic();
boolean b2 = ((XSSFCell)loc2.cell).getCellStyle().getFont().getItalic();
if (b1 != b2) {
addMessage(loc1, loc2,
CELL_FONT_ATTRIBUTES_DOES_NOT_MATCH,
(b1 ? "" : "NOT ")+"ITALICS",
(b2 ? "" : "NOT ")+"ITALICS"
);
}
}
/**
* Checks if cell font size matches.
*/
private void isCellFontSizeMatches(Locator loc1, Locator loc2) {
if (!(loc1.cell instanceof XSSFCell)) return;
short size1 = ((XSSFCell)loc1.cell).getCellStyle().getFont().getFontHeightInPoints();
short size2 = ((XSSFCell)loc2.cell).getCellStyle().getFont().getFontHeightInPoints();
if (size1 != size2) {
addMessage(loc1, loc2,
"Cell Font Size does not Match ::",
Short.toString(size1),
Short.toString(size2)
);
}
}
/**
* Checks if cell hidden matches.
*/
private void isCellHiddenMatches(Locator loc1, Locator loc2) {
boolean b1 = loc1.cell.getCellStyle().getHidden();
boolean b2 = loc1.cell.getCellStyle().getHidden();
if (b1 != b2) {
addMessage(loc1, loc2,
"Cell Visibility does not Match ::",
(b1 ? "" : "NOT ")+"HIDDEN",
(b2 ? "" : "NOT ")+"HIDDEN"
);
}
}
/**
* Checks if cell locked matches.
*/
private void isCellLockedMatches(Locator loc1, Locator loc2) {
boolean b1 = loc1.cell.getCellStyle().getLocked();
boolean b2 = loc1.cell.getCellStyle().getLocked();
if (b1 != b2) {
addMessage(loc1, loc2,
"Cell Protection does not Match ::",
(b1 ? "" : "NOT ")+"LOCKED",
(b2 ? "" : "NOT ")+"LOCKED"
);
}
}
/**
* Checks if cell type matches.
*/
private boolean isCellTypeMatches(Locator loc1, Locator loc2) {
CellType type1 = loc1.cell.getCellTypeEnum();
CellType type2 = loc2.cell.getCellTypeEnum();
if (type1 == type2) return true;
addMessage(loc1, loc2,
"Cell Data-Type does not Match in :: ",
type1.name(), type2.name()
);
return false;
}
/**
* Checks if cell under line matches.
*
* @param cellWorkBook1
* the cell work book1
* @param cellWorkBook2
* the cell work book2
* @return true, if cell under line matches
*/
private void isCellUnderLineMatches(Locator loc1, Locator loc2) {
// TOOO: distinguish underline type
if (!(loc1.cell instanceof XSSFCell)) return;
byte b1 = ((XSSFCell)loc1.cell).getCellStyle().getFont().getUnderline();
byte b2 = ((XSSFCell)loc2.cell).getCellStyle().getFont().getUnderline();
if (b1 != b2) {
addMessage(loc1, loc2,
CELL_FONT_ATTRIBUTES_DOES_NOT_MATCH,
(b1 == 1 ? "" : "NOT ")+"UNDERLINE",
(b2 == 1 ? "" : "NOT ")+"UNDERLINE"
);
}
}
}