Extract from Excel to to JSON

Question

I need to extract data from an Excel Spreadsheet and port it into a JSON file.  The code below extracts the rows I need.  But the format of the spreadsheet is such that type is actually a collection:

example Excel input 

Row   Name          Type

1        John Doe     First base 

2                            Second base

3                            Pitcher

4        Jack Smith  Third Base

 

 

 example output

{
   "types": [

      {"type": "First Base"},

      {"type":"Second Base"},

      {"type":"Pitcher"}
   ],
   "name": {
      "name": "John Doe"
   }
}

 

This code will extract the rows but because the types are on separate rows, how do I roill all types of a player into one JSON dcoument?

 

static void AlternateRead()
{
var pathToExcel = @"C:\temp\Players.xlsx";
var sheetName = "Sheet1";
var destinationPath = @"C:\Temp\Players.json";

var connectionString = $@"
Provider=Microsoft.ACE.OLEDB.12.0;
Data Source={pathToExcel};
Extended Properties=""Excel 12.0 Xml;HDR=YES""
";
try
{
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();

var cmd = conn.CreateCommand();
cmd.CommandText = $"SELECT * FROM [{sheetName}$] WHERE PlayerID IS NOT NULL OR Positiontype IS NOT NULL";

using (var rdr = cmd.ExecuteReader())
{

//LINQ query - when executed will create anonymous objects for each row
var query = rdr.Cast<DbDataRecord>().Select(row => new
{
id = Guid.NewGuid(),
PlayerName = row[3].ToString().Trim(),
Type = row[4].ToString().Trim()   //// <== there are 1 to many types per player
});

var json = JsonConvert.SerializeObject(query);

File.WriteAllText(destinationPath, json);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error {0}", ex.Message);
Console.ReadLine();

}
}