【Excel篇】Openpyxl库-课5-公式、图表与数据处理

from openpyxl import Workbookwb = Workbook()ws = wb.active# 写入基础数据data = [    ["产品", "1月", "2月", "3月", "合计", "平均"],    ["鼠标", 120, 135, 145, None, None],    ["键盘", 75, 82, 88, None, None],    ["U盘", 180, 195, 210, None, None],]for row in data:    ws.append(row)# 写入公式# 合计：SUM(B2:D2)ws["E2"] = "=SUM(B2:D2)"ws["E3"] = "=SUM(B3:D3)"ws["E4"] = "=SUM(B4:D4)"# 平均：AVERAGE(B2:D2)ws["F2"] = "=AVERAGE(B2:D2)"ws["F3"] = "=AVERAGE(B3:D3)"ws["F4"] = "=AVERAGE(B4:D4)"# 总计行ws["A5"] = "总计"ws["E5"] = "=SUM(E2:E4)"ws["F5"] = "=AVERAGE(F2:F4)"wb.save("公式示例.xlsx")

from openpyxl import Workbookwb = Workbook()ws = wb.active# 写入示例数据for i in range(1, 11):    ws.append([i, i * 2, i * 3])# 数学函数ws["D1"] = "=SUM(A1:A10)"      # 求和ws["D2"] = "=AVERAGE(A1:A10)"  # 平均值ws["D3"] = "=MAX(A1:A10)"      # 最大值ws["D4"] = "=MIN(A1:A10)"      # 最小值ws["D5"] = "=COUNT(A1:A10)"    # 计数ws["D6"] = "=COUNTIF(A1:A10,\">5\")"  # 条件计数ws["D7"] = "=SUMIF(A1:A10,\">5\")"    # 条件求和ws["D8"] = "=PRODUCT(A1:A5)"   # 乘积ws["D9"] = "=POWER(A1,2)"      # 幂运算ws["D10"] = "=SQRT(A10)"       # 平方根# 逻辑函数ws["E1"] = "=IF(A1>5,\"大于5\",\"小于等于5\")"ws["E2"] = "=AND(A2>3,B2>5)"   # 与ws["E3"] = "=OR(A3>3,B3>5)"    # 或# 文本函数ws["F1"] = '=CONCATENATE("值:",A1)'ws["F2"] = '=LEFT(B2,1)'ws["F3"] = '=RIGHT(B3,2)'# 查找函数（需要数据区域）ws["G1"] = '=VLOOKUP(5,A1:B10,2,FALSE)'wb.save("公式类型示例.xlsx")

from openpyxl import Workbookwb = Workbook()ws = wb.active# 写入数据ws["A1"] = 10ws["B1"] = 5# 相对引用（默认）# 复制公式时会自动调整单元格引用ws["C1"] = "=A1+B1"      # 相对引用# 绝对引用（使用$符号）# 复制公式时引用位置不变ws["D1"] = "=$A$1+$B$1"  # 绝对引用# 混合引用ws["E1"] = "=$A1+B$1"    # 列绝对，行相对# 演示复制公式for i in range(2, 6):    ws[f"A{i}"] = i * 10    ws[f"B{i}"] = i * 5    ws[f"C{i}"] = f"=A{i}+B{i}"        # 相对引用会自动调整    ws[f"D{i}"] = "=$A$1+$B$1"         # 绝对引用保持不变wb.save("引用类型示例.xlsx")

from openpyxl import load_workbook# 注意：openpyxl 默认读取公式本身，不计算公式wb = load_workbook("公式示例.xlsx")ws = wb.active# 读取公式print(ws["E2"].value)  # 输出：=SUM(B2:D2)# 读取计算结果（需要在加载时指定 data_only=True）wb_data = load_workbook("公式示例.xlsx", data_only=True)ws_data = wb_data.activeprint(ws_data["E2"].value)  # 输出：计算结果（如 400）# 注意事项# 1. 文件必须被 Excel 保存过，计算结果才会被存储# 2. 如果在 openpyxl 中写入公式后立即用 data_only=True 读取，会得到 None# 3. 正确流程：写入公式并保存 → 用 Excel 打开并保存 → 用 data_only=True 读取

