如何在C#（.NET Framework）中从JSON生成DataTable并导出CSV

背景 #

我接手了一个基于Visual Studio的项目（使用.NET Framework 4.7.2），从API接口获取到包含嵌套数组的JSON响应。需要从中选取指定键值对生成DataTable，并将嵌套数组（如order_line_list）扁平化为多行数据，最终生成CSV文件发送至其他服务器。目前尝试的代码无法成功创建并验证DataTable，仅能遍历JSON获取单个键值对。

JSON响应示例 #

[
    {
        "transaction_id": "00000352",
        "transaction_type": "New",
        "transaction_date": "2018-08-23T00:00:00",
        "sold_to_id": "00026",
        "customer_po_number": "34567",
        "po_date": "2018-08-23T00:00:00",
        "notes": null,
        "batch_code": "######",
        "location_id": "MN",
        "req_ship_date": "2018-08-28T00:00:00",
        "fiscal_period": 8,
        "fiscal_year": 2018,
        "currency_id": "USD",
        "original_invoice_number": null,
        "bill_to_id": "00026",
        "customer_level": null,
        "terms_code": "30-2",
        "distribution_code": "01",
        "invoice_number": null,
        "invoice_date": "2018-08-23T00:00:00",
        "sales_rep_id_1": null,
        "sales_rep_id_1_percent": 100,
        "sales_rep_id_1_rate": 0,
        "sales_rep_id_2_id": "SA",
        "sales_rep_id_2_percent": 100,
        "sales_rep_id_2_rate": 10,
        "ship_to_id": null,
        "ship_via": null,
        "ship_method": null,
        "ship_number": null,
        "ship_to_name": "",
        "ship_to_attention": null,
        "ship_to_address_1": null,
        "ship_to_address_2": null,
        "ship_to_city": null,
        "ship_to_region": null,
        "ship_to_country": "USA",
        "ship_to_postal_code": null,
        "actual_ship_date": null,
        "order_line_list": [
            {
                "entry_number": 1,
                "item_id": "5' Glass/Wood Table",
                "description": "Glass/ Wood Combo Coffee Table",
                "customer_part_no": null,
                "additional_description": null,
                "location_id": "MN",
                "quantity_ordered": 11,
                "units": "EA",
                "quantity_shipped": 0,
                "quantity_backordered": 0,
                "req_ship_date": null,
                "unit_price": 599.99,
                "extended_price": 6599.89,
                "reason_code": null,
                "account_code": null,
                "inventory_gl_account": "120000",
                "sales_gl_account": "400000",
                "cogs_gl_account": "500000",
                "sales_category": null,
                "tax_class": 0,
                "promo_id": null,
                "price_id": null,
                "discount_type": 0,
                "discount_percentage": 0,
                "discount_amount": 0,
                "sales_rep_id_1": null,
                "sales_rep1_percent": null,
                "sales_rep1_rate": null,
                "sales_rep_id_2": "SA",
                "sales_rep2_percent": 100,
                "sales_rep2_rate": 5,
                "unit_cost": 241.4467,
                "extended_cost": 2655.91,
                "status": "Open",
                "extended_list": [],
                "serial_list": []
            }
        ],
        "tax_group_id": "ATL",
        "taxable": false,
        "tax_class_adjustment": 0,
        "freight": 0,
        "tax_class_freight": 0,
        "tax_location_adjustment": null,
        "misc": 0,
        "tax_class_misc": 0,
        "sales_tax": 0,
        "taxable_sales": 0,
        "non_taxable_sales": 6599.89,
        "payment_list": []
    }
]

尝试过的代码 #

创建DataTable的代码 #

var doc = JsonDocument.Parse(response.Content);
JsonElement root = doc.RootElement;

Console.WriteLine(String.Format("Root Elem: {0}", doc.RootElement));

// 创建DataTable
var jsonObject = JObject.Parse(response.Content);
Console.WriteLine(String.Format("jsonObject col: {0}", jsonObject));
DataTable jsdt = jsonObject[doc.RootElement].ToObject<DataTable>();

int totalRows = jsdt.Rows.Count;

Console.WriteLine(String.Format("jsonObject col: {0}", jsonObject));
Console.WriteLine(String.Format("totalRows col: {0}", totalRows));
Console.WriteLine(String.Format("jsdt col: {0}", jsdt.Rows.Count));

遍历JSON的代码 #

var users = root.EnumerateArray();

while (users.MoveNext())
{
    var user = users.Current;

    var props = user.EnumerateObject();

    while (props.MoveNext())
    {
        var prop = props.Current;
        if (prop.Name == "transaction_date") {
            // Console.WriteLine(String.Format("key: {0}", $"{prop.Name}: {prop.Value}"));
        }
    }
}

解决方案 #

