c# - 来自 Excel 的 JSON 重复输入部分

Question

我有一个特定的 JSON 字符串，我需要为 rest 调用匹配它。我正在从 Excel 电子表格中提取数据。其中一个部分具有如下所示的重复输入。我的电子表格中的数据如下所示:

我需要生成的 JSON 如下所示:

"detailInputs": [
  {
    "name": "SOGrid",
    "repeatingInputs": [
        {
            "inputs": [
                {
                    "name": "ItemNumber",
                    "value": "XYZ"
                },
                {
                    "name": "Quantity",
                    "value": "1"
                }
            ]
        },
        {
            "inputs": [
                {
                    "name": "ItemNumber",
                    "value": "ABC"
                },
                {
                    "name": "Quantity",
                    "value": "3"
                }
            ]
        }
    ]

到目前为止我尝试过的如下(注意 jsonArraystring 是在上一节中格式化的 header 信息):

using (var conn = new OleDbConnection(connectionString))
{
    sheetName = "Detail";
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT * FROM [{sheetName}$]";

    using (var rdr = cmd.ExecuteReader())
    {
        var query = rdr.Cast<DbDataRecord>().Select(row => new {
            name = row[0],
            value = row[1],
            //description = row[2]
        });

        var json = JsonConvert.SerializeObject(query);
        jsonArrayString = jsonArrayString + ",\"detailInputs\":[{\"name\":\"SOGrid\",\"repeatingInputs\":[{\"inputs\": " + json + "}]}]}";

这非常接近，但是将“重复输入”全部放在一个输入部分中。

我还尝试将值分配给字典和列表，希望从中提取适当的对并格式化 JSON，这只是开始，但我不太熟悉解开键值对以获得格式正确。

using (var conn = new OleDbConnection(connectionString))
{
    sheetName = "Detail";
    conn.Open();
    int counter = 0;
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT * FROM [{sheetName}$]";
    var values = new List<Dictionary<string, object>>();
    var ListValues = new List<string>();

    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            var fieldValues = new Dictionary<string, object>();
            var fieldValuesList = new List<string>();

            for (int i = 0; i < rdr.FieldCount; i++)
            {
                fieldValues.Add(rdr.GetName(i), rdr[i]);
                fieldValuesList.Add(rdr.GetName(i));
            }

            // add the dictionary on the values list
            values.Add(fieldValues);
        }

根本问题是如何通过提取 excel 数据来创建 JSON 示例中所示的重复输入结构。

Answer 1

您要做的是使用特定结构将 Excel 工作表的内容序列化为 "repeatingInputs" 属性的数组值。我建议将其分解为一系列 LINQ 转换。

首先介绍几个扩展方法:

public static class DataReaderExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/1202973
    // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
    // By https://stackoverflow.com/users/3043/joel-coehoorn
    public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
    {
        while (reader.Read())
        {
            yield return select(reader);
        }
    }
}

public static class EnumerableExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/419058
    // To https://stackoverflow.com/questions/419019/split-list-into-sublists-with-linq/
    // By https://stackoverflow.com/users/50776/casperone
    public static IEnumerable<List<T>> ChunkWhile<T>(this IEnumerable<T> enumerable, Func<List<T>, T, bool> shouldAdd)
    {
        if (enumerable == null || shouldAdd == null)
            throw new ArgumentNullException();
        return enumerable.ChunkWhileIterator(shouldAdd);
    }

    static IEnumerable<List<T>> ChunkWhileIterator<T>(this IEnumerable<T> enumerable, Func<List<T>, T, bool> shouldAdd)
    {
        List<T> list = new List<T>();
        foreach (var item in enumerable)
        {
            if (list.Count > 0 && !shouldAdd(list, item))
            {
                yield return list;
                list = new List<T>();
            }
            list.Add(item);
        }
        if (list.Count != 0)
        {
            yield return list;
        }
    }
}

第一个方法将一个IDataReader 打包成一个可枚举类型的对象，每行一个。这样做可以更轻松地将数据读取器的内容提供给后续的 LINQ 转换。第二种方法根据某些谓词条件将平面可枚举分解为列表“ block ”的可枚举。这将用于在每个 ItemNumber 行将行分成 block 。

使用这两种扩展方法，我们可以生成所需的 JSON，如下所示:

public static string ExtractRows(string connectionString, string sheetName)
{
    using (var conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);
            using (var rdr = cmd.ExecuteReader())
            {
                var query = rdr
                    // Wrap the IDataReader in a LINQ enumerator returning an array of key/value pairs for each row.
                    // Project the first two columns into a single anonymous object.
                    .SelectRows(r =>
                    {
                        // Check we have two columns in the row, and the first (Name) column value is non-null.
                        // You might instead check that we have at least two columns.
                        if (r.FieldCount != 2 || r.IsDBNull(0))
                            throw new InvalidDataException();
                        return new { Name = r[0].ToString(), Value = r[1] };
                    })
                    // Break the columns into chunks when the first name repeats
                    .ChunkWhile((l, r) => l[0].Name != r.Name)
                    // Wrap in the container Inputs object
                    .Select(r => new { Inputs = r });

                // Serialize in camel case
                var settings = new JsonSerializerSettings
                {
                    ContractResolver = new CamelCasePropertyNamesContractResolver(),
                };
                return JsonConvert.SerializeObject(query, Formatting.Indented, settings);
            }
        }
    }
}

这将为 "repeatingInputs" 生成所需的值:

[
  {
    "inputs": [
      {
        "name": "ItemNumber",
        "value": "XYZ"
      },
      {
        "name": "Quantity",
        "value": "1"
      }
    ]
  },
  {
    "inputs": [
      {
        "name": "ItemNumber",
        "value": "ABC"
      },
      {
        "name": "Quantity",
        "value": "3"
      }
    ]
  }
]