openpyxl示例

from django.http import HttpResponse
from django.shortcuts import render
import openpyxl


def test1(request):
    """ 读取excel表格 """
    file_path = 'excel_file/测试数据-表格.xlsx'

    # 工作薄对象 <openpyxl.workbook.workbook.Workbook object at 0x00000220839CD550>
    workbook = openpyxl.load_workbook(file_path)
    
    # 表对象（单表） # <Worksheet "Sheet1">
    sheet = workbook.active  
    
    # 打印单表每一行
    for row in sheet.iter_rows(values_only=True):
        num, name, class_s = row
        print(num, name, class_s)

        # 1.row对象       for row in sheet.iter_rows()
        # (< Cell 'Sheet1'.A1 >, < Cell 'Sheet1'.B1 >, < Cell 'Sheet1'.C1 >)
        # (< Cell 'Sheet1'.A2 >, < Cell 'Sheet1'.B2 >, < Cell 'Sheet1'.C2 >)
        # (< Cell 'Sheet1'.A3 >, < Cell 'Sheet1'.B3 >, < Cell 'Sheet1'.C3 >)
        # (< Cell 'Sheet1'.A4 >, < Cell 'Sheet1'.B4 >, < Cell 'Sheet1'.C4 >)
        # (< Cell 'Sheet1'.A5 >, < Cell 'Sheet1'.B5 >, < Cell 'Sheet1'.C5 >)

        # 2.values_only=True    for row in sheet.iter_rows(values_only=True):
        # ('学号', '姓名', '班级')
        # (3220913023, '张艺卓', '计221')
        # (3220913023, '周英杰', '计221')
        # (3220913023, '孟子恒', '计221')
        # (3220913023, '段青垚', '计221')

        # 3.解包；num, name, class_s = row
        # 学号 姓名 班级
        # 3220913023 张艺卓 计221
        # 3220913023 周英杰 计221
        # 3220913023 孟子恒 计221
        # 3220913023 段青垚 计221

    return HttpResponse('ok')


def test2(request):
    """ 生成excel表格,并保存至指定路径 """

    # 生成工作薄：工作簿是一个 Excel 文件，可以包含多个工作表。它是整个文件的容器。
    workbook = openpyxl.Workbook()

    # 工作表：工作表是工作簿中的单个页面或表格
    sheet = workbook.active

    # 设置工作表名称
    sheet.title = "学生表"

    # 添加表头
    headers = ['学号', '姓名', '班级']
    sheet.append(headers)

    # 添加用户数据
    users = [
        ('1', '张三', '计222'),
        ('2', '李四', '计222'),
        ('3', '王五', '计222'),
    ]
    for row in users:
        sheet.append(row)

    # 保存
    save_path = "excel_file/gen/计222学生信息.xlsx"
    workbook.save(save_path)

    return HttpResponse('ok')