python-openpyxl的简单应用

import openpyxl


# 读
def read16excel(path):

    # 实例化excel表
    wb = openpyxl.load_workbook(path)

    # 创建新工作表
    wb.create_sheet("Sheet2")

    # 查看sheet页名字
    print(wb.sheetnames)

    # 通过索引定位sheet页
    sheet = wb.worksheets[0]

    # sheet.values返回一个生成器 每行信息放在一个元组内
    print(type(sheet.values))
    g_values = sheet.values
    print(g_values.__next__())
    print(g_values.__next__())
    print(g_values.__next__())

    # 定位cell 循环列   iter_rows可以循环行 （指定循环的最小/大列、最小/大行数）
    data_list = []
    for column in sheet.iter_cols(min_col=1, max_col=2, min_row=1, max_row=5):
        for cell in column:
            data_list.append(cell.value)

    # 实例调用save方法可以关闭表格，否则后续调用会出现占用报错Permission denied
    wb.save(path)
    return data_list


# 写/修改
list1 = [[1, "a"], [2, "b"], [3, "c"], [4, "d"], [5, "e"]]


def write16excel(data, path, sheetname):

    # 实例化excel表
    # Workbook()方法可以新建excel，如果已经存在会覆盖
    # load_workbook()可以载入已存在的excel进行修改
    # wb = openpyxl.load_workbook(path)
    wb = openpyxl.Workbook()

    # 新建sheet页
    wb.create_sheet(sheetname)

    # 激活实例
    # 修改_active_sheet_index值可以更换要激活的sheet页索引，默认是0
    wb._active_sheet_index = 1
    sheet = wb.active

    # 获取sheet页的最大行和列
    # max_row = sheet.max_row
    # max_col = sheet.max_column

    # 循环插入sheet页
    for num in range(len(data)):
        sheet.cell(row=num+1, column=2, value=str(data[num][0]))
        sheet.cell(row=num+1, column=3, value=str(data[num][1]))
        if num + 1 == 2:
            sheet.cell(row=num+1, column=4, value="查询结果不一致")
        elif num + 1 == 3:
            sheet.cell(row=num+1, column=4, value="无查询结果")
        else:
            continue

    # 定义/更改当前操作的sheet页名
    sheet.title = "sheet3"

    # 指定保存路径
    wb.save(path)
    return "保存成功"


test1 = read16excel("d:\\test1.xlsx")
test2 = write16excel(list1, "d:\\test2.xlsx", "sheet2")
print(test1)
print(test2)