When working with modern APIs and web services, developers frequently encounter complex JSON structures with deeply nested arrays and objects. Converting these hierarchical data into flat Excel tables presents a significant challenge. This guide demonstrates how to effectively convert nested JSON arrays to Excel using Aspose.Cells for .NET.

Introduction

When working with modern APIs and web services, developers frequently encounter complex JSON structures with deeply nested arrays and objects. Converting these hierarchical data into flat Excel tables presents a significant challenge. This guide demonstrates how to effectively convert nested JSON arrays to Excel using Aspose.Cells for .NET.

The Challenge: Complex Nested JSON Structures

Consider this typical JSON response from a web API:

Converting this hierarchical data to a flat Excel table creates several challenges:

  • How to handle multiple nested arrays (departments, employees, skills)
  • How to maintain relationships between parent and child items
  • How to create a readable spreadsheet structure

Step-by-Step Solution

Step 1: Install Aspose.Cells

First, install Aspose.Cells for .NET:

dotnet add package Aspose.Cells

Step 2: Set Up JsonLayoutOptions

Create properly configured JsonLayoutOptions to handle arrays:

using Aspose.Cells;
using Aspose.Cells.Utility;
// Create JsonLayoutOptions with array handling
JsonLayoutOptions options = new JsonLayoutOptions();
options.ArrayAsTable = true;  // Crucial for proper flattening
options.ConvertNumericOrDate = true;
options.IgnoreNull = true;

Step 3: Load Complex JSON Data

Load your complex nested JSON data:

// Sample JSON with nested arrays
string jsonData = File.ReadAllText("complex_data.json");
// Initialize workbook and worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

Step 4: Configure Advanced Flattening

For advanced nested structures, implement a custom flattening solution:

// Define starting cell position
int startRow = 0;
int startColumn = 0;
// Import the JSON data with our configured options
JsonUtility.ImportData(jsonData, worksheet.Cells, startRow, startColumn, options);

Step 5: Handle Multi-level Nested Arrays

For complex multi-level arrays, we need additional processing:

// Create a second worksheet for detailed employee data
Worksheet employeeSheet = workbook.Worksheets.Add("Employees");
int empRow = 0;
// Add headers for the employee sheet
string[] headers = { "Department", "Employee ID", "Employee Name", "Skills" };
for (int i = 0; i < headers.Length; i++)
{
    employeeSheet.Cells[empRow, i].PutValue(headers[i]);
}
empRow++;
// Parse JSON to extract and flatten employee data
// Note: This would require a JSON parsing library like Newtonsoft.Json
// JObject root = JObject.Parse(jsonData);
// foreach (var dept in root["departments"])
// {
//     string deptName = dept["name"].ToString();
//     foreach (var emp in dept["employees"])
//     {
//         employeeSheet.Cells[empRow, 0].PutValue(deptName);
//         employeeSheet.Cells[empRow, 1].PutValue((int)emp["id"]);
//         employeeSheet.Cells[empRow, 2].PutValue(emp["name"].ToString());
//         employeeSheet.Cells[empRow, 3].PutValue(string.Join(", ", emp["skills"].ToObject<string[]>()));
//         empRow++;
//     }
// }

Step 6: Apply Professional Formatting

Enhance readability with proper formatting:

// Format both worksheets as tables with headers
worksheet.ListObjects.Add(0, 0, worksheet.Cells.LastCell.Row, worksheet.Cells.LastCell.Column, true);
employeeSheet.ListObjects.Add(0, 0, empRow - 1, 3, true);
// Auto-fit columns for better readability
worksheet.AutoFitColumns();
employeeSheet.AutoFitColumns();

Step 7: Save the Result

Export the workbook with flattened data:

// Save as Excel file
workbook.Save("flattened_data.xlsx");

A Simplified Solution Using Aspose.Cells

For many scenarios, Aspose.Cells provides a simpler approach using its built-in JSON handling:

// Initialize workbook
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Configure JSON import options
JsonLayoutOptions options = new JsonLayoutOptions
{
    ArrayAsTable = true,
    ConvertNumericOrDate = true,
    IgnoreNull = true,
    TitleStyle = new CellsFactory().CreateStyle(),
    NestedArrayAsTable = true  // Important for nested arrays
};
// Set title style for better readability
options.TitleStyle.Font.IsBold = true;
// Import JSON
JsonUtility.ImportData(jsonData, sheet.Cells, 0, 0, options);
// Save result
workbook.Save("flattened_output.xlsx");

Key Insights and Best Practices

  1. Use ArrayAsTable = true - This is essential for proper representation of arrays
  2. Consider creating multiple worksheets for complex hierarchical data
  3. Apply formatting to make the output more readable
  4. Use NestedArrayAsTable = true when available for better handling of nested structures
  5. For extremely complex structures, consider pre-processing the JSON before import

Common Issues and Solutions

IssueSolution
Nested arrays appear as single cellsEnable ArrayAsTable and NestedArrayAsTable options
Data relationships are lostCreate multiple tables/sheets with relationship columns
Column names are incorrectUse the DateTimeGroupSeparator option to customize naming
Memory issues with large filesProcess the JSON in chunks or use streaming approaches

More in this category