[Question] How to tell if a worksheet is hidden #464

New Issue

2016-08-31T01:37:52Z

enobufs commented

2016-08-31 01:37:52 +00:00

(Migrated from github.com)

This xlsx module has been working great!

I know a worksheet can be hidden/unhidden, and I would like to know if there is a way to tell if a particular sheet is hidden or not using this xlsx module. Is it supported?

This xlsx module has been working great! I know a worksheet can be hidden/unhidden, and I would like to know if there is a way to tell if a particular sheet is hidden or not using this xlsx module. Is it supported?

Thowk commented

2017-02-08 12:45:46 +00:00

(Migrated from github.com)

Hi. Having exactly the same problem. I was reading the code and found this method:

/* 2.4.28 */
function parse_BoundSheet8(blob, length, opts) {
	var pos = blob.read_shift(4);
	var hidden = blob.read_shift(1) >> 6;
	var dt = blob.read_shift(1);
	switch(dt) {
		case 0: dt = 'Worksheet'; break;
		case 1: dt = 'Macrosheet'; break;
		case 2: dt = 'Chartsheet'; break;
		case 6: dt = 'VBAModule'; break;
	}
	var name = parse_ShortXLUnicodeString(blob, 0, opts);
	if(name.length === 0) name = "Sheet1";
	return { pos:pos, hs:hidden, dt:dt, name:name };
}

As you can see there is a hidden property, which I suposse should have one of 3 states (visible, hidden, very hidden)

The problem is that it always have value: 0 (tested on xls with hidden datasheets)

Any ideas, how to read that Excel flag?

Hi. Having exactly the same problem. I was reading the code and found this method: ```javascript /* 2.4.28 */ function parse_BoundSheet8(blob, length, opts) { var pos = blob.read_shift(4); var hidden = blob.read_shift(1) >> 6; var dt = blob.read_shift(1); switch(dt) { case 0: dt = 'Worksheet'; break; case 1: dt = 'Macrosheet'; break; case 2: dt = 'Chartsheet'; break; case 6: dt = 'VBAModule'; break; } var name = parse_ShortXLUnicodeString(blob, 0, opts); if(name.length === 0) name = "Sheet1"; return { pos:pos, hs:hidden, dt:dt, name:name }; } ``` As you can see there is a **hidden** property, which I suposse should have one of 3 states (visible, hidden, very hidden) The problem is that it always have value: 0 (tested on xls with hidden datasheets) Any ideas, how to read that Excel flag?

SheetJSDev commented

2017-02-08 19:06:18 +00:00

(Migrated from github.com)

@Thowk good catch!

That comment points to section 2.4.28 of the [MS-XLS] spec. Web link
https://msdn.microsoft.com/en-us/library/dd773237(v=office.12).aspx

Based on the layout, the hidden state is stored as the lowest 2 bits of the fifth byte. The correct line should be: var hidden = blob.read_shift(1) & 0x03.

Even with the aforementioned correction, the hidden state is not stored anywhere. To follow along, in the parse_workbook function jump down to the BoundSheet8 case:

				case 'BoundSheet8': {
					Directory[val.pos] = val;
					opts.snames.push(val.name);
				} break;

So this just captures the position in the stream and stores the name of the sheet. We have to actually save the hidden state somewhere (it is passed along as val.hs in that block). This raises 2 questions:

where should the sheet visibility be stored?

XLSB and XLS both store the metadata in the workbook object so it's probably best to stick it somewhere in the object, probably as an array of sheet-level properties. But it is also tempting to just store it in the sheet objects themselves (with a key like !visibility)

what values are appropriate for the visibility states?

XLSX uses string values; XLS and XLSB use numbers, and VBA actually uses a different set of numbers:

State	VBA Constant	VBA	XLS	XLSB	XLSX
Visible	`xlSheetVisible`	-1	0	0	`"visible"`
Hidden	`xlSheetHidden`	0	1	1	`"hidden"`
Very Hidden	`xlSheetVeryHidden`	2	2	2	`"veryHidden"`

I think the VBA numbers differ from XLS/XLSB because 0 is commonly the default value for the file formats whereas VBA is using the serialized equivalent of true and false.

(VBA is relevant here because, as far as I can tell, the only way to set a worksheet to "very hidden" is to set the Visible property of the worksheet in the Visual Basic editor or in a VBA macro)

