Python处理Office文件模版代码（自己参考用）-Excel-openpyxl包

一、安装

pip install openpyxl

二、样例代码

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font,Alignment,colors

def openExcelFile():
    workBook = load_workbook('./file_path/example.xlsx')
    sheet1 = workBook.active
    sheet2 = workBook['Sheet1']
    print(f'======SheetNames:{workBook.sheetnames}======')
    for sheet in workBook:
        print(f'#####SheetTitle:{sheet.title}#####')
    pass

def getCellValue():
    workBook = load_workbook('./file_path/example.xlsx')
    sheet = workBook.active
    value1 = sheet['A1'].value
    value2 = sheet.cell(row=1, column=2).value
    # 切片
    cell1 = sheet['c2':'d3']
    # 整行整列
    cell_row = sheet[3]
    cell_col = sheet['c']
    # 全部数据
    for row in sheet.iter_rows(1,sheet.max_row,1,sheet.max_column):
        for cell in row:
            print(cell.value)
            pass
        pass

def createExcelFile():
    workBook = Workbook()
    sheet = workBook.active
    sheet2 = workBook['sheetName',0]                    # 0可选，表示将该工作表设置为第一个工作表
    workBook.save('./file_path/exampleCreate.xlsx')
    pass

def setValueAndFont():
    workBook = load_workbook('./file_path/example.xlsx')
    sheet = workBook.active
    bold_italic_red_34_font = Font(name='Arial',size=34,bold=True,italic=True,color=colors.RED)
    sheet['A1'] = 'hello'
    sheet['A1'].font = bold_italic_red_34_font
    data = ['a','b','c','d','e','f','g']
    for i,d in enumerate(data):
        sheet.cell(i + 1, 2).value = d
    workBook.save('./file_path/exampleSetValueAndFont.xlsx')

def setStyle():
    workBook = load_workbook('./file_path/example.xlsx')
    sheet = workBook.active
    sheet.row_dimensions[1].height = 30
    sheet.column_dimensions['A'].width = 30
    data = ['Python','Java','C++','C#','PHP','Ruby','Perl','Go','Swift','Kotlin','Rust','Julia']
    for i,d in enumerate(data):
        sheet.cell(i+1,1).value = d
        sheet.cell(i+1,1).alignment = Alignment(horizontal='center',vertical='center')
    workBook.save('./file_path/exampleSetStyle.xlsx')

def drawChart():
    from openpyxl.chart import LineChart,BarChart,PieChart,Reference
    # 线形图
    workBook = Workbook()
    sheet = workBook.active
    rows = [
        ['Name','Batch 1','Batch 2',"Batch 3"],
        ['aaa',40,30,25],
        ['bbb',40,25,30],
        ['ccc',30,30,45],
    ]
    for row in rows:
        sheet.append(row)
        pass

    lineChart = LineChart()
    lineChart.title = "Line Chart"
    lineChart.x_axis.title = "Batch"
    lineChart.y_axis.title = "Amount"
    
    data = Reference(sheet,min_col=2,min_row=2,max_col=4,max_row=4)
    lineChart.add_data(data, titles_from_data=True)
    sheet.add_chart(lineChart,"E1")

    # 柱状图
    from openpyxl.chart import series
    data=[
        ('Number','Batch 1','Batch 2'),
        (2,10,30),
        (3,40,60),
        (4,50,70),
    ]
    sheet2 = workBook.create_sheet('BarChart')
    for row in data:
        sheet2.append(row)
        barChart = BarChart()
        barChart.type = 'col'
        barChart.style = 10
        barChart.title = 'Bar Chart'
        barChart.x_axis.title = 'Test_number'
        barChart.y_axis.title = 'Sample length(mm)'
        data = Reference(sheet2,min_col=2,min_row=1,max_col=3,max_row=7)
        cats = Reference(sheet2,min_col=1,min_row=2,max_row=7)
        barChart.add_data(data,titles_from_data=True)
        barChart.set_categories(cats)
        barChart.shape = 4
        sheet2.add_chart(barChart,'A10')
        
    # 饼图
    from openpyxl.chart.series import DataPoint

    data=[
        ['名称','数值'],
        ['苹果',50],
        ['草莓',30],
        ['椰子',10],
    ]
    sheet3 = workBook.create_sheet('PieChart')
    for row in data:
        sheet3.append(row)
    
    pie = PieChart()
    labels = Reference(sheet3,min_col=1,min_row=2,max_row=3)
    data   = Reference(sheet3,min_col=2,min_row=1,max_row=3)
    pie.add_data(data,titles_from_data=True)
    pie.set_categories(labels)
    pie.title = 'Pies sold by category'
    
    slice = DataPoint(idx=0, explosion=10)                      # Cut the first slice out of the pie
    pie.series[0].data_points = [slice]                         # Cut the first slice out of the pie
    sheet3.add_chart(pie,'D1')
    pass

