Leveraging Dependency Injection in ASP.NET Core

Millions of software has been built in last couple of decades which helped in coming up a set of patterns for resolving different problems and we have a ready reference as GoF design patterns from quite long now. In last decade, Inversion of Control and one of its implementation Dependency Injection (DI) became one of most used pattern across platforms for building great soft wares. Agile methodology for building software which focuses on small releases and more adaptive to the changes, made it very important tool. In this post, we will discuss Dependency Injection and different.

What is Dependency Injection?

Dependency Injection is a design pattern which is an implementation of Inversion of Control (IoC) that provides us a way to resolve dependencies. Objects works in collaboration model and the whole communication becomes pretty complex as system grows.  Inversion of Control says that the objects should not itself create the object/component on which they are dependent to their work, instead these are passed from external sources.

ASP.NET Core and DI

Due to DI’s long list of advantages it has become the norm in software development. But implementation in earlier versions of ASP.NET was not straight forward. Although there are different extensions and libraries made our life easy but ASP.Net as a framework was not open for the same. Due to these kind of new challenges, Microsoft has overhauled the complete framework and made the changes from ground up. The new framework is modularized and using the DI at its core. ASP.Net Core itself provides basic IoC container that we will discuss and later we will how we can use other.

Default container in ASP.NET Core

As mentioned earlier that ASP.NET Core uses DI for instantiating all its components and services. This container is configured in startup.cs class which is the entry point to an ASP.NET Core application. In Startup.cs we have method ConfigureServices which is where we configure all the services that can be later used in the entire application’s life cycle. Let’s see what all are the options provided by ASP.NET core.

aspnetdicore

ASP.NET Core provides above three options to register the services. Let’s discuss each.

AddTransient

Services registered using this method get instantiated each time it is accessed. If we have a service which is used multiple places in same request, a new instance would be created each time.

This method should be used for lightweight, stateless and rarely used services. Let see an example.

Here I have created a service named as TestService which has a method named as GetUniqueId. In this method, I am returning the hash code of the for the instance to check the uniqueness of the instance as

    public class TestService : ITestService
    {
        public int GetUniqueId()
        {
            return this.GetHashCode();
        }    
    }

This service is injected at two places in the sample MVC application

  1. In my home controller as
        public class HomeController : Controller
        {
            private ITestService testService;
    
            public HomeController(ITestService _testService)
            {
                this.testService = _testService;
            }
            public IActionResult Index()
            {
                ViewBag.ServiceInstanceId = testService.GetUniqueId();
                return View();
            }
        }
    
  2. And in my view (Yes we can inject services in the View as well) as
    cshtmlinject

Here service is injected in it at line 1 and called the GetUniqueId method at line 6. While at Line 4, unique Id for the instance is displayed that got injected in home controller. Now it’s time to test the application and register the service as

public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddTransient<ITestService, TestService>();
        }

When we run the application
addtransient
Here we can see that in the same request we are getting two instances as expected.

AddScoped

In case of AddScoped, only one instance is created for each request, regardless the number of times it is used in the request. So if you have a service which maintains some state in each request or used frequently then it could be a better choice.

In the same example, we will change the registration of service as

services.AddScoped<ITestService, TestService>();

And run the application
addscoped1
Here we see that only one instance created for the same request. Let’s see multiple requests
addscoped2
As expected here, two different instances gets created for different request while it is unique per request.

AddSingleton

As the name suggests, it created only once instance which get created in the first request itself. In every subsequent request, same instance is used. So, if there is requirement of a singleton behavior, maintaining state across requests then this option is best. Creating our own singleton behavior is not recommended.

It provides two important flavors.

  1. First where we let the framework to create the instance as
    services.AddSingleton<ITestService, TestService>();
    
  2. Second where we create the instance and register the same instance as
    ITestService testService = new TestService();
    services.AddSingleton(testService);
    

    So if we have some custom heavy object that can be used across request or do some changes in the instance before registering then second option is good.

Now let’s run the application
addsingelton
Here we get the same instance across multiple requests.

Third party DI containers

For basic usages of DI, we can use the default DI container but for the advance usage, we can use any third part DI container with the ASP.NET like Autofac, Unity, Ninject etc or some custom one. For that we need to have an adapter which provides implementation of IServiceProvider or we can write our own. We have already the required implementation for Autofac which can be easily integrated. let’s see that.

