To compare two Excel workbooks in Java, use Apache POI to open each file, match sheets by name, and compare corresponding cells. The example below reports missing sheets and differences in cell values or formulas; it ignores formatting and treats an absent cell and a blank cell as equivalent.
Why use Apache POI?
Apache POI is a Java library for reading and writing Microsoft Office file formats. Its Excel support lets a program open common binary and XML workbooks, including older .xls files and newer .xlsx files, without requiring Excel to be installed. The WorkbookFactory class can select the appropriate workbook implementation based on the file it opens.
For a Java project using Maven, add Apache POI’s poi-ooxml artifact as a dependency; it includes the workbook-reading APIs used below. Use a current compatible release and follow its documentation for your Java version. If you use Gradle, add the same artifact to the project dependencies. The library is open source, but it can still take memory and processing time when workbooks are large.
A useful comparison starts by deciding what counts as “the same.” Two workbooks might contain identical displayed numbers but different formulas, or identical values with different formatting. There is no universal comparison that answers every such question, so the sample makes its rules explicit.
What the example compares
The program pairs worksheets by their names, not by their position in the workbook. For each pair, it checks cells at the same row and column. It reports a difference if a sheet exists on only one side, if cell types differ, or if cell contents differ. Formula cells are compared by their formula text. Numeric cells are compared as stored numeric values, and text, Boolean, and error cells are checked according to their types.
- Sheet order is ignored; sheet names determine the matching pairs.
- Formula text is compared, but formulas are not recalculated and cached results are not compared.
- Cell styles, column widths, row heights, comments, charts, images, and workbook metadata are outside the comparison.
- A missing cell and an explicitly blank cell are treated as equal.
These choices are appropriate for a straightforward content check, but may not fit a test that cares about presentation, calculated results, or workbook structure. The later sections explain ways to change the comparison.
Java code to compare two Excel files
Save this class as ExcelCompare.java. Run it with the two workbook paths as command-line arguments. It prints a line for every detected difference; if it prints none, the workbooks match under the rules above.
import java.io.File;
import java.util.Objects;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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;
public final class ExcelCompare {
public static void main(String[] args) throws Exception {
if (args.length != 2) {
System.err.println("Usage: java ExcelCompare <first.xlsx> <second.xlsx>");
System.exit(2);
}
try (Workbook first = WorkbookFactory.create(new File(args[0]));
Workbook second = WorkbookFactory.create(new File(args[1]))) {
compareWorkbooks(first, second);
}
}
private static void compareWorkbooks(Workbook first, Workbook second) {
for (int i = 0; i < first.getNumberOfSheets(); i++) {
Sheet left = first.getSheetAt(i);
Sheet right = second.getSheet(left.getSheetName());
if (right == null) {
System.out.println("Sheet only in first file: " + left.getSheetName());
} else {
compareSheets(left, right);
}
}
for (int i = 0; i < second.getNumberOfSheets(); i++) {
String name = second.getSheetAt(i).getSheetName();
if (first.getSheet(name) == null) {
System.out.println("Sheet only in second file: " + name);
}
}
}
private static void compareSheets(Sheet left, Sheet right) {
int lastRow = Math.max(left.getLastRowNum(), right.getLastRowNum());
for (int r = 0; r <= lastRow; r++) {
Row leftRow = left.getRow(r);
Row rightRow = right.getRow(r);
int leftLast = leftRow == null ? 0 : Math.max(0, leftRow.getLastCellNum());
int rightLast = rightRow == null ? 0 : Math.max(0, rightRow.getLastCellNum());
int lastCell = Math.max(leftLast, rightLast);
for (int c = 0; c < lastCell; c++) {
Cell a = leftRow == null ? null : leftRow.getCell(c);
Cell b = rightRow == null ? null : rightRow.getCell(c);
if (!sameCell(a, b)) {
String address = CellReference.convertNumToColString(c) + (r + 1);
System.out.printf("%s!%s: first=%s, second=%s%n",
left.getSheetName(), address, describe(a), describe(b));
}
}
}
}
private static boolean sameCell(Cell a, Cell b) {
if (isBlank(a) || isBlank(b)) {
return isBlank(a) && isBlank(b);
}
if (a.getCellType() != b.getCellType()) {
return false;
}
switch (a.getCellType()) {
case FORMULA:
return Objects.equals(a.getCellFormula(), b.getCellFormula());
case STRING:
return Objects.equals(a.getStringCellValue(), b.getStringCellValue());
case NUMERIC:
return Double.compare(a.getNumericCellValue(), b.getNumericCellValue()) == 0;
case BOOLEAN:
return a.getBooleanCellValue() == b.getBooleanCellValue();
case ERROR:
return a.getErrorCellValue() == b.getErrorCellValue();
default:
return true;
}
}
private static boolean isBlank(Cell cell) {
return cell == null || cell.getCellType() == CellType.BLANK;
}
private static String describe(Cell cell) {
if (cell == null) {
return "<missing>";
}
if (cell.getCellType() == CellType.FORMULA) {
return "FORMULA(" + cell.getCellFormula() + ")";
}
return cell.getCellType() + "(" + cell.toString() + ")";
}
}
How to run it and read the output
Compile and run the class with Apache POI and its required libraries available on the classpath. In a Maven or Gradle project, the build tool normally resolves those libraries for you. For a command-line project, make sure the POI dependencies are included in the classpath during both compilation and execution.
- Keep a copy of each input file accessible to the Java process.
- Pass the first workbook path and then the second workbook path, for example
java ExcelCompare old.xlsx new.xlsxwhen the classpath is already configured. - Review the printed sheet names and cell addresses. An address such as
Summary!C7means the difference is on the Summary sheet, in column C, row 7. - If the program prints no differences, interpret that as “equal under this program’s comparison rules,” not proof that every workbook feature is identical.
For example, if the first file has the text North in Summary!C7 and the second has South, the program reports both values and the address. If a sheet appears only in the second workbook, it reports the sheet separately rather than trying to compare it to an unrelated sheet at the same position.
Formulas, displayed values, and dates
A formula cell stores a formula and may also contain a cached result from the last calculation performed by a spreadsheet application. The example compares formula text. Thus, =A1+B1 and =SUM(A1:B1) are different even if they currently produce the same number. It also does not evaluate formulas, so it cannot tell you whether different formulas happen to return equal results.
If your requirement is to compare calculated results instead, use POI’s FormulaEvaluator and compare the evaluated cell values. Be aware that results can depend on formula support, external links, calculation settings, and whether the workbook has up-to-date inputs. For dependable result comparisons, test the formulas and decide how to handle errors and unsupported functions. Do not assume that a cached value is fresh just because it is present.
Excel dates are generally represented internally as numeric serial values, with a number format controlling how they appear. This code compares the stored numbers and ignores number formats. Consequently, two cells containing the same serial number compare equal even if one is formatted as a date and the other as a decimal. Conversely, values that display similarly after rounding can still compare different if their underlying numbers differ.
Important limits and useful adjustments
The sample uses exact comparisons. For measurements where tiny floating-point differences should count as equal, replace the numeric comparison with a tolerance chosen for the data. For example, comparing whether the absolute difference is below a threshold is often more meaningful for calculated measurements, but there is no single correct threshold for every workbook. Currency, identifiers, and dates may require different rules from scientific measurements.
You may also want to change how blanks, text, and formulas are handled. Decide, for example, whether leading or trailing spaces matter, whether text comparisons should ignore letter case, and whether formulas should be compared as written or by result. If you normalize text, make that rule explicit: trimming spaces can make two values compare equal even though Excel users see different cell contents when editing.
- To compare formatting, inspect cell styles as well as values. Compare only the style properties that matter, such as number format or fill, rather than assuming style identifiers are meaningful across separate workbooks.
- To compare sheet order, check each sheet name at the same index instead of matching by name alone.
- To treat absent and blank cells differently, revise
isBlankso a null cell does not automatically equal a cell whose type isBLANK. - To produce a reusable report, collect differences into a CSV, JSON, or a new workbook instead of printing them to the console.
Performance, file types, and safe handling
The code scans the rectangular range from the first row through the last row used by either sheet, checking columns up to the last cell in each row. That is simple and usually adequate for ordinary reports. A workbook with formatting applied far beyond its visible data can make its recorded used range much larger than expected, leading to unnecessary work. For large or sparse sheets, consider iterating only existing rows and cells, while still accounting for cells present in one file but not the other.
Both workbooks are opened at once, so memory use increases with their size. Close them with try-with-resources, as shown, and avoid loading many large files concurrently without measuring the memory impact. WorkbookFactory.create(File) handles common Excel workbook formats, but password-protected files need a password-aware approach, and unusual workbook features may need additional handling. The example reads the files only; it does not modify or save them.
Workbook files are external input. In a service that accepts uploads, limit file size, restrict access to stored files, and handle parsing errors rather than assuming every upload is a valid workbook. Use maintained POI releases and review the library’s security advisories and documentation as part of normal dependency maintenance.
Alternatives and choosing the right approach
Apache POI is a practical choice when the comparison must run in Java and you need control over exactly which workbook properties count. A dedicated spreadsheet-diff product may be easier if users need a visual side-by-side report, but check whether it compares formulas, formatting, hidden sheets, and workbook structure in the way your process requires. Excel’s own features can help people inspect changes manually, but a repeatable automated comparison needs clear rules and a report that can be checked or archived.
Before selecting an approach, try representative files: a normal workbook, one with formulas, one with blank-looking cells, and one with formatting or hidden sheets that matter to the task. Compare the tool’s output with the decisions a human reviewer would make. This small validation step helps expose assumptions such as matching sheets by position or treating displayed text as the underlying value.
Frequently Asked Questions
Can Java compare two Excel files without Microsoft Excel installed?
Yes. Apache POI reads and compares workbook files in Java without launching Excel. The sample reads common .xls and .xlsx formats and does not require the desktop application.
Does this code compare cell formatting?
No. It compares cell contents and formula text, not fonts, fills, borders, number formats, widths, or other presentation details. Add explicit style-property comparisons if formatting differences are important.
Will it detect a formula whose result changed?
It detects a change in formula text, but it does not calculate formulas or compare cached results. Use a formula evaluator and define how to handle calculation limitations if your goal is to compare computed results.
Does it match worksheets by their order?
No. It matches worksheets by name, so reordering sheets alone is not reported. Change the sheet-matching logic if order is part of the workbook requirements.
Can it compare two files with different extensions?
Often, yes. WorkbookFactory determines the workbook format from the file contents, so the comparison logic can work across supported Excel formats. Features specific to a format may still need separate testing.
Why are cells that look the same reported as different?
Displayed appearance is not necessarily the stored value. Number formats can round or reformat values, and formulas can differ while returning the same result. Check the reported cell types and decide whether your comparison should use raw values, evaluated results, or formatted display text.
Related
- Undo an Excel Table Without Losing Your Data
- Undo Changes in Excel After Saving: Your Recovery Options
- How to Turn On AutoFit in Excel for Rows and Columns
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Checkbox Conditional Formatting GuideTo apply conditional formatting to a Google Sheets checkbox, select the cells you want to format, open Format…
- How to Protect a Sheet in Excel: Steps and TipsTo protect a sheet in Excel for Microsoft 365 on Windows, open the worksheet, select Review > Protect…
- How to Compare Two Excel Sheets for Matching ValuesTo find values from one sheet anywhere in another, put this formula beside the first value: =IF(COUNTIF(Sheet2!$A:$A,A2)>0,"Match","Missing"). With…
- How to Insert a CAC Signature in WordTo sign a Word document with a CAC, open it in the desktop version of Word for Windows,…
- Outlook Email Signature: How to Create a New SignatureTo create a new email signature in Outlook, open the signature settings for your version of Outlook, choose…
- Excel Geographical Heat Map: Create and Troubleshoot OneIn Excel for Microsoft 365 on Windows, select a column of named geographic regions and a column of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.