Bug 56887

Summary: [PATCH] Remove names pointing at a sheet when the sheet is deleted
Product: POI Reporter: David North <dtn-asfbugs>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEEDINFO ---    
Severity: enhancement Keywords: PatchAvailable
Priority: P2    
Version: 3.11-dev   
Target Milestone: ---   
Hardware: PC   
OS: Linux   
Attachments: Patch: remove names referencing removed sheet

Description David North 2014-08-26 11:33:51 UTC
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.
Comment 1 Nick Burch 2014-08-26 11:51:19 UTC
Could you confirm what the behaviour is in Microsoft Excel when you delete a sheet with a global name pointing to it?
Comment 2 David North 2014-08-26 12:44:16 UTC
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.
Comment 3 Nick Burch 2014-08-26 13:04:01 UTC
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?
Comment 4 Javen O'Neal 2016-06-11 03:12:06 UTC
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.