4 Easy Steps to Export Excel to JSON Using C#
Detailed Blog page Skeleton loader
4 Easy Steps to Export Excel Files to JSON Using C#

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.

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

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.

Input template document
Input template document

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:

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.
    Combo Box
  • DataGrid: To display the JSON data that is converted from the Excel file.

    JSON data bound to DataGrid
    JSON data bound to DataGrid
  • 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.

The UI design of the example
The UI design of the example

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

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.

Excel document converted to JSON stream and bound to the DataGrid
Excel document converted to JSON stream and bound to the DataGrid

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!

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-PDFExcel-to-HTML, worksheet-to-imageExcel-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

Be the first to get updates

Johnson Manohar

Meet the Author

Johnson Manohar

Johnson Manohar is a Product Manager for XlsIO in Syncfusion Software. He is passionate about managing and delivering quality products. He is a music composer and has released various album titles.

Comments (3)

Jan Palmer Vergel
Jan Palmer Vergel

Hi Johnson Manohar,

Can this spreadsheet support Microsoft Excel Macro ? I wish to create an mvc web based Excel manipulation with macro support. No downloading the excel, just pure web excel manipulation. Can syncfusion do this?

Thanks
Jan

@ Jan Palmer Vergel  

Hi Jan,

Syncfusion XlsIO allows you to create, read, and edit Macro VBA code in Excel documents. You can explore the following user guide documentation to learn more about macros in XlsIO:

https://help.syncfusion.com/file-formats/xlsio/working-with-macros

Best regards,
Mohan

Gayathri Ramalingam
Gayathri Ramalingam
@ Jan Palmer Vergel  

Hi Jan,

Currently, we don’t have Macros support in our spreadsheet. However, we have added it to our feature request list, we will implement the mentioned feature in any our upcoming release. We will let you know when this feature is implemented. The status of implementation can be tracked through our Features Management System:
Feedback Portal Link: https://www.syncfusion.com/feedback/43799/provide-support-for-including-macros-while-importing-and-exporting-in-spreadsheet

Regards,
Gayathri R

Comments are closed.