如何对数据使用条件格式？

bcs8qyzn 于 2021-07-13 发布在 Java

关注(0)|答案(1)|浏览(309)

我正在尝试将数据导出到应用了条件格式的excel文件中：

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
wb  = writer.book
ws = writer.sheets['Sheet1']

ws.conditional_formatting.add('A1:A10',
                          ColorScaleRule(start_type='min', start_color='AA0000',
                                         end_type='max', end_color='00AA00')
                         )

worksheet.conditional_formatting.add("O2:O295", color_scale_rule)
workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")

我收到以下错误：

AttributeError: 'Worksheet' object has no attribute 'conditional_formatting'

我的目标是：

python openpyxl

来源：https://stackoverflow.com/questions/67285517/how-do-i-use-condition-formatting-for-data

1条答案

按热度按时间

5ktev3wc1#

您创建的Dataframe不正确。

import pandas as pd
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
path = 'pandas_simple.xlsx'
df.to_excel(path, sheet_name='Sheet1', index=False)
wb  = openpyxl.load_workbook(path)
ws = wb['Sheet1']

# Add a three-color scale

ws.conditional_formatting.add('A1:A10',
                ColorScaleRule(start_type='percentile', start_value=10, start_color='AA0000',
                            mid_type='percentile', mid_value=50, mid_color='0000AA',
                            end_type='percentile', end_value=90, end_color='00AA00')
                              )

wb.save(filename="sample_conditional_formatting_color_scale.xlsx")

输出：

调整色阶以匹配预期输出是留给读者的练习。