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”

.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

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

Implementation

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.

UseAuthenticationConfiguration

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.

TokenControlerInfo

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.

AjaxCallWithBearerToken

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

AuthorizeAttribute

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.

AjaxCallRequestHeaders

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

LogoutInfo

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.

What’s new in FileMaker 16?

FileMaker 16 will change the way how custom apps are built. The new version provides ample scope for the professional developers to try out new features. It also offers a lot of educational content in order to help the developers.

So what’s new in FileMaker 16? Some of the most important features in this platform are:

  • Better support with iBeacons:

    FileMaker 16 automatically looks for known beacons and it can also be used to set up geofences. In the earlier version, one had to actively scan them.

  • Login via multiple platforms:

    Historically, FileMaker allowed single sign-on authentication through Windows or Mac OS X. Now it is possible to log in to FileMaker through Google, Amazon or Microsoft Azure. FileMaker 16 supports ‘OAuth’ which allows integration with external cloud authentication providers.

login.jpg

  • Support for JSON:

    FileMaker 16 allows accessing JSON in FileMaker. This will help in sharing of data from non – FileMaker sites. It solves the major problem of passing data from different sources to FileMaker. The new version is trying to move closer towards the objective of reaching out to a phase wherein it will be easily possible for FileMaker to connect with other services and vice versa.

  • Look and feel:

    The new version of FileMaker allows single-document interface model which liberates itself from the application frame. This makes the app look as a part of Windows 10 platform.

card window.jpg
Another feature is ‘Card window’ which is basically without a window frame and a menu bar giving a more browser like experience.

  • Usability –All document Windows are independent of one another hence they can be placed anywhere on the monitor. There is information about all the functions available in the Specify Calculation Dialog Box. This box provides information about the specific function. Copy pasting value lists with standard keyboard functions in FileMaker Pro is now possible.
  • Lock icons – You can view the encryption information about your connection and host by clicking on the lock icon.
  • Improved layout Objects Window – With FileMaker 16, you can easily select, hide or name objects that are present on the current layout.
  • Scripting – You can run a specified script when an Ios device enters a specific region. One can also open the Launch Center for opening, creating and managing FileMaker applications. Some other scripting changes are:
    • The zoom and volume settings for video can be changed.
    • You can provide animations when users switch or move across layouts helping the user to visually orient himself which further enriches the mobile user experience.
    • An enhanced signature capture using transitions provides a great user experience.
    • With the Print Now function one can create PDFs in the web browser.

MetaSys Software develops custom applications using Dot Net, PHP-MySQL, and FileMaker Pro. We have a team of Certified FileMaker developers with over 12 years of experience building FileMaker applications.

As a FileMaker Business Alliance Member, we are also authorized resellers of FileMaker licenses. If you are looking to buy any of the FileMaker licenses then please click here.