Copying from a range of cells with openpyxl, Error: Colors must be aRGB hex values

Question

I am copying a range of cells with openpyxl from one workbook to another with the following code:

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

But I get the following error when running it:

Traceback (most recent call last):
  File "d:\Python\LCR_skema_opdater\202203-test\Skema\Moder\LCR_opdater_skema.py", line 18, in <module>
    workbook_skema_72 = openpyxl.load_workbook(skema_72_path)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 317, in load_workbook
    reader.read()
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\reader\excel.py", line 281, in read
    apply_stylesheet(self.archive, self.wb)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 198, in apply_stylesheet    
    stylesheet = Stylesheet.from_tree(node)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py", line 103, in from_tree
    return super(Stylesheet, cls).from_tree(node)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 83, in from_tree     
    obj = desc.from_tree(el)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\descriptors\sequence.py", line 85, in from_tree
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\descriptors\sequence.py", line 85, in <listcomp>        
    return [self.expected_type.from_tree(el) for el in node]
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\styles\fonts.py", line 109, in from_tree
    return super(Font, cls).from_tree(node)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 87, in from_tree     
    obj = desc.expected_type.from_tree(el)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\styles\colors.py", line 93, in __init__
    self.rgb = rgb
  File "C:\Users\e694896\Anaconda3\lib\site-packages\openpyxl\styles\colors.py", line 61, in __set__
    raise ValueError("Colors must be aRGB hex values")
ValueError: Colors must be aRGB hex values
PS C:\Users\e694896> 

What am I doing wrong? There us some color fomatting in the workbooks, but I don't care that much about the colors the numbers is the important part.

I hope you can point me in the right direction.

#Update: When I open and save the file the problem is fixed, is there a way I can avoid having to do that manually?

Answer 1

I found a workaround. If I open the file and save it, the issue is solved.

The best way I found was using the following code to fix the files:

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)

It works but I am not proud of the solution I found.