Bug 49878 - setSheetHidden for XSSF workbook not working properly
Summary: setSheetHidden for XSSF workbook not working properly
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2010-09-03 10:53 UTC by Martin Studer
Modified: 2010-09-05 09:09 UTC (History)
0 users

XSSF workbook with a series of sheets some of which are hidden and very hidden (6.96 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2010-09-03 11:12 UTC, Martin Studer

Note You need to log in before you can comment on or make changes to this bug.
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:


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.