Using Autofac with ASP.NET

It’s very simple to leverage to use the power of Autofac. We need to include the following package in our project.

Autofac.Extensions.DependencyInjection

Once that is added, we need to make few changes in ConfigureServices Method. By default, it returns void but to use other provider, it should return IServiceProvider. We will create the instance of ContainerBuilder then register our services. Then get the container and return the service provider from the context.

        public IServiceProvider ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            var builder = new ContainerBuilder();
            builder.RegisterType<TestService>().As<ITestService>().InstancePerLifetimeScope();
            builder.Populate(services);
            var container = builder.Build();
            return container.Resolve<IServiceProvider>();
        }

Here we can see that we provided the life time of the instance while registering the service similar to AddScoped. We can also see that we used default container to add mvc, it means we can mix and match with containers.

Final words

Dependency Injection is very helpful for writing loosely coupled and testable software. Its usage with earlier versions of ASP.NET was bit tricky but with the new version ASP.NET, It has become the first-class citizen of ASP.NET Stack. We have seen that it provides a basic DI container and discussed with an example with details. Also, we discussed that how we can configure other third party containers like Ninject, Autofac, unity etc. We have configured Autofac in our example and saw we can use multiple container in same application.

Advertisements

Wish you all a very Happy New Year : 2017

Print

As the year 2016 is coming to an end, I want to take this opportunity to Wish all my blog readers a very Happy New Year. Wish that the New Year brings joy, happiness, good health and you achieve all the things that you wished.

2016 has been a fantastic year for me. My MVP award renewed again in this year 6th time. My first video course got published by Packt on Building Web API using ASP.NET Core framework. ASP.NET Core is the latest version of ASP.NET which got complete bottom up change. I would encourage you to have a look on it here. The first wish of any MVP after getting the award, is to attend the MVP summit. This year, it got fulfilled and I attended my First MVP Summit after almost six years. I also visited Delhi during a conference and got a chance to meet so many friends again. .

In last couple of months, I could not provide enough time to my blog and other community activities due to personal reasons and lot of travel. I hope it would get normalized very soon and I will be able to give more time here and connect to all of you via various ways.

Thank you all again for your continuous support and feedback. Again Wish you all a very Happy New Year.

Cheers,
Brij

Few ways of Handling Exceptions in ASP.NET MVC

Exception handling is one of the major areas of an application development and when it is a web application, it requires additional care so that errors gets handled gracefully without breaking the complete system and relevant information is shown to the users with proper error message. Also making sure that no internal details gets propagated till user when application crashes or error occur because it could be a major security threat. All the exception details and other additional information should be logged so that it can be later used for proper investigation. In this post we will talk about the few options available to handle the exception in ASP.NET MVC and best practices to use them.

In ASP.NET MVC, the request first hits the route handler which identifies the controller and action to be serving the request. We put the whole logic in our controller itself. There are various type of filters in ASP.NET MVC that are also part of the request processing and some time we extend them to put some custom logic based on specific requirement. There is an Exception filter for handling exceptions as well and this Filter is key in handling exceptions in MVC. First let’s see available filters and their order of execution in request processing flow

orderoffilters

So we can see here that there are four filters in total and exception filter executes at end. It means if we use exception filter then it will be caught there whether the exception occurs in Action or even in Authorization/Action/Result filters.

Note – Filters are added as an attribute so it also inherits from ‘System.Attribute’.

Broadly we can say that exceptions may occur in controller or in some cases while processing the routes and filters. But as most of our core logic resides in action so the chances in are most. Before focusing on filters, let’s first discuss one basic way to handle to exception that is part of C#.

Using Try/Catch block

This is C# feature and one of the basic ways to handle exceptions so we can wrap all our code in our Action as below.

try
{
    // Add your code here
}
catch
{
    // Exception Handling code
} 

But there are many issues with this approach and the primary issue is the limitation to single Action. To handle that, we need to put try catch block in each Action of the application which is repeating the same exception handling code which defies the code re-usability logic. It does not mean that we should never use it but there are some scenarios where we require to perform some another activity in case of exception without letting the user know or more specifically if you are calling to some third part services etc. then it might be a good Idea to use this approach.

Global Error Handling

