python 使用 openpyxl 的用法(三)

修改 sheet 表的名称 sheet.title

from openpyxl import load_workbook

workbook = load_workbook(filename='../data/8.1test.xlsx')

sheet = workbook.active
print(workbook.sheetnames)
# ['Sheet', 'Sheet Copy']

sheet.title = "sheetUpdate"
print(workbook.sheetnames)

# ['sheetUpdate', 'Sheet Copy'] 第一张 sheet 表的表名已改变
# 若excel表中存在多个 sheet 表，默认修改第一张 sheet 表

# 也可以指定 sheet 表更改表名
updateSheet = workbook['Sheet Copy']

updateSheet.title = "sheetUpdate2"
print(workbook.sheetnames)
# ['sheetUpdate', 'sheetUpdate2']

workbook.save(filename='../data/8.1test.xlsx')

创建新的 excel 表格文件

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

# sheet 表名为"表格 1"
sheet.title = "new Sheet"

# 表名为 “new_excel_name.xlsx”
workbook.save(filename = "new_excel_name.xlsx")

插入空行和空列 .insert_cols()和.insert_rows()

from openpyxl import load_workbook

workbook = load_workbook(filename='../data/8.1test.xlsx')

sheet = workbook.active
# 在第二列插入一列空列
sheet.insert_cols(idx=2,amount=1)

# 在第三行插入两行空行
sheet.insert_rows(idx=3,amount=2)

workbook.save(filename='../data/8.1test.xlsx')

 只有一个副本的Sheet表格(一个日常需求事例)

# 这是一个openpyxl 的 综合应用
from openpyxl import load_workbook
from OpenpyxlTest.utils.random_util import RandomUtil

r = RandomUtil()


def creat_data(file_path):
    workbook = load_workbook(file_path)
    sheet = workbook.active
    sheet.append(['序号', '姓名', '地址', '手机号', '状态(山东状态:0,其余状态:-1)'])
    for index in range(1000):
        sheet.append([index + 1, r.create_name(), r.create_city(), r.create_phone()])
    workbook.save(file_path)


def inster_data(file_path):
    workbook = load_workbook(file_path)
    # 如果存在就先删除
    if f'{workbook.sheetnames[0]} Copy' in workbook.sheetnames:
        remove_sheet = workbook[f'{workbook.sheetnames[0]} Copy']
        workbook.remove(remove_sheet)
    sheet = workbook.active
    # 创建副本，防止操作失误
    workbook.copy_worksheet(sheet)
    for index in range(1, sheet.max_row):
        if '山东省' in sheet[f'C{index + 1}'].value:
            sheet[f'E{index + 1}'] = 0
        else:
            sheet[f'E{index + 1}'] = -1
    workbook.save(file_path)

# 构造数据
# creat_data('../data/8.1test.xlsx')
# 插入数据
inster_data('../data/8.1test.xlsx')

写在最后

入门使用就这些，更高深的用法在实践中去发现吧。。。