Create json from Excel Range C#

Create json from Excel Range C#

Range object

In Excel COM the range object exposes two dimensional array that can be used to create json quickly. In this article I will cover, how you can transpose entire selected range into an array and get a handy json.

This example covers Excel Addin which is VSTO (Visual studio tools for office) C# technology, however no need to surprise as I have plenty of examples where you can understand how you can create your own Excel Addin using C# Visual Studio

Code example

private string ConvertExcelRangeToJson()
	//retain active row selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;            
	//Convert range into two dimentional object arry
	object[,] valueArrayObjects=((object[,])oRange.Value2);
	//Create Javascript object
	JavaScriptSerializer jsObjectSerializer = new JavaScriptSerializer();
	//to minimize memory heap issue
	jsObjectSerializer.MaxJsonLength = Int32.MaxValue;
	//Finally serialize json into a string
	return jsObjectSerializer.Serialize(valueArrayObjects);

Above code we have used oRange object which will retain user selection and creates a Range. Then we are transforming our range into two dimensional object which will populate array. Finally the array will go to Serialize method of JavaScriptSerializer class which can be extended from System.Web.Extenstions class

If I make selection for given data in excel, the code gives me output as given in next screenshot:

Josn output

Next >> Link Cell value to Sheet Header/Footer with example

Leave a Reply

Your email address will not be published. Required fields are marked *