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
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Font, PatternFill

    data = [
        {'姓名': '小王', '学科': '语文', '成绩': 80},
        {'姓名': '小王', '学科': '数学', '成绩': 90},
        {'姓名': '小王', '学科': '英语', '成绩': 70}
    ]

    wb = Workbook()
    ws = wb.active
    ws.title = '学习成绩表'

    # 合并单元格
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3)
    cell = ws['A1']
    cell.value = '一三班上学期成绩表'
    # 设置上下左右居中对齐
    cell.alignment = Alignment(horizontal='center', vertical='center')
    # 设置字体：颜色、加粗、字体、小大
    cell.font = Font(color='2563fc', bold=True, name='PingFang SC', size=16)
    # 设置单元格填充色
    cell.fill = PatternFill(fill_type='solid', fgColor='999999')

    ws.append(['姓名', '学科', '成绩'])
    for item in data:
        ws.append([item['姓名'], item['学科'], item['成绩']])

    wb.save('demo.xlsx')