Excel 自动化：OpenPyXL 与 Pandas 处理 Excel 文件

好的，各位观众老爷，各位未来的Excel武林盟主！今天咱们就来聊聊如何用Python这把倚天剑，配合OpenPyXL和Pandas这两大内功心法，玩转Excel江湖！🚀

咱们的目标是：不再做Excel表里的打工人，而是成为Excel表的主宰者！😎

开场白：Excel，爱恨交织的你啊！

话说这Excel，真是让人又爱又恨。爱的是它功能强大，制表、统计、分析，样样精通；恨的是，手动操作起来，那真是费时费力，让人头昏眼花。尤其当数据量一大，那感觉就像在汪洋大海里捞针，捞到最后，怀疑人生！🤯

作为一名优秀的程序员，怎么能忍受这种重复性的劳动呢？所以，我们要用Python来解放双手，让Excel乖乖听话！

第一章：内功心法之OpenPyXL

OpenPyXL，顾名思义，就是“打开Python，处理Excel”的意思。它是一个Python库，专门用来读写Excel 2010 xlsx/xlsm/xltx/xltm文件。你可以把它想象成一把锋利的手术刀，能够精确地定位到Excel的每一个单元格，进行切割、缝合、填充等操作。

1. 安装OpenPyXL：磨刀不误砍柴工

   在使用OpenPyXL之前，我们需要先把它安装好。打开你的终端或命令提示符，输入以下命令：

   pip install openpyxl

   搞定！就像给宝剑开光一样，仪式感满满！✨

2. 读取Excel文件：知己知彼，百战不殆

   想要操作Excel，首先得把它读进来。就像要了解一个人，得先和他聊聊天一样。

   from openpyxl import load_workbook
   
   # 加载工作簿
   workbook = load_workbook(filename="你的Excel文件.xlsx")
   
   # 获取工作表
   sheet = workbook["Sheet1"] # 或者 workbook.active
   
   # 读取单元格内容
   cell_value = sheet["A1"].value
   print(cell_value) # 输出A1单元格的值

   这段代码就像是打开了一扇通往Excel世界的大门，让我们能够窥探其中的奥秘。🔍

3. 写入Excel文件：指哪打哪，随心所欲

   读取只是第一步，更重要的是写入。就像画家在画布上挥毫泼墨，我们要把数据写到Excel里，创造属于自己的艺术品。

   from openpyxl import Workbook
   
   # 创建一个新的工作簿
   workbook = Workbook()
   
   # 获取活动工作表
   sheet = workbook.active
   
   # 写入单元格
   sheet["A1"] = "Hello, Excel!"
   sheet["B2"] = 123
   
   # 保存工作簿
   workbook.save(filename="新的Excel文件.xlsx")

   这段代码就像是拥有了一支神奇的笔，可以随意地在Excel上书写，创造出各种各样的数据表格。✍️

4. 常用操作：十八般武艺，样样精通

   OpenPyXL的功能非常强大，除了基本的读写操作，还有很多高级功能，比如：

   • 循环读取单元格：

     for row in sheet.iter_rows(min_row=1, max_row=10, min_col=1, max_col=5):
        for cell in row:
            print(cell.value)

     这个就像巡逻一样，一行一列地扫描Excel的内容。

   • 修改单元格样式：

     from openpyxl.styles import Font, Color, Alignment
     
     # 创建字体样式
     font = Font(name="Arial", size=12, bold=True, color="FF0000") # 红色加粗Arial字体
     
     # 设置单元格样式
     cell = sheet["A1"]
     cell.font = font
     cell.alignment = Alignment(horizontal="center", vertical="center") # 居中对齐

     这就像给Excel化了个妆，让它变得更加漂亮。💅

   • 插入公式：

     sheet["C1"] = "=A1+B1"

     这就像给Excel注入了灵魂，让它能够自动计算。🧠

   • 创建图表：

     from openpyxl.chart import BarChart, Reference
     
     # 创建图表
     chart = BarChart()
     chart.title = "销售额统计"
     chart.x_axis.title = "月份"
     chart.y_axis.title = "金额"
     
     # 添加数据
     data = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=7)
     categories = Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=7)
     chart.add_data(data, titles_from_data=True)
     chart.set_categories(categories)
     
     # 将图表添加到工作表
     sheet.add_chart(chart, "E2")

     这就像给Excel穿上了华丽的衣服，让数据变得更加直观。📊

