如何让Django的views.py读取Excel时显示空白单元格而非‘nan’

我正在开发一个基于Django和Plotly的项目，读取Excel表格数据并展示。所有功能运行正常，但子调查（Child Survey）列仅部分父调查有填充值，未填充的单元格显示nan而非空白。

现有代码 #

views.py #

def survey_status_view(request):
    # Fetch data for all CLINs and calculate necessary values
    clin_data = []

    # Get all unique combinations of CLIN and child survey
    unique_clins = SurveyData.objects.values('clin', 'survey_child').distinct()

    for clin in unique_clins:
        clin_id = clin['clin']
        child_survey = clin['survey_child']

        # Check if the child_survey is 'None' (the string), and handle it appropriately
        if child_survey == 'None':
            child_survey = ''  # Replace 'None' with an empty string

        # Filter by both clin and child_survey
        survey_data = SurveyData.objects.filter(clin=clin_id, survey_child=child_survey)

        # Total units for the current CLIN and child survey
        total_units = survey_data.first().units if survey_data.exists() else 0

        # Count redeemed units based on non-null redemption dates
        redeemed_count = survey_data.filter(redemption_date__isnull=False).count()

        # Calculate funds for this CLIN and child survey
        total_funds = total_units * survey_data.first().denomination if total_units > 0 else 0
        spent_funds = redeemed_count * survey_data.first().denomination if redeemed_count > 0 else 0
        remaining_funds = total_funds - spent_funds

        # Append results for this CLIN and child survey
        clin_data.append({
            'clin': clin_id,
            'survey': survey_data.first().survey if survey_data.exists() else '',
            'child_survey': child_survey,  # Use the updated child_survey with empty string if it's 'None'
            'status': survey_data.first().status if survey_data.exists() else '',
            'launch_date': survey_data.first().launch_date if survey_data.exists() else '',
            'expiration_date': survey_data.first().expiration_date if survey_data.exists() else '',
            'total_units': total_units,
            'redeemed_count': redeemed_count,
            'spent_funds': spent_funds,
            'total_funds': total_funds,
            'remaining_funds': remaining_funds,
            'redemption_rate': (redeemed_count / total_units * 100) if total_units > 0 else 0,
        })

    # Log the results for debugging
    for data in clin_data:
        print(f"CLIN: {data['clin']}, Child Survey: {data['child_survey']}, Total Units: {data['total_units']}, "
              f"Redeemed Count: {data['redeemed_count']}, Spent Funds: {data['spent_funds']}, "
              f"Remaining Funds: {data['remaining_funds']}")

    # Render the data in the template
    return render(request, 'surveyStatus.html', {'clin_data': clin_data})

HTML模板（surveyStatus.html） #

{% load humanize %}
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Survey Status</title>
    <style>
        table {
            width: 100%;
            border-collapse: collapse;
        }
        table, th, td {
            border: 1px solid black;
        }
        th, td {
            padding: 10px;
            text-align: left;
        }
        th {
            background-color: #002060;
            color: white;
        }
        .active-row {
            background-color: #e2f3d7;
        }
        .inactive-row {
            background-color: #fde9d9;
        }
    </style>
</head>
<body>
    <h1>Survey Status</h1>
    <form method="GET" action="{% url 'virtual_delivery' %}">
        <table>
            <thead>
                <tr>
                    <th>SELECT</th>
                    <th>CLIN</th>
                    <th>Survey</th>
                    <th>Child Survey</th>
                    <th>Status</th>
                    <th>Launch Date</th>
                    <th>Expiration Date</th>
                    <th>Redemption Rate</th>
                    <th>Total Funds</th>
                    <th>Spent Funds</th>
                    <th>Remaining Funds</th>
                </tr>
            </thead>
            <tbody>
                {% for clin in clin_data %}
                <tr class="{% if clin.status == 'Active' %}active-row{% elif clin.status == 'Pre-Launch' or clin.status == 'Inactive' %}inactive-row{% endif %}">
                    <td><input type="checkbox" name="clin" value="{{ clin.clin }}"></td>
                    <td>{{ clin.clin }}</td>
                    <td>{{ clin.survey }}</td>
                    <td>{{ clin.child_survey }}</td>
                    <td>{{ clin.status }}</td>
                    <td>{{ clin.launch_date }}</td>
                    <td>{{ clin.expiration_date }}</td>
                    <td>{{ clin.redemption_rate|floatformat:1}}%</td>
                    <td>${{ clin.total_funds|floatformat:0|intcomma }}</td> <!-- Show total funds with thousand separator -->
                    <td>${{ clin.spent_funds|floatformat:0|intcomma }}</td>  <!-- Show spent funds with thousand separator -->
                    <td>${{ clin.remaining_funds|floatformat:0|intcomma }}</td>  <!-- Show remaining funds with thousand separator -->
                </tr>
                {% endfor %}
            </tbody>
        </table>
        <br>
        <button type="submit">View Charts</button>
    </form>
</body>
</html>

解决方案 #

问题出在仅处理了字符串'None'，但Excel导入的空单元格通常会被解析为float类型的NaN，或者存储为字符串'nan'，这些情况都没覆盖到。

修改views.py的空值处理逻辑 #

1. 先导入math模块（用于判断float类型的NaN）：

import math

2. 修改child_survey的判断逻辑，覆盖所有空值场景：

child_survey = clin['survey_child']

# 处理字符串'None'、'nan'、float类型NaN以及None对象
if (child_survey is None 
    or str(child_survey).lower() in ['none', 'nan'] 
    or (isinstance(child_survey, float) and math.isnan(child_survey))):
    child_survey = ''

模板层兜底处理（可选） #

如果担心后端仍有遗漏，可以在模板中使用default过滤器直接显示空白：

<td>{{ clin.child_survey|default:"" }}</td>

这样就能确保所有未填充的Child Survey单元格显示空白而非nan。

内容的提问来源于stack exchange，提问作者confusedcoder