python openpyxl模块excel读写操作

一、安装

pip install openpyxl

二、简介

openpyxl对excel的抽象，主要有Workbook、Worksheet、Cell三个。
Workbook是对工作簿的抽象，Worksheet是对表格的抽象，Cell是对单元格的抽象。每个类都包含了若干的属性和方法。

2.1 Workbook对象

属性

active：获取活跃的Worksheet
read_only：是否以read_only模式打开excel文档
encoding：文档的元数据，如标题、创建者、常见日期等
worksheets：以列表形式返回所有的Worksheets

方法

get_sheet_names：获取所有表格的名称
get_sheet_by_name：通过表格名称获取表格对象
get_active_sheet：获取活跃的表格
remove_sheet：删除表格
create_sheet：创建一个空的表格
copy_sheet：在工作簿内拷贝表格

2.2 Worksheet对象

属性

title：表格的标题
max_row：表格的最大行
min_row：表格的最小行
max_column：表格的最大列
min_column：表格的最小列
rows：按行获取单元格（Cell对象）
column：按列获取单元格（Cell对象）
freeze_panes冻结窗格
values：按行获取表格的内容

方法

iter_rows：按行获取所有的单元格(Cell对象)
iter_columms：按列获取所有的单元格
append：在表格末尾添加数据
mered_cells：合并多个单元格
unmerge_cells：移除合并的单元格

2.3 Cell对象

row：单元格所在的行
column：单元格所在的列
value：单元格的取值
cordinate：单元格的坐标

三、小应用

3.1 合并excel文档

将多个excel合并为单个文件，对于熟练的excel操作人员也没有办法快速处理。这时利用python编程可以快速处理excel合并，比如合并部门所有人员的绩效总结

import os
import glob
import openpyxl


def merge_xlsx_files(xlsx_files):
    wb = openpyxl.load_workbook(xlsx_files[0])
    ws = wb.active
    ws.title = 'merge_results'

    for filename in xlsx_files[1:]:
        workbook = openpyxl.load_workbook(filename)
        worksheet = workbook.active
        for row in worksheet.iter_rows(min_row = 2):
            values = [cell.value for cell in row]
            ws.append(values)
    return wb

def get_all_xlsx_files(path):
    xlsx_files = glob.glob(os.path.join(path, 'example*.xlsx'))
    xlsx_files_sorted = sorted(xlsx_files, key=str.lower)
    return xlsx_files_sorted

def main():
    xlsx_files = get_all_xlsx_files('../attaches')
    wb = merge_xlsx_files(xlsx_files)
    wb.save('merge_result.xlsx')


if __name__ == '__main__':
    main()

3.2 计算平均分和总分

import openpyxl

def process_worksheet(sheet):
    min_col = sheet.max_column + 1
    max_col = sheet.max_column + 2

    for row in sheet.iter_rows(min_row=2, min_col=3):
        scores = [cell.value for cell in row]
        sum_score = sum(scores)
        avg_scores = int(sum_score / len(scores))

        sheet.cell(row=row[0].row, column=min_col).value = sum_score
        sheet.cell(row=row[0].row, column=max_col).value = avg_scores

    sheet.cell(row=1,column=min_col).value = 'sum'
    sheet.cell(row=1,column=max_col).value = 'avg'

def main():
    wb = openpyxl.load_workbook('../attaches/example.xlsx')
    sheet = wb.get_sheet_by_name('student')
    process_worksheet(sheet)
    wb.save('example_huizong.xlsx')


if __name__ == '__main__':
    main()