Snippets: Python: Excel

20th August 2020 at 2:19pm
Snippets: Python

openpyxl 来操作 Excel。下面例子用的版本是 2.5.3。

def generate_excel_file(dest_file, questions):
    wb = Workbook()
    ws = wb.active

    # Header
    ws['A1'] = '题目'
    ws['B1'] = '选项'
    ws['C1'] = '答案'

    # Styling
    ws.row_dimensions[1].font = Font(b=True)   # 首行加粗
    ws.freeze_panes = "B2"   # 冻结首行

    ws.column_dimensions['A'].width = 100
    ws.column_dimensions['B'].width = 60
    ws.column_dimensions['C'].width = 15

    al = Alignment(wrap_text=True)   # 自动换行

    # Data
    for i, q in enumerate(questions, 2):
        ws[f'A{i}'] = q.title
        
        # alignment 的设置,对 Cell 有效,对 column / row 无效,原因不详
        ws[f'A{i}'].alignment = al
        
        ws[f'B{i}'] = "\n".join(q.options)
        ws[f'B{i}'].alignment = al
        ws[f'C{i}'] = ''.join(q.answers)

    wb.save(dest_file)   # dest_file 是文件路径