openpyxl简单用

openpyxl

工作需要，利用openpyxl读取文件文件夹中所有xlsx，并对xlsx中的单个工作簿指定范围内数据进行替换，解脱双手。
import openpyxl
from openpyxl.styles import Font
import os
import re

def singleworksheet(worksheet):
# 单个worksheet修改
for i in range(0, 20):
rowid = 10+i*3
# 精确读取表格中的某一单元格
content_new = worksheet.cell(row=rowid, column=2).value
print(content_new)
if worksheet.cell(row=rowid, column=2).value is None:
continue
else:
# 此处的行数和列数都是从1开始计数的
content_new = content_new.replace(‘1212’, ‘2212’)
# 合并单元格不能直接赋值，先拆分再赋值
worksheet.unmerge_cells(start_row=rowid, start_column=2,
end_row=rowid+2, end_column=3)
worksheet.cell(row=rowid, column=2).value = content_new
worksheet.merge_cells(start_row=rowid, start_column=2,
end_row=rowid+2, end_column=3)

def singleworkbook(workbook):
# 获取工作簿 workbook的所有工作表
shenames = workbook.sheetnames
print(shenames)
shenum = len(shenames)
for i in range(1, shenum):
# 还可以通过索引方式获取表对象
worksheet = workbook.worksheets[i]
print(worksheet)
# 经过上述操作，我们已经获得了第一个“表”的“表对象“，接下来可以对表对象进行操作
name = worksheet.title # 获取表名
print(name) #
# 获取该表相应的行数和列数
rows = worksheet.max_row
columns = worksheet.max_column
print(rows, columns)
singleworksheet(worksheet)

def multi_workbook():
# 多个workbook
path = “C:\Users\Administrator\Desktop\excel”
filenames = os.listdir(path)
for filename in filenames:
print(filename)
for i in filenames:
if 'xlsx’in i:
excel_path = “C:\Users\Administrator\Desktop\excel\”+i
# 获取工作簿对象
workbook = openpyxl.load_workbook(excel_path)
singleworkbook(workbook)
workbook.save(excel_path)
else:
continue
multi_workbook()

参考官方文档：http://yumos.gitee.io/openpyxl3.0/index.html#document-pivot