XLSX: a short row with one changed cell is shown as deleted and re-inserted (26.11)
Comparison for Java 26.11. Two workbooks with one sheet each, all cells plain text. Only RQ-1’s Status changes, from Draft to Approved.
old.xlsx
| ID | Title | Owner | Status | Risk |
|---|---|---|---|---|
| RQ-1 | Login | Ann | Draft | Low |
| RQ-2 | Export | Bob | Draft | High |
| RQ-3 | Search | Cid | Approved | Medium |
| RQ-4 | Audit log | Dee | Draft | Low |
new.xlsx
| ID | Title | Owner | Status | Risk |
|---|---|---|---|---|
| RQ-1 | Login | Ann | Approved | Low |
| RQ-2 | Export | Bob | Draft | High |
| RQ-3 | Search | Cid | Approved | Medium |
| RQ-4 | Audit log | Dee | Draft | Low |
CompareOptions options = new CompareOptions();
options.setDetalisationLevel(DetalisationLevel.LOW);
options.setWordsSeparatorChars(new char[] {' '});
options.setSensitivityOfComparison(90);
try (Comparer comparer = new Comparer("old.xlsx")) {
comparer.add("new.xlsx");
comparer.compare("result.xlsx", options);
}
Expected: RQ-1’s Status cell is marked inline (Draft deleted, Approved inserted), as it is when the same edit is made on RQ-4.
Actual: RQ-1 is shown as an inserted row followed by a deleted row, with no marks inside the cells.
What decides it
- The row has five words or fewer. Words count, characters don’t. A 5-word row is replaced even with 40-character cells, and a 6-word row is always marked inline. Which cell changes, what it changes to, and which row it is make no difference.
- The edited cell’s column doesn’t hold enough unchanged text. Nothing else in the sheet counts. Longer values or 200-character cells in other columns leave the cutoff where it was.
How many data rows the column needs before the same edit is marked inline:
| Other values in the column | Inline from |
|---|---|
one word, 8 letters (Approved, Rejected) |
12 rows |
one word, 5 letters (Draft) |
16 rows |
one word, 4 letters (Done) |
17 rows |
| one letter | 36 rows |
| one word, 19 letters | 8 rows |
two words (In review) |
6 rows |
| three or five words | 5 rows |
| empty | never |
With only 2 or 3 data rows, the whole column is replaced instead of the row.
For one-word values, the cutoff follows the column’s character count: about 93% of its characters must stay unchanged. Multi-word cells count for much more than their characters.
At the default sensitivity of 75, six-word rows are replaced too. setSensitivityOfComparisonForTables has no effect on XLSX.
Question
Is this expected? Short rows like this are common in spreadsheets such as risk registers and trackers. Is there an option that marks a one-cell edit inline whatever the row length?