0

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

enter image description here

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 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?

Nikola Mitrovic
  • 51
  • 8
  • What is wrong with OLEDB? OLEDB is a standard used to query databases. Excel is a database. Excel is a proprietary format and there are only a few drivers that Microsoft developed to allow uses to retrieve the data. OLEDB is much more efficient than using the INTEROP. The only other choice is OPEN XML which Microsoft doesn't support very well and doesn't work with the XLS formats. Most libraries internally use OLEDB. – jdweng Apr 07 '21 at 08:33
  • 1
    @jdweng Performance and Simplicity. For exact data, please refer to this answer:https://stackoverflow.com/a/60029861/9602400 – Nikola Mitrovic Apr 07 '21 at 08:42
  • The links you provided is using a library that is using xml format to read file and your excel is xlsx. The format of a xlsx is proprietary and you must use a Microsoft driver to read the file as xlsx which is what oledb uses. The JET or ACE are Microsoft written drivers that are designed to read an excel file and uses a proprietary interface. – jdweng Apr 07 '21 at 10:01
  • ODBC was originally written as a standard specification to import and export data from databases. Then OLEDB was written as an improved interface to replace OLEDB. OLEDB was easier to use, but people quickly found out it was slower. So a lot of people are still using the older ODBC methods. – jdweng Apr 07 '21 at 10:04

0 Answers0