openpyxl使用

一、用openpyxl包操作Excel文件

1.1 引用包

import openpyxl

1.2 读取当前已存在的excel

from openpyxl import load_workbook
wb = load_workbook(filename)
# filename “.xlsx”格式

1.3 读取未存在的excel

from openpyxl.workbook import Workbook
wb = Workbook() # 创建excel
wb.save(filename)  #保存excel，必须有的操作

1.4 设置sheet页

# 创建新的sheet页，并命名为sheetname
ws = wb.create_sheet(sheetname)

# 读取目前的sheet页，并设置名称
ws = wb.active
ws.title = sheetname

1.5 基本信息

# pandas转ws,将pandas的每一行转为excel每一行
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
# excel的最大列
ws.max_column
# excel的最大行
ws.max_row

1.6 填充样式设置

from openpyxl.styles import Color,PatternFill,Font

# 设置填充
fill = PatternFill(patternType='solid', fgColor=Color(index=44))

# fgColor=Color(), bgColor=Color()设置前景与背景颜色

# 单元格填充底纹纹路样式
patternType = {'darkDown', 'darkUp', 'lightDown', 'darkGrid', 'lightVertical', 
               'solid', 'gray0625', 'darkHorizontal', 'lightGrid', 'lightTrellis', 
               'mediumGray', 'gray125', 'darkGray', 'lightGray', 'lightUp', 
               'lightHorizontal', 'darkTrellis', 'darkVertical'}

 1.7 字体设置

from openpyxl.styles import Font
# Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
font = Font(name='宋体', size=11, italic=False, color=Color(index=0), bold=True)
font1 = Font(name='仿宋', size=11, italic=False, color=Color(index=0), bold=False)

1.8 边框设置

from openpyxl.styles import Side,Border

# 设置边，单边的粗细与颜色
thin = Side(style="medium", color=Color(index=0))
# style可选项

style = ('dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin')

#设置框，框是由边组成
border = Border(top=thin, left=thin, right=thin, bottom=thin)

1.9 设置单元格展示效果

from openpyxl.styles import Alignment
  
# 设置字体展示位置，单元格具体展示效果
align = Alignment(horizontal='center', vertical='center', wrapText=True)
align1 = Alignment(horizontal='left', vertical='center', wrapText=True)

# horizontal 设置水平效果；vertical 设置垂直效果
horizontal = {'fill', 'distributed', 'centerContinuous', 'right',
              'justify', 'center', 'left', 'general'}
vertical = {'distributed', 'justify', 'center', 'bottom', 'top'}

1.10 其他设置

# 设置行高，单位是像素
ws.row_dimensions[1].height = 20
# 设置列宽，单位是像素，这里的i是第几列：第一列 i = 1 
from openpyxl.utils import get_column_letter
ws.column_dimensions[get_column_letter(i)].width = 10

1.11 示例

# 要求，对一行做特殊设置，有颜色填充
# 对其余行处填充外均需设置

ws.row_dimensions[1].height = 20  # 设置第一行行高为20
for i in range(1, ws.max_column + 1):
    if i in [1,2,3]:  #  设置列宽
        ws.column_dimensions[get_column_letter(i)].width = 10
    else:
        ws.column_dimensions[get_column_letter(i)].width = 20

    # 设置第一行的格式
    ws[get_column_letter(i) + str(1)].font = font
    ws[get_column_letter(i) + str(1)].fill = fill
    ws[get_column_letter(i) + str(1)].alignment = align
    ws[get_column_letter(i) + str(1)].border = border
    
    # 从第二行开始
    for j in range(2, ws.max_row + 1):
        ws.row_dimensions[j].height = 30 # 设置行高

        # 设置字体格式 与边框格式 
        ws[get_column_letter(i) + str(j)].font = font1
        ws[get_column_letter(i) + str(j)].border = border

        # 设置单元格格式
        if i in [1,2,3]: 
            ws[get_column_letter(i) + str(j)].alignment = align1
        else:
            ws[get_column_letter(i) + str(j)].alignment = align