python_通过openpyxl读写Excel

一、模块安装

openpyxl模块可实现对excel文件的读、写和修改，使用之前需要先安装该模块。

# openpyxl生成的xlsx格式支持wps和office，xls格式只支持wps，使用office打开会报错
pip install openpyxl

二、 使用方法

• Excel演示内容，整个Excel文件称为工作簿，工作簿中的每个页称为工作表，工作表又由单元格组成。
  

1、新建Excel并写入数据

import openpyxl

# 创建一个新的工作簿对象
wb = openpyxl.Workbook()

# 获取工作表对象(sheet)
ws = wb.active
print(ws)

# 设置Sheet名称
ws.title = '学生表'

# 创建一个新sheet，可以指定名称，index表示新创建的工作簿放在第几个位置, index从0开始计数
ws_1 = wb.create_sheet(index=1, title='成绩表')
ws_2 = wb.create_sheet(index=2, title='科目表')

# 获取所有工作表名称
print(wb.sheetnames)

# 方法一：写入单个单元格
ws['A1'] = '姓名'
ws['B1'] = '班级'
ws['c1'] = '年龄'

# 方法二：写入单个单元格(行，列，内容); 也可以直接ws.cell(1, 4, '学校')
ws.cell(row=1, column=4, value='学校')

# 写入多个单元格(追加模式，不会覆盖之前的，从有数据的下一行开始)
ws.append(['王明', '三年级一班', '9岁'])

# 第一列和第三列插入数据，第二列插入[None]值
ws.append(['王五', None, '10岁'])

# 追加第二行数据，过滤空值
# 获取工作表总行数
max_row = ws.max_row
col_values = []
# 遍历第二列所有行
for row in range(1, max_row+1):
    # 获取当前单元格的值
    cell_value = ws.cell(row=row, column=2).value
    # 如果当前单元格的值不为空
    if cell_value is not None:
        # 将当前单元格的值添加到列表中
        col_values.append(cell_value)
print("第二列有 %d 行数据" % len(col_values))
ws.cell(len(col_values)+1, 2, "三年级二班")

# 将新数据追加到最后一行，忽略第二列
data = [['张三', '10岁'], ['李四', '15岁']]
for row in data:
    # 在第二列插入 None 值，实现跳过该列
    row.insert(1, None)
    ws.append(row)

# 保存
wb.save('1.xlsx')

2、读取Excel数据

import openpyxl

# 获取工作簿对象
wb = openpyxl.load_workbook('1.xlsx')

# 获取所有工作表名称
print(wb.sheetnames)

# 获取工作表对象,三种方法
sheet1 = wb['学生表']
sheet2 = wb.worksheets[1]
sheet3 = wb[wb.sheetnames[2]]
print(sheet1, sheet2, sheet3)

# 获取工作表名称
title = sheet1.title
print(title)

# 获取工作表总行数
rows = sheet1.max_row
# 获取工作表总列数
cols = sheet1.max_column
# 总行，总列
print(rows, cols)


# 获取某一单元格内容(行, 列)，例：2行1列，列表从1开始；也可以直接sheet1.cell(2, 1).value
cell = sheet1.cell(row=2, column=1).value
print(cell)

# 读取第一行的所有内容
row_list = []
for i in range(1, cols+1):
    cell_value = sheet1.cell(row=1, column=i).value
    row_list.append(cell_value)

print(row_list)

# 读取第一列的所有内容
column_list = []
for i in range(1, rows+1):
    cell_value = sheet1.cell(row=i, column=1).value
    column_list.append(cell_value)

print(column_list)


# 读取第二列的所有内容，不包含空值
col_values = []
# 遍历第二列所有行
for i in range(1, rows+1):
    # 获取当前单元格的值
    cell_value = sheet1.cell(row=i, column=2).value
    # 如果当前单元格的值不为空
    if cell_value is not None:
        # 将当前单元格的值添加到列表中
        col_values.append(cell_value)

print(col_values, len(col_values))

结果：

3、编辑Excel数据

import openpyxl

# 获取工作簿对象
wb = openpyxl.load_workbook('1.xlsx')

# 获取工作表对象
sheet = wb['学生表']

# 修改指定单元格内容
sheet.cell(row=1, column=4, value='老师')

# 写入多个单元格(追加模式，不会覆盖之前的，从有数据的下一行开始)
sheet.append(['王五', '三年级二班', '10岁'])

# 复制"学生表"，新sheet名称为"学生表 Copy"
ws_2 = wb.copy_worksheet(wb['学生表'])

# 保存
wb.save('1.xlsx')

4、删除Excel数据

import openpyxl

# 获取工作簿对象
wb = openpyxl.load_workbook('1.xlsx')

# 选择要删除内容的工作表和单元格(行, 列)
sheet1 = wb['学生表']
cell_to_delete = sheet1.cell(row=2, column=1)
# 删除单元格内容
cell_to_delete.value = None

# 删除 成绩表 工作表
del wb['成绩表']

# 保存
wb.save('1.xlsx')