xlrd and hidden worksheets

I use the xlrd module in Python scripts to extract data from Excel workbooks. You can use the Python xlrd module to list the worksheets in a workbook and you can use the xlrd.sheet "visibility" value to determine whether a sheet is hidden and, if it is hidden, whether a user can unhide the sheet. The value should be either 0, 1, or 2 with the numbers having the following meaning:

Visibility of the sheet:

0 = visible
1 = hidden (can be unhidden by user -- Format -> Sheet -> Unhide)
2 = "very hidden" (can be unhidden only by VBA macro)

If you right-click on a worksheet tab at the bottom of a workbook, you will usually see a "Hide" and "Unhide" option that will allow you to hide or unhide a sheet.

If you select the "hide" option, you can mark the worksheet you clicked on as hidden. If there are no worksheets currently hidden, the "unhide" option will be grayed out. If it isn't grayed out, one or more sheets are hidden and you can choose to unhide a hidden sheet.

But, when I received a "no sheet named" error message because the case of letters I was using for a sheet name didn't match the all uppercase letters of the actual sheet name when I tried to access a value from a hidden worksheet in a Python script, when I right-clicked on a worksheet tab at the bottom of the window Microsoft Excel for Mac 2016 on my MacBook Pro laptop, the Hide and Unhide options were both grayed out. And when I clicked on Format and selected Sheet from the menu bar at the top of the Excel window, Hide and Unhide were greyed out.

I knew the sheet was present and where the value I was looking for was located, though the workbook was created by someone else, because I could see a defined name for the value pointing to the location when I clicked on Insert, selected Name, and then Define. I saw the defined name I was interested in pointing to =REFERENCE!$L$33. When I modified my script to use the correct case for the sheet name, I was able to obtain the value. But when I also included some code to view the visibility setting for the sheet, I saw the visibility value listed as "1".

#!/usr/bin/python

# Display the value in cell L33 from a worksheet named "REFERENCE"
# in an Excel workbook and also display the visibility status of that
# worksheet, i.e, whether it is hidden or not hidden


import xlrd as xl

sheetname = "REFERENCE"
# Desired value is in L33
rowx = 32 # row 33, but 32 because first row is 0
colx = 11 # column L; A column is 0

file_name = raw_input("File: ")
workbook = xl.open_workbook(file_name)
theSheet = workbook.sheet_by_name(sheetname)

isHiddenNum = theSheet.visibility
print "Visibility =", isHiddenNum
if isHiddenNum == 0:
   print "Worksheet", sheetname, "is visible"
elif isHiddenNum == 1:
   print "Worksheet", sheetname, "hidden (can be unhidden by user)"
else:
   print "Worksheet", sheetname, "very hidden (can be unhidden only by VBA macro)"

theValue = theSheet.cell(rowx, colx)
print "Value =", theValue

$ ./sheetvalue.py 
File: ../CRQ1222539.xlsm
Visibility = 1
Worksheet REFERENCE hidden (can be unhidden by user)
Value = text:u'CRQ1222539'
$

The text:u before the value of the cell indicates the cell holds a text value with the text encoded in the Unicode standard. If I use the following print statement, I would only see CRQ1222539 after the equals sign.

print "Value =", theValue.value

The workbooks I receive are created by others using Excel on Microsoft Windows systems. When I open them on my Mac, I see the message "This workbook contains macros. Do you want to disable macros before opening the file?" The default option is "Disable Macros", but I normally choose "Enable Macros".

But when I opened the spreadsheet with macros disabled, I then was able to unhide the worksheet. When I clicked on Format and selected Sheet, none of the options were grayed out.

I was then able to choose Unhide and select the "REFERENCE" sheet, so that it would become visible.