from openpyxl import Workbookfrom openpyxl.chart import BarChart, Referencewb = Workbook()ws = wb.active# 准备数据data = [    ["产品", "销量", "销售额"],    ["鼠标", 450, 22050],    ["键盘", 245, 31605],    ["U盘", 585, 34515],    ["移动硬盘", 120, 59880],]for row in data:    ws.append(row)# 创建条形图bar_chart = BarChart()bar_chart.title = "产品销售统计"bar_chart.x_axis.title = "产品"bar_chart.y_axis.title = "数量/金额"# 设置数据范围# 销量数据（B2:B5）data = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=2)# 分类标签（A2:A5）categories = Reference(ws, min_col=1, min_row=2, max_row=5)# 添加数据到图表bar_chart.add_data(data, titles_from_data=True)bar_chart.set_categories(categories)# 添加第二个数据系列（销售额）data2 = Reference(ws, min_col=3, min_row=1, max_row=5, max_col=3)bar_chart.add_data(data2, titles_from_data=True)# 设置图表样式（1-48）bar_chart.style = 10# 设置数据标签bar_chart.dataLabels = Truebar_chart.dataLabels.showVal = True# 添加到工作表（指定位置）ws.add_chart(bar_chart, "E2")wb.save("条形图示例.xlsx")

from openpyxl import Workbookfrom openpyxl.chart import LineChart, Referencewb = Workbook()ws = wb.active# 准备数据（12个月销售趋势）months = ["1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月"]sales = [120, 135, 145, 160, 185, 210, 230, 225, 215, 200, 195, 180]ws.append(["月份", "销售额"])for month, sale in zip(months, sales):    ws.append([month, sale])# 创建折线图line_chart = LineChart()line_chart.title = "年度销售趋势"line_chart.x_axis.title = "月份"line_chart.y_axis.title = "销售额（万元）"line_chart.style = 12# 设置数据data = Reference(ws, min_col=2, min_row=1, max_row=13)categories = Reference(ws, min_col=1, min_row=2, max_row=13)line_chart.add_data(data, titles_from_data=True)line_chart.set_categories(categories)# 添加数据标签line_chart.dataLabels = Trueline_chart.dataLabels.showVal = True# 设置线条平滑line_chart.smooth = Truews.add_chart(line_chart, "D2")wb.save("折线图示例.xlsx")

from openpyxl import Workbookfrom openpyxl.chart import PieChart, Referencewb = Workbook()ws = wb.active# 准备数据（市场份额）data = [    ["产品", "市场份额"],    ["鼠标", 35],    ["键盘", 25],    ["U盘", 30],    ["移动硬盘", 10],]for row in data:    ws.append(row)# 创建饼图pie_chart = PieChart()pie_chart.title = "产品市场份额"# 设置数据labels = Reference(ws, min_col=1, min_row=2, max_row=5)data = Reference(ws, min_col=2, min_row=1, max_row=5)pie_chart.add_data(data, titles_from_data=True)pie_chart.set_categories(labels)# 设置饼图样式pie_chart.style = 10# 显示数据标签pie_chart.dataLabels = Truepie_chart.dataLabels.showVal = Truepie_chart.dataLabels.showPercent = Truepie_chart.dataLabels.showCatName = True# 设置第一个扇区起始角度pie_chart.firstSliceAng = 90ws.add_chart(pie_chart, "D2")wb.save("饼图示例.xlsx")

