Excel为什么不能“按颜色过滤"?关于由 openpyxl 创建的样式? [英] Why can't Excel "filter by color" on styles created by openpyxl?

问题描述

我使用 openpyxl 创建了一个 Excel 工作簿和工作表.

我像这样设置了一些单元格的填充和字体颜色:

ws.cell(row=2, column=1).font = some_font_defined_earlierws.cell(row=2, column=1).fill = some_fill_defined_earlier

字体和填充应用正确，在 Excel 中看起来很棒.但是，在第 1 行的自动过滤器中，我没有看到通常的按颜色过滤"选项，当它们在 Excel 中被着色时，我通常会看到这些选项.

我是否以错误/不同的方式应用了样式?如何设置填充和字体颜色，同时让 Excel 允许我过滤颜色?

明确地说，我已经使用以下方法应用了自动过滤器:

ws.auto_filter.ref = ws.dimensions

我看到了值的自动过滤器，但没有看到颜色.Filter by Color 显示为灰色，即使许多单元格都有颜色.

解决方案

看来这一切的答案都在 Excel 中，而不是在工作簿中.对于 Filter by Color 变灰，Excel 似乎有一些不可预测的行为.

就我而言，我有超过 10,000 行，并且如 提示&技巧 120 - 按颜色过滤下拉菜单变灰问题，按颜色过滤 无法工作:

<块引用>

4. 此外，如果第一个彩色单元格出现在大约 10000 行之后，Excel 可能无法检测到颜色.要克服这种行为，请添加颜色在第一个单元格中，现在它将启用按颜色过滤.仅有的问题是这个彩色单元格将在您的过滤列表中额外显示.

I created an Excel workbook and worksheet using openpyxl.

I set the fill and font colour of some cells like this:

ws.cell(row=2, column=1).font = some_font_defined_earlier
ws.cell(row=2, column=1).fill = some_fill_defined_earlier

The font and fill are applied correctly and look great in Excel. However, in the autofilter of row 1, I do not see the usual "Filter by Color" options that I typically see with coloured cells when they've been coloured in Excel.

Have I applied the styles in a wrong/different way? How can I set the fill and font colour but also have Excel allow me to filter on the colours?

EDIT1:

To be clear, I have applied the autofilter using:

ws.auto_filter.ref = ws.dimensions

I see the autofilter on the values, but not the colours. Filter by Color is greyed out, even though many of the cells have colours.

解决方案

It appears the answer to this is all in Excel, not in the Workbook. Excel seems to have some unpredictable behaviour with respect to Filter by Color being greyed out.

In my case, I had more than 10,000 rows and, as described in #4 of Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem, Filter by Color can just not work:

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

这篇关于Excel为什么不能“按颜色过滤"?关于由 openpyxl 创建的样式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！