Global error handling is one of the simplest way to handle exceptions at application level. It leverages the Exception Filter to handle exception and applies at application level itself. This is out of the box feature and can be easily set up by following step.

  1. Set customErrors errors as On in web.config as
    <customErrors mode="On"></customErrors>
  2. Have a common error view (in Shared folder with name error.cshtml) which will be shown in case of error aserrorviewHere we see that we get a model of type HandleErrorInfo class which provides the details about that error occurred, controller and action name etc.
  3. Make sure we registere HandleErrorAttribute in Application_Start (Global.asax)method
    RegisterGlobalFilters(GlobalFilters.Filters);
    
    public static void RegisterGlobalFilters(GlobalFilterCollection filters)
    {
         filters.Add(new HandleErrorAttribute());
    }
    

    Now if any error occurs then error view will be shown.

Customizing global error handling

We have seen that how easily we can configure the error handling at application level. But what if we want to handle it bit differently. Say we want to save the details in database and/or want to send the email notification when error occurs. We have two options here

  1. Override OnException method :

    We can override OnException method as in our controller as

    protected override void OnException(ExceptionContext filterContext)
    {
        Exception ex = filterContext.Exception;
        // Log Exception ex in database
    
        // Notify  admin team
    
        filterContext.ExceptionHandled = true;
    
        // Setting the View in case of error
        filterContext.Result = new ViewResult()
        {
            ViewName = "CustomErrorView"
        };
    }
    

    Here we can log our exception, send the mail etc. and set our own view that will be shown (like here I used CustomErrorView) in case of error. But this code won’t be reusable and need to write in each controller wherever we need.

  2. To handle it in better way, we need to extend HandleErrorAttribute as
    public class MyCustomHandleErrorAttribute : HandleErrorAttribute
    {
        public override void OnException(ExceptionContext filterContext)
        {
                       Exception ex = filterContext.Exception;
                	     // Log Exception ex in database
    
                	     // Notify  admin team
    
                	     filterContext.ExceptionHandled = true;
    
    
            filterContext.ExceptionHandled = true;
    
            filterContext.Result = new ViewResult()
            {
                ViewName = "DVZ"
            };
        }
    }
    

    And instead of registering the default HandleErrorAttribute we need to register the custom one as

    filters.Add(new MyCustomHandleErrorAttribute());
    

    Now this custom error attribute will be used in the whole application.

Handling exception at more Granular level

Till now, we have seen that how can we apply filter at global level. MVC allows us to handle exceptions at more granular level similar at controller and action level as
granularexceptions

For Controller either we can use the default HandleErrorAttribute or we can use the extended attribute similar to MyCustomHandleErrorAttribute and put it at controller as

[MyCustomHandleError]
public class EventController : Controller
{
// Controller code
  	…
}

For Action

public class EventController : Controller
{
	[MyCustomHandleError]
public ActionResult About()
{
	// Action code
}
…
}

Note – Here I have put ‘MyCustomHandleError’ as an attribute. We can use default ‘HandleError’ attribute instead of custom one.

Another variation

There may be some scenarios where we may need to show specific view or details based on the type of exception occurs in Controller/Action. In other words, say if in Controller/Action, if a specific exception occurs, then showing user one view and any other exception occurs then show a different view. Let’s see an example

[HandleError(ExceptionType = typeof(DivideByZeroException), View = "DVZ")]
[MyCustomHandleError]
public ActionResult Create()
{
	// Action code
}

Here if DivideByZeroException occurs then view DVZ will be loaded else default one would be loaded. We can add as many type of exception based on requirement. Also similarly we can apply at Controller level as well.

ExceptionHandled property usage

In OnException method, you must have seen the following line many times.

filterContext.ExceptionHandled = true;

As the name suggests that when we set it true (default: false), then the exception does not propagate further and it is handled in the same method. Say we have put the Exception Filter at action, added a Global exception filer and we did not set the ExceptionHandled filter or set it false then once the exception is caught by action level exception filter that will be thrown further to next level and caught at global filter. Normally when we handle exception we make it false because we have already handled the exception. But there could be few scenarios where we do something with exception details and throw it further so accordingly we need to set this property.

Exception handling outside the scope of MVC

Exceptions are bound to happen and it can always find the way to reach user. We need to block every route. As we know handled the exceptions using MVC framework features but if something happens outside of the MVC scope. As we know that MVC framework is built on top of the ASP.NET platform then we can use the Application_Error method that is available since beginning to handle Application level error. It can be depicted pictorially as
aspnetnmvc