1. 定义实体类（强类型处理） #

对应JSON结构定义实体类，方便反序列化和数据操作：

using System.Collections.Generic;

public class Transaction
{
    public string transaction_id { get; set; }
    public string transaction_type { get; set; }
    public string transaction_date { get; set; }
    public string sold_to_id { get; set; }
    public string customer_po_number { get; set; }
    // 按需添加其他需要的主表字段
    public List<OrderLine> order_line_list { get; set; }
}

public class OrderLine
{
    public int entry_number { get; set; }
    public string item_id { get; set; }
    public string description { get; set; }
    public decimal quantity_ordered { get; set; }
    public decimal unit_price { get; set; }
    public decimal extended_price { get; set; }
    // 按需添加其他需要的子表字段
}

2. 反序列化JSON到实体集合 #

使用Newtonsoft.Json（需通过NuGet安装Newtonsoft.Json包）进行反序列化：

using Newtonsoft.Json;

// 反序列化JSON数组到Transaction集合
var transactions = JsonConvert.DeserializeObject<List<Transaction>>(response.Content);

3. 扁平化数据生成DataTable #

遍历每个交易及其订单行，将主表字段与子表字段合并为一行，添加到DataTable中：

using System.Data;

// 创建DataTable并定义列
DataTable flatTable = new DataTable();
// 添加主表列
flatTable.Columns.Add("transaction_id", typeof(string));
flatTable.Columns.Add("transaction_type", typeof(string));
flatTable.Columns.Add("transaction_date", typeof(string));
// 按需添加其他主表列
// 添加子表列
flatTable.Columns.Add("entry_number", typeof(int));
flatTable.Columns.Add("item_id", typeof(string));
flatTable.Columns.Add("description", typeof(string));
flatTable.Columns.Add("quantity_ordered", typeof(decimal));
// 按需添加其他子表列

// 填充数据
foreach (var trans in transactions)
{
    // 如果订单行为空，也可以生成一行（根据需求调整）
    if (trans.order_line_list == null || trans.order_line_list.Count == 0)
    {
        DataRow row = flatTable.NewRow();
        row["transaction_id"] = trans.transaction_id;
        row["transaction_type"] = trans.transaction_type;
        // 其他主表字段赋值
        flatTable.Rows.Add(row);
        continue;
    }

    foreach (var line in trans.order_line_list)
    {
        DataRow row = flatTable.NewRow();
        // 赋值主表字段
        row["transaction_id"] = trans.transaction_id;
        row["transaction_type"] = trans.transaction_type;
        row["transaction_date"] = trans.transaction_date;
        // 其他主表字段
        // 赋值子表字段
        row["entry_number"] = line.entry_number;
        row["item_id"] = line.item_id;
        row["description"] = line.description;
        row["quantity_ordered"] = line.quantity_ordered;
        // 其他子表字段
        flatTable.Rows.Add(row);
    }
}

4. 将DataTable导出为CSV文件 #

实现CSV导出逻辑，处理字段中的逗号、引号等特殊字符：

using System.IO;

public void DataTableToCsv(DataTable table, string filePath)
{
    using (StreamWriter writer = new StreamWriter(filePath))
    {
        // 写入表头
        string header = string.Join(",", table.Columns.Cast<DataColumn>().Select(col => EscapeCsvValue(col.ColumnName)));
        writer.WriteLine(header);

        // 写入行数据
        foreach (DataRow row in table.Rows)
        {
            string line = string.Join(",", row.ItemArray.Select(item => EscapeCsvValue(item?.ToString() ?? "")));
            writer.WriteLine(line);
        }
    }
}

private string EscapeCsvValue(string value)
{
    // 如果包含逗号、引号或换行，需要用引号包裹，内部引号转义
    if (value.Contains(",") || value.Contains("\"") || value.Contains("\n") || value.Contains("\r"))
    {
        return $"\"{value.Replace("\"", "\"\"")}\"";
    }
    return value;
}

// 调用示例
DataTableToCsv(flatTable, @"C:\temp\output.csv");

5. 发送CSV文件到其他服务器 #

使用HttpClient发送文件：

using System.Net.Http;
using System.Threading.Tasks;

public async Task SendCsvToServer(string csvFilePath, string apiUrl)
{
    using (HttpClient client = new HttpClient())
    using (MultipartFormDataContent form = new MultipartFormDataContent())
    using (FileStream fs = File.OpenRead(csvFilePath))
    {
        form.Add(new StreamContent(fs), "file", Path.GetFileName(csvFilePath));
        HttpResponseMessage response = await client.PostAsync(apiUrl, form);
        response.EnsureSuccessStatusCode();
    }
}

// 调用示例（注意异步方法调用）
// await SendCsvToServer(@"C:\temp\output.csv", "https://target-server/api/upload");

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