c#通过Linq表达式将excel数据转换成json

1.首先读取本地excel文件,比如我的数据格式为

using (var reader = new StreamReader(@"d:\data.csv"))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
    var records = csv.GetRecords<model>().ToList();
    records.ForEach(p =>
    {
        if (string.IsNullOrWhiteSpace(p.image))
        {
            p.image = "connection.png";
        }
    }
 );

2. 通过Linq表达式拼接相应得json格式

var results = records.ToLookup(p => (string)p.shop_code, StringComparer.OrdinalIgnoreCase)
          .Select(g =>
              new
              {
                  topic = $"glkf:bu1:{g.Key.ToLower()}:devices",
                  ttl_sec = 0,
                  data = new
                  {
                      shop_code = g.Key.ToLower(),
                      shop_name = g.Key,
                      lines = g.ToLookup(q => (string)q.line_code, StringComparer.OrdinalIgnoreCase)
                 .Select(g1 =>
                 new
                 {
                     line_code = g1.Key.ToLower(),
                     line_name = g1.Key,
                     devices = g1.Where(ppp=>!string.IsNullOrWhiteSpace(ppp.image)).Select(p1 =>
                     new
                     {
                         dev_code = p1.dev_code.ToLower(),
                         dev_name = p1.dev_code,
                         dev_type = p1.dev_type.ToLower(),
                         dev_seq = int.Parse(p1.dev_seq.ToLower()),
                         is_iot = p1.is_iot,
                         
                     }).OrderBy(p2 => p2.dev_seq)
                 })

                  }
              }

          );

3. 通过序列化转为json格式

    var jsonOptions = new JsonSerializerOptions()
    {
        Encoder = JavaScriptEncoder.UnsafeRelaxedJsonEscaping,
        AllowTrailingCommas = true,
        IgnoreNullValues = true,
        WriteIndented = true
    };

    var json = JsonSerializer.Serialize(results, jsonOptions);

4. 结果为

 {
    "topic": "glkf:bu1:smt:devices",
    "ttl_sec": 0,
    "descrption":"smt设备信息",
    "data": {
      "shop_code": "bu4-smt",
      "shop_name": "bu4-smt",
      "lines": [
        {
          "line_code": "bu4smt06",
          "line_name": "bu4smt06",
          "devices": [
            {
              "dev_code": "hs0601040470",
              "dev_name": "印刷机",
              "dev_type": "smt_syj_01",
              "dev_seq": 10,
              "is_iot": true
            }
        }
}