Simple Way to Convert Excel to JSON and JSON to Excel

Introduction

Here in this blog Simple Way to Convert Excel to JSON and JSON to Excel, we are going to discusse the steps to simply convert a excel to json and json to excel without using any third party library.

Excel to JSON and JSON to Excel

Getting Started

There are various libraries you may get from NuGet or other sources to convert a excel to json, here we will discuss how to convert to json. We are not going to use any third pary libray read excel sheet . Simple we will use the library provided with Microsoft Visual Studeio and Newtonsoft.json to read and convert an excel to json and viceversa.

Newtonsoft.json library provides namespaces and classes that are used to implement the core services of the framework. It provides methods for converting between .NET types and JSON typesis. It is available on the NueGet. Below of this blog how to add referance of the library is given.

Steps to Convert Excel To Json

1. Import excel to datatable.
2. Convert the DataTable to json.

Steps to Convert Json to Excel

1. Convert the json string to DataTable.
2. Then Export the DataTable to excel file.

Import Excel to DataTable in C#

ADO.NET is a data access technology from the Microsoft .NET Framework that provides communication between relational and non-relational systems through a common set of components. It is a set of computer software components that programmers can use to access data and data services from a database or excel sheet.

The following clode blocks is for exporting datatable to excel in c# using ado.net. To know the use of each and every class, visit my blog post.

 private DataTable ReadExcelFile()    
   {    
    Console.WriteLine("Readig Excel File : " + ExcelFilePath + " completed");    
    string connectionString = this.GetConnectionString();    
    using (OleDbConnection conn = new OleDbConnection(connectionString))    
    {    
    conn.Open();    
    OleDbCommand cmd = new OleDbCommand();    
    cmd.Connection = conn;    
    string sheetName = "Student$";    
    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";  
    //data table excel
    DataTable dt = new DataTable();    
    dt.TableName = sheetName.Replace("$", string.Empty);    
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);    
    da.Fill(dt);    
    cmd = null;    
    conn.Close();    
    }    
    Console.WriteLine("Readig Excel File completed");    
    return dt;    
   }    

Code Excel to DataTable C#

Convert DataTable to Json

Using the Newtonsoft.json library, the datatable can be converted to json string. the below code is converting the datable to json string.

 string jsonString = JsonConvert.SerializeObject(dataTable);  

Excel to JSON

Converting Json to DataTable

The following code example is converts a json string to DataTable.

 var obj = JsonConvert.DeserializeObject<DataTable>(jsonString); 

Export DataTable To Excel in C#

The following code example exports the DataTable to excel. To export the DataTable the code block used the FileStream and StreamWriter class.

 public void ExportToExcel(string ExcelFilePath, DataTable dataTable)    
    {    
     // Initilization    
     bool isSuccess = false;    
     StreamWriter sw = null;    
     try    
     {    
      // Initialization.    
      StringBuilder stringBuilder = new StringBuilder();    
      // Saving Column header.    
      stringBuilder.Append(string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToList()) + "\n");    
      // Saving rows.    
      dataTable.AsEnumerable().ToList<DataRow>().ForEach(row => stringBuilder.Append(string.Join(",", row.ItemArray) + "\n"));    
      sw = new StreamWriter(new FileStream(ExcelFilePath, FileMode.Create, FileAccess.Write));    
      // Saving.    
      sw.Write(stringBuilder.ToString(););    
     }    
     catch (Exception ex)    
     {    
      // Info.    
      throw ex;    
     }    
     finally    
     {    
      // Closing.    
      sw.Flush();    
      sw.Dispose();    
      sw.Close();    
     }    
     // Info.    
    }    
    #endregion    
   }    

C# datatable to excel

The FileStream class to read from, write to, open, and close files on a file system, and to manipulate other file-related operating system handles, including pipes, standard input, and standard output. StreamWriter is implements a TextWriter for writing characters to a stream in a particular encoding.

GetConnectionString Function

 private string GetConnectionString()    
   {    
    Dictionary<string, string> props = new Dictionary<string, string>();    
    // XLSX - Excel 2007, 2010, 2012, 2013    
    props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";    
    props["Extended Properties"] = "Excel 12.0 XML";    
    props["Data Source"] = ExcelFilePath;    
    // XLS - Excel 2003 and Older    
    //props["Provider"] = "Microsoft.Jet.OLEDB.4.0";    
    //props["Extended Properties"] = "Excel 8.0";    
    //props["Data Source"] = "C:\\MyExcel.xls";    
    StringBuilder sb = new StringBuilder();    
    foreach (KeyValuePair<string, string> prop in props)    
    {    
    sb.Append(prop.Key);    
    sb.Append('=');    
    sb.Append(prop.Value);    
    sb.Append(';');    
    }    
    return sb.ToString();    
   }    

Thanks