openpyxl

1 表格的基本操作

from openpyxl import Workbook
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active

ws['A1'] = "赋值"  # 给A1赋值
ws.cell(row=2, column=1).value = "赋值"  # 给第2行第1列赋值
ws.append([1, 2, 3, 4, 5])  # 在末尾增加一行

# 将表格按行导入到新表
df = pd.read_excel(r'盛丰WOW12月13日日报.xlsx')
for r in dataframe_to_rows(df, index=True, header=True):
    ws.append(r)

# 新建sheet, 1表示新建在第一个sheet后面
ws2 = wb.create_sheet('第二个sheet', 1)
# 赋值ws2表
ws3 = wb.copy_worksheet(ws2)
ws3.title = '第三个sheet'
ws3.sheet_properties.tabColor = 'FFEE0000'  # 更改sheet的标签颜色
wb.remove(ws3)  # 删除ws3

# 表里面所有的sheet名字
sheet_name_list = wb.sheetnames

# 选取单元格
ws[10]  # 选中第10行
ws[5:10]  # 选中第5到10行
ws['C']  # 选中第C列
ws['A:C']  # 选中第A到C列
ws['A1':'C5']  # 选取A1到C5

# # 插入行或列
ws.insert_cols(3, 2)  # 在第3列插入2列
ws.insert_rows(3, 2)  # 在第3行插入2行
ws.delete_cols(3, 2)  # 在第3列删除2列
ws.delete_cols(3, 2)  # 在第3行删除2行

# # 设置列宽和行高
ws.column_dimensions['A'].width = 20
ws.row_dimensions[1].height = 40

# # 隐藏行和列
ws.row_dimensions.group(7, 10, hidden=True)  # 隐藏7到10行
ws.column_dimensions.group('D', 'P', hidden=True)   # 隐藏D到P列

ws.merged_cells('D2:E2')  # D2到E2合并单元格
ws.unmerge_cells('D2:E2')  # D2到E2解除合并单元格
# 合并与解除单元格 选中方式 左上角单元格 top_left_cell=ws['D2']

2 设置字体

from openpyxl.styles import Font

font = Font(name='微软雅黑',  # 字体
            size=12,  # 字体大小
            bold=False,  # 是否加粗
            italic=False,  # 是否斜体
            vertAlign=None,  # 垂直对齐 superscript向上对齐 baseline居中对齐 subscript向下对齐
            underline='none',  # 下划线 double双下划线 single单下划线 doubleAccounting覆盖双下划线 singleAccounting覆盖单下划线
            strike=False,  # 删除线
            color='FF0F0F0F'  # 字体颜色 ARGB
            )
# 设置字体只能一个单元格一个单元格设置
a1 = ws['B4']
a1.font = font

3 单元格填充

from openpyxl.styles import PatternFill

patternfill = PatternFill(fill_type=None,  # 填充图案样式
                          start_color='FF0F0F0F',  # 前景色填充
                          end_color='FF0F0F0F'  # 背景色填充
                          )
a1 = ws['A1']
a1.fill = patternfill

4 单元格边框

from openpyxl.styles import Border, Side

border = Border(left=Side(border_style='thick', color='FF000000'),
                right=Side(border_style='thick', color='FF000000'),
                top=Side(border_style='thick', color='FF000000'),
                bottom=Side(border_style='thick', color='FF000000'),
                diagonal=Side(border_style='thick', color='FF000000'),
                diagonalUp=True,
                diagonalDown=True  # 对角线 diagonalDown diagonalUp
                )
# border_style样式:thin细线 dashDot点画线 double双线 mediumDashed中等虚线 dashed短线 thick粗线 dotted虚点线
#                   mediumDashDot中等点划线 hair虚线 slantDashDot斜点画线
a1 = ws['A1']
a1.border = border

5 设置对齐方式

from openpyxl.styles import Alignment

alignment = Alignment(horizontal='general',  # 水平对齐方式
                      vertical='bottom',  # 垂直对齐
                      text_rotation=0,  # 文本旋转 30 60
                      wrap_text=False,  # 是否换行
                      shrink_to_fit=False,  # 是否缩小字体适应列宽
                      indent=False,  # 缩进
                      )
# horizontal：justify两端对齐 fill填满对齐 left左对齐 general一般对齐 right右对齐 center居中对齐 distributed分散对齐
# vertical：bottom靠下 justify两端对齐 center居中 distributed分散对齐 top靠上

6 设置EXECL数字

a1.number_format = '格式'

7 条件格式

# 突出显示单元格
from openpyxl.formatting.rule import CellIsRule

# operator:notEqual不等于 greaterThanOrEqual大于或等于 lessThanOrEqual小于或等于 notContains不包含
# notBetween不介于 lessThan小于 beginsWith以XX开头 containsText包含XX equal等于 between endsWith greaterThan
rule = CellIsRule(operator='lessThan', formula=['C$1'], fill=patternfill)
ws.conditional_formatting.add('A1:B10', rule)
# 数据条
from openpyxl.formatting.rule import DataBarRule

# start_type:percentile百分点值 formula公式 percent百分比 max最大值 min最小值 num数字
rule2 = DataBarRule(start_type='percentile', start_value=10, end_type='percentile', end_value=100, color='FF638EC6',
                    showValue='None')
ws.conditional_formatting.add('A1:B10', rule2)
# 色阶
from openpyxl.formatting.rule import CellIsRule

rule3 = CellIsRule(start_type='percentile', start_value=10, start_color='FFAA0000',
                   mid_type='percentile', mid_value=50, mid_color='FFAA00AA',
                   end_type='percentile', end_value=90, end_color='FF00AA00')
ws.conditional_formatting.add('A1:B10', rule3)
# 图标集
from openpyxl.formatting.rule import IconSetRule

rule4 = IconSetRule('3Flags', 'percent', [0, 30, 50], showValue=True, reverse=False)
ws.conditional_formatting.add('A1:B10', rule4)