Transform Excel data into specific JSON Format

Viewed 87

I'm getting a Excel file (.xlsx) which looks like this:

enter image description here

The amount of rows and columns can vary. It can also look like this for example:

enter image description here

For the excel sheet from the frist image, the JSON should look like this:

{
   "value":[
      {
         "Prename":"Nik",
         "Age":"17",
         "Country":"Switzerland"
      },
      {
         "Prename":"Tom",
         "Age":"19",
         "Country":"Russia"
      },
      {
         "Prename":"Isak",
         "Age":"18",
         "Country":"Switzerland"
      }
   ]
}

For the second excel sheet, the JSON should look like this:

{
   "value":[
      {
         "Prename":"Nik",
         "Age":"17",
         "Country":"Switzerland",
         "Car":"Audi"
      },
      {
         "Prename":"Tom",
         "Age":"19",
         "Country":"Russia",
         "Car":"Mercedes"
      },
      {
         "Prename":"Isak",
         "Age":"18",
         "Country":"Switzerland",
         "Car":"Tesla"
      },
      {
         "Prename":"Jeff",
         "Age":"27",
         "Country":"Belarus",
         "Car":"BMW"
      }
   ]
}

I am using the LightweightExcelReader Framework.

I use this methods from the framework to get the data from the sheets:

Read a row

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

With this i get the first row of the of the excel sheet:

enter image description here

My current whole code looks like this, as you can see it is very basic.

var excelReader = new ExcelReader("path\to\excel");
var sheetReader = excelReader[0];
IEnumerable<object> row1 = sheetReader.Row(1);

My main problem is combining the data into JSON arrays and then combining them into one JSON Object.

How can i build the JSON I need with the given row function I have for reading the excel?

0 Answers
Related