from openpyxl import Workbookfrom openpyxl.chart import BarChart, LineChart, Referencefrom openpyxl.chart.series import DataPointfrom openpyxl.chart.label import DataLabelListfrom openpyxl.drawing.fill import GradientFillwb = Workbook()ws = wb.active# 准备数据data = [    ["季度", "2023年", "2024年"],    ["Q1", 850, 920],    ["Q2", 890, 980],    ["Q3", 870, 1020],    ["Q4", 920, 1100],]for row in data:    ws.append(row)# 创建组合图表bar_chart = BarChart()bar_chart.title = "年度业绩对比"bar_chart.x_axis.title = "季度"bar_chart.y_axis.title = "业绩（万元）"bar_chart.style = 10# 添加数据data_ref = Reference(ws, min_col=2, min_row=1, max_row=5, max_col=3)categories_ref = Reference(ws, min_col=1, min_row=2, max_row=5)bar_chart.add_data(data_ref, titles_from_data=True)bar_chart.set_categories(categories_ref)# 设置系列颜色series1 = bar_chart.series[0]series1.graphicalProperties.solidFill = "4472C4"  # 蓝色series2 = bar_chart.series[1]series2.graphicalProperties.solidFill = "ED7D31"  # 橙色# 设置图例位置bar_chart.legend.position = "b"  # 底部# 设置坐标轴属性bar_chart.y_axis.majorGridlines = None  # 隐藏网格线bar_chart.x_axis.tickLblPos = "low"# 设置数据标签bar_chart.dataLabels = DataLabelList()bar_chart.dataLabels.showVal = Truebar_chart.dataLabels.showCatName = Truews.add_chart(bar_chart, "F2")wb.save("高级图表示例.xlsx")

from openpyxl import Workbookfrom openpyxl.worksheet.datavalidation import DataValidationwb = Workbook()ws = wb.active# 创建数据验证# 1. 下拉列表（序列）dv1 = DataValidation(type="list", formula1='"是,否"', allow_blank=True)dv1.errorTitle = "输入错误"dv1.error = "请从下拉列表中选择"dv1.promptTitle = "请选择"dv1.prompt = "请选择是或否"ws.add_data_validation(dv1)dv1.add("B2:B10")  # 应用到 B2:B10# 2. 整数范围验证dv2 = DataValidation(type="whole", operator="between", formula1=0, formula2=100)dv2.error = "请输入0-100之间的整数"ws.add_data_validation(dv2)dv2.add("C2:C10")# 3. 小数范围验证dv3 = DataValidation(type="decimal", operator="between", formula1=0, formula2=1)dv3.error = "请输入0-1之间的小数"ws.add_data_validation(dv3)dv3.add("D2:D10")# 4. 日期范围验证dv4 = DataValidation(type="date", operator="between", formula1="2024-01-01", formula2="2024-12-31")dv4.error = "请输入2024年的日期"ws.add_data_validation(dv4)dv4.add("E2:E10")# 5. 文本长度验证dv5 = DataValidation(type="textLength", operator="lessThan", formula1=10)dv5.error = "请输入少于10个字符"ws.add_data_validation(dv5)dv5.add("F2:F10")# 写入表头headers = ["姓名", "是否在职", "分数(0-100)", "比例(0-1)", "日期", "备注(≤10字)"]ws.append(headers)# 写入示例数据ws.append(["张三", "是", 85, 0.85, "2024-06-01", "正常"])ws.append(["李四", "否", 92, 0.92, "2024-05-15", "优秀员工"])wb.save("数据验证示例.xlsx")

from openpyxl import Workbookfrom openpyxl.drawing.image import Imagefrom PIL import Image as PILImageimport iowb = Workbook()ws = wb.active# 方法1：从文件加载图片try:    img = Image("logo.png")  # 替换为实际图片路径    img.width = 200    img.height = 100    ws.add_image(img, "A1")except:    print("图片文件不存在，使用方法2")# 方法2：动态创建图片（使用PIL）# 创建一个简单的彩色图片img_data = PILImage.new('RGB', (300, 100), color='#4472C4')# 如果有文字需求，可以添加文字from PIL import ImageDraw, ImageFontdraw = ImageDraw.Draw(img_data)draw.text((10, 40), "销售报表", fill='white')# 保存到字节流img_bytes = io.BytesIO()img_data.save(img_bytes, format='PNG')img_bytes.seek(0)# 加载到 openpyxlimg = Image(img_bytes)img.width = 300img.height = 100ws.add_image(img, "A20")wb.save("图片示例.xlsx")

