启动Excel时，如何使用openpyxl将Excel工作表中的所有列折叠全部隐藏或显示? [英] How to use openpyxl to make all column folds in Excel sheet all hidden or showed when starting Excel?

问题描述

我正在使用openpyxl修改现有的Excel文件.在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)  

首先，这些列已被分组.但是，仅当列尚未分组时，此代码才对我有用.其次，隐藏参数似乎不会影响输出.实际上，无论我是否将False的标志设置为True，我都会得到一个隐藏的组.

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

我期望的是，它需要所有列组/折叠并关闭它们.取而代之的是，此代码隐藏了所有列(从A到C).

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).

推荐答案

对于您的问题并不是一个很好的答案，但这是我对从Google表格生成的.xlsx文件进行源分割时发现的内容.首先，您使用 unzip 解压缩.xlsx文件，然后进入 xl/worksheets/sheet1.xml 或等效文件.

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:

• 最顶层(最上层)来自 6:40

第二级(中间)来自 6:13

第三级(底部)来自 6:8

，它们跨越6-40列.(列号从'A'开始为1索引).这是xlsx文件中的代码:

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>

一些注意事项:

• 列组永远不会重叠！如果这样做，它们会自动合并，这是不直观的.
• 最底层的级别被编号为最高.最重要的是outlineLevel#1
• 上方"大纲级别(较低的数字)将吞下"较低的.所以outlineLevel 1最终会跨越 6:40 ，而outlineLevel 2将会跨越 6:13

• 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

然后是生成的openpyxl代码:

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)

我将查看是否可以添加到 https://stackoverflow.com/users的软件包文档中/2385133/charlie-clark .这纯粹是xlsx规范(或行为)的细微差别，而不是特定于openpyxl的.但是，最好在API中提供示例.

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.

如果您对行而不是列感兴趣，请在这里查看我的答案: https://stackoverflow.com/a/67203916/7018268

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

这篇关于启动Excel时，如何使用openpyxl将Excel工作表中的所有列折叠全部隐藏或显示?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！