TL;DR: Want to convert Excel files to JSON using C#? Learn how in just 4 simple steps with Syncfusion’s XlsIO library. Convert workbooks, worksheets, or specific ranges to JSON, and bind the results to a DataGrid in an ASP.NET Core web application. Build efficient and scalable solutions without any Microsoft dependencies!
Syncfusion Excel (XlsIO) Library is a .NET Excel library that allows users to convert Excel documents to various file formats such as PDF, image, HTML, ODS, and JSON. Among them, the Excel-to-JSON conversion is supported from the 18.3 version onward. The resultant JSON files can be bound to any control in any platform. This is one of the major advantages for users.
In this blog, we are going to see how an Excel file can be converted to a JSON stream in C# using the Syncfusion XlsIO library. We’ll also look at the procedure to bind the JSON stream to a Syncfusion DataGrid control. The following options are available in the Excel to JSON conversion:
The following screenshot shows the input template to be converted into a JSON stream.
Let’s see how to convert an Excel file to a JSON stream and display the JSON stream in a data grid in an ASP.NET Core web application.
Step 1: Create an ASP.NET Core project.
Step 2: Install the following NuGet packages as references to your .NET Core applications from NuGet.org:
Step 3: Add the following code example in the index.cshtml file to design the UI screen.
@{
ViewData["Title"] = "Home Page";
}
<div class="Common">
@{ Html.BeginForm("Index", "Home", FormMethod.Post);
<div class="tablediv">
<div class="rowdiv">
<p style="font-weight:normal">
Click the "Input Template" button to view the input Excel document. Please note that the Microsoft Excel viewer or Microsoft Excel is required to view the Excel document.
</p>
<p style="font-weight:normal">
Click the "Convert to JSON" button to view the JSON data.
</p>
</div>
<br />
</div>
<div class="rowdiv">
<div class="celldiv">
<label style="font-weight:normal">
Convert
</label>
<select name="ConvertOptions" id="convertOption" style="width:120px;">
<option>Workbook</option>
<option>Worksheet</option>
<option>Range</option>
</select>
</div>
<br />
</div>
<div>
<ejs-tab id="ej2Tab">
<e-tab-tabitems>
<e-tab-tabitem >
<e-content-template>
<div>
<div class="e-tab-header">
<div>Sheet1</div>
<div>Sheet2</div>
</div>
<div class="e-content">
<div>
<ejs-grid id="Sheet1" dataSource="@ViewBag.Tab1" height="250" width="900">
<e-grid-columns>
<e-grid-column field="Category" headerText="Category" width="120"></e-grid-column>
<e-grid-column field="Category Detail" headerText="Category Detail" textAlign="Left" width="150"></e-grid-column>
<e-grid-column field="Title(s)" headerText="Title(s)" width="150"></e-grid-column>
<e-grid-column field="Firm Name" headerText="Firm Name" textAlign="Left" width="150"></e-grid-column>
<e-grid-column field="Client" headerText="Client" textAlign="Left" width="100"></e-grid-column>
<e-grid-column field="Award" headerText="Award" width="100"></e-grid-column>
</e-grid-columns>
</ejs-grid>
</div>
<div>
<div>
<ejs-grid id="Sheet2" dataSource="@ViewBag.Tab2" height="250" width="900">
<e-grid-columns>
<e-grid-column field="Date" headerText="Date" textAlign="Left" width="120"></e-grid-column>
<e-grid-column field="Region" headerText="Region" textAlign="Left" width="150"></e-grid-column>
<e-grid-column field="Employee" headerText="Employee" width="150"></e-grid-column>
<e-grid-column field="Item" headerText="Item" textAlign="Left" width="150"></e-grid-column>
<e-grid-column field="Units" headerText="Units" textAlign="Left" width="100"></e-grid-column>
<e-grid-column field="Unit Cost" headerText="Unit Cost" width="100"></e-grid-column>
<e-grid-column field="Total" headerText="Total" textAlign="Left" width="150"></e-grid-column>
</e-grid-columns>
</ejs-grid>
</div>
</div>
</div>
</div>
</e-content-template>
</e-tab-tabitem>
</e-tab-tabitems>
</ejs-tab>
</div>
<br/>
<div class="rowdiv">
<div class="celldiv">
<input id="inputTemplate" class="e-btn" type="submit" name="button" value="Input Template" style="width:150px;" />
<input id="convertToJSON" class="e-btn" type="submit" name="button" value="Convert to JSON" style="width:150px;" />
</div>
</div>
}
</div>
<script>
var GridData =@Html.Raw( @Json.Serialize(ViewBag.data));
function load() {
var grid = document.getElementById('Grid').ej2_instances[0];
grid.dataSource = GridData;
}
</script> That code example contains the following components:
This code will create a webpage, as shown in the following image.
Step 4: Click Convert to JSON. The input Excel document will be converted to a JSON stream according to the option selected (workbook, worksheet, or range) in the combo box. Then, the converted JSON data will be bound to the DataGrid created on the webpage.
The following is the code written in the HomeController.cs class.
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json.Linq;
using Syncfusion.EJ2.Navigations;
using Syncfusion.XlsIO;
using System.Collections.Generic;
using System.Dynamic;
using System.IO;
using System.Text;
namespace Excel_JSON_Grid.Controllers
{
public class HomeController : Controller
{
IHostingEnvironment _env;
public HomeController(IHostingEnvironment env)
{
_env = env;
}
public IActionResult Index(string button, string ConvertOptions)
{
ViewBag.Sheet1 = new TabHeader { Text = "Sheet1" };
ViewBag.Sheet2 = new TabHeader { Text = "Sheet2" };
if (button == null)
return View();
else if (button == "Input Template")
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Load the input Excel file.
FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
IWorkbook workbook = application.Workbooks.Open(stream);
stream.Close();
//Save the input Excel file to a stream.
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
excelEngine.Dispose();
string contentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "Sample.xlsx";
//Return the input Excel file stream.
return File(ms, contentType, fileName);
}
else
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Xlsx;
//Load the input Excel file.
FileStream stream = new FileStream(_env.WebRootPath + "\\ExcelToJSON.xlsx", FileMode.Open, FileAccess.ReadWrite);
IWorkbook book = application.Workbooks.Open(stream);
stream.Close();
//Access first worksheet.
IWorksheet worksheet = book.Worksheets[0];
//Access a range.
IRange range = worksheet.Range["A1:H10"];
MemoryStream jsonStream = new MemoryStream();
if (ConvertOptions == "Workbook")
book.SaveAsJson(jsonStream); //Save the entire workbook as a JSON stream.
else if (ConvertOptions == "Worksheet")
book.SaveAsJson(jsonStream, worksheet); //Save the first worksheet as a JSON stream.
else if (ConvertOptions == "Range")
book.SaveAsJson(jsonStream, range); //Save the range as JSON stream.
excelEngine.Dispose();
byte[] json = new byte[jsonStream.Length];
//Read the JSON stream and convert to a JSON object.
jsonStream.Position = 0;
jsonStream.Read(json, 0, (int)jsonStream.Length);
string jsonString = Encoding.UTF8.GetString(json);
JObject jsonObject = JObject.Parse(jsonString);
//Bind the converted JSON object to the DataGrid.
if (ConvertOptions == "Workbook")
{
//The first worksheet in the input document is converted to a JSON stream and bound to the DataGrid in the first tab.
ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject<List<CustomDynamicObject>>();
//The second worksheet in the input document is converted to a JSON stream and bound to the DataGrid in the second tab.
ViewBag.Tab2 = ((JArray)(jsonObject["Sheet2"])).ToObject<List<CustomDynamicObject>>();
return View();
}
else if (ConvertOptions == "Worksheet" || ConvertOptions == "Range")
{
ViewBag.Tab1 = ((JArray)(jsonObject["Sheet1"])).ToObject<List<CustomDynamicObject>>();
ViewBag.Tab2.Visible = false;
}
jsonStream.Position = 0;
return View();
}
}
}
#region Helper Classes
/// <summary>
/// Custom dynamic object class.
/// </summary>
public class CustomDynamicObject : DynamicObject
{
/// <summary>
/// The dictionary property used to store the data.
/// </summary>
internal Dictionary<string, object> properties = new Dictionary<string, object>();
/// <summary>
/// Provides the implementation for operations that get member values.
/// </summary>
/// <param name="binder">Get Member Binder object</param>
/// <param name="result">The result of the get operation.</param>
/// <returns>true if the operation is successful; otherwise, false.</returns>
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
result = default(object);
if (properties.ContainsKey(binder.Name))
{
result = properties[binder.Name];
return true;
}
return false;
}
/// <summary>
/// Provides the implementation for operations that set member values.
/// </summary>
/// <param name="binder">Set memeber binder object</param>
/// <param name="value">The value to set to the member</param>
/// <returns>true if the operation is successful; otherwise, false.</returns>
public override bool TrySetMember(SetMemberBinder binder, object value)
{
properties[binder.Name] = value;
return true;
}
/// <summary>
/// Return all dynamic member names.
/// </summary>
/// <returns>the property name list</returns>
public override IEnumerable<string> GetDynamicMemberNames()
{
return properties.Keys;
}
}
#endregion
} After executing this code example, we will get output like in the following screenshot. The entire Excel workbook is converted into a JSON stream, the data is displayed in the DataGrid, and each worksheet is shown in separate tabs.
You can download the complete source code of this example from this GitHub link.
As you can see, Syncfusion Excel (XlsIO) Library provides support to convert Excel documents to JSON in C#. You can do this easily by following the steps given in this blog. Take a moment to peruse our documentation, where you’ll find info on other conversions, like Excel-to-PDF, Excel-to-HTML, worksheet-to-image, Excel-to-CSV, and Excel-to-ODS, all with accompanying code samples.
Using the XlsIO library, you can also export Excel data to data tables, TSV, collections of objects, and many more file formats.
If you are new to our Excel Library, we highly recommend you follow our Getting Started guide.
Are you already a Syncfusion user? You can download the product setup here. If you’re not yet a Syncfusion user, you can download a free 30-day trial here.
If you have any questions about these features, please let us know in the comments section below. You can also contact us through our support forum, support portal, or feedback portal. We are always happy to assist you!