import openpyxl
#打开文件
wb = oepnpyxl.load_workbook('example.xlsx')

#workbook
##workbook对象属性
wb.active    //获取活跃的worksheet
wb.read_only
wb.encoding
wb.properties  //文档元数据，如标题、创建者、创建日期
##workbook对象方法
wb.sheetnames //获取所有表格名称
ws = wb['sheetname']   //获取worksheet对象
wb.remove_sheet         //删除工作表
wb.create_sheet         //创建工作表
wb.copy_worksheet
wb.save('sample.xlsx')   //保存工作簿

#worksheet
##worksheet对象属性
ws.title       //表格标题
ws.dimensions //表格大小区域  ，A1:E11
ws.max_row     //最大行
ws.min_row     //最小行
ws.max_column   //最大列
ws.min_column   //最小列
ws.rows        //按行获取单元格（cell对象）
ws.columns     //按列获取单元格（cell对象）
ws.values       //按行获取表格内容
ws.freeze_panes  //冻结窗格，冻结第一行-A2;冻结第一列B1;冻结第一行列B2
##columns rows values 通过生成器返回数据
##worksheet对象方法
iter_rows        //按行获取所有单元格，可以指定起点、终点
list(ws.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3))  //使用例子 
iter_columns
append           //在表格末尾加数据
merged_cell      //合并多个单元格
unmerged_cell    //移除合并的单元格

#Cell对象
ws['A2']   //cell对象
ws.cell(row=1, column=2)
##cell属性
row        //单元格所在行
column     //单元格所在列
cordinate  //单元格的坐标，行和列

##打印表格内容
###Example 1
for row in ws.values;
    print(*row)

###Example 2
for row in ws.rows:
    print(*[cell.value for cell in row])

###Example 3
for row in ws.iter_rows():
    print(*[cell.value for cell in row])

示例代码1

#！/usr/bin/python
#-*- coding: utf-8 -*-
import openpyxl

##计算表中学生的总成绩和平均成绩

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=3):
        scores = [cell.value for cell in row]
        sum_scores = sum(scores)
        avg_scores = sum_scores / len(scores)

        #计算平均分和总分，并且保存到最后两列
        sheet.cell(row=row[0].row, column=avg_column).value = avg_scores
        sheet.cell(row=row[0].row, column=sum_column).value = sum_scores
    #设置平均分和总分的标题部分
    sheet.cell(row=1, column=avg_column).value = 'avg'
    sheet.cell(row=1, column=sum_column).value = 'sum'

def main():
    wb = openpyxl.load_workbook('example.xlsx')
    sheet = wb['student']
    process_worksheet(sheet)
    wb.save('example_copy.xlsx')

if __name__ == '__main__':
    main()

示例代码2

#！/usr/bin/python
#-*- coding: utf-8 -*-
import os 
import glob

import openpyxl

##合并多个excle到一个

def merge_xlsx_files(xlsx_files):
    wb = openpyxl.load_workbook(xlsx_files[0])
    ws = wb.active
    ws.title = "merged result"

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

    return wb

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

def main():
    xlsx_files = get_all_xlsx_files(os.path.expanduser('~lmx'))
    wb = merge_xlsx_files(xlsx_files)
    wb.save('merged_form.xlsx')

if __name__ == '__main__':
    main()