openpyxl在关闭文件时选中多个单元格时突出显示多个单元格 [英] openpyxl highlighting multiple cells when one of them is selected upon closing the file

问题描述

当我以编程方式突出显示openpyxl中的单元格时，我遇到了奇怪的行为，最初在这篇文章中进行了描述

I have come across weird behaviour when programmatically highlighting cells in openpyxl which was initially described in this post of mine. I spent some (much more) time investigating this and managed to track the source of this error. I don't know if this is the only way to reproduce it, maybe some steps are not necessary, but the following example code is closest to what I am actually doing:

import openpyxl

def doStuff(iterations=1, fName='test'):
    for i in range(iterations):
        wb = openpyxl.reader.excel.load_workbook(fName+'.xlsx')
        ws=wb.get_active_sheet()

        a1=ws.cell('A1')
        a1.style.fill.fill_type=openpyxl.style.Fill.FILL_SOLID
        a1.style.fill.start_color.index='0000CC'

        namedRanges=wb.get_named_ranges()
        for namedRange in namedRanges:
             if namedRange.name in 'myName':
                 desiredNamedRange=namedRange

        dest=desiredNamedRange.destinations
        namedCell=ws.cell(dest[0][2].split('$')[1]+dest[0][3].split('$')[2])
        namedCell.style.fill.fill_type=openpyxl.style.Fill.FILL_SOLID
        namedCell.style.fill.start_color.index='FF33CC'

        wb.save(fName+'.xlsx')

会发生什么情况，我用一些值(数字和文本)打开了一个已经存在的工作簿.数字位于我稍后将突出显示的单元格中( A1 和一个称为"myName"的单元格，在我的测试案例中为 E7 )，而文本位于相邻的列中.然后，我用一些颜色填充 A1 和命名的单元格并保存工作簿.如果需要，可以重复多次(以模拟长时间使用我的原始代码).

What happens is I open an already-existing workbook with some values (numbers and text). Numbers are located in the cells which I will highlight later (A1 and the one called "myName", which is E7 in my test cases) whereas the text is in adjacent columns. I then fill A1 and the named cell with some colours and save the workbook. This can be repeated a number of times if desired (to simulate prolonged usage of my original code).

第一次运行后，一切都会按预期运行.第一次运行后，当您在Excel中打开工作簿，对其中带有 A1 的一系列单元格应用"no fill"，并在静止时关闭工作簿 时，会出现此问题.保持选中这些单元格.当您再次以10或20次迭代运行我的代码时，您会注意到，当在Excel中打开测试工作簿时，最初保留的所选单元格区域现在被突出显示为与 A1 相同的颜色.

After the first run everything works fine as you'd expect. The problem occurs when, after first run, you open the workbook in Excel, apply "no fill" to a range of cells with A1 in it and close the workbook while still keeping those cells selected. When you run my code with 10 or 20 iterations again you'll notice, when you open the test workbook in Excel, that the cell range which you initially left selected is now highlighted to the same colour as A1.

我的问题是:

• 我在这里做错什么了吗?该方案的性质要求 它会多次执行此过程，而我不能禁止 用户关闭选择了单元格的工作簿.

• Am I doing something wrong here? The nature of the program requires it to do this procedure several times over and I cannot forbid the users from closing their workbooks with cells selected.

有什么办法可以防止这种情况的发生?而且，如果是的话，如何 这样吗?

Is there any way to prevent this from happening? And, if yes, how to do this?

其他信息 我研究了构成工作簿的XML文件，在其中观察到了这种行为(请参见

EXTRA INFO I've looked into the XML files which form the workbook where I observed this behaviour (see this website for the standard). I noticed that, for some reasons, the cell style reference changes when you leave them selected when using openpyxl to change the formatting. For example the style of cell B1 (letter s) should be 0 (or non-existent as 0 is assumed by default) whereas openpyxl changed it to 1 when running the above code on the workbook:

<c r="A1" s="1">
    <v>1</v>
</c>
<c r="B1" s="1" t="s">
    <v>0</v>
</c>

这将导致更改单元格样式.我非常怀疑自己在这里做错了什么，所以:

This causes the cell style to be changed. I have very strong doubts I am doing something wrong here, so:

我的新问题是: openpyxl模块的哪一部分最有可能对此负责?因为有些东西告诉我它需要修订.

My new question is: Which part of the openpyxl module is most likely to be responsible for this? Because something tells me it needs a revision.

推荐答案

不是我解决了这个问题(因为这将需要修改我没有时间的库)，但是我确实找到了解决方法: Python中的>模块，并通过COM从Python脚本驱动Excel.像梦一样工作，并且具有比openpyxl更多的功能.唯一的缺点是您需要Excel许可证才能使用此解决方案.对于有兴趣这样做的人，我建议您此页面.

Well not that I solved this problem (as this would require modifying the library which I have no time for) but I did find a way around this: win32com module in Python and driving Excel from a Python script through the COM. Works like a dream and has vastly more functionality than openpyxl. The only drawback being that you need Excel license in order to use this solution. For anyone interested in doing the same I do recommend this page.

这篇关于openpyxl在关闭文件时选中多个单元格时突出显示多个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！