openpyxl库操作excel

openpyxl是一个读写xlsx文件的Python库，能够同时读取和修改Excel文档。

• 安装openpyxl库

#windows系统
pip install openpyxl
#苹果或linux系统
pip3 install openpyxl

• 导入openpyxl

from openpyxl import *
from openpyxl.drawing.image import Image
from openpyxl.formatting import Rule
from openpyxl.styles import Font, PatternFill, Border
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import ColorScale, FormatObject
from openpyxl.styles import Color
from openpyxl.formatting.rule import ColorScaleRule

• 加载数据

#最好使用wb表示工作簿，workbook简写
wb = load_workbook(r'data.xlsx')
#最好使用ws表示工作表，worksheet简写
ws = wb['Sheet1']
ws = wb['Sheet2']

• 创建工作簿

# 实例化
wb = Workbook()
# 创建工作表
ws = wb.create_sheet("Mysheet")     # 方式1：插入最后位置
ws = wb.create_sheet("Mysheet", 0)   # 方式2：插入到指定位置

• 读写数据

# 写入数据
ws['A1'] = 1
ws.cell(3,2,value = '测试')
#读取数据
ws['A1'].value
#添加注释
ws["A1"].comment =  Comment('注释, '作者')

• 增删行列

ws.insert_rows(1,2)
ws.insert_cols(1,2)
ws..delete_rows(1,2)
ws.delete_cols(1,2)

• 移动单元格

ws.move_range("D4:F10", rows=-1, cols=2)
ws.move_range("G4:H10", rows=1, cols=1, translate=True)

• 添加格式

data_bar = DataBar(cfvo=[FormatObject(type='min'), FormatObject(type='max')], color="638EC6", showValue=True, minLength=None, maxLength=None)
rule = Rule(type='dataBar', dataBar=data_bar)
i = len(ws['B'])
ws.conditional_formatting.add(f'B2:B{i}',rule )

• 折叠

ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)

• 添加图形

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

• 合并单元格

ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

• 修改格式

for a in ws['A']:
    a.number_format = 'yyyy-mm-dd'

• 修改样式

#设置字体
font_stype = Font(
        size=9,
        italic=True,
        color='ff0000',
        bold=True,
        strike=True
)
ws['A1'].font = font_stype

#填充颜色
fill_style = PatternFill(
        fill_type='solid',# 设置填充样式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', 'lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown', 'mediumGray', 'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp'
        start_color='ffff00'
)
ws['A1'].fill = fill_stype

#对齐方式
alignment_style = Alignment(
        horizontal='right',     # 水平方向:center, left, right
        vertical='top'       # 垂直方向: center, top, bottom
)
ws['A1'].alignment = alignment_style 

#设置边框对象
border_style = Border(left=Side(border_style='thin',color='000000'),
                      right=Side(border_style='thin',color='000000'),
                      top=Side(border_style='thin',color='000000'),
                      bottom=Side(border_style='thin',color='000000'))
ws['A1'].border = border_style

#设置指定列的宽度
ws.column_dimensions['A'].width = 20   # 设置指定列的宽
ws.row_dimensions[1].height = 45   # 设置指定行的高度

• 保存

wb.save(r'data.xlsx')