ASP.NET Core MVC: How to load a partial view

 Introduction

This article is going to describe how to show a partial view when a button clicks. The sample application shows the search form with all the students records. When search button clicks, it's going to only render student list from students partial view, search form is not getting rendered again. This is a small example of how to load a partial view for a user action.You can use this as a sample and handle a more complex scenario.

Background

• If you love to start with .NET Core on a Linux server, check what are the basic things you have to do, Install .NETCore in a Linux server
• Say Hello from .NET Core application in a Linux server,.NET Core with a Linux server: Hello World!
• If you are interested in open source platforms and want to try out .NET Core application development in Linux, try with these steps,.NETCore: Create a web application with a Linux server
• If you want to know how to create an ASP.NET Core service application check this article, ASP.NETCore: Create a Web API application
• If you want to show a loading panel on the screen using ASP.NET Core, go through this article, ASP.NET Core : How to show a loading panel in a page
• If you like to use template generators for .NET Core this is the ideal article for you, .NET Core: Create web application with Yeoman template generator
• When you are developing ASP.NET Core web applications, security is a crucial thing, follow this article to know more about it, Secure your .NETCore web applications using IdentityServer 4
• Check how to show a confirmation dialog with jquery from this post, ASP.NET Core : How to show a confirmation dialog with jquery
• If you want to try out advanced features in jquery dialog on top of .NET Core, this will be a great article for you, ASP.NET Core : Advanced JQuery dialog actions
• If you are interested in user authentication & ASP.NET Identity follow this link, ASP.NET Identity: Customize User Authentication
• You can send a simple smtp email by checking this article, C#: Create a simple SMTP email with HTML body
• Check this article to know how to use a windows service to fire an email How to fire an email using a windows service
• If you ever want to log error messages, warning or any information in your application, you have to select a logging framework, Go through this article and learn what are the available logging frameworks, Log messages using NLog framework
• Check this article and learn how to use hashing and salting to protect your passwords, keys C#: How to generate a unique key or password using salting + hashing
• If you are a LINQ query fan and want to know few basic tricks, this is the article for you LINQ - list update tricks
• If you are interested to follow on ODATA queries, check this article ODATA Reads with Parameters
• Go through ODATA Reads to know about read queries with ODATA
• If you are interested to know about Azure storage, blobs check this article,Send smtp email with Azure blob storage attachments
• We are going to create a Function app and publish that in Azure, if you are curious to know more about Azure functions check this one, Azure Function App: Create a trigger on Cosmos DB
• If you are interested in Microsoft Graph and want to know more about Outlook API, check this article, NET Core: Building Function app with Microsoft Graph API and Azure Functions
• If you like to know about Microsoft Graph and how we can integrate our business solution with it, go through this article, .NET Core: Process a Excel file with Microsoft Graph API & Azure Function
• Check this article and get to know how to work with Azure Cosmos DB - Document API, Explore Azure Cosmos DB - document API
• Learn how to map your on premise database in to a cloud NOSQL based database, Azure Cosmos DB: Designing your data structure

Create ASP.NET Core project

Let's create a ASP.NET Core project from Visual Studio, Let's check what is the Visual Studio version in your development environment, if it's not up to date, will update it to the latest version.
Open Visual Studio, from menu go to Help -> Check for Updates It will show Visual Studio is up to date and latest version is Visual Studio 2017 - 15.8.1

Let's create new solution from Visual Studio, Click on File -> New -> Project, you will get a window as shown below, click on Visual C# -> .NET Core and select ASP.NET Core Web Application from available templates. You can give a name and a location for the solution

You can select a project template from below screen, let's go with Web Application (Model - View - Controller) since we are going to create a solution with MVC pattern, In this sample we are creating the project with the latest .NET Core version, ASP.NET Core 2.1

Add Student Model

You will get separate folders for Models, Views and Controllers in your project. Let's create a model class called Student as below

namespace ajaxPartial.Models
{
    public class Student
    {

        public string Name { get; set; }
        public int CourseId { get; set; }
        public string Course { get; set; }
        public string Lab { get; set; }
        public double Marks { get; set; }
     }
}



Let's create a method to return list of students