第二章：内功心法之Pandas

Pandas，是Python Data Analysis Library的缩写，翻译过来就是“Python数据分析库”。它是一个强大的数据分析工具，特别擅长处理表格型数据。你可以把它想象成一个数据挖掘机，能够快速地从Excel文件中提取出有用的信息。

1. 安装Pandas：工欲善其事，必先利其器

   同样，在使用Pandas之前，我们需要先把它安装好。

   pip install pandas

   搞定！就像给挖掘机加满油，准备开工！⛽

2. 读取Excel文件：一览众山小

   Pandas读取Excel文件非常简单，只需要一行代码：

   import pandas as pd
   
   # 读取Excel文件
   dataframe = pd.read_excel("你的Excel文件.xlsx", sheet_name="Sheet1")
   
   # 打印数据
   print(dataframe)

   这段代码就像是拥有了一双千里眼，能够一眼看穿Excel文件的所有内容。👀

3. 写入Excel文件：挥斥方遒，指点江山

   Pandas写入Excel文件也很方便：

   import pandas as pd
   
   # 创建一个DataFrame
   data = {"姓名": ["张三", "李四", "王五"], "年龄": [20, 22, 24], "成绩": [80, 90, 85]}
   dataframe = pd.DataFrame(data)
   
   # 写入Excel文件
   dataframe.to_excel("新的Excel文件.xlsx", sheet_name="Sheet1", index=False)

   这段代码就像是拥有了一支点石成金的手指，能够将数据变成漂亮的Excel表格。🖐️

4. 常用操作：十八般武艺，样样精通

   Pandas的功能也非常强大，比如：

   • 数据筛选：

     # 筛选年龄大于22岁的人
     filtered_dataframe = dataframe[dataframe["年龄"] > 22]
     print(filtered_dataframe)

     这就像警察抓小偷一样，把符合条件的数据找出来。👮

   • 数据排序：

     # 按照年龄排序
     sorted_dataframe = dataframe.sort_values(by="年龄", ascending=False) # 降序排列
     print(sorted_dataframe)

     这就像给队伍排队一样，让数据井然有序。🧍

   • 数据分组：

     # 按照性别分组，计算平均年龄
     grouped_dataframe = dataframe.groupby("性别")["年龄"].mean()
     print(grouped_dataframe)

     这就像把人群分成不同的组，然后进行统计分析。👨‍👩‍👧‍👦

   • 数据透视表：

     # 创建数据透视表
     pivot_table = pd.pivot_table(dataframe, values="成绩", index="性别", columns="班级", aggfunc="mean")
     print(pivot_table)

     这就像给数据做了个3D建模，能够从不同的角度观察数据。🏢

第三章：融会贯通：OpenPyXL + Pandas，天下无敌！

OpenPyXL和Pandas各有千秋，OpenPyXL胜在精细，能够精确地控制Excel的每一个细节；Pandas胜在高效，能够快速地处理大量数据。如果把它们结合起来，那就真是天下无敌了！💪

举个例子：

import pandas as pd
from openpyxl import load_workbook

# 读取Excel文件
dataframe = pd.read_excel("你的Excel文件.xlsx", sheet_name="Sheet1")

# 对数据进行处理
dataframe["总分"] = dataframe["语文"] + dataframe["数学"] + dataframe["英语"]