Here we can see that once the control reaches ASP.NET platform, this method can help us so we should handle the exception here as well and we can put all the logging and notification code here as

protected void Application_Error()
{
    Exception ex = Server.GetLastError();
    // Log Exception ex in database

    // Notify  admin team

    // Clear the error
    Server.ClearError();

    // Redirect to a landing page
    Response.Redirect("home/index");
}

Note: Application_Error should not be used in replacement MVC global exception filer, because as soon as you get out of MVC scope, you won’t get its execution context which is very important to provide the relevant details about the exception.

Conclusion

We have discussed various possible ways of handling exceptions. We find that all the exception handling moves around the handle error attribute with many variations. Another two that we discussed: using try catch block and using Application_Error. Best solution for any application would be a combination of these approaches like extend HandleErrorAttribute based on the requirement and use it accordingly. Application Error should be used as if an exception somehow find its way to get out from MVC scope, then it will be caught here. Try Catch block should be really avoided as it just not makes the code ugly but we can miss lots of relevant information that may be helpful in fixing the issue so unless specific case, do not use it.

Issue while creating a stock like excel chart using PowerShell

Although this post is not in continuation of my previous posts on Excel charts and PowerShell but discusses a specific issue while creating Stock like chart. As topics are same I will advice you to go through the previous posts as I will be referring few things from that. The links of my previous posts are below

So to create a basic stock like chart we need to provide the data is a specific format.
b4-1

So lets write the script for the same

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

$xl = new-object -ComObject Excel.Application   
$fileName = ''
$wb = $xl.Workbooks.Open($fileName) 
$wsData = $wb.WorkSheets.item(1) 

#Activating the Data sheet
$wsData.activate() 

#Selecting the source data - We cn select the first cell with Range and select CurrentRegion which selects theenire table
$DataforChart = $wsData.Range("A1").CurrentRegion

# Adding a new sheet where the chart would be created
$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

#Adding the Charts
$stockChart = $wsChart.Shapes.AddChart().Chart

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)

# Set it true if want to have chart Title
$stockChart.HasTitle = $true

# Providing the Title for the chart
$stockChart.ChartTitle.Text = "Stock like Chart"

# Setting up the position of chart (Not required if the sheet has just one chart). It will create the chart at top left corner
$wsChart.shapes.item("Chart 1").top = 0
$wsChart.shapes.item("Chart 1").left = 0

# Saving the sheet
$wb.Save();

# Closing the work book and xl
$wb.close() 
$xl.Quit()
# Releasting the com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

So the above code looks fine as similar to our previous posts but when we run it throws an exception as
exceptionb4

It does not show any specific details for this exception and the error code also does not lead to any details but we do get default bar chart.

So it a look a little while to find the cause as it was not a common as for other charts it was working very smoothly.

For stock charts, it expects the data before providing chart types so if we change the below lines


# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

#Providing the source data
$stockChart.SetSourceData($DataforChart)

to


# Providing the source data
$stockChart.SetSourceData($DataforChart)

# Providing the chart types
$stockChart.ChartType = $xlChart::xlStockHLC

Its starts running. Let’s see a quick look on resultant chart
stock-chart

So it was very peculiar as the resolution does not seem to have any logic but next time you face similar issue, it may be helpful.

Cheers,
Brij

How to create Excel Chart using PowerShell – Part 3

This post is in continuation of my previous two posts on creating excel chart using PowerShell and in the first post we created a basic chart then in the next post we we added some more features to our chart and added two charts. The link of the previous posts are given below

Today we will see some more useful features.

Let’s first discuss the chart types. In our previous post, we created two charts in which first one was Bar chart which was default and second one was line where we provided the chart type as 4 which turned into Line chart. The better way to use the chart enums for that. To use that first we need to get the chart type as

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

Now we can assign the chart type as

$chart.ChartType=$xlChart::xlBarClustered

One key difference here is property name casing, when we provide the number it was chartType and now ChartType which takes enum as above.

Note- It may not work for you as is as show that it is not able to load/recognize Microsoft.Office.Interop.Excel.XLChartType so you need to add the type as

<em>Add-Type -AssemblyName Microsoft.Office.Interop.Excel</em>
/sourcecode]
In our example, I have chart types as

