iOfficeAI / iOfficeAI/OfficeCLI
[BUG] xlsx: remove sheet leaves dangling localSheetId in definedNames — file unreadable, validate passes
- 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
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