Python3 对Excel表格进行读取、写入、追加

常用函数

import openpyxl

# load_workbook()函数接受文件名，
# 返回一个 workbook 数据类型的值。这个 workbook 对象代表这个 Excel 文件，有点类似 File 对象代表一个打开的文本文件。
wb = openpyxl.load_workbook('disbursement_order_history_20211022_0(1).xlsx')
sheets = wb.sheetnames  # 取得工作簿中所有表名的列表
print(sheets)  # ['sheet1']
sheetName = sheets[0]  # 获取指定表名称
print(sheetName)  # sheet1
sheet = wb[sheetName]  # 获取指定表对象
print(sheet)  # <Worksheet "sheet1">
table = wb.active  # 获得当前活跃的工作页，默认为第一个工作页
print(table.title)  # 输出表名  sheet1

print(sheet.max_row)  # 获得最大行数
print(sheet.max_column)  # 获得最大行数

wb.create_sheet('Test1')  # 添加页
sheets = wb.sheetnames  # 取得工作簿中所有表名的列表
print(sheets)  # ['sheet1', 'Test1']

wb.remove(wb['Test1'])  # 删除表
sheets = wb.sheetnames  # 取得工作簿中所有表名的列表
print(sheets)  # ['sheet1']

写入

# coding=UTF-8
import openpyxl

# 使用openpyxl写xlsx文件
excelData = openpyxl.Workbook()  # 新建工作簿
excelData.create_sheet('Test1')  # 添加页
# exceltable = excelData['Test1'] # 获得指定名称页
exceltable = excelData.active  # 获得当前活跃的工作页，默认为第一个工作页
exceltable.title = 'Test'
# 行，列，值 这里是从1开始计数的
exceltable.cell(row=1, column=1, value='Test1')
exceltable.cell(row=1, column=2, value='Test2')
exceltable.cell(row=1, column=3, value='Test3')
exceltable.cell(row=1, column=4, value='Test4')
exceltable.cell(row=1, column=5, value='Test5')
exceltable.cell(row=2, column=1, value='Test15')
# 保存
excelData.save('excel_test.xlsx')
print("xlsx格式表格写入数据成功！")

读取

# 使用openpyxl读取xlsx文件
print('使用openpyxl读取xlsx文件:')
workbook = openpyxl.load_workbook('excel_test.xlsx')  # 读取xlsx文件
sheet = workbook['Test']
nrows = sheet.rows  # 获得行数 类型为迭代器
ncols = sheet.columns  # 获得列数 类型为迭代器
for row in sheet.rows:
    for cell in row:
        # print(row)  # 包含了页名，cell，值
        print(cell.value, "\t", end="")
    print()

# 读取一列
for row in ncols:
    # print(row)  # 包含了页名，cell，值
    line = [col.value for col in row]  # 取值
    print(line)

# 读取单元格
print('读取指定单元格内容：')
print(sheet.cell(1, 1).value)

追加写入

# 综合使用openpyxl对Excel内容追加写入
print('综合使用openpyxl对Excel内容追加写入:')
workbook = openpyxl.load_workbook('excel_test.xlsx')
print(workbook.defined_names.definedName)  # 输出工作页索引范围
print(workbook.sheetnames)  # 输出所有工作页的名称
# # 获得当前活跃的工作页，默认为第一个工作页
table = workbook.active
print(table.title)  # 输出表名
nrows = table.max_row  # 获得行数
ncolumns = table.max_column  # 获得行数
values = ['E', 'X', 'C', 'E', 'L']
for value in values:
    table.cell(nrows + 1, 1).value = value
    nrows = nrows + 1
workbook.save('excel_test.xlsx')

使用封装函数进行读取和写入

# 写入和追加excel数据
def write_excel_xlsx(path, sheet_name, value):
    index = len(value)
    try:
        workbook = openpyxl.load_workbook(path)
    except:
        workbook = openpyxl.Workbook()
        print('except')

    sheet = workbook.active
    sheet.title = sheet_name
    maxRows = sheet.max_row  # 获取当前excel最后一行
    maxColumn = sheet.max_column  # 获取当前excel最后一列
    if maxRows == 1:
        maxRows = 0
    if maxColumn == 1:
        maxColumn = 0
    print(maxRows)
    print(maxColumn)
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.cell(row=maxRows + i + 1, column=j + 1, value=str(value[i][j]))
    workbook.save(path)
    print("xlsx格式表格写入数据成功！")


# 读取excel数据
def read_excel_xlsx(path, sheet_name):
    workbook = openpyxl.load_workbook(path)
    sheet = workbook[sheet_name]
    for row in sheet.rows:
        for cell in row:
            print(cell.value, "\t", end="")
        print()

book_name_xlsx = 'xlsx格式测试工作簿.xlsx'
sheet_name_xlsx = 'xlsx格式测试表'
value3 = [["姓名", "性别", "年龄", "城市", "职业"],
          ["小米", "女", "26", "石家庄", "前台"],
          ["小明", "男", "55", "南京", "运维工程师"],
          ["小马", "女", "27", "苏州", "销售"], ]
write_excel_xlsx(book_name_xlsx, sheet_name_xlsx, value3)
# 追加
value4 = [
    ["小李", "男", "36", "北京", "运维工程师"],
    ["小吴", "男", "28", "湖南", "程序员"],
    ["小皮", "女", "27", "广东", "老师"],
]
write_excel_xlsx(book_name_xlsx, sheet_name_xlsx, value4)
read_excel_xlsx(book_name_xlsx, sheet_name_xlsx)