python-openpyxl

python-openpyxl

概念

工作蒲（workbook）

表单（worksheet）

行、列、单元格（row、column、cell）

工作簿的操作

创建工作簿

file_name = r"c:/creat.xlsx"
book = openpyxl.Workbook(file_name)
book.save(file_name)

读取工作簿

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
# 使用切片选择工作表
s = book["Sheet"]

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
# 选择默认激活的工作簿
s = book.active

读取工作簿所有表名

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
# return list and openpyxl object
titles = book.worksheets

for i in titles:
    print(i.title)

删除工作表

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
book.remove(book["Sheet"])
# 填写其他路径为保存新文件
book.save(file_name)

创建工作表

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
book.create_sheet("创建新的工作表名称")
book.save(file_name)

复制工作表

# 后缀加入Copy
file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
new_sheet = book.copy_worksheet(book["创建新的工作表名称"])
book.save(file_name)

修改工作表名

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
s = book["创建新的工作表名称"]
s.title = "被修改的表"
book.save(file_name)

单元格的操作

读取单元格

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
datas = sheet["A1"].value
# 读取指定行列
# datas = sheet["A1:C10"].value
# datas = sheet["1:10"].value
print(datas)

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
datas = sheet.cell(row=1,column=1).value # 第一行，第一列
print(datas)

小练习

"""读取所有行内容"""
file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
for i in sheet:
    for x in i:
        print(x.value)
        
"""return list[tuple,tuple,tuple]"""
file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
datas = list(sheet.values)
print(datas)

"""动态获取所有行/列"""
file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 获取所有行
for i in sheet.rows:
    for datas in i:
        print(datas.value)
# 获取所有列
for i in sheet.columns:
    for datas in i:
        print(datas.value)

查看单元格对应的数字/字母

# 数字转字母
number_str = openpyxl.utils.get_column_letter(26)
# 字母转数字
str_number = openpyxl.utils.column_index_from_string("A")

获取最大行/列

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 最大行
sheet_max_rows = sheet.max_row
print(sheet_max_rows)
# 最大列
sheet_max_column = sheet.max_column
print(sheet_max_column)

获取所有数据

"""按行获取所有数据，存入list中"""
file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
list1 = []
for i in sheet.rows:
    for data in i:
        list1.append(data.value)
print(list1)

写入数据

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
sheet["A1"] = "啦啦啦啦"
book.save(file_name)

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 一行八列
sheet.cell(1,8,value = "哈哈哈")
book.save(file_name)

以列表的形式追加工作簿

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
list1 = ["1","2","3"]
sheet.append(list1)
book.save(file_name)

插入数据

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 在第二列，插入四列
sheet.insert_cols(idx=2,amount=4)
book.save(file_name)

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 在第二行，插入四行
sheet.insert_rows(idx=2,amount=4)
book.save(file_name)

删除数据

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 在第二行，删除四行
sheet.delete_rows(idx=2,amount=4)
book.save(file_name)

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 在第二列，删除四列
sheet.delete_cols(idx=2,amount=4)
book.save(file_name)

移动数据

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 移动单元格，选中A1到C3区域，向下移动十行，向右移动十列；-10代表向反方向移动
sheet.move_range("A1:C3",rows=10,cols=10
book.save(file_name)

冻结单元格

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
sheet.freeze_panes = "C3"
book.save(file_name)

获取每一行数据，返回list,list,list...

file_name = r"c:/creat.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["被修改的表"]
# 动态获取数据
max_rows = sheet.max_row
max_column = sheet.max_column
# 获取迭代器，从第一行到最大一行，从第一列到最大一列的数据，不指定默认为无限大
for i in sheet.iter_rows(1,max_rows,1,max_column):
    print([data.value for data in i])

合并/取消合并单元格

file_name = r"d:\d.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["Sheet1"]
# 合并单元格
sheet.merge_cells("A1:C4")
book.save(file_name)

file_name = r"d:\d.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["Sheet1"]
# 取消合并单元格
sheet.unmerge_cells("A1:C4")
book.save(file_name)

批注

file_name = r"d:\d.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["Sheet1"]
# 批注
pizhu = openpyxl.comments.Comment("批注的内容","批注人的姓名")
sheet["C1"].comment = pizhu
book.save(file_name)

设置字体格式

file_name = r"d:\d.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["Sheet1"]
"""设置字体格式
字体名称 中文需加上u name=u"宋体"
size 设置字体格式
bold是否加粗
italic是否倾斜
vertAlign: 'None’(默认)/ 'superscript’(上标)/ 'subscript’(下标)
underline: "None'(默认) / 'single(单下划线) /'double(双下划线) / 'singleAccounting'(会计用单下划线) /'doubleccounting'(会计用双下划线)
strike：是否显示删除线
color：颜色
"""
font_obj = openpyxl.styles.Font(name="Microsoft YaHei UI",size=11,bold=False,italic=False,vertAlign=None,underline="none",strike=False,color="f1c232")
sheet["A1"].font = font_obj
book.save(file_name)

插入图片

出现异常ImportError: You must install Pillow to fetch image objects，需要安装第三方库 pip install Pillow

file_name = r"d:\d.xlsx"
book = openpyxl.load_workbook(file_name)
sheet = book["Sheet1"]
# 插入图片
img = openpyxl.drawing.image.Image(r"C:\Users\admin\Pictures\图片.jpg")
# 设置图片高度
img.height = 100
# 设置图片宽度
img.width = 70
# 设置插入的位置
sheet.add_image(img,"A1")
book.save(file_name)

感谢B站UP主【孙兴华zz老师】讲解；

更多详细关于openpyxl：https://files.cnblogs.com/files/blogs/533226/Python办公自动化之Excel篇.rar