Python openpyxl模块原理及用法解析

#!/usr/bin/env python

# -*- coding: utf-8 -*-

import openpyxl

from openpyxl.styles import Alignment

def process_worksheet(sheet):

  avg_column = sheet.max_column + 1 # 平均数，存放在最后一列

  sum_column = sheet.max_column + 2 # 求和，存放在最后第二列

  for row in sheet.iter_rows(min_row=2, min_col=2):

    scores = [cell.value for cell in row] # 获取一行的值

    sum_score = sum(scores) # 求一行的和

    avg_score = sum_score / len(scores) # 求一行的平均数

    avg_cell = sheet.cell(row=row[0].row, column=avg_column)

    sum_cell = sheet.cell(row=row[0].row, column=sum_column)

    avg_cell.value = avg_score # 定位到单元格，设置总分

    sum_cell.value = sum_score # 定位到单元格，设置平均分

    # 设置对齐方式，水平是右对齐，垂直是居中

    align = Alignment(horizontal='left', vertical='center', wrap_text=True)

    avg_cell.alignment = align

    sum_cell.alignment = align

  # 设置平均分和总分的标题

  sheet.cell(row=1, column=avg_column).value = '平均分'

  sheet.cell(row=1, column=sum_column).value = '总分'

def main():

  wb = openpyxl.load_workbook('example.xlsx')

  sheet = wb.get_sheet_by_name('Sheet1')

  process_worksheet(sheet)

  wb.save('example.xlsx')

if __name__ == '__main__':

  main()