$firstChart.ChartType = $xlChart::xlBarClustered
$secondChart.ChartType = $xlChart::xlLine
$thirdChart.ChartType = $xlChart::xlAreaStacked

It looks more professional. To know the complete list of chart types click here

In this post, we will create three different charts and put it in a new sheet. Adding a sheet in the excel is pretty simple and can be added as

$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

In the second line, I have provided the sheet name.

Now we will be adding charts in this new sheet as

$firstChart = $wsChart.Shapes.AddChart().Chart
$secondChart = $wsChart.Shapes.AddChart().Chart
$thirdChart = $wsChart.Shapes.AddChart().Chart

So lets just see the data in our sheet

dataforchart-3

Now let’s run script and see the charts

threecharts

So lets see the complete script

$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]

$xl = new-object -ComObject Excel.Application   
$fileName = ''
$wb = $xl.Workbooks.Open($fileName) 
$wsData = $wb.WorkSheets.item(1) 

#Activating the Data sheet
$wsData.activate() 

#Selecting the source data - We cn select the first cell with Range and select CurrentRegion which selects theenire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion
$DataforSecondChart = $wsData.Range("A11").CurrentRegion
$DataforThirdChart = $wsData.Range("A21").CurrentRegion

# Adding a new sheet where the chart would be created
$wsChart = $wb.Sheets.Add();
$wsChart.Name = "Charts"

#Adding the Charts
$firstChart = $wsChart.Shapes.AddChart().Chart
$secondChart = $wsChart.Shapes.AddChart().Chart
$thirdChart = $wsChart.Shapes.AddChart().Chart

# Providing the chart types
$firstChart.ChartType = $xlChart::xlBarClustered
$secondChart.ChartType = $xlChart::xlLine
$thirdChart.ChartType = $xlChart::xlAreaStacked

#Providing the source data
$firstChart.SetSourceData($DataforFirstChart)
$secondChart.SetSourceData($DataforSecondChart)
$thirdChart.SetSourceData($DataforThirdChart)

# Set it true if want to have chart Title
$firstChart.HasTitle = $true
$secondChart.HasTitle = $true
$thirdChart.HasTitle = $true

# Providing the Title for the chart
$firstChart.ChartTitle.Text = "Domain controller's usage- Bar Chart"
$secondChart.ChartTitle.Text = "Events- Line Chart"
$thirdChart.ChartTitle.Text = "Events Daily- Stacked Area Chart"

# Setting up the position of chart (Not required if the sheet has just one chart). It will create the chart at top left corner
$wsChart.shapes.item("Chart 1").top = 0
$wsChart.shapes.item("Chart 1").left = 0

$wsChart.shapes.item("Chart 2").top = 250
$wsChart.shapes.item("Chart 2").left = 0

$wsChart.shapes.item("Chart 3").top = 500
$wsChart.shapes.item("Chart 3").left = 0


# Saving the sheet
$wb.Save();

# Closing the work book and xl
$wb.close() 
$xl.Quit()
# Releasting the com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Above script is self explanatory. We provided different positions (top and left) for each chart so that it gets located at properly on sheet.There are more customizations available which we can use based on our need.

Cheers,
Brij

How to create Excel Chart using PowerShell – Part 2

This post is in continuation of my previous post on creating excel charts using PowerShell where we discussed how can we create a basic excel chart. Refer below link for first post

How to create Excel Chart using PowerShell – Part 1

Today we will see some more useful features. Let me start with a question

What if there are multiple data sets available in the sheet?

As in previous post, we didn’t provide any data source to the chart but in an ideal case we should. Now we have multiple sets of data and we want to create a chart based on each set. Say our sheet looks like

MultipleSetofData

We want to create two charts based on the two data tables as above. So we need to provide it as a data source. The easy way to select data, is using the current region property. Lets see that

#Select the first cell via Range and select CurrentRegion which selects the entire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion

Here we DataforFirstChart refers the first table. A1 is the first cell of the table and current region returns the complete table. Similarly we can get the second table

$DataforSecondChart = $wsData.Range("A11").CurrentRegion

First cell of the second table is A11 so we used the same.

As we are going to create two charts we need to define the positions as well. It provides two properties top and left which can be used as

