5 Ways to Compare Excel Sheets in Java

In today’s data-driven world, the ability to compare data from multiple sources quickly and accurately is indispensable. For businesses and individuals alike, Excel remains a ubiquitous tool for data storage and analysis. However, when it comes to comparing two or more Excel sheets programmatically, Java offers robust solutions that can automate this process, saving time and reducing human error. Let’s delve into five effective ways to compare Excel sheets using Java:

1. Using Apache POI

Apache POI is a powerful library for working with Microsoft Office documents in Java. Here’s how to use it to compare Excel sheets:

import org.apache.poi.ss.usermodel.*;

public class ExcelComparator {
    public static void compareExcelFiles(String file1, String file2) throws IOException {
        Workbook workbook1 = WorkbookFactory.create(new File(file1));
        Workbook workbook2 = WorkbookFactory.create(new File(file2));

        for (int i = 0; i < workbook1.getNumberOfSheets(); i++) {
            Sheet sheet1 = workbook1.getSheetAt(i);
            Sheet sheet2 = workbook2.getSheetAt(i);

            // Compare sheet names
            if (!sheet1.getSheetName().equals(sheet2.getSheetName())) {
                System.out.println("Sheets are not identical");
                return;
            }

            // Compare row by row
            for (Row row1 : sheet1) {
                Row row2 = sheet2.getRow(row1.getRowNum());
                if (!rowsEqual(row1, row2)) {
                    System.out.println("Sheets are not identical");
                    return;
                }
            }

            if (sheet1.getLastRowNum() != sheet2.getLastRowNum()) {
                System.out.println("Sheets are not identical");
                return;
            }
        }
        System.out.println("Sheets are identical");
    }

    private static boolean rowsEqual(Row row1, Row row2) {
        if ((row1 == null) != (row2 == null)) return false;
        if (row1 == null) return true;

        for (Cell cell1 : row1) {
            Cell cell2 = row2.getCell(cell1.getColumnIndex());
            if (!cellsEqual(cell1, cell2)) return false;
        }
        return true;
    }

    private static boolean cellsEqual(Cell cell1, Cell cell2) {
        if (cell1 == null && cell2 == null) return true;
        if ((cell1 == null) != (cell2 == null)) return false;

        CellType type1 = cell1.getCellType();
        if (cell2.getCellType() != type1) return false;

        switch (type1) {
            case STRING:
                return cell1.getStringCellValue().equals(cell2.getStringCellValue());
            case NUMERIC:
                return cell1.getNumericCellValue() == cell2.getNumericCellValue();
            // Add other types as needed
            default:
                return false;
        }
    }
}

🎓 Note: This method only checks for absolute identity in structure and content. If order or structure is different, this comparison will not show equality.

2. Using JExcelApi

JExcelApi is an alternative library for Excel manipulation in Java, offering more lightweight options:

import jxl.*;

public class JExcelComparator {
    public static void compareSheets(String file1, String file2) throws BiffException, IOException {
        Workbook workbook1 = Workbook.getWorkbook(new File(file1));
        Workbook workbook2 = Workbook.getWorkbook(new File(file2));

        for (int sheetIndex = 0; sheetIndex < workbook1.getNumberOfSheets(); sheetIndex++) {
            Sheet sheet1 = workbook1.getSheet(sheetIndex);
            Sheet sheet2 = workbook2.getSheet(sheetIndex);

            // Compare sheet names
            if (!sheet1.getName().equals(sheet2.getName())) {
                System.out.println("Sheets are not identical");
                return;
            }

            // Compare cells
            for (int row = 0; row < sheet1.getRows(); row++) {
                for (int col = 0; col < sheet1.getColumns(); col++) {
                    Cell cell1 = sheet1.getCell(col, row);
                    Cell cell2 = sheet2.getCell(col, row);

                    if (cell1.getType() != cell2.getType() || !cell1.getContents().equals(cell2.getContents())) {
                        System.out.println("Sheets are not identical");
                        return;
                    }
                }
            }
        }
        System.out.println("Sheets are identical");
    }
}

3. Using a Third-Party Diff Tool

Sometimes, leveraging external diff tools through Java can simplify complex comparisons:

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;

public class DiffToolComparator {
    public static void compareExcelFiles(String file1, String file2, String diffToolPath) throws IOException, InterruptedException {
        ProcessBuilder pb = new ProcessBuilder(diffToolPath, file1, file2);
        Process p = pb.start();
        int exitVal = p.waitFor();

        if (exitVal == 0) {
            System.out.println("Sheets are identical according to " + diffToolPath);
        } else {
            System.out.println("Sheets differ");
        }
    }
}

⚠️ Note: Ensure the diff tool supports Excel file formats or convert Excel to plain text before comparison.

4. Custom XML Comparison

If you export Excel files to XML, you can compare these XML files directly:

import org.w3c.dom.Document;
import org.xml.sax.SAXException;

public class XmlComparator {
    public static boolean compareXmlFiles(String file1, String file2) throws IOException, SAXException {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();

        Document doc1 = builder.parse(new File(file1));
        Document doc2 = builder.parse(new File(file2));

        return doc1.isEqualNode(doc2);
    }
}

5. Using Hash Functions

For a simple equality check without worrying about the specifics:

import java.nio.file.*;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

public class HashComparator {
    public static boolean compareFilesByHash(String file1, String file2) throws IOException, NoSuchAlgorithmException {
        MessageDigest md = MessageDigest.getInstance("SHA-256");
        byte[] digest1 = md.digest(Files.readAllBytes(Paths.get(file1)));
        byte[] digest2 = md.digest(Files.readAllBytes(Paths.get(file2)));

        return MessageDigest.isEqual(digest1, digest2);
    }
}

Final Thoughts

Comparing Excel sheets in Java can be approached in various ways, each with its own advantages:

• Apache POI is robust but heavyweight.
• JExcelApi is lighter but less comprehensive.
• External Tools can simplify the process but rely on third-party software.
• XML Comparison works well for structured data but requires a conversion step.
• Hash Functions provide a quick check for equality but offer no insight into the differences.

Choosing the right method depends on your specific needs, such as the level of detail required in the comparison, the nature of the data, and the resources available. Java’s versatility in handling different libraries and methods for Excel comparison ensures you can find a solution tailored to your business or personal project requirements.

Ultimately, automating Excel sheet comparison not only reduces manual effort but also improves accuracy and allows for complex comparisons that would be impractical to perform manually.

Which method is best for comparing large datasets?

+

Using Apache POI or a hash function might be better due to their capability to handle large files and provide detailed or quick comparisons, respectively.

Can I compare Excel files across different versions?

+

Yes, if the library or tool you use supports the file formats of those versions. Both Apache POI and JExcelApi can handle multiple Excel formats.

How do I deal with formatting when comparing?

+

You’d need a method that checks for formatting as well. Apache POI supports comparing cell styles, fonts, and colors.