2

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?

Nightscape
  • 464
  • 6
  • 19
  • @jdweng i rewrote the question as suggested, as the one before isn't specific enough. In this question i tried to be as specific as i can. I didn't delete any comments though. – Nightscape Apr 07 '21 at 11:47
  • I would use oledb data adapter to fill a datatable (see https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbdataadapter.fill?force_isolation=true&view=dotnet-plat-ext-5.0). The datatable will give all the columns without you having to manually read each row. The convert the table to json using a library method like this : https://www.c-sharpcorner.com/UploadFile/9bff34/3-ways-to-convert-datatable-to-json-string-in-Asp-Net-C-Sharp/?force_isolation=true – jdweng Apr 07 '21 at 11:51
  • 1
    See my answer to [this question](https://stackoverflow.com/a/67009847/1916816). – Chris HG Apr 08 '21 at 20:37

0 Answers0