excel文件

常见的处理模块

• OpenPyXL
  • 是个读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库，简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有，图表功能是其一大亮点
• xlwings
  • 是一个基于 BSD 授权协议的 Python 库，可以轻松的使用 Python 操作 Excel，也可以在 Excel 中调用 Python，以接近 VBA 语法的实现 Excel 编程，支持 Excel 宏，并且可以作为 Web 服务器，提供 REST API 接口
• pandas
  • 数据处理是 pandas 的立身之本，Excel 作为 pandas 输入/输出数据的容器
• win32com
  • 从命名上就可以看出，这是一个处理 windows 应用的扩展，Excel 只是该库能实现的一小部分功能。该库还支持 office 的众多操作。需要注意的是，该库不单独存在，可通过安装 pypiwin32 或者 pywin32 获取
• Xlsxwriter
  • 拥有丰富的特性，支持图片/表格/图表/筛选/格式/公式等，功能与 openpyxl 相似，优点是相比 openpyxl 还支持 VBA 文件导入，迷你图等功能，缺点是不能打开/修改已有文件，意味着使用 xlsxwriter 需要从零开始
• DataNitro
  • 一个 Excel 的付费插件，内嵌到 Excel 中，可完全替代 VBA，在 Excel 中使用 python 脚本。既然被称为 Excel 中的 python，同时可以与其他 python 库协同。
• xlutils
  • 基于 xlrd/xlwt，老牌 python 包，算是该领域的先驱，功能特点中规中矩，比较大的缺点是仅支持 xls 文件。
• xlrd/xlwt
  • 领域的先驱，功能特点中规中矩，仅支持 xls 文件

场景选择

• 不想使用 GUI 而又希望赋予 Excel 更多的功能，openpyxl 与 xlsxwriter，二者可选其一；
• 需要进行科学计算，处理大量数据，建议 pandas+xlsxwriter 或者 pandas + openpyxl，是不错的选择；
• 想要写 Excel 脚本，会 Python 但不会 VBA，可考虑 xlwings 或 DataNitro；
• win32com 功能还是性能都很强大，不过需要一定的 windows 编程经验才能上手，它相当于是 windows COM 的封装，另外文档不够完善

openypxl

官方文档

基础使用

创建对象

from openpyxl import Workbook
# 创建一个 workbook
wb = Workbook()
# 获取被激活的 worksheet
ws = wb.active
# 设置单元格内容
ws['A1'] = 42
# 设置一行内容
ws.append([1, 2, 3])
# python 数据类型可以被自动转换
import datetime
ws['A2'] = datetime.datetime.now()
# 保存 Excel 文件
wb.save("sample.xlsx")

打开文件wb = load_workbook()

from openpyxl import load_workbook

load_workbook(
  read_only,   # 是否为只读模式，对于超大型文件，要提升效率有帮助
  keep_vba,    # 是否保留 vba 代码，即打开 Excel 文件时，开启并保留宏
  guess_types, # 是否做在读取单元格数据类型时，做类型判断
  data_only,   # 是否将公式转换为结果，即包含公式的单元格，是否显示最近的计算结果
  keep_links,  # 是否保留外部链接
)

from openpyxl import load_workbook

wb = load_workbook('test.xlsx')

# 显示文档中包含的 表单 名称
print(wb.sheetnames)

Sheet 操作

from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')
ws = wb.active

#创建一个 sheet 名为 sheet
ws1 = wb.create_sheet("sheet")  
ws1.title = "新表单"  # 设置 sheet 标题
ws2 = wb.create_sheet("mysheet", 0) # 创建一个 sheet，插入到最前面 默认插在后面
ws2.title = u"你好"  # 设置 sheet 标题

ws1.sheet_properties.tabColor = "1072BA"  # 设置 sheet 标签背景色

# 获取 sheet
ws3 = wb.get_sheet_by_name(u"你好")

# ws4 = wb['New Title']
# 复制 sheet
ws1_copy = wb.copy_worksheet(ws1)

# 删除 sheet
wb.remove(ws1)

wb.save("sample1.xlsx")

• 每个 Workbook 中都有一个被激活的 sheet，一般都是第一个，可以通过 active 直接获取
• 可以通过 sheet 名来获取 sheet 对象
• 创建 sheet时需要提供 sheet 名称参数，如果该名称的 sheet 已经存在，则会在名称后添加 1，再有重复添加 2，以此类推
• 获得 sheet 对象后，可以设置 名称（title），背景色等属性
• 同一个 Workbook 对象中，可以复制 sheet，需要将源 sheet 对象作为参数，复制的新 sheet 会在最末尾
• 可以删除一个 sheet，参数是目标 sheet 对象

