Finding hidden cells using openpyxl

Question

I've been trying to write a script to copy formatting from one workbook to another and, as anyone dealing with openpyxl knows, it's a big script. I've gotten it to work pretty well, but one thing I can't seem to figure out is how to read from the original if columns are hidden.

Can anyone tell me where to look in a workbook, worksheet, column or cell object to see where hidden columns are?

Answer 1

The attributes you are looking for are inside the column_dimensions and row_dimensions attributes of the Worksheet object.

These are bound dictionaries whose values are ColumnDimension/RowDimension objects. The specific attribute you're looking for is ColumnDimension.hidden.

The following will print the column letter of all hidden columns in worksheet ws:

for colLetter,colDimension in ws.column_dimensions.items():
  if colDimension.hidden == True:
     print(colLetter)

And for rows:

for rowNum,rowDimension in ws.row_dimensions.items():
  if rowDimension.hidden == True:
     print(rowNum)

As I understand it, loading your workbook as read_only can mess with ws.row_dimensions, so be careful in this case.

Answer 2

Worksheets have row_dimensions and column_dimensions objects which contain information about particular rows or columns, such as whether they are hidden or not. Column dimensions can also be grouped so you'll need to take that into consideration when looking.