@Thowk good catch! That comment points to section 2.4.28 of the [MS-XLS] spec. Web link https://msdn.microsoft.com/en-us/library/dd773237(v=office.12).aspx Based on the layout, the hidden state is stored as the lowest 2 bits of the fifth byte. The correct line should be: `var hidden = blob.read_shift(1) & 0x03`. Even with the aforementioned correction, the hidden state is not stored anywhere. To follow along, in the parse_workbook function jump down to the `BoundSheet8` case: ```js case 'BoundSheet8': { Directory[val.pos] = val; opts.snames.push(val.name); } break; ``` So this just captures the position in the stream and stores the name of the sheet. We have to actually save the hidden state somewhere (it is passed along as `val.hs` in that block). This raises 2 questions: 1) where should the sheet visibility be stored? XLSB and XLS both store the metadata in the workbook object so it's probably best to stick it somewhere in the object, probably as an array of sheet-level properties. But it is also tempting to just store it in the sheet objects themselves (with a key like `!visibility`) 2) what values are appropriate for the visibility states? XLSX uses string values; XLS and XLSB use numbers, and VBA actually uses a different set of numbers: | State | VBA Constant | VBA | XLS | XLSB | XLSX | |:------------|:--------------------|----:|----:|-----:|---------------:| | Visible | `xlSheetVisible` | -1 | 0 | 0 | `"visible"` | | Hidden | `xlSheetHidden` | 0 | 1 | 1 | `"hidden"` | | Very Hidden | `xlSheetVeryHidden` | 2 | 2 | 2 | `"veryHidden"` | I think the VBA numbers differ from XLS/XLSB because `0` is commonly the default value for the file formats whereas VBA is using the serialized equivalent of `true` and `false`. (VBA is relevant here because, as far as I can tell, the only way to set a worksheet to "very hidden" is to set the Visible property of the worksheet in the Visual Basic editor or in a VBA macro)

👍 1

Thowk commented

2017-02-10 15:05:15 +00:00

(Migrated from github.com)

Thank you @SheetJSDev for help. Now it works, after removing right-shift operator (additional & 0x03 is optional I guess. Assuming that unused bits are always 0).

For me, this msdn Documentation is confusing. Their bits order are in different notation (big endian vs little endian (?), but I'm not sure if I understand that correctly.

Storing hidden state
For my specyfic needs, I simply added additional property to workbook like this:

function parse_workbook(blob, options)
//---
wb.VisibleSheetNames = Object.keys(Directory)
	.filter(function (x) { return Directory[x].hs == 0; })
	.map(function (x) { return Directory[x].name; })
//---
//---usage:
workbook = XLSX.read(bstr, { type: 'binary', bookSheets: true });
workbook.VisibleSheetNames

State values
The best for user will be to normalize visibility states across all workbook extensions.

I would like to add PR for that, but that need more complex changes, and tests. I'll think about that.

Thank you @SheetJSDev for help. Now it works, after removing right-shift operator (additional **& 0x03** is optional I guess. Assuming that unused bits are always 0). For me, this msdn Documentation is confusing. Their bits order are in different notation (big endian vs little endian (?), but I'm not sure if I understand that correctly. 1. Storing hidden state For my specyfic needs, I simply added additional property to workbook like this: ```javascript function parse_workbook(blob, options) //--- wb.VisibleSheetNames = Object.keys(Directory) .filter(function (x) { return Directory[x].hs == 0; }) .map(function (x) { return Directory[x].name; }) //--- //---usage: workbook = XLSX.read(bstr, { type: 'binary', bookSheets: true }); workbook.VisibleSheetNames ``` 2. State values The best for user will be to normalize visibility states across all workbook extensions. I would like to add PR for that, but that need more complex changes, and tests. I'll think about that.

SheetJSDev commented

2017-03-31 15:49:22 +00:00

(Migrated from github.com)

The final resolution is to store the metadata in the workbook's Workbook.Sheets[n].Hidden properties. The value 0 will correspond to visible, while 1 is hidden and 2 is very hidden. This ensures that !!Workbook.Sheets[n].Hidden will be true for hidden and very hidden sheets

The final resolution is to store the metadata in the workbook's `Workbook.Sheets[n].Hidden` properties. The value 0 will correspond to visible, while 1 is hidden and 2 is very hidden. This ensures that !!Workbook.Sheets[n].Hidden will be true for hidden and very hidden sheets

Sign in to join this conversation.