4 Easy Steps to Export Excel to JSON Using C#

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.

Enjoy a smooth experience with Syncfusion’s Excel Library! Get started with a few lines of code and without Microsoft or interop dependencies.

Explore Now

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:

• Excel workbook to a JSON file.
• Excel worksheet to a JSON file.
• Excel worksheet ranges to a JSON file.

The following screenshot shows the input template to be converted into a JSON stream.

Steps to export Excel data to JSON format

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:

• Syncfusion.XlsIO.Net.Core: To convert the Excel files into JSON streams. Click here to learn more about the NuGet packages required to create, edit, or convert Excel files.
• Syncfusion.EJ2.AspNet.Core: To bind JSON objects and display the data in the DataGrid.

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:

• ComboBox: To choose to convert the entire workbook to a JSON file, or a particular worksheet to a JSON file, or a specific range of worksheets to a JSON file.
  
• DataGrid: To display the JSON data that is converted from the Excel file.

  

• Input Template button: To download the input Excel file.
• Convert to JSON button: To convert the input Excel file to a JSON file.

This code will create a webpage, as shown in the following image.

Immerse yourself in practical examples spotlighting the extraordinary features of Syncfusion’s C# Excel Library!

Try Now

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.

GitHub samples

You can download the complete source code of this example from this GitHub link.

Don't settle for ordinary spreadsheet solutions. Switch to Syncfusion and upgrade the way you handle Excel files in your apps!

Try It Free

Wrapping up

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!

Related blogs

• Optical Character Recognition (OCR) Made Easy with the .NET PDF Library in C#
• Secure Your PDF Documents Like a Pro Using C#
• Effortlessly Compare Word Documents Using C#
• Explore the Possibilities of the What-If Analysis Scenario Manager in Excel Using C#