Created attachment 31942 [details] Patch: remove names referencing removed sheet Summary: [PATCH] Remove names pointing at a sheet when the sheet is deleted See attached patch. If a Name is scoped to a particular worksheet, then onSheetDelete removes the name when the sheet is removed. If the Name is however global, but points at the sheet being deleted (so that Name.getSheetName refers to the deleted sheet), the Name is not removed. It'd be preferable if the Name was also removed in this case. Patch including unit test attached.
Could you confirm what the behaviour is in Microsoft Excel when you delete a sheet with a global name pointing to it?
Excel leaves the name intact but replaces references to DeletedSheetName with #REF! I had previously thought that POI's current behaviour was leading to workbooks which Excel refused to open without repairing. However, I'm struggling to reproduce that, so perhaps this should be left as-is.
I'm somewhat minded to say we ought to do the same That might also be what we already effectively do for HSSF, since that stores sheet references in formulas (like names) differently and by index Could you tweak your unit test to try for .xls / hssf too, so we can verify what happens there?
From o.a.p.hssf.model.InternalWorkbook#removeSheet: >> Within NameRecords, it's ok to have the formula >> part point at deleted sheets. It's also ok to >> have the ExternSheetNumber point at deleted >> sheets. >> However, the sheet index must be adjusted, or >> excel will break. (Sheet index is either 0 for >> global, or 1 based index to sheet) Looks like the Names cleanup may need to span HSSFWorkbook and InternalWorkbook since HSSFWorkbook maintains a data structure for HSSFNames.