python - 属性错误: 'list' object has no attribute 'iter_rows'

Question

我读了一个Excel文件，在那里我得到了所有的工作表，但是当这样做的时候得到那个错误。

View .py

from Django.shortcuts import render
import openpyxl


def index(request):
    if "GET" == request.method:
        return render(request, 'file/index.html', {})
    else:
        excel_file = request.FILES["excel_file"]

        # you may put validations here to check extension or file size

        wb = openpyxl.load_workbook(excel_file)

        # getting all sheets
        worksheet = wb.sheetnames
        print(worksheet)

        excel_data = list()
        # iterating over the rows and
        # getting value from each cell in row
        for row in worksheet.iter_rows():
            row_data = list()
            for cell in row:
                row_data.append(str(cell.value))
            excel_data.append(row_data)

        return render(request, 'file/index.html', {"excel_data": excel_data})

Answer 1

worksheet = wb.sheetnames 实际上以 list 形式返回工作表名称。

如何获取表格:

if 'sheet name' in wb.sheetnames:
    sheet = wb['sheet name']

您可以执行以下操作:

for name in wb.sheetnames:
    sheet = wb[name]
    for row in sheet.iter_rows():
        # Your code

更新(保存到数据库)::如果一个工作表对应一个模型，并且您想将数据保存到数据库中，请参阅以下部分:

sheet_to_model = {
    'sheet1':{
        'model': Model1, 
        'column_map': {
            'xl column 1': 'model_field_1',
            'xl column 2': 'model_field_2',
        }
    }

}

# Also map each sheet's column name to your model's field name

for name in wb.sheetnames:
    sheet = wb[name]
    for row in sheet.iter_rows():
        # Here get model name using sheet name and the sheet_to_model dict. 
        # Get each cell value from row and column name and create a dict using  
        # the model's field name and the cell value. Then inset using Model.objects.create(**data)

更新 2(保存到数据库):假设您的 xl 文件具有以下工作表和相应的列:

• 工作表 1(列:“s1c1”、“s1c2”、“s1c3”)
• 工作表 1(列:“s1c1”、“s1c2”、“s1c3”)

并且您拥有名为 Model1 和 Model2 的模型。 Sheet1 的数据将保存到 Model 中，Sheet2 的数据将保存到 Model2 现在，请看下面的代码来理解数据如何存储在相应的模型中:

import openpyxl
from django import models


class Model1(models.Model):
    m1f1 = models.IntergerField()
    m1f2 = models.IntergerField()
    m1f3 = models.IntergerField()


class Model2(models.Model):
    m2f1 = models.CharField(max_length=128)
    m2f2 = models.CharField(max_length=128)
    m2f3 = models.CharField(max_length=128)


sheet_to_model = {
    'Sheet1':{
        'model': Model1, 
        'columns': ['s1c1', 's1c2', 's1c3'],
        'column_map': {
            's1c1': 'm1f1',
            's1c2': 'm1f2',
            's1c3': 'm1f3',
        }
    }, 
    'Sheet2':{
        'model': Model2, 
        'columns': ['s2c1', 's2c2', 's2c3'],
        'column_map': {
            's2c1': 'm2f1',
            's2c2': 'm2f2',
            's2c3': 'm2f3',
        }
    }

}

wb = openpyxl.load_workbook('./datas.xlsx')

print(wb.sheetnames)

for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]
    for index, row in enumerate(sheet.iter_rows()):
        data = {}
        if index: # First row is columns name
            for idx2, col in enumerate(row):
                # print(col.value)
                p = sheet_to_model[sheet_name]['columns'][idx2]
                # print(p)
                data[sheet_to_model[sheet_name]['column_map'][p]] = col.value
            # print(data)
            sheet_to_model[sheet_name]['model'].objects.create(**data)

您还可以学习和使用Pandas更轻松地处理此类情况。