1,首先安装:
pip3.7 install openpyxl

2写程序

import openpyxl
import os
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment


# 判断文件是否存在
def file_exist(file_path):
    path = os.path.join(file_path)
    p1 = os.path.exists(path)
    return p1


# 更新或生成excel
def create_excel(nws):

    filename = '19年'+str(nws[0].value)+str(nws[1].value)+'线下积分核对.xlsx';
    if file_exist(filename):
        nwb = openpyxl.load_workbook(filename)
    else:
        nwb = Workbook()
    sheets = nwb.sheetnames
    exist_sheet = False
    for name in sheets:
        if name == '七月':
            exist_sheet = True
            break
    if exist_sheet:
        ws1 = nwb.get_sheet_by_name('七月')
    else:
        ws1 = nwb.create_sheet("七月")

    # 设置边框
    bd = 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'),
                diagonal=Side(border_style=None,
                              color='FF000000'),
                diagonal_direction=0,
                outline=Side(border_style=None,
                             color='FF000000'),
                vertical=Side(border_style=None,
                              color='FF000000'),
                horizontal=Side(border_style=None,
                                color='FF110000')
                )

    ws1['A1'] = '体系'
    ws1['B1'] = '姓名'
    ws1['C1'] = '体系业绩(不含盟主)'
    ws1['D1'] = '是否托管体系'
    ws1['E1'] = '盟主利润'
    ws1['F1'] = '个人返点'
    ws1['G1'] = '团队推广'
    ws1['H1'] = '盟主实发'

    ws1['A1'].border = bd
    ws1['B1'].border = bd
    ws1['C1'].border = bd
    ws1['D1'].border = bd
    ws1['E1'].border = bd
    ws1['F1'].border = bd
    ws1['G1'].border = bd
    ws1['H1'].border = bd

    for cell in nws:
        ws1.cell(row=2, column=cell.column).value = cell.value
        ws1.cell(row=2, column=cell.column).border = bd

    # 套用公式
    ws1['I2'] = '=SUM(G2:H2)'
    # 合并单元格
    ws1.merge_cells('A7:E8')
    # 填充
    fill = PatternFill(fill_type="solid",fgColor="FFFF00")
    # 对齐
    alignment = Alignment(horizontal='center',
                          vertical='center',
                          text_rotation=0,
                          wrap_text=False,
                          shrink_to_fit=False,
                          indent=0)
    # 字体
    font = Font(u'宋体', size=11, bold=True)

    ws1['A7'].fill = fill
    ws1['A7'].border = bd
    ws1['A7'].font = font
    ws1['A7'].alignment = alignment
    ws1['A7'].value = '团队推广'
    ws1['A9'] = 'CODE'
    ws1['B9'] = '姓名'
    ws1['C9'] = '个人业绩'
    ws1['D9'] = '团队业绩(不含自身)'
    ws1['E9'] = '实发积分'

    ws1['A9'].border = bd
    ws1['B9'].border = bd
    ws1['C9'].border = bd
    ws1['E9'].border = bd
    ws1['D9'].border = bd

    # 保存文件
    nwb.save('19年'+str(nws[0].value)+str(nws[1].value)+'线下积分核对.xlsx')


# 加载excel
wb = openpyxl.load_workbook('201907线下积分核对.xlsx')
# 获取sheet
ws = wb.get_sheet_by_name('盟主实发')
# 遍历行 max_row为最大行数
for row in range(2, ws.max_row+1):
    create_excel(ws[row])

以上为实际工作中写的一个小工具。
下面为openpyxl的一些总结:

ws = wb.active #获取第一个sheet
ws1 = wb.create_sheet(“Mysheet”) #创建一个sheet
ws1.title = “New Title” #设定一个sheet的名字

更多的高级用法这个博客挺全的
https://www.cnblogs.com/zeke-python-road/p/8986318.html

发表评论

电子邮件地址不会被公开。