Python：读写 excel

欢迎访问我的博客首页。

---

  Python 有三种常用的 excel 处理库 xlrd/xlwt、openpyxl、pandas。xlrd/xlwt 只能处理 xls，下标从 0 开始；openpyxl 只能处理 xlsx，下标从 1 开始；pandas 基于 xlrd/xlwt 和 openpyxl，因此可以处理 xls 和 xlsx，下标从 0 开始。

1. xlrd 和 xlwt

---

  xlrd 和 xlwt 只能处理 xls 文件而不能处理 xlsx 文件。和它们配合使用的还有 xlutils。

pip install xlrd
pip install xlwt
pip install xlutils

1.1 xlrd 读

---

  使用 xlrd 只能读取 xls 文件，读取 xlsx 文件会报错。xlrd 从 0 开始标记单元格。

# encoding=utf-8
import xlrd

if __name__ == '__main__':
    workbook = xlrd.open_workbook('example.xls')
    sheets = workbook.sheets()
    names = workbook.sheet_names()
    print(names)
    # 1.获取第一个工作表。
    worksheet1 = workbook.sheets()[0]
    # worksheet1 = workbook.sheet_by_index(0)
    # worksheet1 = workbook.sheet_by_name(names[0])
    # 2.逐行读取。
    for row in range(worksheet1.nrows):
        print(worksheet1.row_values(row))
    # 3.逐格读取。
    for row in range(worksheet1.nrows):
        for col in range(worksheet1.row_len(row)):
            print(worksheet1.cell_value(row, col), end=' ')
        print()

1.2 xlwt 写

---

  使用 xlwt 只能保存 xls 文件，保存 xlsx 文件不会报错但不能打开。下面是 xlwt 创建并编辑 excel 的例子。如果要设置字体和颜色等，请参阅参考文献。

  由于 xlwt 从 0 开始标记单元格，所以使用 cell(r, c) 可以为第 r+1 行第 c+1 列赋值。xlwt 支持不连续赋值：即使有空行或空列，也能为下一行或下一列赋值。

# encoding=utf-8
import xlwt

if __name__ == '__main__':
    # 编码方式，是否压缩。
    workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)
    # 添加 sheet1，单元格允许覆盖。
    worksheet1 = workbook.add_sheet('one', cell_overwrite_ok=True)
    worksheet1.write(0, 0, '表一内容1')
    # 添加 sheet2，单元格允许覆盖。
    worksheet2 = workbook.add_sheet('two', cell_overwrite_ok=True)
    worksheet2.write(0, 0, '表二内容1')
    # 保存。
    workbook.save('example.xls')

2. openpyxl

---

  openpyxl 只能读取 xlsx，保存成 xls 时打开会提示错误，但忽略错误就可以打开。安装 openpyxl 的命令是

pip install openpyxl

2.1 openpyxl 读

---

  openpyxl 从 1 开始标记单元格，因此不能访问行号或列号为 0 的单元格，否则会报错。

import openpyxl

if __name__ == '__main__':
    workbook = openpyxl.load_workbook('example.xlsx')
    names = workbook.sheetnames
    print(names)
    # 1.获取第一个工作表。
    worksheet1 = workbook[names[0]]
    # worksheet1 = workbook.worksheets[0]
    # 2.逐格读取。
    for row in range(worksheet1.max_row):
        for cell in worksheet1[row + 1]:
            print(cell.value, end=' ')
        print()
    # 3.逐格读取，可能读到空值。
    for row in range(worksheet1.max_row):
        for col in range(worksheet1.max_column):
            print(worksheet1.cell(row + 1, col + 1).value, end=' ')
        print()
    # 4.逐格读取。
    for row in worksheet1:
        for cell in row:
            print(cell.value, end=' ')
        print()

2.2 openpyxl 写

---

  由于 openpyxl 从 1 开始标记单元格，所以使用 cell(r, c) 可以为第 r 行第 c 列赋值。openpyxl 支持不连续赋值。

  需要注意的是，openpyxl 会在默认 Sheet 工作表的基础上增加 create_sheet 指定的工作表，但是我们可以指定新增工作表在所有工作表中的顺序。

