Excel Class Data to Json

Demo.cs

// A C# solution to the PowerShell Excel->JSON conversion described in this blog post:
// https://devblogs.microsoft.com/powershell-community/convert-specific-table-of-excel-sheet-to-json/
// Using the Sylvan data libaries.

using Sylvan.Data;
using Sylvan.Data.Excel;

if (args.Length != 3)
{
    Console.WriteLine("Args: file sheet \"class name\"");
    return -1;
}

var file = args[0];
var sheet = args[1];
var className = args[2];

var reader = ExcelDataReader.Create(file);

if (!reader.TryOpenWorksheet(sheet))
{
    Console.WriteLine($"Could not find subject '{sheet}' in the workbook.");
    return 1;
}

bool foundData = false;
while (reader.Read())
{
    for (int i = 0; i < reader.RowFieldCount; i++)
    {
        if (StringComparer.OrdinalIgnoreCase.Equals(reader.GetString(i), className))
        {
            // advance to the next line where the column headers are
            reader.Read();
            // initialize the reader using the current line as headers
            reader.Initialize();
            foundData = true;
            break;
        }
    }
    if (foundData)
        break;
}

if (!foundData)
{
    Console.WriteLine($"Could not find data for class '{className}'.");
    return 2;
}

// the reader is now positioned on the data we want to read.

// get the column header names
string[] cols =
    reader
    .GetColumnSchema()
    .Select(c => c.ColumnName)
    .Where(n => !string.IsNullOrEmpty(n))
    .ToArray();

var data =
    reader
    // read until we get to the end of the class7 table
    .TakeWhile(r => reader.RowFieldCount > 4)
    // select just the named columns (skips the first 2 empty cols)
    .Select(cols)
    // include a computed column containing the row number
    .WithColumns(new CustomDataColumn<int>("RowNumber", x => reader.RowNumber));

// write the data as JSON to the standard output stream.
data.WriteJson(Console.OpenStandardOutput());
return 0;