写一个python脚本,用python实现excel表格根据四个动态区间设置填充颜色并保存填充数据表, styled_table = data.style.applymap(lambda x: color_map2[x>value_k_12])
时间: 2024-02-21 10:02:11 浏览: 139
工具变量城市供应链创新试点数据(2007-2023年).xlsx
好的,以下是一个实现此功能的 Python 脚本:
```python
import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter
# 读取 Excel 文件
wb = openpyxl.load_workbook('data.xlsx')
sheet = wb.active
# 定义四个区间和对应的填充颜色,这里将数值范围作为变量,可根据实际情况进行修改
interval1_min = 0
interval1_max = 59
interval1_color = 'FF0000'
interval2_min = 60
interval2_max = 69
interval2_color = 'FFC000'
interval3_min = 70
interval3_max = 89
interval3_color = 'FFFF00'
interval4_min = 90
interval4_max = 100
interval4_color = '92D050'
# 遍历表格中的每一个单元格
for row in sheet.iter_rows():
for cell in row:
# 如果单元格不是标题栏
if cell.row != 1:
value = cell.value
# 根据数值所在的区间设置填充颜色
if value >= interval1_min and value <= interval1_max:
fill = PatternFill(start_color=interval1_color, end_color=interval1_color, fill_type='solid')
cell.fill = fill
elif value >= interval2_min and value <= interval2_max:
fill = PatternFill(start_color=interval2_color, end_color=interval2_color, fill_type='solid')
cell.fill = fill
elif value >= interval3_min and value <= interval3_max:
fill = PatternFill(start_color=interval3_color, end_color=interval3_color, fill_type='solid')
cell.fill = fill
elif value >= interval4_min and value <= interval4_max:
fill = PatternFill(start_color=interval4_color, end_color=interval4_color, fill_type='solid')
cell.fill = fill
# 保存修改后的 Excel 文件
wb.save('output.xlsx')
```
这个脚本会读取名为 `data.xlsx` 的 Excel 文件,并根据四个动态区间设置填充颜色。然后将填充颜色应用到对应的单元格,并将修改后的表格保存为名为 `output.xlsx` 的新文件。你可以根据实际情况修改区间数值、填充颜色等设置。
阅读全文