This short tutorial explains how to convert JSON file to Excel using C# by reading JSON data from any source and then saving it as an Excel file say XLSX after defining the formatting of imported data. An empty or existing workbook will be loaded and JSON data will be imported into a user-defined starting cell in the selected worksheet. During this conversion of JSON to Excel in C#, you will have complete control over the font settings, grid display, text alignment, page setup, and a variety of other parameters.
Steps to Convert JSON File to Excel using C#
- Add the reference to the Aspose.Cells library from NuGet Package Manager to convert JSON file to Excel
- Create a style for the JSON data titles by setting different properties like alignment, font color, and Bold flag
- Initialize JsonLayoutOptions class object using the above style and other flags
- Create an empty Workbook and get a reference to the target worksheet
- Read the entire JSON content into a string variable
- Call ImportData function of JsonUtility class to convert JSON string to Excel
- Save the output Excel file containing imported JSON data
Here we first initialize the optional formatting parameters required for the titles in the output Excel file. In the next step, the workbook is initialized and the reference to the target worksheet is obtained. Finally, the source JSON data is loaded into a string variable and used in the ImportData function as a parameter along with other required information. To export JSON to Excel C# sample code is demonstrated below.
Code to Convert JSON to Excel in C#
| using Aspose.Cells; | |
| using System.IO; | |
| using Aspose.Cells.Utility; | |
| namespace ConvertCsvToPdfUsingCSharp | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) | |
| { | |
| // Instantiate the license at the beginning of the program to avoid trial version restrictions | |
| License JsonToExcelLicense = new License(); | |
| JsonToExcelLicense.SetLicense("Aspose.Cells.lic"); | |
| // Create a style to format the json fields title in the output workbook | |
| CellsFactory factory = new CellsFactory(); | |
| Style jsonTitleStyle = factory.CreateStyle(); | |
| jsonTitleStyle.HorizontalAlignment = TextAlignmentType.Center; | |
| jsonTitleStyle.Font.Color = System.Drawing.Color.BlueViolet; | |
| jsonTitleStyle.Font.IsBold = true; | |
| // Declare and define the layout of the data imported from JSON to Excel | |
| JsonLayoutOptions jsonLayoutOptions = new JsonLayoutOptions(); | |
| jsonLayoutOptions.TitleStyle = jsonTitleStyle; | |
| jsonLayoutOptions.ArrayAsTable = true; | |
| // Initialize an empty workbook to import JSON data | |
| Workbook emptyWbForJsonData = new Workbook(); | |
| // Get reference to the worksheet where data is to be imported | |
| Worksheet targetWorksheet = emptyWbForJsonData.Worksheets[0]; | |
| // Read the Json file into a string variable that will be used to import date | |
| string inputJsonString = File.ReadAllText("SampleJsonData.json"); | |
| // Call the ImportData function to import JSON data into the worksheet | |
| JsonUtility.ImportData(inputJsonString, targetWorksheet.Cells, 3, 5, jsonLayoutOptions); | |
| // Save Excel file | |
| emptyWbForJsonData.Save("SampleJsonToXlsx.xlsx"); | |
| } | |
| } | |
| } |
This code uses CellsFactory to create a style for the output JSON data titles by defining the horizontal alignment and font settings that are later used in the JsonLayoutOptions object. The JsonUtility.ImportData() function requires the source JSON string, reference to the Cells collection of the target worksheet, the first row and column where data is to be imported, and the layout options set at the beginning of the program.
This short tutorial has guided us how using C# convert JSON to Excel. However if you want to learn how to convert JSON to CSV, refer to the article on how to convert JSON to CSV in C#.
