XLSX comparison matches rows by position, so one deleted row marks every following row as changed

GroupDocs.Comparison Product Family
,
1
  • Product: GroupDocs.Comparison for Java 26.10
  • Runtime: Java 21, macOS
  • License: metered (evaluation mode gives identical results)

Summary

When a row is deleted from (or inserted into) a worksheet, the comparison pairs each row with the row at the same index in the other workbook instead of matching rows by content. Every row after the deletion is reported as rewritten, and the deleted row is shown as a deleted copy of the last row.

Steps to reproduce

  1. del-old.xlsx has 6 data rows, R-001 to R-006, on sheet Risks.
  2. del-new.xlsx is identical except that row R-003 is removed.
  3. Compare them with default options (RowDeletionRepro.java in the attachment):
try (Comparer comparer = new Comparer("del-old.xlsx")) {
  comparer.add("del-new.xlsx");
  comparer.compare("del-result.xlsx", new CompareOptions());
}

Expected

R-003 is marked as a deleted row in place. Rows R-004, R-005 and R-006 are unchanged. getChanges() reports the deleted row, plus the Summary sheet’s risk count changing from 6 to 5.

Actual

  • getChanges() returns 91 changes.
  • Rows R-003 to R-005 in the result are redlined word by word into R-004 to R-006. For example, cell A4 shows R-00 + deleted 3 + inserted 4.
  • The original R-006 is appended at the bottom inside a red border as a deleted row.
  • So three unchanged rows appear edited, and the removed row is labelled R-006.
Row Old file New file Shown in result
4 R-003 R-004 R-003 redlined into R-004
5 R-004 R-005 R-004 redlined into R-005
6 R-005 R-006 R-005 redlined into R-006
7 R-006 – R-006 as a deleted row

The same happens on real files. On a generated 300-row SBOM workbook where 4 rows were added and 24 changed, getChanges() returns 33,369 changes, 298 of 300 rows are marked (270 of them unchanged), changed rows are compared against the wrong old row, and the comparison takes 3 to 4.5 minutes (two runs). We can’t share that file, but the minimal repro shows the same behaviour.

Also tried

Option Changes Result
new CompareOptions() 91 Rows paired by position
setDetalisationLevel(DetalisationLevel.HIGH) 89 Same row pairing
setSensitivityOfComparisonForTables(100) 91 No difference

Question

Is there an option to match spreadsheet rows by content (an LCS-style alignment of rows), or to pick a key column for matching rows? If not, we would like to request it. Our use case is redlining generated files between releases, where rows are routinely added and removed.

Lower priority, observed in the same runs

  • Unchanged numeric cells come back as text, and formula cells are replaced with fixed values.
  • StyleSettings.setHighlightColor is not applied to changed cells.

Attachment

groupdocs-xlsx-row-repro.zip: del-old.xlsx, del-new.xlsx, del-result.xlsx, RowDeletionRepro.java, output.txt
groupdocs-xlsx-row-repro.zip (17.3 KB)

2

hi @ristomattip ,
Thank you for your detailed report and for providing the reproduction steps. We’ll investigate the issue and get back to you once we have more information.

3

hi @ristomattip ,

We’ve investigated the issue and were able to reproduce the problem where deleting a row causes all subsequent rows to be marked as changed. We’ll fix this in an upcoming release and let you know once it’s available.

Regarding StyleSettings.setHighlightColor , you’re right — there’s currently no way to set the background color for changed cells. We’ve also planned this improvement and expect to implement it soon.

As for formulas, the comparison currently uses their calculated values rather than the formulas themselves. We’ll look into how we can improve this, possibly by adding a new option to compare excel formula cells either by their calculated values or by the formula text. There’s one thing we’d like to discover. Suppose cell C2 contains =A2*B2 , and A2 changes from 2 to 4. In the comparison result, A2 would show both values (2 in red and 4 in blue), potentially resulting in 24 . If we keep the original formula in C2, it would then calculate 24*B2 instead of 4*B2 , so the result would no longer match the target document.

How would you expect formula comparison to work in this situation? Do you have some ideas? We’d appreciate your thoughts so we can better understand what would work for your use case.

1 Like
4

Thank you very much.

Do you have any estimates about the timeline of the position fix?

Formulas are not super important on our use case at this point, it was a question out of curiosity. I will get back to you if I get some ideas.

5

Hi! We’re planning to release on Monday.

1 Like
6

Hi.

We were expecting a new release yesterday, as planned, but haven’t seen it come through yet.

Could you share a quick update on where things stand and when we can expect the release?

7

Hi @ristomattip,

Sorry for the delay. The release that will contain the fix for row matching has grown into a large update, and we need some more time to finish testing it. As in your other thread, we’re sorry we didn’t let you know sooner.

We’ll post here as soon as the release is published.

8

Hi,

thanks for the update.

Do you have any estimate about the timeline of the release?

9

Hi @ristomattip,

Thank you for your patience, and sorry again for the delay. Rows are now matched by content in GroupDocs.Comparison for Java 26.11, which is now available:

<dependency>
    <groupId>com.groupdocs</groupId>
    <artifactId>groupdocs-comparison</artifactId>
    <version>26.11</version>
</dependency>

Your repro

del-old.xlsx compared with del-new.xlsx, default CompareOptions:

26.10 26.11
getChanges() 91 changes 8 changes
Row R-003 shown as R-006, appended at the bottom marked as deleted in its own place
Rows R-004 to R-006 redlined word by word unchanged

The 8 changes are the six cells of the deleted row R-003 and the risk count on the Summary sheet changing from 6 to 5, which is what you expected.

What changed

Before, the text used to align rows was made of the positions of a row’s non-empty cells, not of their content, so rows with the same layout always looked alike and were paired by index. Now a row is aligned by the content of its cells. Rows that are the same in both files are paired directly, and only the rows between them are aligned, so an inserted or deleted row no longer shifts every row after it.

The same applies to larger sheets. A 300 × 23 sheet with 4 inserted rows, roughly the shape of your SBOM report, now reports 92 changes (the 4 × 23 inserted cells) and is compared in about 10 seconds on our test laptop.

The other two points from your report

  • Unchanged cells are written back with their value, so numbers and dates keep their type instead of coming back as text.
  • StyleSettings.setHighlightColor now fills the background of the changed cells:
CompareOptions options = new CompareOptions();
StyleSettings deleted = new StyleSettings();
deleted.setHighlightColor(Color.PINK);
options.setDeletedItemStyle(deleted);
StyleSettings inserted = new StyleSettings();
inserted.setHighlightColor(Color.YELLOW);
options.setInsertedItemStyle(inserted);

Formula cells are still written with their calculated values, as we discussed above. Thank you for the answer on that; if you have ideas later, we’ll be glad to hear them.

Release notes: https://releases.groupdocs.com/comparison/java/release-notes/2026/groupdocs-comparison-for-java-26-11-release-notes/

Best regards,
Aleksei

1 Like