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')