CODEWITHSUNDEEP
apachecompareapache-poi
user840930
user840930

Reputation: 5578

Apache POI : easy way to compare worksheets?

I need to compare two worksheets with Apache POI. Is there an easy way to do this, for instance a worksheet compare function, or must I compare each cell?

Upvotes: 2

Views: 2341

Answers (3)

Jonas_Hess
Jonas_Hess

Reputation: 2018

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;
import org.junit.Assert;


public class CompareExcel {

    public static void assertWorkbooksEqual(final XSSFWorkbook workbookA, final XSSFWorkbook workbookB) {
        for (int i = 0; i < workbookA.getNumberOfSheets(); i++) {
            final XSSFSheet sheetA = workbookA.getSheetAt(i);
            final XSSFSheet sheetB = workbookB.getSheetAt(i);

            compareTwoSheets(sheetA, sheetB);
        }
    }

    private static void compareTwoSheets(final XSSFSheet sheetA, final XSSFSheet sheetB) {
        final int firstRowA = sheetA.getFirstRowNum();
        final int lastRowB = sheetA.getLastRowNum();


        final String sheetName = sheetA.getSheetName();
        Assert.assertEquals(sheetName, sheetB.getSheetName());

        Assert.assertEquals("In sheet: " + sheetName, firstRowA, sheetB.getFirstRowNum());
        Assert.assertEquals("In sheet: " + sheetName, lastRowB, sheetB.getLastRowNum());

        for(int i=firstRowA; i <= lastRowB; i++) {

            final XSSFRow rowA = sheetA.getRow(i);
            final XSSFRow rowB = sheetB.getRow(i);

            compareTwoRows(sheetName, rowA, rowB);
        }
    }

    private static void compareTwoRows(final String sheetName, final XSSFRow rowA, final XSSFRow rowB) {
        if((rowA == null) && (rowB == null)) {
            return;
        }
        Assert.assertNotNull("In sheet: " + sheetName, rowA);
        Assert.assertNotNull("In sheet: " + sheetName, rowB);

        final int firstCellA = rowA.getFirstCellNum();
        final int lastCellA = rowA.getLastCellNum();

        Assert.assertEquals("In sheet: " + sheetName, firstCellA, rowB.getFirstCellNum());
        Assert.assertEquals("In sheet: " + sheetName, lastCellA, rowB.getLastCellNum());

        for(int i=firstCellA; i <= lastCellA; i++) {
            final XSSFCell cellA = rowA.getCell(i);
            final XSSFCell cellB = rowB.getCell(i);
            compareTwoCells(sheetName, cellA, cellB);
        }
    }

    private static void compareTwoCells(final String sheetName, final XSSFCell cellA, final XSSFCell cellB) {
        if((cellA == null) && (cellB == null)) {
            return;
        }
        Assert.assertNotNull(cellA);
        Assert.assertNotNull(cellB);

        String valueA = null;
        String valueB = null;

        Assert.assertEquals(cellA.getCellTypeEnum(), cellB.getCellTypeEnum());

        switch(cellA.getCellTypeEnum()) {
            case NUMERIC:
                valueA = String.valueOf(cellA.getNumericCellValue());
                valueB = String.valueOf(cellB.getNumericCellValue());
                break;
            case STRING:
                valueA = cellA.getStringCellValue();
                valueB = cellB.getStringCellValue();
                break;
            default:
                Assert.fail(
                        String.format(
                                "Unexpected cell type '%s' in sheet: '%s' row: '%s' column: '%s'",
                                cellA.getCellTypeEnum().name(),
                                sheetName,
                                cellA.getRowIndex(),
                                cellA.getColumnIndex()
                        ));
        }

        Assert.assertEquals(
                String.format(
                        "Cell values do not match in sheet: '%s' row: '%s' column: '%s'",
                        sheetName,
                        cellA.getRowIndex(),
                        cellA.getColumnIndex()
                ),
                valueA, valueB);
    }
}

Upvotes: 1

michael.d.snider
michael.d.snider

Reputation: 101

As this is one of the first issues that comes up on Google about comparing POI workbooks, I'd like to share a solution that works for unit testing. I found a good Hamcrest matcher to solve the issue:

http://baddotrobot.com/blog/2012/09/14/diff-excel-with-java-and-hamcrest/

Basically, import the library in through Maven (detailed in the Github README). Once you have two POI workbooks, you write an assertion similar to this:

MatcherAssert.assertThat("Workbooks to be identical", actualWorkbook,
    WorkbookMatcher.sameWorkbook(expectedWorkbook));

Note: I use MatcherAssert here to get retrieve more detailed notes on comparison, but JUnit Assert is usable as well. This is noted on the author's website.

It's available on GitHub and through the author's personal Maven repository.

Upvotes: 5

Ivan Klaric
Ivan Klaric

Reputation: 423

Try iterating through all the cells and calculating a running hash or something like that. You run it twice, once for each worksheet and if the hash is the same - the worksheets are the same. AFAIK, there is no out-of-the-box method doing that.

I believe this is a better approach than doing a compare on a cell-to-cell basis since you'll be having only one worksheet opened at once (as opposed to two worksheets in the cell-by-cell approach).

Upvotes: -1

Related Questions