How To Convert Excel To JSON Using C#

I have an Excel sheet consisting of Employee details. I need to convert it to JSON through API. Let's see how to do that.

First Create a .net core web API project with the framework of .Net Core 3.1.
Install ExcelDataReader through nuget package. It is the library written in C# for reading data from Excel files.

Add an Endpoint in the Controller File, which then calls our Conversion logic. Will receive the excel data from the httpcontext request through file option.

[Route("ToJson")]
[HttpPost]
public IActionResult ConvertToJsonAsResult()
{
    try
    {
        ExcelConverter excelConverter = new ExcelConverter();
        return Ok(excelConverter.XLToJson(HttpContext, pathDestination));

    }
    catch (Exception ex)
    {
        return StatusCodeResult(HttpStatusCode.InternalServerError);
    }
}

Will create a separate class for writing the core converter logic.

Define the model class, like how we want to display in our json. In our example, I want to display employee and its certificates and its recognizing countries. So I created a separate class for these models

public class Employee
{
    public string EmployerCode { get; set; }
    public string EmployerLastName { get; set; }
    public string EmployerFirstName { get; set; }
    public string EmployerDOB { get; set; }
    public List<Certificate> Certificates { get; set; }
}

public class Certificate
{
    public string CertificateName { get; set; }
    public string CertificateNumber { get; set; }
    public List<RecognizingCountries> RecognizingCountries { get; set; }
}

public class RecognizingCountries
{
    public string Name { get; set; }
}

In the core Converter logic class, read the excel file from http request as file streams and pass it to excelreader factory to create an instance of Excel reader. From the Excel reader, we can convert it to Dataset and we can manipulate it as how we want.

public string XLToJson(HttpContext context, string pathDestination)
{
    LogWriter logWriter = new LogWriter();
    string logfileName = @"logs\log.txt";
    string logpathDestination = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), logfileName);
    if (!Directory.Exists(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "logs")))
    {
        Directory.CreateDirectory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "logs"));
    }
    try
    {
        #region Variable Declaration
        var files = context.Request.Form.Files;
        Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
        var httpRequest = context.Request;
        DataSet dsexcelRecords = new DataSet();
        IExcelDataReader excelReader = null;
        IFormFile Inputfile = null;
        Stream FileStream = null;
        var json = "";

        if (httpRequest.Form.Files.Count > 0)
        {
            Inputfile = httpRequest.Form.Files[0];
            FileStream = Inputfile.OpenReadStream();

            if (Inputfile != null && FileStream != null)
            {
                excelReader = ExcelReaderFactory.CreateReader(FileStream);
            }

            if (excelReader != null)
            {
                DataSet result = excelReader.AsDataSet();
                List<Employee> employees = new List<Employee>();
                if (result != null && result.Tables.Count > 0)
                {
                    foreach (DataTable dtt in result.Tables)
                    {
                        var dt = dtt;
                        dt.Columns["Column0"].ColumnName = "EmployerCode";
                        dt.Columns["Column1"].ColumnName = "Employer Last Name";
                        dt.Columns["Column2"].ColumnName = "Employer First Name";
                        dt.Columns["Column3"].ColumnName = "Employer DOB";
                        dt.Columns["Column4"].ColumnName = "Certificate Name";
                        dt.Columns["Column5"].ColumnName = "Certificate  Number";
                        dt.Columns["Column6"].ColumnName = "Recognizing Countries";
                        dt.Rows.RemoveAt(0);

                        DataRow[] drrr = dt.Select("[EmployerCode] IS NULL");
                        if(drrr.Length > 0)
                        {
                            return "Employer code is Empty in the attached Excel Sheet";
                        }
                        
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            var item = employees.Where(x => x.EmployerCode == Convert.ToString(dt.Rows[i]["EmployerCode"])).FirstOrDefault();
                            if (item == null &&  !string.IsNullOrEmpty(Convert.ToString( dt.Rows[i]["EmployerCode"])))
                            {
                                Employee employee = new Employee
                                {
                                    EmployerCode = Convert.ToString(dt.Rows[i]["EmployerCode"])
                                ,
                                    EmployerLastName = Convert.ToString(dt.Rows[i]["Employer Last Name"])
                                ,
                                    EmployerFirstName = Convert.ToString(dt.Rows[i]["Employer First Name"])
                                ,
                                    EmployerDOB = Convert.ToDateTime(dt.Rows[i]["Employer DOB"]).ToString("dd-MM-yyyy")
                                ,
                                    Certificates = GetCertificates(dt, Convert.ToString(dt.Rows[i]["EmployerCode"]))
                                };
                                employees.Add(employee);
                            }
                            
                        }
                    }
                }
                json = JsonConvert.SerializeObject(employees);
                using (StreamWriter writer = new StreamWriter(pathDestination))
                {
                    writer.WriteLine(json);
                }
            }

        }
        #endregion
        return json;


    }
    catch (Exception ex)
    {
        logWriter.WriteToLogFile(logpathDestination, ex);
        throw;
    }
}

public List<Certificate> GetCertificates(DataTable dataTable, string employeecode)
{
    DataView dataView = dataTable.DefaultView;
    dataView.RowFilter = "EmployerCode = '" + employeecode + "'";
    DataTable dt = dataView.ToTable();
    List<Certificate> Certificates = new List<Certificate>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        if (!string.IsNullOrEmpty(Convert.ToString(dt.Rows[i]["Certificate Name"])))
        {
            Certificates.Add(new Certificate
            {
                CertificateName = Convert.ToString(dt.Rows[i]["Certificate Name"])
            ,
                CertificateNumber = Convert.ToString(dt.Rows[i]["Certificate  Number"])
            ,
                RecognizingCountries = GetCountries(Convert.ToString(dt.Rows[i]["Recognizing Countries"]) ?? string.Empty)

            });
        }

    }
    return Certificates;
}

public List<RecognizingCountries> GetCountries(string countries)
{
    List<RecognizingCountries> RecognizingCountries = new List<RecognizingCountries>();
    foreach (string country in countries.Split(','))
    {
        if (!string.IsNullOrEmpty(Convert.ToString(country)))
        {
            RecognizingCountries.Add(new RecognizingCountries
            {
                Name = Convert.ToString(country).Trim()
            });
        }
    }
    return RecognizingCountries;
}

Output

Convert Excel to Json Using C#