CODEWITHSUNDEEP
pythonexcelopenpyxl
William Tang
William Tang

Reputation: 53

How to use openpyxl to make all column folds in Excel sheet all hidden or showed when starting Excel?

I'm using openpyxl to modify an existing Excel file. In the Excel file, there are column folds. I want to write some code so that I can either show all the column folds or hide all the column folds.

I tried using this code to achieve my goal. However, there's two problems:

ws.column_dimensions.group(start='A', end='C', hidden=False)

First, the columns are already grouped. However, this code is only useful to me if the columns are not yet grouped. Second, the hidden parameter doesn't seem to affect the output. In fact, no matter if I put the flag to False to True, I get a group that is hidden.

for col in ['A', 'B', 'C']:
    ws.column_dimensions[col].hidden=False

What I expect is that it takes all the column groups/folds and close them. Instead of that, this code hides all the column (from A to C).

Upvotes: 1

Views: 2624

Answers (1)

maxslug
maxslug

Reputation: 121

Not quite an answer to your question but here is what I found when source-diving an .xlsx file generated from google sheets. First you unzip the .xlsx file using unzip then you go into xl/worksheets/sheet1.xml or equivalent.

Let's say I want to create three nested outline level of grouped columns:

  • Outermost (top) level goes from 6:40

  • Second (middle) level goes from 6:13

  • Third (bottom) level goes from 6:8

    and they span columns 6-40. (Column numbers are 1-indexed starting at 'A'). Here is the code in the xlsx file:

<cols>
  <col min="6" max="8" outlineLevel="3"/>
  <col min="9" max="13"  outlineLevel="2"/>
  <col min="14" max="40" outlineLevel="1"/>
</cols>

Some things to note:

  • Column groups never overlap! If they do they are automatically merged, which is non-intuitive.
  • The bottom most level is numbered the highest. The top one is outlineLevel #1
  • The "above" outline levels (lower numbered) will "swallow" the lower ones. So outlineLevel 1 will end up spanning 6:40 and outlineLevel 2 will span 6:13

Here is then the resulting openpyxl code:

ws.column_dimensions.group(get_column_letter(6), get_column_letter(8),outline_level=3)
ws.column_dimensions.group(get_column_letter(9), get_column_letter(13),outline_level=2)
ws.column_dimensions.group(get_column_letter(14), get_column_letter(40),outline_level=1)

I'll see if I can get something to add to the package documentation for https://stackoverflow.com/users/2385133/charlie-clark. This is purely a nuance of the xlsx spec (or behavior), not openpyxl specific. But it would be nice to have the example available in the API.

EDIT: If you are interested in rows instead of columns, see my answer here : https://stackoverflow.com/a/67203916/7018268

Upvotes: 3

Related Questions