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

Solution 1:

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