python

Python使用openpyxl读写excel

  • 读取excel的内容

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    wb = load_workbook('/Users/aka/Downloads/w_pyxl.xlsx')
    ws = wb['Sheet1']

    for row in ws.rows:  # 遍历所有行
        # 按索引取出每行的指定位置的值
        print(row[0].value, row[1].value, row[2].value, row[3].value)
        

    # 每行组合成字典返回
    def parse_ws(sheet):
        keys = []
        for index, row in enumerate(sheet.rows):
            if index == 0:
                for item in row:
                    keys.append(item.value)
                continue

            values = [x.value for x in row]
            yield dict(zip(keys, values))


    wb = load_workbook('/Users/aka/Downloads/w_pyxl.xlsx')
    ws = wb['Sheet1']
    for line in parse_ws(ws):
        print('姓名：', line['姓名'])   # 通过表头来取值

  • 生成excel文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    from openpyxl import Workbook

    wb = Workbook()
    ws = wb.active

    data = [
        ('张三', '一三班', '100'),
        ('李四', '一三班', '65'),
        ('王武', '一三班', '95')
    ]

    ws.append(('姓名', '班级', '分数'))
    for item in data:
        ws.append(item)

    wb.save('/Users/aka/Downloads/test.xlsx')