Exporting JSON data from your ASP .Net Web API to a Microsoft Excel Spreadsheet using Macro Programming

November 20, 2020

Exporting your data to an excel spread sheet is a very common requirement, but what if this data is coming from a  database as a Web API  service . This is possible using Macro Programming.

In this article, I would like to give a  brief overview of how to export your data from the ASP .net Web API controller to your Excel Spread Sheet on a specific look up field using a live example.

Example:  We had a sport lookup field which populated the data from a second tab sheet where there was Master data.

Sports lookup field on Excel tab sheet 1

Sport Look Up Field
We had a web application developed in MVC and a Web API. The web application had a feature where the end-users could download an excel file template with columns of static data on one sheet and master data present on the second sheet using data validations (Feature of Excel). As the data fields increased we had to keep updating this excel template with the static data for selection so that the end users could use the template with the latest data. As this was quite a tedious process, we decided to write a program in macro. The macro program was supposed to get the latest data from the database and populate the excel file with the same fields whenever you opened it.

Master values for the Sports look-up field from the second tab sheet.

Mastervalues 2

On the Web API side, we had the controller Method Get() returning a JSON object with all the data in it. You can name it as GetSportsData(). On the macro program side, we consumed the service from the API with the below code.

Here is the following code snippet which we wrote for macro programming. The programming langauge which was used here is VB;

Dim requestObject As Object

Dim URL As String

Dim boolAsync As Boolean

Dim strResponse As String

Set requestObject = CreateObject(“MSXML2.XMLHTTP”)

URL = http://mylittleblog.com/api/GetSportsData <>

boolAsync = True

With requestObject

.Open “GET”, URL, boolAsync

.SetRequestHeader “Content-Type”, “application/json”

.Send

While requestObject.readyState <> 4

DoEvents

Wend

strResponse = .ResponseText

End With

The variable ‘strResponse‘ contains the JSON object and the data from this JSON object can be used in the sheet and formatted into rows and columns.

The readyState property holds the status of the XMLHttpRequest which you can find in any AJAX Server reponses.

readyState

The various status of the XMLHttpRequest.

0: request not initialized
1: server connection established
2: request received
3: processing request
4: request finished and response is ready

Points to consider:

You must make sure that while executing the macro program, the WEB API service must be running or else we will not get the latest data from the API.
If you are not able to hit the WEB API endpoint, then you can write the code to populate the data from the master tab sheet as it may happen that the user may not be connected to the internet.

This way, one can find a solution to connect to the WEB API world from the Microsoft Excel sheet. We at MetaSys Software have been building such customized solutions using Microsoft technologies for clients across different industry verticals. For more info. https://www.metasyssoftware.com/dot-net

Leave a Comment

Tags :

Category :