from openpyxl import Workbookfrom openpyxl.styles import Font, Alignment, PatternFill, Border, Sidefrom openpyxl.chart import BarChart, LineChart, PieChart, Referencefrom openpyxl.worksheet.datavalidation import DataValidationfrom datetime import datetimeimport randomwb = Workbook()ws = wb.activews.title = "销售分析仪表盘"# ========== 1. 样式定义 ==========title_font = Font(name="微软雅黑", size=20, bold=True, color="1E90FF")title_fill = PatternFill(fill_type="solid", start_color="E6F0FA")header_font = Font(name="微软雅黑", size=12, bold=True, color="FFFFFF")header_fill = PatternFill(fill_type="solid", start_color="4472C4")border = Border(    left=Side(style="thin"),    right=Side(style="thin"),    top=Side(style="thin"),    bottom=Side(style="thin"))total_fill = PatternFill(fill_type="solid", start_color="FFCC99")# ========== 2. 标题 ==========ws.merge_cells("A1:G1")ws["A1"] = "2024年销售数据分析仪表盘"ws["A1"].font = title_fontws["A1"].alignment = Alignment(horizontal="center", vertical="center")ws["A1"].fill = title_fillws.row_dimensions[1].height = 40# 生成时间ws["G2"] = f"生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M')}"ws["G2"].alignment = Alignment(horizontal="right")ws["G2"].font = Font(size=8, color="808080")# ========== 3. 销售数据表 ==========ws["A4"] = "销售数据明细"ws["A4"].font = Font(size=14, bold=True)headers = ["月份", "产品A", "产品B", "产品C", "产品D", "合计", "增长率"]for col_idx, header in enumerate(headers, 1):    cell = ws.cell(row=5, column=col_idx, value=header)    cell.font = header_font    cell.fill = header_fill    cell.alignment = Alignment(horizontal="center")    cell.border = border# 生成12个月的数据months = [f"{i}月" for i in range(1, 13)]base_values = [100, 110, 115, 130, 145, 160, 180, 175, 165, 155, 148, 140]for row_idx, (month, base) in enumerate(zip(months, base_values), start=6):    ws.cell(row=row_idx, column=1, value=month)    # 生成各产品数据（围绕基准值波动）    for col_idx, factor in enumerate([0.8, 1.0, 1.2, 0.6], start=2):        value = int(base * factor * random.uniform(0.95, 1.05))        cell = ws.cell(row=row_idx, column=col_idx, value=value)        cell.border = border        cell.alignment = Alignment(horizontal="center")    # 计算合计    total_formula = f"=B{row_idx}+C{row_idx}+D{row_idx}+E{row_idx}"    ws.cell(row=row_idx, column=6, value=total_formula)    ws.cell(row=row_idx, column=6).border = border    ws.cell(row=row_idx, column=6).alignment = Alignment(horizontal="center")    # 计算增长率（与上月对比）    if row_idx > 6:        growth_formula = f"=(F{row_idx}-F{row_idx-1})/F{row_idx-1}"        ws.cell(row=row_idx, column=7, value=growth_formula)        ws.cell(row=row_idx, column=7).number_format = "0.00%"        ws.cell(row=row_idx, column=7).border = border        ws.cell(row=row_idx, column=7).alignment = Alignment(horizontal="center")    else:        ws.cell(row=row_idx, column=7, value="—")# 总计行total_row = 18ws.cell(row=total_row, column=1, value="年度合计")ws.cell(row=total_row, column=6, value=f"=SUM(F6:F{total_row-1})")for col_idx in range(2, 6):    ws.cell(row=total_row, column=col_idx, value=f"=SUM({chr(64+col_idx)}6:{chr(64+col_idx)}{total_row-1})")for col_idx in range(1, 7):    cell = ws.cell(row=total_row, column=col_idx)    cell.fill = total_fill    cell.font = Font(bold=True)    cell.border = border    cell.alignment = Alignment(horizontal="center")# ========== 4. 图表1：月度销售趋势（折线图） ==========line_chart = LineChart()line_chart.title = "月度销售趋势"line_chart.x_axis.title = "月份"line_chart.y_axis.title = "销售额"line_chart.style = 12line_chart.height = 8line_chart.width = 12# 合计列数据total_data = Reference(ws, min_col=6, min_row=5, max_row=17)months_cat = Reference(ws, min_col=1, min_row=6, max_row=17)line_chart.add_data(total_data, titles_from_data=True)line_chart.set_categories(months_cat)line_chart.dataLabels = Truews.add_chart(line_chart, "A20")# ========== 5. 图表2：产品占比（饼图） ==========pie_chart = PieChart()pie_chart.title = "产品销售占比"pie_chart.height = 8pie_chart.width = 10product_data = Reference(ws, min_col=2, min_row=total_row, max_col=5, max_row=total_row)product_labels = Reference(ws, min_col=1, min_row=4, max_col=5, max_row=4)pie_chart.add_data(product_data)pie_chart.set_categories(product_labels)pie_chart.dataLabels = Truepie_chart.dataLabels.showPercent = Truepie_chart.dataLabels.showCatName = Truews.add_chart(pie_chart, "M20")# ========== 6. 图表3：各产品月度对比（条形图） ==========bar_chart = BarChart()bar_chart.title = "各产品月度销售对比"bar_chart.x_axis.title = "月份"bar_chart.y_axis.title = "销量"bar_chart.style = 10bar_chart.height = 10bar_chart.width = 14all_data = Reference(ws, min_col=2, max_col=5, min_row=5, max_row=17)bar_chart.add_data(all_data, titles_from_data=True)bar_chart.set_categories(months_cat)ws.add_chart(bar_chart, "A38")# ========== 7. 数据验证：月份筛选下拉列表 ==========dv_month = DataValidation(type="list", formula1='"1月,2月,3月,4月,5月,6月,7月,8月,9月,10月,11月,12月"')ws.add_data_validation(dv_month)dv_month.add("K2")ws["J2"] = "选择月份："ws["J2"].font = Font(bold=True)ws["J2"].alignment = Alignment(horizontal="right")# ========== 8. 关键指标卡片 ==========def add_metric_card(row, title, value, unit=""):    ws.merge_cells(f"A{row}:C{row+1}")    cell = ws.cell(row=row, column=1)    cell.value = title    cell.font = Font(size=10, color="808080")    cell.alignment = Alignment(horizontal="center")    ws.merge_cells(f"D{row}:G{row+1}")    value_cell = ws.cell(row=row, column=4)    value_cell.value = f"{value}{unit}"    value_cell.font = Font(size=18, bold=True, color="4472C4")    value_cell.alignment = Alignment(horizontal="center")    # 添加背景色    for r in [row, row+1]:        for c in range(1, 8):            cell = ws.cell(row=r, column=c)            cell.fill = PatternFill(fill_type="solid", start_color="F5F5F5")            cell.border = border# 添加指标卡片add_metric_card(50, "年度总销售额", "=F18", "元")add_metric_card(50, "年度总销量", "=SUM(B18:E18)", "件")add_metric_card(50, "平均增长率", "=AVERAGE(G7:G17)", "%")add_metric_card(50, "最高月销售额", "=MAX(F6:F17)", "元")# 设置列宽column_widths = {'A': 10, 'B': 12, 'C': 12, 'D': 12, 'E': 12, 'F': 12, 'G': 12}for col, width in column_widths.items():    ws.column_dimensions[col].width = width# 设置行高ws.row_dimensions[3].height = 15for row in range(5, total_row + 1):    ws.row_dimensions[row].height = 22wb.save("销售分析仪表盘.xlsx")print("仪表盘生成完成！")