如何将JsonResult转换为ClosedXML格式？JSON格式数据集写入ClosedXML的实现方案

1. General Approach: Convert JsonResult to ClosedXML #

Turning a JsonResult into an Excel workbook with ClosedXML follows three core steps:

• Extract the raw data from JsonResult.Data
• Deserialize the JSON data into a .NET-compatible structure (either a strongly-typed class or DataTable)
• Use ClosedXML's API to populate a worksheet with the deserialized data

2. Handling Your Specific Nested JSON Dataset #

Your JSON has a nested structure with a total count and a data array of models containing fields. To work with this safely, first define strongly-typed classes that match your JSON schema—this avoids messy dynamic parsing and improves maintainability:

// Match the nested structure of your JSON
public class FieldMetadata
{
    public string Title { get; set; }
    // Add other field properties (like value, type) based on your actual JSON
}

public class DocumentModel
{
    public Dictionary<string, FieldMetadata> Fields { get; set; }
}

public class DataSetResponse
{
    public string Total { get; set; }
    public List<DocumentModel> Data { get; set; }
}

If your fields have fixed, known properties (instead of dynamic keys), replace the Dictionary<string, FieldMetadata> with a concrete class (e.g., DocumentFields with DocumentId, Title, etc. properties).

3. Corrected Implementation Code #

Let's fix your SetWorkbook method to handle both your specific dataset and general use cases:

Option 1: For Your Specific Nested Dataset #

private void SetWorkbook(IXLWorkbook workbook, JsonResult dataSet) 
{
    var worksheet = workbook.Worksheets.Add("Export");
    
    // Deserialize JsonResult data to our strongly-typed response class
    var jsonString = JsonConvert.SerializeObject(dataSet.Data);
    var response = JsonConvert.DeserializeObject<DataSetResponse>(jsonString);
    
    // Optional: Write total record count at the top
    worksheet.Cell(1, 1).Value = "Total Records";
    worksheet.Cell(1, 2).Value = response.Total;
    worksheet.Row(1).Style.Font.Bold = true;
    
    // Exit early if there's no data to export
    if (!response.Data.Any())
    {
        worksheet.Cell(3,1).Value = "No data available to export.";
        return;
    }
    
    // Write headers using the first document's field titles
    int headerColumn = 1;
    foreach (var field in response.Data.First().Fields)
    {
        worksheet.Cell(3, headerColumn).Value = field.Value.Title;
        headerColumn++;
    }
    
    // Populate rows with data from each document
    int rowNumber = 4;
    foreach (var doc in response.Data)
    {
        int columnNumber = 1;
        foreach (var field in doc.Fields)
        {
            // Adjust this line to pull the actual value from your field object
            worksheet.Cell(rowNumber, columnNumber).Value = field.Value?.ToString();
            columnNumber++;
        }
        rowNumber++;
    }
    
    // Auto-fit columns for better readability
    worksheet.Columns().AdjustToContents();
}

Option 2: General Case (Flat JSON Array to DataTable) #

If your JsonResult contains a flat array (no nested structures), your original approach can work with safeguards:

private void SetWorkbook(IXLWorkbook workbook, JsonResult dataSet) 
{
    var worksheet = workbook.Worksheets.Add("Export");
    
    try
    {
        // Serialize and deserialize to DataTable (works for flat JSON arrays)
        string json = JsonConvert.SerializeObject(dataSet.Data);
        DataTable dataTable = JsonConvert.DeserializeObject<DataTable>(json);
        
        // Insert the DataTable directly into the worksheet
        worksheet.Cell(1, 1).InsertTable(dataTable);
        
        // Auto-fit columns
        worksheet.Columns().AdjustToContents();
    }
    catch (Exception ex)
    {
        // Handle deserialization errors (e.g., incompatible JSON structure)
        worksheet.Cell(1,1).Value = $"Error converting data: {ex.Message}";
    }
}

Key Takeaways #

• Strongly-Typed Classes: Prefer these over DataTable for nested JSON—they're safer, easier to debug, and maintainable.
• ClosedXML Best Practices: Use InsertTable for flat data, manually build rows/columns for nested structures, and always adjust column widths with AdjustToContents().
• Error Handling: Add try-catch blocks to handle cases where the JSON structure doesn't match your expected model.

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