# encoding=utf-8
import openpyxl

if __name__ == '__main__':
    workbook = openpyxl.Workbook()
    worksheet1 = workbook.create_sheet('one', 0)
    worksheet1.cell(2, 2).value = '表一内容1'
    workbook.save('example.xlsx')

3. pandas

---

  pandas 功能很强大，它不但可以处理 excel，还可以处理 csv 和 xml。

  pandas 基于 xlrd/xlwt 和 openpyxl 处理 excel，因此需要同时安装 xlrd/xlwt 和 openpyxl。安装 pandas 的命令是

pip install pandas

3.1 pandas 读

---

  pandas 从 0 开始标记单元格。

# encoding=utf-8
import pandas as pd

if __name__ == '__main__':
    # 1. 直接读取某个 sheet 或默认 sheet。
    worksheet = pd.read_excel('example.xls', header=0, index_col=None, sheet_name='one')
    # 1.1 逐行读取。
    for row in range(worksheet.shape[0]):
        print(worksheet.iloc[row].values)
    # 1.2 逐格读取。
    for row in range(worksheet.shape[0]):
        for col in range(worksheet.shape[1]):
            print(worksheet.iloc[row, col], end=' ')
        print()
    # 2. 读取所有 sheet。
    workbook = pd.ExcelFile('example.xls')
    sheet_names = workbook.sheet_names
    for sheet_name in sheet_names:
        worksheet = workbook.parse(sheet_name, header=None, index_col=None)
        # 逐行读取。
        for row in range(worksheet.shape[0]):
            print(worksheet.iloc[row].values)

3.2 pandas 写

---

  pd.DataFrame() 的返回值类似一个二维列表，使用 at 或 loc 为单元格赋值，相当于使用 append 给列表赋值，因此 pandas 不支持不连续赋值。如果有单元格需要为空，可以使用空字符串给它赋值。

# encoding=utf-8
import pandas as pd

if __name__ == '__main__':
    # 1.只有一个 sheet。
    dataframe = pd.DataFrame()
    dataframe.at[0, 0] = 0
    dataframe.loc[3, 3] = 111
    dataframe.to_excel('example2.xls', sheet_name='one', header=None, index=None, merge_cells=False)
    # 2.多个 sheet。
    with pd.ExcelWriter('example2.xlsx') as writer:
        dataframe.to_excel(writer, sheet_name='one', header=None, index=None)
        dataframe.to_excel(writer, sheet_name='two', header=None, index=None)

  逐行写 Excel 是很常用的操作，下面使用 append 方法实现。需要注意的是，append 方法提供返回值给出操作结果。

def add_row(df):
    x = [21, 22, 23, 24]
    x = np.expand_dims(x, 0).tolist()
    df = df.append(x)
    return df

if __name__ == '__main__':
    dataframe = pd.DataFrame()
    dataframe = dataframe.append([['11', '12', 2.718, 3.141]])
    dataframe = add_row(dataframe)
    dataframe.to_excel(excel_writer='example3.xls',
                       sheet_name='two',
                       float_format='%.2f',
                       columns=None,
                       header=False,
                       index=False,
                       merge_cells=False)

  append 方法即将被废弃，新的方法是 pandas.concat。该方法同样通过返回值给出操作结果。

def add_row(df):
    x = [21, 22, 23, 24]
    x = np.expand_dims(x, 0).tolist()
    df = pd.concat([df, pd.DataFrame(x)])
    return df

if __name__ == '__main__':
    dataframe = pd.DataFrame()
    dataframe = pd.concat([dataframe, pd.DataFrame([['11', '12', 2.718, 3.141]])])
    dataframe = add_row(dataframe)
    dataframe.to_excel(excel_writer='example3.xls',
                       sheet_name='two',
                       float_format='%.2f',
                       columns=None,
                       header=False,
                       index=False,
                       merge_cells=False)

4. 参考

---

1. xlwt 字体、颜色、格式设置，CSDN，2022。
2. openpyxl 写入及对齐方式设置，CSDN，2022。