Convert Excel to JSON using LightweightExcelReader

Question

I have a Excel file (.xlsx) with a sheet, which looks like this:

Name     | Age | Country      |
Nik        17    Switzerland
Thomas     28    Kuba
Waslim     12    Russia

I want to convert this excel sheet into JSON Format.

The result should look like this:

[
    {
        "Name":"Nik",
        "Age":17,
        "Country":"Switzerland"
    },
    {
        "Name":"Thomas",
        "Age":28,
        "Country":"Kuba"
    },
    {
        "Name":"Waslim",
        "Age":12,
        "Country":"Russia"
    }
]

I would like to use the LightweightExcelReader framework. I know that there is a similar question already asked, but the answer uses OLEDB which shouldn't be best practice anymore. I would like to solve this transformation with a easier and faster framework.

IMPORTANT: The number of rows and columns is dynamic and can vary, but the format of the excel sheet stays the from sheet to sheet the same.

Here is my attempt. As you can see i didn't manage alot and it's pretty basic. I managed to get the first row for the key in the JSON:

var excelReader = new ExcelReader(@"path\to\file\test.xlsx");
var sheetReader = excelReader[0];

IEnumerable<object> keys = sheetReader.Row(1);

How can I convert a Excel Sheet to JSON Format using the LightweightExcelReader Framework?

score 1 · Answer 1 · answered Apr 06 '21 at 19:51

LightWeightExcelReader states on their github readme

If you want to map a spreadsheet to a collection of objects, use our sister project, ExcelToEnumerable

The sample on their sister project looks quite like what you are trying to achieve. https://github.com/ChrisHodges/ExcelToEnumerable

public class Animal
{
  public string Name { get; set; }
  public string Colour { get; set; }
  public int Legs { get; set; }
  public decimal TopSpeed { get; set; }
}

IEnumerable<Animal> animals = "path/to/ExcelFile.xlsx".ExcelToEnumerable<Animal>();

and animals should then easily be serializable with Newtonsoft Json

using (StreamWriter file = File.CreateText(@"c:\animals.json"))
{
    JsonSerializer serializer = new JsonSerializer();
    serializer.Serialize(file, animals);
}

The problem with the ExcelToEnumerable Framework is, that the rows and columns canno't be dynamic and have to be always the same. This is a must-have criteria for me. In the ExcelToEnumerable you have to define a class, to which you than map the excel. So the amount of keys (Name, Age, Coutry and possibly more) canno't vary, because you always have the same fields in the class. Please correct me, if it is still see a possibilty. — Nightscape, Apr 06 '21 at 20:12

score 1 · Accepted Answer · answered Apr 08 '21 at 18:27

If you don't mind a dependency on Newtonsoft JSON you could do something like:

public static class ExcelJsonExtensionMethods
{
    public static string ToJson(this SheetReader sheetReader)
    {
        IDictionary<int, string> HeaderNames = GetHeaderNames(sheetReader);
        var jArray = new JArray();
        while (sheetReader.ReadNext())
        {
            var jObject = new JObject();
            do
            {
                var propertyName = HeaderNames[new CellRef(sheetReader.Address).ColumnNumber];
                jObject[propertyName] = sheetReader.Value?.ToString();
            } while (sheetReader.ReadNextInRow());
            jArray.Add(jObject);
        }

        return jArray.ToString();
    }

    private static IDictionary<int, string> GetHeaderNames(SheetReader sheetReader)
    {
        var headerNames = new Dictionary<int, string>();
        while (sheetReader.ReadNextInRow())
        {
            headerNames.Add(new CellRef(sheetReader.Address).ColumnNumber, sheetReader.Value?.ToString());
        }

        return headerNames;
    }
}

Use it like:

var excelReader = new ExcelReader(@"path\to\file\test.xlsx");
var sheetReader = excelReader[0];
var sheetJson = sheetReader.ToJson();

Bear in mind that for this code to work:

The headers need to be on the first row
The header names need to be valid JSON property names
You can't have duplicate header names
There can't be any blank columns in the header

C# newbie. A bit of guidance is needed. Copied the code and received the following error messages that I'm having hard time resolving. Thoughts? The type or namespace name 'SheetReader' could not be found 'ExcelReader' could not be found 'JArray' could not be found 'JObject' could not be found 'CellRef' could not be found — TPV, Aug 28 '23 at 13:43

Convert Excel to JSON using LightweightExcelReader

2 Answers2

Linked