英文:
"ValueError: 1 is not a valid column name" error when using .save from openpyxl
问题
我有一些代码，它打开一个由另一个代码创建的Excel文件。然后对工作表进行一些基本格式设置，然后保存并关闭文件。这段代码之前正常运行，但突然出现了一些与保存函数有关的随机错误。错误消息是"Key Error '1'"和"1 is not a valid column name"。
以下是您的代码：
import openpyxl as opxl
from openpyxl.styles import Font, PatternFill
# ...（省略部分代码）...
output.save(output_path)
output.close
当前的错误是：
File "openpyxl\utils\cell.py:121 in openpyxl.utils.cell.column_index_from_string
KeyError: '1';
ValueError: 1 is not a valid column name
您已经尝试使用不同的文件名保存，但没有成功。如果去掉.save()行，代码可以运行，但不会保存任何结果。任何帮助将不胜感激。这段代码以前是正常工作的。您对创建output.xlsx文件的代码进行了更改，但该文件仍然保持相同的格式，看起来不会导致这个错误。
如果在第一个错误后再次运行代码，您会遇到一个不同的错误，错误消息是"File is not a zip file"。然后，如果在这个错误之后再次运行代码，会出现另一个不同的错误，错误消息是"There is no item named '[Content_Types].xml' in the archive"。
英文:
I have a some code that opens an excel file that I created with another code. It then does some basic formatting to the sheet and then saves and closes. This code was working fine and then randomly I started getting some random errors on the .save function. It says "Key Error '1'" and "1 is not a valid column name"
Below is my code
import openpyxl as opxl
from openpyxl.styles import Font, PatternFill
chime_low = 60
chime_high = 66
adj_low = -3
adj_high = 3
red_fill = PatternFill(start_color='FFFF0000', end_color='FFFF0000', fill_type='solid')
green_fill = PatternFill(start_color='0000FF00', end_color='0000FF00', fill_type='solid')
output_path = 'output.xlsx'
output = opxl.load_workbook(filename= output_path)
output_sheet = output.active
max_row = output_sheet.max_row
max_column = output_sheet.max_column
a1 = output_sheet['A1']
a1.font = Font(bold=True)
a1.value = 'Chime ID'
v_centerAlignment = opxl.styles.Alignment(
horizontal="center", vertical="center", wrapText=True
)
for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row = max_row, max_col = max_column):
for cell in col:
cell_str = str(cell)
col_let = cell_str[-3]
cell.alignment = v_centerAlignment
output_sheet.column_dimensions[f'{col_let}'].width = 20
for col in output_sheet.iter_cols(min_row=2, min_col=3, max_row = max_row, max_col = 3):
for cell in col :
if chime_low <= cell.value <= chime_high:
cell.fill = green_fill
else:
cell.fill = red_fill
for col in output_sheet.iter_cols(min_row=2, min_col=4, max_row = max_row, max_col = 4):
for cell in col :
if adj_low <= cell.value <= adj_high:
cell.fill = green_fill
else:
cell.fill = red_fill
output.save(output_path)
output.close
And here is the current error
Formatting.py', wdir='C:/Users/LZMYKK/Documents/Python Scripts/Chime Automation')
Traceback (most recent call last):
File openpyxl\utils\cell.py:121 in openpyxl.utils.cell.column_index_from_string
KeyError: '1'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\spyder_kernels\py3compat.py:356 in compat_exec
exec(code, globals, locals)
File c:\users\lzmykk\documents\python scripts\chime automation\chime post processing excel formatting.py:49
output.save(output_path)
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\workbook\workbook.py:407 in save
save_workbook(self, filename)
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:293 in save_workbook
writer.save()
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:275 in save
self.write_data()
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:75 in write_data
self._write_worksheets()
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:215 in _write_worksheets
self.write_worksheet(ws)
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\writer\excel.py:200 in write_worksheet
writer.write()
File openpyxl\worksheet\_writer.py:358 in openpyxl.worksheet._writer.WorksheetWriter.write
File openpyxl\worksheet\_writer.py:103 in openpyxl.worksheet._writer.WorksheetWriter.write_top
File openpyxl\worksheet\_writer.py:87 in openpyxl.worksheet._writer.WorksheetWriter.write_cols
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:233 in to_tree
for col in sorted(self.values(), key=sorter):
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:227 in sorter
value.reindex()
File ~\AppData\Local\miniconda3\envs\spyder-env\lib\site-packages\openpyxl\worksheet\dimensions.py:176 in reindex
self.min = self.max = column_index_from_string(self.index)
File openpyxl\utils\cell.py:123 in openpyxl.utils.cell.column_index_from_string
ValueError: 1 is not a valid column name
I tried using a different filename for saving and it did not work. I also got rid of the .save() line and the code runs but of course does not actually save any of my results. Any help would be appreciated. This code completely worked before. I did make changes to the code that creates the output.xlsx file, but that file is all still in the same format and seems like it wouldn't cause this error.
If I run the code a second time after the first error I get a different error that says "File is not a zip file" and then if I run it again after this error, I get another different error that says "There is no item named '[Content_Types].xml' in the archive"
答案1
得分: 2
以下是代码部分的中文翻译：
我认为您在这段代码中存在问题，这可能导致"Key Error '1'"和"1不是有效的列名"错误：
# for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=max_row, max_col=max_column):
for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=1, max_col=max_column):
for cell in col:
# cell_str = str(cell)
# col_let = cell_str[-3]
col_let = cell.column_letter
cell.alignment = v_centerAlignment
output_sheet.column_dimensions[f'{col_let}'].width = 20
您从cell_str[-3]中确定列字母，这对于行1到9的单元格（如'A1'，'A2'，'A3'）可以正常工作，返回'A'。但是当您达到行'A10'及更高行（直到'A19'）时，返回值现在是1（直到'A20'，它将变为2，依此类推）。然后，您会得到一个整数作为列字母，当您运行以下行时：
output_sheet.column_dimensions[f'{col_let}'].width = 20
您将会有一个损坏的工作表。
要获取列字母，请使用：
cell.column_letter
正如CC指出的那样，您不需要遍历所有已使用的单元格，只需遍历第一行。迭代应该是max_row=1：
for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=1, max_col=max_column):
如果这样做，您的错误将永远不会发生，但是获取列字母的方法应该进行更改。
另外，您不需要以下这一行：
output.close
对于您的代码来说，它没有任何作用。
英文:
I believe you have an issue with this bit of code which would cause a "Key Error '1'" and "1 is not a valid column name" error;
# for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=max_row, max_col=max_column):
for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=1, max_col=max_column):
for cell in col:
# cell_str = str(cell)
# col_let = cell_str[-3]
col_let = cell.column_letter
cell.alignment = v_centerAlignment
output_sheet.column_dimensions[f'{col_let}'].width = 20
You are determining column letter from cell_str[-3] which would work for cells in rows 1 to 9, like 'A1', 'A2', 'A3' returning 'A'. However when you reach row 'A10' and up [to 'A19'] the return is now 1 (until 'A20' where it will be 2 and so on). So then you have a column letter which is an integer and when you run this line
output_sheet.column_dimensions[f'{col_let}'].width = 20
you have a corrupted sheet. <br><br>
To get the column letter use
cell.column_letter
As CC points out; you dont need to loop thru all the used cells anyway, only the first row. The iteration should be max_row=1;
for col in output_sheet.iter_cols(min_row=1, min_col=1, max_row=1, max_col=max_column):
If this was done your error would never have occurred but your method to obtain the column letter should be changed either way.<br>
<br>
See edits to your code sample above.<br>
<br>
Also you do not need the line
output.close
for your code it does nothing.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论