def merge_Excel():
    import os
    workBook = Workbook()
    sheet = workBook.active
    all_data = []

    for name in os.listdir('./file_path'):
        path = f'./file_path/{name}'
        tmp_wb = load_workbook(path)
        tmp_sheet = tmp_wb.active
        for r in range(1, tmp_sheet.max_row + 1):
            row_data = []                                       # 获取每一行的数据
            for c in range(1, tmp_sheet.max_column + 1):
                cell_value = tmp_sheet.cell(row=r, column=c).value
                row_data.append(cell_value)
            if row_data not in all_data:                        # 去重
                all_data.append(row_data)
        for data in all_data:
            sheet.append(data)
        workBook.save('./file_path/test.xlsx')

def merge_Excel_inDifferentSheet():
    import os
    workBook = Workbook()
    for name in os.listdir('./file_path'):
        path = f'./file_path/{name}'
        tmp_wb = load_workbook(path)
        tmp_sheet = tmp_wb.active
        sheet = workBook.create_sheet(name[:-5])
        for r in range(1, tmp_sheet.max_row + 1):
            row_data = []
            for c in range(1, tmp_sheet.max_column + 1):
                cell_value = tmp_sheet.cell(row=r, column=c).value
                row_data.append(cell_value)
            sheet.append(row_data)
        del workBook['Sheet']                                   # 删除默认自动创建的sheet
        workBook.save('./file_path/test.xlsx')

def split_Excel():
    # 按行拆分为单独文件
    workBook = load_workbook('./file_path/test.xlsx',data_only=True)
    sheet = workBook.active
    title = ['column1','column2','column3']
    for i,row in enumerate(sheet.rows):
        if i == 0:
            continue
        else:
            temp_workBook = Workbook()
            temp_sheet = temp_workBook.active
            temp_sheet.append(title)
            row_data = [cell.value for cell in row]
            temp_sheet.append(row_data)
            temp_workBook.save(f'./file_path/{row_data[0]}.xlsx')

def gapColor_changeStyle4Excel():
    from openpyxl.styles import PattenFill
    from datetime import date
    workBook = Workbook()
    sheet = workBook.active
    rows = [
        ['Date','Batch 1','Batch 2','Batch 3'],
        [date(2020,12,1),40,30,25],
        [date(2020,12,2),40,25,30],
    ]
    for row in rows:
        sheet.append(row)
    backColor = PattenFill('solid', fgColor="FF0000")
    for i in range(1, sheet.max_row + 1):
        if i % 2 == 0:
            for cell in range(1, sheet.max_column + 1):
                sheet.cell(row=i, column=cell).fill = backColor
    workBook.save('./file_path/test.xlsx')

def calculateTimeInExcel():
    workBook = load_workbook('./file_path/TimeData.xlsx',data_only=True)
    sheet = workBook.active
    data = []
    for i in range(2,sheet.max_row+1):
        t_data = []
        for j in range(1,sheet.max_column+1):
            t_data.append(sheet.cell(row=i,column=j).value)
    # 统计
        h,m = t_data[2].split(':')
        full = int(h)*60 + int(m)
        tmp = full - 18 * 60
        t_data.append(tmp)
        # 处理时间问题
        t_data[0] = t_data[0].date()
        data.append(t_data)
    wb = Workbook()                                                             # 保存
    new_sh = wb.active
    for row in data:
        new_sh.append(row)
    workBook.save('./file_path/test.xlsx')

def markDuplicate4Excel():
    from openpyxl.styles import PatternFill
    workBook = load_workbook('./file_path/test.xlsx',data_only=True)
    sheet = workBook.active
    index = []
    tmp = []                        # 没有重复的数据
    for i,c in enumerate(sheet['B']):
        if c.value  not in tmp:
            tmp.append(c.value)
        else:
            index.append(i)
    fill = PatternFill('solid', fgColor='AEEEEE')
    for i,r in enumerate(sheet.rows):
        if i in index:
            for c in r:
                c.fill = fill
            print(f'第{i+1}行是重复数据')        
    workBook.save('./file_path/test.xlsx')





if __name__ == '__main__':
    openExcelFile()
    getCellValue()
    createExcelFile()
    setValueAndFont()
    setStyle()
    drawChart()
    merge_Excel()
    merge_Excel_inDifferentSheet()
    split_Excel()
    gapColor_changeStyle4Excel()
    calculateTimeInExcel()
    markDuplicate4Excel()