Python库 | openpyxl介绍，用代码操作Excel工作表

openpyxl 是一个用于读写 Excel 2010 及以上版本文件（.xlsx、.xlsm、.xltx、.xltm）的 Python 库，支持通过编程方式自动化操作 Excel 文件，包括数据读写、样式设置、图表生成等。

打开与保存

新建一个工作簿

from openpyxl import Workbook,
load_workbookwb = Workbook()

打开已有工作簿

wb = load_workbook('test.xlsx') #正常模式
#只读模式打开已有工作表，只读模式下只能通过append增加数据
wb = load_workbook(filename = 'test.xlsx',read_only=True)

保存工作簿

wb.save('test.xlsx')

表格Sheet页操作

获取工作簿中所有sheet页

wb.sheetnames

新建一个工作表sheet页

wb.create_sheet('Sheet2',2)

删除sheet页：

del wb["Sheet2"]

选择sheet页

ws = wb["Sheet1"]

修改sheet页名称

ws.title = "sheet11"

复制sheet页

cp_wb = wb.copy_worksheet(ws)

移动sheet页

wb.move_sheet(cp_wb,-1) #向前移动一个位置

获取Sheet页数据信息

print(ws.max_row)  # 最大行数，例如14
print(ws.max_column)  # 最大列数，例如20
print(ws.dimensions)  # 已启用的单元格范围，例如A1:T14
print(ws.encoding)  # 编码类型，例如utf-8
print(ws.sheet_view)  # 对象信息

表格操作

设置单元格的值

wb = Workbook()
ws = wb.active
ws['a1'] = 1
#或者
ws.cell(1,2,2)
#行,列,值

追加一行值

ws.append([1,2,3])

获取单元格的值

value = ws['a1'].value
value = ws.cell(1,2).value

获取整行的值

row_cells = ws[1] #选取工作表第一行
row_values = [i.value for i in row_cells] #获取工作表第一行的值
print(row_values)
rows =  ws[1:3] #选取工作表中多行数据
rows =  ws.rows: #选取工作表中所有行
for row_cells in rows:
    row_values = [i.value for i in row_cells] #获取工作表多行数据的值 
    print(row_values)

获取整列的值

col_cells = ws['A'] #选取工作表第一列
col_values = [i.value for i in col_cells] #获取工作表第一列的值 
print(col_values)
cols =  ws['A':'C'] #选取工作表中多列数据
cols = ws.columns #选取工作表中所有列
for col_cells in cols:
    col_values = [i.value for i in col_cells] #获取工作表多列数据的值
    print(col_values)

插入图片

from openpyxl.drawing.image import Image
img = Image('path_to_image.png') 
ws.add_image(img, 'A1')

写入公式

from openpyxl.formula.translate import Translator
ws.append(["成绩1", "成绩2", "总分"])
ws.append([88, 63])
ws.append([60, 70])
ws.append([95, 68])
ws['C2'] = "=SUM(A2,B2)"
for i in range(3,ws.max_row+1):
    print(i)
    index = f'C{i}'
    translator = Translator(formula="=SUM(A2,B2)",origin="C2").translate_formula(index)
    ws.cell(i,3,translator)
wb.save('test.xlsx')

合并单元格

#合并表格会保留左上角的单元格数据和样式，其他单元格数据会被清空
#合并单元格
ws.merge_cells('A1:B2')
#取消合并单元格
ws.unmerge_cells('A1:B2')

删除或插入行列

#在第五列插入一列，原来第五列往后移
ws.insert_cols(5,1) 
#在第五列(包含)开始往后删除一列
ws.delete_cols(5,1) 
#在第二行插入一行,原来第二行往后移
ws.insert_rows(2,2)  
#在第二行(包含)开始往后删除两行
ws.delete_rows(2,1)

移动单元格

如果移动到的位置原来有数据会被覆盖掉，移动后公式会丢失，可以设置translate=True来更新，默认值为False

ws.move_range('A1:B2',rows=2,cols=1,translate = True) 
#移动单元格，向下移动2行，向右移动1列(向上移动一行为-1，向左同理)

设置样式

字体样式

from openpyxl.styles import Font
font = Font(
    name = "微软雅黑",  #字体
    size = 15,          #字体大小
    color = "0000FF",   #字体颜色
    bold = True,        #是否加粗
    italic = True,      #是否斜体
    strike = None,      #是否使用删除线
    underline = 'double',   #是否使用下划线,可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
#设置单元格样式
ws.cell(1,2).font = font #或者ws['A1'].font = font
#设置整行样式
col_len = ws.max_column
for i in range(1,col_len+1):
    ws.cell(1,i).font = font
#设置整列样式
row_len = ws.max_row
for i in range(1,row_len+1):
    ws.cell(i,1).font = font
wb.save('test.xlsx')

设置行高列宽

#设置第一行行高为20
ws.row_dimensions[1].height = 20 
#设置第一列列宽为30
ws.column_dimensions['A'].width = 30

对齐方式

from openpyxl.styles import Alignment
#设置样式
alignment = Alignment(
    horizontal = 'left',  #水平对齐,可选general、left、center、right、fill、justify、centerContinuous、distributed
    vertical = 'top',     #垂直对齐，可选top、center、bottom、justify、distributed
    text_rotation = 0,    #字体旋转 0-180
    wrap_text = False,    #是否自动换行
    shrink_to_fit = False,#是否缩小字体填充
    indent = 0            #缩进值
)
ws.cell(1,1).alignment = alignment

设置边框

from openpyxl.styles import Border,Side
#设置边框样式
side = Side(
    style = 'dashDot', #边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
    color = '0000FF' #边框颜色
)
#设置单元格边框线
border = Border(
    top = side, #上边框
    bottom = side, #下
    left = side,#左
    right = side,#右
    diagonal = side#对角线
)
ws.cell(2,2).border = border

填充与渐变

from openpyxl.styles import PatternFill, GradientFill
fill = PatternFill(
    patternType="solid",  # 填充类型，可选none、solid、darkGray、mediumGray、lightGray、lightDown、lightGray、lightGrid
    fgColor="ffff00",  # 前景色
    bgColor="0000ff",  # 背景色
)
ws.cell(2,1).fill = fill
ws.cell(2,2).fill = GradientFill(
    degree=0,  # 角度
    stop=("000000", "FFFFFF")  # 渐变颜色，16进制rgb
)