# 将处理后的数据写入Excel文件
with pd.ExcelWriter("新的Excel文件.xlsx", engine="openpyxl") as writer:
    dataframe.to_excel(writer, sheet_name="Sheet1", index=False)

    # 获取工作簿和工作表
    workbook = writer.book
    sheet = workbook["Sheet1"]

    # 修改表头样式
    from openpyxl.styles import Font

    font = Font(bold=True)
    for cell in sheet[1]: # 第一行是表头
        cell.font = font

    # 自动调整列宽
    from openpyxl.utils import get_column_letter

    for column_cells in sheet.columns:
        length = max(len(str(cell.value)) for cell in column_cells)
        sheet.column_dimensions[get_column_letter(column_cells[0].column)].width = length + 2

# 保存文件
workbook.save("新的Excel文件.xlsx")

这段代码先用Pandas读取Excel文件，然后对数据进行处理，计算总分。接着，用Pandas将处理后的数据写入Excel文件，并使用OpenPyXL修改表头样式，自动调整列宽。

这样一来，既利用了Pandas强大的数据处理能力，又利用了OpenPyXL精细的样式控制能力，简直是完美！💯

第四章：实战演练：解决实际问题

理论讲得再好，不如实际操作一下。下面我们来解决一个实际问题：

假设你是一家电商公司的运营人员，需要每天从Excel文件中提取销售数据，进行分析，并生成日报。

1. 读取数据：

   import pandas as pd
   
   # 读取Excel文件
   sales_data = pd.read_excel("销售数据.xlsx", sheet_name="Sheet1")

2. 数据清洗：

   # 删除空行
   sales_data = sales_data.dropna()
   
   # 转换数据类型
   sales_data["销售额"] = sales_data["销售额"].astype(float)
   sales_data["日期"] = pd.to_datetime(sales_data["日期"])

3. 数据分析：

   # 计算总销售额
   total_sales = sales_data["销售额"].sum()
   
   # 计算每日销售额
   daily_sales = sales_data.groupby("日期")["销售额"].sum()
   
   # 计算各产品销售额
   product_sales = sales_data.groupby("产品名称")["销售额"].sum()

4. 生成日报：

   from openpyxl import Workbook
   
   # 创建一个新的工作簿
   workbook = Workbook()
   
   # 获取活动工作表
   sheet = workbook.active
   
   # 写入数据
   sheet["A1"] = "总销售额"
   sheet["B1"] = total_sales
   
   sheet["A3"] = "每日销售额"
   row = 4
   for date, sales in daily_sales.items():
      sheet[f"A{row}"] = date.strftime("%Y-%m-%d")
      sheet[f"B{row}"] = sales
      row += 1
   
   sheet["A10"] = "各产品销售额"
   row = 11
   for product, sales in product_sales.items():
      sheet[f"A{row}"] = product
      sheet[f"B{row}"] = sales
      row += 1
   
   # 保存工作簿
   workbook.save(filename="销售日报.xlsx")

   这样，每天只需要运行这段代码，就可以自动生成销售日报，再也不用手动操作了！🎉

第五章：进阶之路：探索更多可能

掌握了OpenPyXL和Pandas的基本用法，只是Excel自动化之路的开始。还有很多高级功能等待你去探索，比如：

• 使用openpyxl-styler: 一个可以更方便地设置excel样式的库。
• 使用XlsxWriter: 另一个用于写入excel文件的库，在处理大型文件时可能更快。
• 连接数据库： 将Excel数据与数据库连接起来，实现数据的自动同步。
• 使用Flask或Django： 将Excel自动化脚本部署到Web服务器上，实现Web界面的操作。
• 学习更高级的数据分析技巧： 使用Pandas进行更复杂的数据分析，比如时间序列分析、机器学习等。

总之，Excel自动化的世界非常广阔，只要你不断学习，不断探索，就能成为真正的Excel武林盟主！🏆

结尾：告别加班，拥抱美好生活！

各位观众老爷，今天的Excel自动化之旅就到这里了。希望通过今天的讲解，能够帮助大家摆脱Excel的束缚，告别加班，拥抱更加美好的生活！记住：编程的意义，不仅仅是写代码，更是解放生产力，创造价值！ 让我们一起用Python，让Excel变得更加智能，更加高效！ 谢谢大家！鞠躬！🙇‍♂️