遍历操作

以列为基础遍历

# 列编列 -> 读取
for each_col in working_sheet.iter_cols():
        for each_cell in each_col:
                print(each_cel.value)
        
# 列遍历 -> 写入
col_index = 0
for each_col in working_sheet.iter_cols():
    col_index += 1
    row_index = 0
    for each_cell in each_col:
        row_index += 1
        cell_data = each_cell.value
        
        # 其他处理
        new_cell = ...#
        working_sheet.cell(row=row_index, column=col_index).value = new_cell

working_book.save(out)

以行为基础遍历

# 行编列 -> 读取
for each_col in working_sheet.iter_rows():
        for each_cell in each_col:
                print(each_cel.value)
        
# 列遍历 -> 写入
row_index = 0
for each_col in working_sheet.iter_cols():
    col_index = 0
    row_index += 1
    for each_cell in each_col:
        col_index += 1
        cell_data = each_cell.value
        
        # 其他处理
        new_cell = ...#
        working_sheet.cell(row=row_index, column=col_index).value = new_cell

working_book.save(out)

单元格 操作

单元格（cell）是 Excel 中存放数据的最小单元，就是图形界面中的一个个小格子，OpenPyXl 可以操作单个单元格，也可以批量操作单元格

ws1 = wb.create_sheet("Mysheet")  

# 创建一个sheet
# 通过单元格名称设置
ws1["A1"]=123.11
ws1["B2"]="你好"

# 通过行列坐标设置
d = ws1.cell(row=4, column=2, value=10)

• 可以通过单元格名称设置，类似于 sheet 的某种属性
• 也可以通过行列坐标类设置

批量操作

• 指定行列

# 操作单列
for cell in ws["A"]:
    print(cell.value)
    
# 操作单行
for cell in ws["1"]:
    print(cell.value)
    
# 操作多列
for column in ws['A:C']:
    for cell in column:
        print(cell.value)
        
# 操作多行
for row in ws['1:3']:
    for cell in row:
        print(cell.value)
        
# 指定范围
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

pandas

基础使用

• pd.ExcelFile() -> Dataframe
• pd.read_excel() -> Dataframe

两个函数都是等价的，返回的都是Dataframe

常用示例

excel_dataframe = pd.read_excel(
    dtype="str",          # 明确输入的数据以什么类型展示，无法转换的数据自动转换为 nan
    index_col=False,      # 是否使用行索引
    header=0,             # 是否有表头，指定表头的行数，表头不会被读取
    names:list[str] = ["A", "B"] # 用来指定列的标签名，后面通过 data[标签] 来访问列
)

通过列名称来读取各列

def read_xlsx_columns(xlsx_file_path: str, columns: list, sheet_name: str = "Sheet1") -> list:
    """
    从给定的xlsx文件中读取指定列的数据，返回一个多维数组。

    参数：
    - xlsx_file_path: Excel文件路径
    - columns: 要提取的数据列名称列表，如["A", "B"]

    返回：
    - 多维数组，格式如[[A1, B1], [A2, B2], ...]
    """
    try:
        # 读取xlsx文件
        df = pd.read_excel(xlsx_file_path, usecols=",".join(columns), sheet_name=sheet_name)

        # 将数据框转换为多维数组
        data_array = df.to_numpy().tolist()

        return data_array

    except Exception as e:
        print(f"Error reading Excel file: {e}")
        return []

# 使用示例
excel_data_list = read_xlsx_columns(excel_file, ["ak", "al", "aj"])

遍历操作

行遍历

row_index = 0
for row in self.table.itertuples(index=False):
    row_index += 1
    for each_cell in row:
        print(each_cell)
        
# 

列遍历

# 列编列 -> 读取
colums = ["A", "L"]

data = pd.read_excel(target, names=colums, dtype="str", index_col=False, header=None)

for col_name in colums:
        for each_cell in data[col_name]:
        print(each_cell)

# 列编列 -> 写入
data = pd.read_excel(target, names=colums, dtype="str", index_col=False, 

colums_names = ["A", "L"]

for each_col_name in colums_names:
        each_col_data = data[each_col_name]

    # 推荐使用
    for index in range(len(each_col_data))
        each_col_data[index] = xxxx