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

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”


While requestObject.readyState <> 4



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.


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

Web API security using JSON web tokens


Today data security during financial transactions is super important and critical. The protection of sensitive user data should be a major priority for developers working on applications that use financial or personal information of the clients.

These days, many apps are accessed through multiple devices including desktops, laptops, mobile phones and tablets. Both web apps, and native apps can use web APIs for accessing data and providing services. This article addresses the topic of ensuring client security of a web API during the development phase. I will share my experience with using JSON web tokens (JWT) to ensure security of a representational state transfer (REST) web API.

There are a two simpler alternatives to JWT that I will briefly mention first:

  1. Basic authentication:

    This method is very easy to implement. A username and password is passed and validated in a database to identify legitimate users. Since the username and password are sent as plain text, every request is very susceptible to cross-site request forgery (CSRF). The security can be improved somewhat by passing the details in the headers section of the web API instead of the URL, nevertheless this method is not very secure as it does not involve any encryption.

  2. API keys:

    This technique is used to overcome the drawbacks of basic authentication. In this method, a unique key is assigned every time the user signs in indicating that the user is known. A user can use the same key to re-enter the system. The security issue with this method is that the key can easily be picked up during network transmission. Often, the key is passed as a query string in the URL, making it easier for someone to compromise the security of the web API.

JWT avoids the security flaws of the two simpler methods, by providing a bearer token authentication of the Web API. With this method, the user name and password validates, whether, the user exists in the system. Information about the validated user like name, email address and UserID can be fetched. These items are included in the ‘claim’. Claims are pieces of information about a user that have been packaged and signed into security tokens.

A JWT token consists of three parts, the header, the payload and the signature.

Header – Contains the type of token and signing algorithm used

Payload – Contains the issuer of the claim, the subject of the claim and the audience, which refers to the intended recipient of the claim. Other information can also be included, such as an expiry time of the token, or additional user information.

Signature –Contains the encoded header, encoded payload and a secret key


To give you more details about JWT implementation, I’ll be going through the steps I took to implement JWT in my web API. First I created a web API project in .Net core 2.2. Next I installed two packages via npm of visual studio, using the following commands:

  • Install-Package System.IdentityModel.Tokens.Jwt -Version 5.6.0
  • Install-Package Microsoft.AspNetCore.Authentication.JwtBearer -Version 3.1.0

In the appsetting.json file, I added my JWT keys including the secret key, issuer, subject and audience as follows:

JWT keys

Next, I registered a JWT authentication schema by using the “AddAuthentication” method and specifying JwtBearerDefaults.AuthenticationScheme. in the ConfigureServices section of the start-up class.

JWT Authentication Schema

I also added app.UseAuthentication() in the configure method of the startup class.


Next, I created a token controller in the web API. This token controller action GetApiToken took the two input parameters: Username and Password, and validated these details against the database. Once the user is validated, I generated a token using the secret key, claims information and signing credentials.


The generated token was then stored as an item in sessionStorage.

For all my web API requests, I used the following key in the header section of each Ajax web API  call request.


Finally, I applied the [Authorize] attribute to my controller to which I was calling the web API.


These were all the steps I required to implement JWT authentication in my Web API. The tokens are encrypted, so they are difficult to tamper with. They expire at specific intervals and are cryptographically signed using a cryptographic algorithm.


The final implementation step is to remove the generated token item which was stored in sessionStorage when a user logs out of the system.


MetaSys has extensive expertise in building secure web APIs for web applications. Our team has experience in building custom software solutions for clients across different industry verticals. Please feel free to contact us if you are in need of a partner to build a secure web API.  For more info, visit our website: https://www.metasyssoftware.com/dot-net.