iOfficeAI / iOfficeAI/OfficeCLI

[BUG] xlsx: remove sheet leaves dangling localSheetId in definedNames — file unreadable, validate passes

Open
#243 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
30.7k
Forks
2.1k
Avg merge
9d 8h
Merged PRs (30d)
5

Description

Version: 1.0.138 (Homebrew build, --no-self-contained), macOS 26.5.2 arm64.

Summary: `remove` on a sheet does not renumber/fix up `localSheetId` indices in `definedNames` (e.g. `_xlnm.Print_Area`) that reference sheets positioned after the removed one. This leaves a dangling reference to a sheet index that no longer exists, and Excel refuses to open the resulting file. `validate` reports the file as passing with no errors.

## Steps to reproduce

```bash
officecli create repro.xlsx
officecli add repro.xlsx / --type sheet --prop name=Data
officecli set repro.xlsx /Data --prop printArea=A1:C10
officecli close repro.xlsx
officecli remove repro.xlsx /Sheet1
officecli close repro.xlsx
```

## Observed

`xl/workbook.xml` after the remove:

```xml

Data!A1:C10

```

Only one sheet remains (valid `localSheetId` is `0`), but the defined name is still `localSheetId="1"` — `Data` shifted from index 1 to index 0 when `Sheet1` was removed, but the defined name wasn't renumbered along with it.

- Opening in Excel: file is reported as unreadable / needs repair.
- `officecli validate repro.xlsx --json` → `{"success": true, "data": "Validation passed: no errors found."}` — does not catch this.
- openpyxl (more lenient) surfaces it as a warning rather than silently dropping it:
```
UserWarning: Defined names for sheet index 1 cannot be located
```

## Expected

- `remove` on a sheet should renumber `localSheetId` references (in `definedNames`, and anywhere else a sheet is referenced by index) for sheets after the removed one, or drop the defined name if it becomes orphaned.
- `validate` should flag a `definedName` whose `localSheetId` is out of range for the remaining sheet count, since it produces a file Excel can't open.

## Notes

Looks like the same class of issue as #189 (sheet rename not cascading to dependent references, `validate` passing anyway) — sheet-mutating operations (rename, remove, possibly move) don't consistently propagate to structures that reference sheets by index/name, and `validate` doesn't catch the resulting inconsistency.

Contributor guide

Open the contributing guide

Research direction

Start by running the listed officecli create, add, set, remove, close, and validate commands, then inspect xl/workbook.xml before and after removing Sheet1. Trace the remove operation and validation path for definedNames; done means references are renumbered or orphaned names are dropped, and validate reports an out-of-range localSheetId instead of passing.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
cli
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.