$wschart.shapes.item("Chart 1").top = 0
$wschart.shapes.item("Chart 1").left = 0

What about chart types? In previous post, we didn’t provide any chart type and by default the bar chart was rendered. We can provide the chart type based on our requirement. We will set the second chart as Line chart and we will discuss it in bit more details in next post.

$secondChart.chartType = 4

So let’s the complete code

$xl = new-object -ComObject Excel.Application	
$fileName = 'D:\TestP\DataforCharts.xlsx'
$wb = $xl.Workbooks.Open($fileName)	
$wsData = $wb.WorkSheets.item(1) 

# Activating the Data sheet
$wsData.activate() 

# Selecting the source data - We cn select the first cell with Range and select CurrentRegion 
# which selects the entire table
$DataforFirstChart = $wsData.Range("A1").CurrentRegion
$DataforSecondChart = $wsData.Range("A11").CurrentRegion

# Adding the Charts
$firstChart = $wsData.Shapes.AddChart().Chart
$secondChart = $wsData.Shapes.AddChart().Chart

# Providing the chart type - Line chart
$secondChart.chartType = 4

# Providing the source data
$firstChart.SetSourceData($DataforFirstChart)
$secondChart.SetSourceData($DataforSecondChart)

# Set it true if want to have chart Title
$firstChart.HasTitle = $true
$secondChart.HasTitle = $true

# Providing the Title for the chart
$firstChart.ChartTitle.Text = "Domain controller's usage- Bar Chart"
$secondChart.ChartTitle.Text = "Events- Line Chart"

# Setting up the position of chart (Not required if the sheet has just one chart). 
# It will create the chart at top left corner
$wsData.shapes.item("Chart 1").top = 0
$wsData.shapes.item("Chart 1").left = 350

$wsData.shapes.item("Chart 2").top = 250
$wsData.shapes.item("Chart 2").left = 350

# Saving the sheet
$wb.Save();

# Closing the work book and xl
$wb.close()	
$xl.Quit()
# Releasting the xl object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Above code is self explanatory, we provided different positions for the both charts. Now it’s time to run the script and see the output.

multichart-b2

Awesome, so we can see two charts one is Bar and another line chart as expected. In the next post, we will add some more features to it and see more options.

Cheers,
Brij

How to create Excel Chart using PowerShell – Part 1

Recently, I had to automate a complete test suite for a very complex system which uses lot of internal and external tools and generate different kind of reports. PowerShell is a primary tool for writing the automation scripts so we started using it. I found it very powerful although any nice editor (free) or proper documentation, best practices etc not available. So I explored many things with my team and I will be sharing few things here. In the coming couple of posts, I will discuss about creating excel charts using PowerShell  and in this post we will start with creating a basic chart.

To create a chart, we need the data in a tabular format which is to be used to create a chart. Lets understand what all basic steps need to be followed for creating the chart.

  • Open the excel and correct sheet .
  • Add a new sheet (Optional)
  • Provide the name to sheet (Optional)
  • Add a chart in the sheet
  • Set the Title if required
  • Save the excel
  • Releasing the com objects

Now let’s go straight to the example. Say we have a excel with data like this

chartExcel

Now its time to see the real script. Refer inline comments for detail

# Creating excel com object
$xl = new-object -ComObject Excel.Application   
$fileName = <path of the excel file>
$wb = $xl.Workbooks.Open($fileName)
#Open the first sheet of the excel 
$wsChart = $wb.WorkSheets.item(1) 

# Activating the Data sheet
$wsChart.activate() 

# Adding the Chart
$chart = $wsChart.Shapes.AddChart().Chart

# Set it true if want to have chart Title
$chart.HasTitle = $true

# Providing the Title for the chart
$chart.ChartTitle.Text = "Domain controller's usage"

# Save the sheet
$wb.Save()  

# Closing the work book and xl
$wb.close() 
$xl.Quit()
# Releasting the excel com object
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

Now its time to run the script and see the chart.

barchart

Looking perfect.

Note – You may not be able to run the script as it may show the script execution permission denied. For that you need to run the following command (Details here).

Set-ExecutionPolicy Unrestricted

Above code is pretty basic and there are couple of things which is taken by default like Chart Type (which is Bar chart), Data range used for chart (Since single data set is present), position of the chart etc.

In the next post, we will take a step further and use more APIs.

Cheers,
Brij