Bug 49878

Summary: setSheetHidden for XSSF workbook not working properly
Product: POI Reporter: Martin Studer <martin.studer>
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.7-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: XSSF workbook with a series of sheets some of which are hidden and very hidden

Description Martin Studer 2010-09-03 10:53:16 UTC
setSheetHidden on an XSSF workbook does not work as documented. Basically, I found that 

int hidden = 2 --> means 'hidden'
int hidden = 3 --> means 'very hidden'

instead of as documented:
int hidden = 1 --> means 'hidden'
int hidden = 2 --> means 'very hidden'

On HSSF workbooks setSheetHidden is working fine and as documented.

POI: 3.7-beta2
OS: Windows 7 64bit
Java: 1.6.0_20 64bit
Comment 1 Nick Burch 2010-09-03 11:08:14 UTC
Any chance you could upload a simple .xlsx file, which has 3 sheets? One should be normal, one hidden, and one very hidden

We can then use this as a basis for a unit test for when we fix this.

(HSSF and XSSF should be using the same meaning for the hidden flags, so it's probably an off-by-one in the xssf code somewhere)
Comment 2 Martin Studer 2010-09-03 11:12:35 UTC
Created attachment 25985 [details]
XSSF workbook with a series of sheets some of which are hidden and very hidden

XSSF workbook with 5 sheets: AAA, BBB, CCC, DDD, EEE

Sheet 'BBB' is hidden
Sheet 'DDD' is very hidden

the rest of the sheets are normally visible (not hidden)
Comment 3 Yegor Kozlov 2010-09-05 09:09:48 UTC
Fixed in r992772.

Nick is right, it was a off-by-one issue. On the low level, the 'hidden' argument is converted into a XmlBeans enum and in XmlBeans ordinals of enums start with 1. 

I also improved the API. Passing an anonymous constant as the second argument of setSheetHidden(int sheetIx, int hidden) is not good. 

I added three constants to the Workbook interface:

Workbook.SHEET_STATE_VISIBLE
Workbook.SHEET_STATE_HIDDEN
Workbook.SHEET_STATE_VERY_HIDDEN

the correct usage of setSheetHidden is as  follows:

workbook.setSheetHidden(sheetIx, Workbook.SHEET_STATE_HIDDEN).

The method throws IllegalArgumentException if the view state is invalid.


Yegor