使用ChoETL库在C#中将含嵌套对象的JSON数组转Excel

Got it, let's walk through how to convert your JSON array—complete with nested objects and empty arrays like the sensors field—to Excel using ChoETL. This solution handles all the edge cases you mentioned, including null values and empty arrays.

Step 1: Install the Required NuGet Package #

First, you'll need the ChoETL.Excel package (it includes the core ChoETL library too). You can install it via the NuGet Package Manager or the Package Manager Console:

Install-Package ChoETL.Excel

Step 2: Full Code Example #

Here's a complete working example that reads your JSON file, processes nested properties, handles empty arrays, and writes the output to Excel:

using ChoETL;
using System.IO;

class JsonToExcelConverter
{
    static void Main(string[] args)
    {
        // Update these paths to match your input JSON and desired output Excel file
        string inputJsonPath = "devices.json";
        string outputExcelPath = "devices_output.xlsx";

        // Read the JSON and write to Excel
        using (var jsonReader = new ChoJSONReader(inputJsonPath))
        {
            using (var excelWriter = new ChoExcelWriter(outputExcelPath)
                .WithSheetName("DeviceData") // Name your Excel sheet
                // Define each column you want to export, including nested properties
                .WithField("Device ID", jsonPath: "$.id")
                .WithField("Device UUID", jsonPath: "$.uuid")
                .WithField("Device Name", jsonPath: "$.name")
                .WithField("Device State", jsonPath: "$.state")
                // Convert array fields to semi-colon separated strings for readability
                .WithField("System Tags", jsonPath: "$.system_tags", 
                    valueConverter: obj => obj is string[] tags ? string.Join(";", tags) : string.Empty)
                .WithField("User Tags", jsonPath: "$.user_tags", 
                    valueConverter: obj => obj is string[] tags ? string.Join(";", tags) : string.Empty)
                // Access nested owner properties
                .WithField("Owner ID", jsonPath: "$.owner.id")
                .WithField("Owner Username", jsonPath: "$.owner.username")
                .WithField("Owner Location City", jsonPath: "$.owner.location.city")
                // Access nested data/location properties
                .WithField("Device Location City", jsonPath: "$.data.location.city")
                .WithField("Device Country", jsonPath: "$.data.location.country")
                // Handle empty sensors array by showing its length
                .WithField("Sensors Count", jsonPath: "$.data.sensors", 
                    valueConverter: obj => obj is object[] sensors ? sensors.Length.ToString() : "0")
            )
            {
                excelWriter.Write(jsonReader);
            }
        }
    }
}

Key Details & Edge Case Handling #

• Nested Properties: Use the jsonPath parameter to target nested fields (e.g., $.owner.id pulls the ID from the nested owner object).
• Empty Arrays: The valueConverter lambda checks if the field is an array. For empty arrays like sensors, it returns "0" (or you could return an empty string if preferred).
• Null Values: ChoETL automatically converts null values (like description or last_reading_at) to empty cells in Excel—no extra code needed.
• Custom Array Formatting: Instead of counting array items, you can join array elements into a single string (like we did for system_tags and user_tags) to make the data more readable in Excel.

If your JSON is in a string variable instead of a file, just replace new ChoJSONReader(inputJsonPath) with new ChoJSONReader(yourJsonString).

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