使用openpyxl从一系列单元格复制，错误:颜色必须是aRGB十六进制值

Question

我正在用openpyxl将一系列单元格从一个工作簿复制到另一个工作簿，代码如下：

import openpyxl
import os
#Current path
path = os.path.dirname(os.path.abspath(__file__))
#Beregningsmodul navn
Beregningsmodul_moder_navn = "Beregning COREP LCR - MODER - 202202.xlsx"
#workbook_beregn path
beregn_path = path + "\" + Beregningsmodul_moder_navn
workbook_beregn = openpyxl.load_workbook(beregn_path)
#Skema 72 navn
skema_72_navn ="C_72_00_a.xlsx"
#skema path
skema_72_path = path + "\" + skema_72_navn
workbook_skema_72 = openpyxl.load_workbook(skema_72_path)
#Kopier til
wb_72C = workbook_beregn["72C"]['E8':'G54']
#kopier fra
C_72_00_a = workbook_skema_72["C_72_00_a"]['D9':'F55']
#Pair the rows
for row1,row2 in zip(C_72_00_a, workbook_beregn):
#within the row pair, pair the cells
for cell1, cell2 in zip(row1,row2):
#assign the value of cell 1 to the destination cell 2 for each row
cell2.value = cell1.value
#save document
workbook_beregn.save('destination.xlsx')

但我在运行时遇到以下错误：

Traceback (most recent call last):
File "d:PythonLCR_skema_opdater202203-testSkemaModerLCR_opdater_skema.py", line 18, in <module>
workbook_skema_72 = openpyxl.load_workbook(skema_72_path)
File "C:Userse694896Anaconda3libsite-packagesopenpyxlreaderexcel.py", line 317, in load_workbook
reader.read()
File "C:Userse694896Anaconda3libsite-packagesopenpyxlreaderexcel.py", line 281, in read
apply_stylesheet(self.archive, self.wb)
File "C:Userse694896Anaconda3libsite-packagesopenpyxlstylesstylesheet.py", line 198, in apply_stylesheet    
stylesheet = Stylesheet.from_tree(node)
File "C:Userse694896Anaconda3libsite-packagesopenpyxlstylesstylesheet.py", line 103, in from_tree
return super(Stylesheet, cls).from_tree(node)
File "C:Userse694896Anaconda3libsite-packagesopenpyxldescriptorsserialisable.py", line 83, in from_tree     
obj = desc.from_tree(el)
File "C:Userse694896Anaconda3libsite-packagesopenpyxldescriptorssequence.py", line 85, in from_tree
return [self.expected_type.from_tree(el) for el in node]
File "C:Userse694896Anaconda3libsite-packagesopenpyxldescriptorssequence.py", line 85, in <listcomp>        
return [self.expected_type.from_tree(el) for el in node]
File "C:Userse694896Anaconda3libsite-packagesopenpyxlstylesfonts.py", line 109, in from_tree
return super(Font, cls).from_tree(node)
File "C:Userse694896Anaconda3libsite-packagesopenpyxldescriptorsserialisable.py", line 87, in from_tree     
obj = desc.expected_type.from_tree(el)
File "C:Userse694896Anaconda3libsite-packagesopenpyxldescriptorsserialisable.py", line 103, in from_tree
return cls(**attrib)
File "C:Userse694896Anaconda3libsite-packagesopenpyxlstylescolors.py", line 93, in __init__
self.rgb = rgb
File "C:Userse694896Anaconda3libsite-packagesopenpyxlstylescolors.py", line 61, in __set__
raise ValueError("Colors must be aRGB hex values")
ValueError: Colors must be aRGB hex values
PS C:Userse694896> 

我做错了什么？工作簿中有一些颜色公式，但我不太在乎颜色——数字是重要的部分。

我希望你能给我指明正确的方向。

#更新：当我打开并保存文件时，问题已经解决，有没有办法避免手动操作？

Answer 1

我刚刚遇到了同样的问题。不确定你是否还需要答案，无论如何，我想给未来的人留下更优雅的解决方案。在你的代码前面插入这个猴子补丁代码：

from openpyxl.styles.colors import WHITE, RGB
__old_rgb_set__ = RGB.__set__

def __rgb_set_fixed__(self, instance, value):
try:
__old_rgb_set__(self, instance, value)
except ValueError as e:
if e.args[0] == 'Colors must be aRGB hex values':
__old_rgb_set__(self, instance, WHITE)  # Change default color here

RGB.__set__ = __rgb_set_fixed__

默认情况下，它会将无效颜色更正为白色。如果需要，可以设置其他颜色。

Answer 2

这不是一个好方法，但作为一种变通方法openpyxl/styles/colors.py你为什么不试着改变

def __set__(self, instance, value):
if not self.allow_none:
m = aRGB_REGEX.match(value)
if m is None:
"""raise ValueError("Colors must be aRGB hex values")"""
value = WHITE
if len(value) == 6:
value = "00" + value
super(RGB, self).__set__(instance, value)

Answer 3

我找到了一个解决方法。如果我打开文件并保存它，问题就解决了。

我发现最好的方法是使用以下代码来修复文件：

import os
import pyautogui
import time
filename = ["C_72_00_a.xlsx", "C_73_00_a.xlsx", "C_74_00_a.xlsx", "C_76_00_a.xlsx"]#list of filenames
path = "D:\Python\Intradag_opdater\04\2022-04-04\LCR skema"

def clean2(file_in):
open = os.path.join(path, file_in)
os.startfile(open,'edit')#open file
time.sleep(5)#timer to wait for excel to open
pyautogui.hotkey('ctrl', 's')#Save file
pyautogui.hotkey('alt', 'f4')#close excel


for x in filename:#loop through all files
clean2(x)
print(x)

它是有效的，但我并不为我找到的解决方案感到骄傲。