作为一个小爱好项目的一部分,我正在try 在上传到Google Drive之前为Excel工作表中的单元格添加一些格式.
单元格可以是0、1或2,根据值的不同,我会应用 colored颜色 (分别为白色、黑色或灰色).
以下是我的try :
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule
from openpyxl.cell.cell import Cell
# Load the Excel workbook
workbook = load_workbook('output.xlsx')
# Select the desired sheet by name or index
sheet = workbook['Sheet1'] # Replace 'Sheet1' with the actual sheet name
# Create the conditional formatting rules
rules = [
{
'value': 1,
'fill_color': '00000000' # Black
},
{
'value': 2,
'fill_color': 'FF808080' # Solid Grey
}
]
for rule in rules:
fill = PatternFill(start_color=rule['fill_color'], end_color=rule['fill_color'], fill_type='solid')
# Apply conditional formatting rule
for row in sheet.iter_rows(min_row=2, values_only=True): # Start from the second row assuming headers are in the first row
for cell in row:
if isinstance(cell, Cell) and cell.value == rule['value']:
cell.fill = fill
# Save the modified workbook with conditional formatting
workbook.save('output_formatted.xlsx')
代码似乎可以运行,但OutputFormatted.xlsx似乎没有任何格式.你知道我哪里出问题了吗,或者你能建议一个更好的方法来解决这个问题吗?
我预计输出如下所示:
image of excel sheet cells color coded scheme个
如果有帮助,这里是前几行数据的摘录(提前为糟糕的格式道歉):
normalM normalF shinyM shinyF darkM darkF goldenM goldenF
0 0 1 0 1 0 1 0
0 0 0 0 0 0 1 0
1 0 0 1 1 0 0 0
0 0 1 1 1 1 1 1
1 0 1 1 1 0 1 0
1 2 0 2 1 2 1 2
1 2 0 2 1 2 1 2
1 1 1 1 0 1 1 1
0 0 0 0 0 0 0 0
0 0 1 0 0 0 1 1
0 1 0 0 1 0 0 1
任何智慧的话语都将不胜感激.谢谢. PS:我也了解Pandas ,如果你有什么建议可以用Pandas 来解决这个问题,我很乐意try 一下.