public static List<Student> GetStudents () 
{ 

 var students = new List<Student>() {
                new Student { Name = "Hank Aaron", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 01", Marks = 84},
                new Student{ Name = "Alfred Adler", CourseId = 2, Course = "Database Systems", Lab = "Lab 02", Marks = 70 }, 
                new Student { Name = "Conrad Aiken", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 03", Marks = 73 }, 
                new Student { Name = "Buzz Aldrin", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 04", Marks = 92 },
                new Student { Name = "Susan Anthony", CourseId = 4, Course = "Software Engineering", Lab = "Lab 01", Marks = 65 },
                new Student { Name =  "Norman Ralph Augustine", CourseId = 2, Course = "Database Systems", Lab = "Lab 03", Marks = 59},
                new Student { Name = "Andy Bathgate", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 02", Marks = 80 },
                new Student { Name = "Jimmy Carter", CourseId = 2, Course = "Database Systems", Lab = "Lab 01", Marks = 84 },
                new Student { Name = "Thomas Edison", CourseId = 4, Course = "Software Engineering", Lab = "Lab 04", Marks = 75 },
                new Student { Name = "Thomas Edison", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 01", Marks = 60 },
                new Student { Name = "William Gladstone", CourseId = 2, Course = "Database Systems", Lab = "Lab 03", Marks = 69 },
                new Student { Name = "Mary Hirsch", CourseId = 2, Course = "Database Systems", Lab = "Lab 01", Marks = 78 },
                new Student { Name = "Derek Jeter", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 02", Marks = 73 },
                new Student { Name = "Ted Kennedy", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 03", Marks = 85 },
                new Student { Name = "Steve Lyons", CourseId = 2, Course = "Database Systems", Lab = "Lab 02", Marks = 79 },
                new Student { Name = "Benito Mussolini", CourseId = 4, Course = "Software Engineering", Lab = "Lab 04", Marks = 59 } };

return students; 
}

Change Index method in HomeController

Go to HomeController and change Index method to get list of students and pass it to the Index view.

public IActionResult Index() 
{ 
  List students = Student.GetStudents(); 
  return View(students); 
} 

Add Student list view

You can change the student Index view as below, add search from with student text box and courses drop down




<h1>Search Students</h1>
<br />

<form>
    <div class="form-row">
        <div class="form-group col-md-6">
            <label for="Student">Student</label>
            <input type="text" id="student" placeholder="Student" class="form-control">
        </div>
        <div class="form-group col-md-6">
            <label for="Course">Course</label>
            <select class="form-control">
                <option value="1">Computer Architecture</option>
                <option value="2">Database Systems</option>
                <option value="3">Data Structure and Algorithms</option>
                <option value="4">Software Engineering</option>
            </select>
        </div>
    </div>
    <div class="form-row">
        <div class="form-group col-md-12">
            <input id="btnSearch" type="button" class="btn btn-primary" value="Search"/>
        </div>
    </div>
</form>

Let's add a grid view below the search form, when initial page loads, it will show all the student records,

<table class="table table-striped">
    <thead>
        <tr>
            <th> Name </th>
            <th> Course </th>
            <th> Lab </th>
            <th> Marks </th>
        </tr>
    </thead>
    <tbody>
        @foreach (var student in Model)
         {
            <tr>
                <td> @student.Name </td>
                <td> @student.Course </td>
                <td> @student.Lab </td>
                <td> @student.Marks </td>
            </tr>
         }
    </tbody>
</table>

Add js file to load students

Let's create javascript file to write js function for Index view and refer it from view as below,

<script src="~/js/index.js"></script>

You can see index.js file as below. Let's write method to execute when search button click using jquery selector

$('#btnSearch').on('click', function (e) { 
  alert(); 
});



Let's run the application, you can see a search form and a list of students as below. When we click on search button, we want to fetch students according to the search criteria.
You can find two ways to do that, load whole page when a button clicks or else refresh only the student list, search form will not be rendered again. We can do that by passing a ajax request and loading a partial view.
In this developed application, search form and student list is available in the same page, let's split the two things into two views and load the second view on a ajax request

Create a partial view for students

Let's create a partial view to hold list of students and separate students grid from Index view, Go to Views folder and click on Add -> View, 

In Add MVC View window, add a view name, select List as the view template, select Student as model class, tick on Create as partial view and click on Add button

You can see _Students partial view as below, It's going to show a list of students in a grid layout

Change data load methods

You can change GetStudents method by passing a filter parameter, It will filter student list according to Student name and Course

public static List GetStudents (StudentFilter filters) 
{ 
  var students = new List() { 
   new Student { Name = "Hank Aaron", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 01", Marks = 84}, 
   new Student{ Name = "Alfred Adler", CourseId = 2, Course = "Database Systems", Lab = "Lab 02", Marks = 70 }, 
   new Student { Name = "Conrad Aiken", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 03", Marks = 73 }, 
   new Student { Name = "Buzz Aldrin", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 04", Marks = 92 }, 
   new Student { Name = "Susan Anthony", CourseId = 4, Course = "Software Engineering", Lab = "Lab 01", Marks = 65 }, 
   new Student { Name = "Norman Ralph Augustine", CourseId = 2, Course = "Database Systems", Lab = "Lab 03", Marks = 59}, 
   new Student { Name = "Andy Bathgate", CourseId = 1, Course = "Computer Architecture", Lab = "Lab 02", Marks = 80 }, 
   new Student { Name = "Jimmy Carter", CourseId = 2, Course = "Database Systems", Lab = "Lab 01", Marks = 84 }, 
   new Student { Name = "Thomas Edison", CourseId = 4, Course = "Software Engineering", Lab = "Lab 04", Marks = 75 }, 
   new Student { Name = "Thomas Edison", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 01", Marks = 60 }, 
   new Student { Name = "William Gladstone", CourseId = 2, Course = "Database Systems", Lab = "Lab 03", Marks = 69 }, 
   new Student { Name = "Mary Hirsch", CourseId = 2, Course = "Database Systems", Lab = "Lab 01", Marks = 78 }, 
   new Student { Name = "Derek Jeter", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 02", Marks = 73 }, 
   new Student { Name = "Ted Kennedy", CourseId = 3, Course = "Data Structure and Algorithms", Lab = "Lab 03", Marks = 85 }, 
   new Student { Name = "Steve Lyons", CourseId = 2, Course = "Database Systems", Lab = "Lab 02", Marks = 79 }, 
   new Student { Name = "Benito Mussolini", CourseId = 4, Course = "Software Engineering", Lab = "Lab 04", Marks = 59 } }; 

 if (!string.IsNullOrEmpty(filters.Student)) 
   students = students.Where(s => s.Name.ToLower().Contains(filters.Student.ToLower())).ToList(); 

 if (filters.CourseId != 0) 
   students = students.Where(s => s.CourseId == filters.CourseId).ToList(); 

 return students; 

 } 

Create StudentFilter model class as follows, we have to pass string value in text box control and selected drop down value 

namespace ajaxPartial.Models 
{ 

   public class StudentFilter 
   { 

       public string Student { get; set; } 
       public int CourseId { get; set; } 

    } 
  } 

Change Index action method implementation to call GetStudents method,
In the page load it's going to call student load method with empty parameters (All option is selected in dropdown & empty textbox)
When click on Search button, call student load method with parameters and pass it to the _Students partial view

namespace ajaxPartial.Controllers 
{ 
  public class HomeController : Controller 
  { 
     public IActionResult Index() 
     { 
       List students = Student.GetStudents(new StudentFilter()); 
       return View(students); 
     } 

     [HttpPost] 
     public IActionResult Students (StudentFilter filters) 
     { 
       List students = Student.GetStudents(filters); 
       return PartialView("_Students", students); 
     } 
 } 
}

You can see _Students partial view as below, It expects list of Students and render them in a table

@model IEnumerable<ajaxPartial.Models.Student>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Course)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Lab)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Marks)
            </th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Course)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Lab)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Marks)
            </td>
        </tr>
}
    </tbody>
</table>

When click on search button, it'll load list of students from _Students partial view into this div tag. Let's see how we can do that from JavaScript function 

@using ajaxPartial.Models;

@model List<Student>

<h1>Search Students</h1>
<br />

<form>
    <div class="form-row">
        <div class="form-group col-md-6">
            <label for="Student">Student</label>
            <input type="text" id="student" placeholder="Student" class="form-control">
        </div>
        <div class="form-group col-md-6">
            <label for="Course">Course</label>
            <select class="form-control" id="course">
                <option value="0">All</option>
                <option value="1">Computer Architecture</option>
                <option value="2">Database Systems</option>
                <option value="3">Data Structure and Algorithms</option>
                <option value="4">Software Engineering</option>
            </select>
        </div>
    </div>
    <div class="form-row">
        <div class="form-group col-md-12">
            <input id="btnSearch" type="button" class="btn btn-primary" value="Search" />
        </div>
    </div>
</form>

<div id="students"> </div>

<script src="~/js/index.js"></script>

Load partial view from javascript method

Go to index.js file, load student list when page loads, we can call it from document.ready event

When click on search button, we can issue a ajax request with student text box and course dropdown values. 

When it succeeds, load _Students partial view into students div tag. We can identify error and stack trace information if anything bad happens
In Ajax request, pass the Controller - Action url, type of the request as POST, filter data as a JSON object and the response in form of a html string since action method returns a partial view 
We can specify the async parameter for the Ajax request, by default it's async - true, If we want to make a synchronous request we can set async to false, sometimes browser can be blocked in a async request, so we have to modify it accordingly
In Ajax request, we can set cache options as well. We can set cache - true for GET & HEAD requests only, If we want to cache the result of a method, we can set cache attribute to true

$(function () { 
  GetStudents(); 
}); 


$('#btnSearch').on('click', function (e) { 
  var filters = { 
      student: $('#student').val(), 
      courseId: $('#course').val() 
    }; 
  GetStudents(filters); 
}); 


function GetStudents(filters) { 
   $.ajax({ 
         url: '/Home/Students', 
         type: 'POST', 
         cache: false, 
         async: true, 
         dataType: "html", 
         data : filters 
    })
   .done(function (result) { 
      $('#students').html(result); 
    }).fail(function (xhr) { 
         console.log('error : ' + xhr.status + ' - ' + xhr.statusText + ' - ' +                       xhr.responseText); 
     }); 
}

Let's run the application and see what happens, it shows search form and student list partial view when the page loads

Let's try to filter students from a course and student name, when click on Search button it's going to load the partial view with filtered data

Download

TechNet Gallery

You can download the source code from TechNet Gallery, ASP.NET Core : loading a partial view

GitHub

You can clone the source code from GitHub, ajax-partialview

Conclusion

In this article, we used ASP.NET Core MVC to develop an application and load a partial view when a button clicks. In the main view, search form is not getting rendered again when search button clicks, it only renders the partial view from a AJAX request.

References

• ASP.NET Core fundamentals
• Partial views in ASP.NET Core
• jQuery - AJAX load() Method
• jQuery.ajax()

.NET Core: Process a Excel file with Microsoft Graph API & Azure Function

Introduction

This article is going to explain how to create a function app on Azure and use Excel API in Microsoft Graph. It shows how to use MS Graph explorer and postman to call function endpoints in Excel API. A demo project is explained how to process an existing excel file, Add a new row to a excel sheet, Calculate total in excel columns, Create a column and pie chart based on excel data.

Background

• If you love to start with .NET Core on a Linux server, check what are the basic things you have to do, Install .NETCore in a Linux server
• Say Hello from .NET Core application in a Linux server, .NET Core with a Linux server: Hello World!
• If you are interested in open source platforms and want to try out .NET Core application development in Linux, try with these steps, .NETCore: Create a web application with a Linux server
• If you want to know how to create an ASP.NET Core service application check this article, ASP.NETCore: Create a Web API application
• If you want to show a loading panel on the screen using ASP.NET Core, go through this article, ASP.NET Core : How to show a loading panel in a page
• If you like to use template generators for .NET Core this is the ideal article for you, .NET Core: Create web application with Yeoman template generator
• When you are developing ASP.NET Core web applications, security is a crucial thing, follow this article to know more about it, Secure your .NETCore web applications using IdentityServer 4
• Check how to show a confirmation dialog with jquery from this post, ASP.NET Core : How to show a confirmation dialog with jquery
• If you want to try out advanced features in jquery dialog on top of .NET Core, this will be a great article for you, ASP.NET Core : Advanced JQuery dialog actions
• If you are interested in user authentication & ASP.NET Identity follow this link, ASP.NET Identity: Customize User Authentication
• If you are interested to know about Azure storage, blobs check this article, Send smtp email with Azure blob storage attachments
• We are going to create a Function app and publish that in Azure, if you are curious to know more about Azure functions check this one, Azure Function App: Create a trigger on Cosmos DB
• If you are interested in Microsoft Graph and want to know more about Outlook API, check this article .NET Core: Building Function app with Microsoft Graph API and Azure Functions

Get profile information 

You should have a Microsoft account to use Microsoft Graph. You can query on top of the data available in MS Graph as your emails, OneDrive files, Calendar events, Tasks etc. At first let's try to get your profile information.

Microsoft Graph 

Microsoft Graph is the API for Microsoft 365 that provides access to all the data available in Office 365, we can connect to mail, calendar, contacts, documents, directories, users. 

Microsoft Graph exposes APIs for Azure Active Directory, Office 365 services like Sharepoint, OneDrive, Outlook, Exchange, Microsoft Team services, OneNote, Planner, Excel 

Get profile details using Graph explorer

We can access to all these Office 365 products through a single REST endpoint and manage millions of data in Microsoft Cloud, Let's see how we can view profile information of your Office 365 account using Graph explorer

Navigate to Graph explorer, https://aka.ms/ge Click on my profile section in sample queries, you can see profile details of a test account as below

Let's login to your office 365 account and view your profile information,

Get profile details in Postman

We connected to Graph explorer using Office 365 account and viewed profile information, Let's try to do the same using Postman

Postman is a tool that interacts with http APIs and helps developers to construct http requests and responses in a more easy way with authentication, different type of responses in different environments

Let's open Postman and try to get profile information, paste url in to the postman like this and click on Send  button, https://graph.microsoft.com/v1.0/me/

It tells you Access Token is empty, we have to get a valid access token with required permissions to call graph API, 

Let's try to retrieve an access token by connecting to your Office 365 account, Go to Authorization tab in postman and click on available authentication type drop down. Select OAuth 2.0 from the drop down. 

OAuth 2 is an authorization framework allows third party applications to grant access to an HTTP service. In this case we are requesting access to Office 365 data by passing valid office 365 account details

You can see available OAuth 2.0 tokens as below, for our application let's try to get a new access token. Click on Get New Access Token button

You can see a window like this to retrieve an access token, At first we have to create an application in Application Registration portal in Microsoft, that app is going to act as an application layer to access all your Office 365 data. 

You can get an access token by providing application details and your Office 365 account details, let's see how we can do that. We have to give authorization urls and application specific details to get an access token,

We can assign a name for this token, in this example let's say its ProfileToken

We have to provide a Authorization server url, url to your Office 365 account https://login.microsoftonline.com/common/oauth2/v2.0/authorize and a url to retrieve the acces token https://login.microsoftonline.com/common/oauth2/v2.0/token

Then we have to tell about the application details that authorization server is going to give the access

Let's navigate to Microsoft Application managemenmt portal https://apps.dev.microsoft.com/ and will create an application to access Office 365 data using your credentials

Let's try to register a new application, Click on Add an app, you can see a screen like this, specify the application name as sldevforum and click on Create button

You can see detail of the application you created as below, It shows Application Id & ways to generate Application secrets

Let's click on Generate New Password and retrieve a password to access this created application

Let's get back to the authentication token retrieval in Postman, we have registered an application in Microsoft Application registration portal, let's paste the Client ID and Client Secret as below

We have to provide the relevant scopes or permissions we should have in the token we are getting. That token should pass its value to the application name sldevforum and access files in your O365 account, pass https://graph.microsoft.com/files.read as permission and request the token

You can see the prompt to login to your Microsoft account, click on it

Let's open Postman Console window to check what happens behind the scene

When trying to get the access token, it shows an error message like this, it says No reply address is registered for the application

We haven't specified a reply address to the application, If you check what is the Callback URL is, its the url that redirects after application is authorized

Navigate to Application registration portal, go to Platforms section, click on Add platform button,

You will get a screen to select the platform as below, let's go with Web option

You can see web platform section as below, we have to specify the redirect url in here,  https://www.getpostman.com/oauth2/callback and save changes

You can see the access token is retrieved as below

Now lets add this token in Request headers, select that option as below

You can see access token is added in headers section with Authorization as the key, click on Send button. You will see the profile information like this

View files in your One Drive

Login to https://www.office.com you can see all the office products as below

Click on OneDrive, you can see available files like this, now lets try to read sldevforum.xlsx file from Graph explorer.

Open sldevforum.xlsx file, you can view available table data like this

Lets' try to view available files in your OneDrive, you can see details of your drive as below, https://graph.microsoft.com/v1.0/me/drive

You can further query the drive and get more details, no of items in your file system etc, ping to https://graph.microsoft.com/v1.0/me/drive/root

We can call /children endpoint and get children in the root drive

We can pass filename and get file details as below, https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx

You can navigate to /workbook endpoint and view available workbooks, https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook

Navigate to /worksheets, you can see 3 worksheets are available https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets

You can retrieve the worksheet by passing the worksheet name as follows https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets/Year1

You can see the table available in Year1 worksheet, let's try to retrieve this table rows by passing Range parameter,

Pass Range parameter like this, Range(address='Year1!A1:H11') with row and column indexes, from column A to H and row 1 to 11
https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets/Year1/Range(address='Year1!A1:H11')

Create Function App

Create basic solution 

Let's create a function App from Visual Studio and try to retrieve table information as we did using Graph explorer
Select Azure Functions v2 template from drop down and select Http trigger from the list, leave everything as it is and click OK

Let's publish this function to Azure, select Create New option to create a new function app in Azure

You can create app service by providing a App name, Azure subscription, Resource group, Hosting plan and Storage account as below, In this example I created a new Resource group, hosting plan and a storage account

When you are publishing function to azure, it asks you to change the function app version to beta, since we used function v2 with .NET Core version

Your function is published to Azure, click on Get Function URL, you can see a window like this with function url, you can see code parameter is appended to your function url since we used function authorization to access the function. If we go with Anonymous authorization level, you dont have to pass a code to access the function

Go to postman and try to call the function, you have to pass name parameter along with the function url as below

Debug your function locally

Let's change function to retrieve your profile details in Office 365 account,

Go to configuration file, you can see access token stetting is available as below

Let's run the application, try to access it from postman as below, you can see access token is logged in to the console window like this

You can locally debug the function and check what are the values available for variables like this, you can see API call output as below, it returns profile details like this

Go to Application settings in ProcessExcel function app, add AccessToken setting as below and save changes

You can call function from postman, you can see following output, it shows your profile details in postman and it shows displayname in function log,

View Excel table from function App

Go to Microsoft application registration portal, https://apps.dev.microsoft.com/#/appList and click on your application, sldevforum & select permission to view and write to files since we want to view content in a excel file and modify it, Files.ReadWrite.All

We have to get a new access token since permission is changed, go to postman and change scope value to https://graph.microsoft.com/files.readwrite 

When you change the application permission, it prompts you to access to new permissions like file read write 

Let's navigate to RetrieveFile method and check what happens there, Its going to get the access token from configuration file, then it calls the api method to get file information by passing access token in the header,

[FunctionName("ProcessModules")] 
public static async Task Run([HttpTrigger(AuthorizationLevel.Function, "get", "post",
 Route = null)]HttpRequest req, TraceWriter log) 
{ 

 int classes; 
 int labs; 
 string file = "sldevforum.xlsx"; 
 string worksheet = "Year1"; 
 string table = "Table2"; 

 log.Info("C# HTTP trigger function processed a request."); 

 ExcelHelper excelHelper = new ExcelHelper(); 

 string fileId = excelHelper.RetrieveFiles(file); 
 log.Info("RetrieveFiles status from main : " + fileId); 

string accessToken = System.Environment.GetEnvironmentVariable("AccessToken", EnvironmentVariableTarget.Process); 
string baseurl = "https://graph.microsoft.com/v1.0/"; 

public string RetrieveFiles (string file) 
{ 
 string fileId = string.Empty; 

 var client = new RestClient(baseurl + "me/drive/root/children/" + file); 
 var request = new RestRequest(Method.GET); 
 request.AddHeader("Authorization", accessToken); 

 IRestResponse response = client.Execute(request); 
 if (response.IsSuccessful) 
 {
JObject filedetails = (JObject)JsonConvert.DeserializeObject(content); fileId = filedetails["id"].ToString();  } 
 return fileId; 
} 

We can implement RetrieveTable method to get table information as below, let's pass API endpoint to view excel table with specific row and column













string fileId = excelHelper.RetrieveFiles(file);
log.Info("RetrieveFiles status from main : " + fileId);

if (!string.IsNullOrEmpty(fileId))
{
 string columns = excelHelper.RetrieveTable(fileId, worksheet);
 if (Convert.ToInt32(columns) > 0) 
 { 
   log.Info("Columns count : " + columns);

public string RetrieveTable(string fileId, string worksheet)
{
 string columnCount = string.Empty;
 string header = "!A1:H1";

 var client = new RestClient(baseurl + "me/drive/items/" + fileId + "/workbook/worksheets/" + worksheet + "/Range(address='" + worksheet + header + "')");

 var request = new RestRequest(Method.GET);
 request.AddHeader("Authorization", accessToken);

 IRestResponse response = client.Execute(request);

 if (response.IsSuccessful)
 {
   string content = response.Content;
   JObject obj = (JObject)JsonConvert.DeserializeObject(content);
   columnCount = obj["columnCount"].ToString();
 }
 return columnCount;
 }

Modify Excel sheet 

we have to get a open session to excel file by calling createSession endpoint with POST action, we should pass request body as below, https://graph.microsoft.com/v1.0/me/drive/items/DD9E77BC53370534!125/workbook/createsession

Let's call create session to modify the content in the excel file



string columns = excelHelper.RetrieveTable(fileId, worksheet);

if (Convert.ToInt32(columns) > 0)
{
 log.Info("Columns count : " + columns);
 string sessionId = excelHelper.CreateSession(fileId, log);

 if (!string.IsNullOrEmpty(sessionId))
 {
 log.Info("Session ID : " + sessionId);

You can see the implementation of CreateSession method, you have to issue a POST request with two header parameters like this



public string CreateSession(string fileId, TraceWriter log)
{
 string sessionId = string.Empty;

 var client = new RestClient(baseurl + "me/drive/items/" + fileId + "/workbook/createsession");

 var request = new RestRequest(Method.POST);
 request.AddHeader("Authorization", accessToken);
 request.AddHeader("persistSession", "true");

 IRestResponse response = client.Execute(request);

 if (response.IsSuccessful)
 {
 string content = response.Content;
 JObject session = (JObject)JsonConvert.DeserializeObject(content);
 sessionId = session["id"].ToString();
 }

 else
{
 log.Info("ERROR : " + response.ErrorMessage + " : " + response.StatusCode);
}

return sessionId;
}

Get available tables in our excel sheet, it shows only one table with id 2, https://graph.microsoft.com/v1.0/me/drive/items/DD9E77BC53370534!125/workbook/tables

We can get all the available rows in the excel sheet by calling /Rows endpoint as below, https://graph.microsoft.com/v1.0/me/drive/items/DD9E77BC53370534!125/workbook/tables/2/rows

we should pass workbook-session-id in the request header, so we have to pass sessionId as a parameter to ModifyTable method


string sessionId = excelHelper.CreateSession(fileId, log);
if (!string.IsNullOrEmpty(sessionId))
{
 log.Info("Session ID : " + sessionId);

 var success = await excelHelper.ModifyTable(fileId, sessionId, worksheet, table);
 log.Info("Modifying table : " + success);



As we saw earlier, excel sheet contains 8 columns, let's create a class to hold properties of an course object as below, course object represents a row in a excel sheet

public class CourseModule
    {

        public string Module { get; set; }
        public float Points { get; set; }
        public int Classes { get; set; }
        public int Labs { get; set; }
        public string Instructor { get; set; }
        public string StartDate { get; set; }
        public string EndDate { get; set; }
        public bool? Weekend { get; set; }


    }

You can see implementation of ModifyTable method as follows, it passes 3 parameters in the request header, its a POST request, we should pass JSON serialized string in the body section. If you check the ModifyTable method we have created a object from TableRequest class and passed index as null since we need to insert a new row to the excel file and the second property is a 2-D array to store table rows

Check ModifyTable implementation as below

public async Task<bool> ModifyTable(string fileId, string sessionId, string worksheet, string table)
        {
            var success = false;
            List<CourseModule> modules = ReadJsonFile();

            var client = new HttpClient();
            var request = new HttpRequestMessage(HttpMethod.Post, baseurl + "me/drive/items/" + fileId 
                + "/workbook/worksheets('" + worksheet + "')/Tables('" + table + "')/Rows");
            request.Headers.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
            request.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            request.Headers.Add("workbook-session-id", sessionId);

            string[] module;
            string[][] modulesArray = new string[modules.Count][];

            for (int i = 0; i< modules.Count(); i++) 
            {
              module = new string[] { modules[i].Module, modules[i].Classes.ToString(),                   modules[i].Labs.ToString(), modules[i].Points.ToString(),                                   modules[i].Instructor, modules[i].StartDate, modules[i].EndDate,                           modules[i].Weekend.ToString() };
              modulesArray[i] = module;
            }

            TableRequest tableRequest = new TableRequest();
            tableRequest.index = null;
            tableRequest.values = modulesArray;

            string jsonBody = JsonConvert.SerializeObject(tableRequest);
            request.Content = new StringContent(jsonBody, Encoding.UTF8, "application/json");

            using (var response = await client.SendAsync(request))
            {
                string statusdescription = response.ReasonPhrase;
                success = response.IsSuccessStatusCode;
            }
            return success;
        }

We have to pass excel row in a certain format, let's create a class called TableRequest and add properties as below, string value and a 2 dimensional array













public class TableRequest
    {
        public string index { get; set; }
        public string[][] values { get; set; }
    }

We have successfully inserted few rows into the excel sheet, let's try to get total in a excel column, you have to call sum function on the column and pass a cell address to write the value of it.

public int GetTotal (string fileId, string sessionId, string worksheet, string fromColumn, string toColumn)
{
int noOfClasses = 0;

var client = new RestClient(baseurl + "me/drive/items/" + fileId + "/workbook/functions/sum");

var request = new RestRequest(Method.POST);
request.AddHeader("Authorization", accessToken);
request.AddHeader("workbook-session-id", sessionId);

StringBuilder classes = new StringBuilder("{\"values\" : [{ \"address\": \"" + worksheet +"!" + fromColumn + ":" + toColumn +"\" }]}");
request.AddParameter("undefined", classes, ParameterType.RequestBody);

IRestResponse response = client.Execute(request);

if (response.IsSuccessful)
{
  string content = response.Content;
  JObject sum = (JObject)JsonConvert.DeserializeObject(content);
  noOfClasses = Convert.ToInt32(sum["value"].ToString());
}

return noOfClasses;
}



Let's create a chart based om excel worksheet data, at first we have to create a class to hold chart parameters as below,

public class ChartRequest
    {
        public string type { get; set; }
        public string sourcedata { get; set; }
        public string seriesby { get; set; }
    }

We have to pass type of the chart and column series we want to map in a chart, column series can be written as A2:C21, A2:B7

excelHelper.CreateChart(fileId, worksheet, "columnclustered", "A2:C21");
excelHelper.CreateChart(fileId, worksheet, "pie", "A2:B7");

public bool CreateChart (string fileId, string worksheet, string type, string columnsrange)
{
 var client = new RestClient(baseurl + "me/drive/items/" + fileId + "/workbook/worksheets('" + worksheet + "')/Charts/Add");

 var request = new RestRequest(Method.POST); 
 request.AddHeader("Authorization", accessToken); 
 request.AddHeader("Content-Type", "application/json"); 

 ChartRequest chartRequest = new ChartRequest { type = type, sourcedata = columnsrange, seriesby = "Auto" }; 

string jsonBody = JsonConvert.SerializeObject(chartRequest);
request.AddParameter("undefined", jsonBody, "application/json", ParameterType.RequestBody);  

IRestResponse response = client.Execute(request); 

return response.IsSuccessful;
}

Let's deploy the function app to azure, Go to solution explorer, click on project and select Publish from the menu.

You can select available publish profile and click on publish, If you check the Output window, you can see application is published with necessary updates



Let's run the function app and check the log window as below, It's not going to retrieve a valid access token as shown in the log.

Open postman and try to get an access token to read files in your outlook account,

You can pass the information required to get an access token as below, we discussed about these parameters in previous sections

When you request the token, you will get a screen like this, select your account and try to login

You can see the excel file with a table as given below, Let's run the function app and see what happens to it

Run the function app again and check the logs window, it shows file id, column count, session id and total of classes and labs columns

You can open the excel sheet and check whether table column has been updated



You can view pie chart and bar chart has been drawn on the other end of the excel sheet

You can programmatically change the content in a excel worksheet using MS Graph Excel API as we discussed above. We used a function app to do this process. You can check the source code of this sample application and extend it to handle more complex scenarios.

6 Download

6.1 TechNet Gallery

Source code can be downloaded from here, msgraph-excel

6.2 GitHub

You can clone the project from github, msgraph-excel

7 Conclusion

In this article we talked about how to access your data in Microsoft account using MS Graph. We described how to modify a excel sheet in your one drive using a Azure function app. We got to see other available apis in Microsoft Graph, how we can use it using Graph Explorer, how to access it using postman and pass a validation token. Now it's your turn to make your apps with MSGraph. 😀 If you are interested to know about Outlook API and how to track changes in your emails, you can follow this article, .NET Core: Building Function app with Microsoft Graph API and Azure Functions

8 References

• Azure Functions
• Overview of Microsoft Graph
• Graph Explorer
• Working with Excel in Microsoft Graph
• Work with tables using the Excel JavaScript API
• Using OneDrive and Excel APIs in the Microsoft Graph for App Storage