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
=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
I saw the following output when I ran the script:
$ ./sheetvalue.py File: ../CRQ1222539.xlsm Visibility = 1 Worksheet REFERENCE hidden (can be unhidden by user) Value = text:u'CRQ1222539' $
text:u before the value of the cell indicates the cell
holds a text value with the text encoded in the
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.