如何确保数据库NULL值导出到Excel时保留为空白/NULL而非0

我尝试将存储过程的结果导出至Excel表格。数据库里有有效的0值（比如温度、使用时长），同时部分无数据的列值为NULL。但导出后，数据库中的NULL和合法0值都变成了0。我需要确保数据库中的NULL在Excel里保留为空白/NULL，合法0值仍保持为0。以下是我当前的C#代码，请问要添加什么逻辑实现这个需求？

using System.Globalization;
using ClosedXML.Excel;
using CsvHelper;
using Microsoft.AspNetCore.Mvc;
using System.Reflection;
using Api.Utilities.Services.Abstractions;
    

namespace Api.Utilities.Services;

/// <summary>
/// Provides functionality to export a collection of data into different formats.
/// </summary>
public class DataExportService : IDataExportService
{
   /// <summary>
   /// Exports a collection of data to an Excel file and returns the result as a file stream.
   /// </summary>
   /// <typeparam name="T">The type of the data to be exported.</typeparam>
   /// <param name="data">The list of data items to be exported.</param>
   /// <param name="fileName">The name of the file to be exported, optional parameter. 
   /// </param>
   /// <returns>
   /// A <see cref="FileStreamResult"/> containing the Excel file.
   /// </returns>

   public FileStreamResult ExportToExcel<T>(IEnumerable<T> data, string? fileName)
   {
        using var workbook = new XLWorkbook();
    
        var worksheet = workbook.Worksheets.Add("Data");
        var properties = typeof(T).GetProperties();
        var currentRow = 1;

        for (var i = 0; i < properties.Length; i++)
        {
            var customNameAttribute = properties[i].GetCustomAttribute<CustomNameAttribute>();
            var columnName = customNameAttribute != null ? customNameAttribute.Name : properties[i].Name;
         
            worksheet.Cell(1, i + 1).Value = columnName;
        }

        currentRow++;

        foreach (var item in data)
        {
            foreach (var nestedRow in GetNestedRows(item))
            {
                for (var j = 0; j < nestedRow.Count; j++)
                {
                    var value = nestedRow[j];
                                    
                    worksheet.Cell(currentRow, j + 1).Value = value switch
                    {
                         null => string.Empty,
                         string str => str,
                         int intVal => intVal,
                         double dblVal => dblVal,
                         DateTime dtVal => dtVal,
                         bool boolVal => boolVal,
                         _ => value.ToString()
                    };
                }
            
                currentRow++;
            }
        }

        worksheet.Columns().AdjustToContents();

        MemoryStream stream = new();
        workbook.SaveAs(stream);
        stream.Position = 0;

        return new FileStreamResult(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
               {
                   FileDownloadName = string.IsNullOrEmpty(fileName) ? "export.xlsx" : fileName
               };
    }

    /// <typeparam name="TData">The type of the data to be exported.</typeparam>
    /// <param name="data">The list of data items to be exported.</param>
    /// <returns>
    /// A <see cref="MemoryStream"/> containing the CSV file.
    /// </returns>
    public MemoryStream ExportToCsv<TData>(IEnumerable<TData> data)
    {
        using var writer = new StringWriter();
        using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);

        var properties = typeof(TData).GetProperties();

        foreach (var prop in properties)
        {
            csv.WriteField(prop.Name);
        }

        csv.NextRecord();

        foreach (var item in data)
        {
            foreach (var nestedRow in GetNestedRows(item))
            {
                foreach (var value in nestedRow)
                {
                    csv.WriteField(value ?? string.Empty);
                }

                csv.NextRecord();
            }
        }

        MemoryStream stream = new();
        StreamWriter streamWriter = new(stream);

        streamWriter.Write(writer.ToString());
        streamWriter.Flush();
        stream.Position = 0;

        return stream;
    }

    private List<List<object?>> GetNestedRows<T>(T item)
    {
        var result = new List<List<object?>>();
        var properties = typeof(T).GetProperties();

        var parentRow = new List<object?>();
        var nestedCollections = new List<(PropertyInfo, IEnumerable<object>)>();

        foreach (var prop in properties)
        {
            var value = prop.GetValue(item);
          
            if (value is IEnumerable<object> collection && !(value is string))
            {
                nestedCollections.Add((prop, collection));
            }
            else
            {
                parentRow.Add(value);
            }
        }

        if (nestedCollections.Count == 0)
        {
            result.Add(parentRow);
        }
        else
        {
            foreach (var (property, collection) in nestedCollections)
            {
                foreach (var nestedItem in collection)
                {
                    var newRow = new List<object?>(parentRow);
                    newRow.AddRange(property.PropertyType.GetProperties().Select(nestedProp => nestedProp.GetValue(nestedItem)));
                
                    result.Add(newRow);
                }
            }
        }

        return result;
    }
}

问题出在你处理null值的逻辑上：当前代码把null转成了空字符串string.Empty，但ClosedXML结合Excel的数值列格式会把空字符串解析成0。另外，代码没处理可空值类型（比如int?、double?）的null情况，导致这类值的null也被错误转换。

你需要修改ExportToExcel方法里的赋值逻辑，调整两点：

1. 原始值为null时，直接把单元格值设为null，不要用空字符串
2. 增加对可空值类型的判断，确保它们的null状态被正确识别

修改后的赋值代码片段：

worksheet.Cell(currentRow, j + 1).Value = value switch
{
    null => null, // 直接设为null，Excel会显示空白
    string str => str,
    int intVal => intVal,
    int? nullableInt => nullableInt.HasValue ? nullableInt.Value : (object)null, // 处理可空int
    double dblVal => dblVal,
    double? nullableDbl => nullableDbl.HasValue ? nullableDbl.Value : (object)null, // 处理可空double
    DateTime dtVal => dtVal,
    DateTime? nullableDt => nullableDt.HasValue ? nullableDt.Value : (object)null, // 处理可空DateTime
    bool boolVal => boolVal,
    bool? nullableBool => nullableBool.HasValue ? nullableBool.Value : (object)null, // 处理可空bool
    _ => value.ToString()
};

如果想进一步确保Excel不会自动把空白显示成0，还可以给数值类型的单元格设置格式：

// 在赋值代码后添加格式设置逻辑
var cell = worksheet.Cell(currentRow, j + 1);
cell.Value = value switch { /* 上面的switch逻辑 */ };

// 判断当前列对应的属性类型，给数值列设置通用格式
var propType = properties[j].PropertyType;
var underlyingType = Nullable.GetUnderlyingType(propType) ?? propType;
if (underlyingType == typeof(int) || underlyingType == typeof(double))
{
    cell.Style.NumberFormat.Format = "General";
}

说明 #

• 直接把null赋值给单元格Value，ClosedXML会在Excel里生成空白单元格，不会显示0
• 显式处理可空值类型是为了避免类型匹配问题，比如int?的null不会被int intVal分支捕获，显式处理更稳妥
• 设置单元格格式是额外保障，防止Excel自动把空白单元格转换成0显示

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