Exploring ASP.NET Core View Component

Partial Views and Child Actions are one the most used features of ASP.NET MVC. Partial Views provides us a way to create a reusable component that can be used in multiple Views. There are Actions which can be marked as Child Actions and these cannot be invoked via URL but inside views or partial views. Child Actions are no more available with ASP.NET Core. View Components are new way to implement this feature in ASP.NET Core.

Few Challenges with Partial Views

Partial Views has certain challenges and limitations. Say if we use child action in a view then while execution, an additional life cycle takes place, first ASP.Net MVC life-cycle executes for the loading the main View and once it gets loaded, another for the Child Action. Also, putting certain filter like Authorize over Child Action, used in layout, makes the complete page fail if authorization fails. It also does not support the awesomeness of async programming, test-ability, Separation of Concerns etc.

What is View Component?

View Component is one of the many awesome features introduced in ASP.NET Core. As mentioned earlier, View Components are similar to Partial View and enables us to write reusable component which can be used in multiple views. View Components are robust, leverages asynchrony, Dependency Injection friendly, follows separation of concerns which makes it easily maintainable and testable.

How to Create a View Component

There are few ways to create a View Component. These are

Class name ends with ViewComponent

We can write a plain C# class and name it with suffix ViewComponent as

public class TopBooksViewComponent
{
    public void InvokeAsync()
    {
        // View Component Logic
    }
}

ASP.NET Core recognizes the suffix and render it as view component. As this is a plain C# class, we would not have the basic MVC feature available like MVC Context etc.

Using ViewComponent attribute

It provides another way to create a View Component. We just need to put this attribute on any class as

[ViewComponent]
public class TopBooks
{
    public void InvokeAsync()
    {
        // View Component Logic
    }
}

This is almost similar as previous one. Here again we won’t have the liberty of MVC features until it is inheriting another entity. There is one more usage of this attribute, to provide a custom name to the View Component as

[ViewComponent(Name = "Featured Books")]
public class TopBooksViewComponent
    {
    public void InvokeAsync()
    {
        // View Component Logic
    }
}

Derive from ViewComponent Class

It is a simplest way to create powerful View Component via leveraging standard MVC features like HttpContext, TempData, ModelState etc. Also it allows us to use View similar to normal mvc controller. Here we don’t need any suffix in the class name (which was required in earlier ASP.NET Core versions). It looks as

public class TopBooks : ViewComponent
{
    public async Task<IViewComponentResult> InvokeAsync()
    {
        // View Component Logic

        return View();
    }
}

We will use the third option in our example. So let’s start

Note – For this example, I am using Visual Studio 2015 update 3 and ASP.NET Core 1.1.1. Complete code can be downloaded from here.

Here we will be creating a View Component which shows Top n (based on the parameter) books. For this, we will be reading the data from a book repository where we have hard coded data but in real applications, it could be read from Database, web service etc. Let’s have a look on this

public class Book
{
    public int Id { get; set; }

    public string Title { get; set; }

    public string Image { get; set; }

    public string Author { get; set; }

    public float Rating { get; set; }

}
// Repository
public interface IBookRepository
{
    Task<IList<Book>> GetTopBooksAsync(int noofBooks);
}

public class BookRepository : IBookRepository
{
    static IList<Book> allBooks;

    public BookRepository()
    {
        allBooks = new List<Book>()
        {
            new Book() { Id=1001, Title="Lifehacker: The Guide to Working Smarter, Faster, and Better", Image="LTGWSFB.jpg", Author="Adam Pash; Gina Trapani", Rating=4.4F },
            new Book() { Id=1002, Title="Mindhacker: 60 Tips, Tricks, and Games to Take Your Mind to the Next Level", Image="M6TTGTYMNL.jpg", Author="Ron Hale-Evans; Marty Hale-Evans", Rating=4.6F },
            new Book() { Id=1003, Title="Information Technology for Management: Improving Strategic and Operational Performance", Image="ITMISOP.jpg", Author="Efraim Turban; Linda Volonino", Rating=5F },
            new Book() { Id=1004, Title="Visualize This: The FlowingData Guide to Design, Visualization, and Statistics", Image="VTTFDGDVS.jpg", Author="Nathan Yau", Rating=4.6F },
            new Book() { Id=1005, Title="Creating Personal Presence", Image="CPP.jpg", Author="Dianna Booher", Rating=4.4F }
        };
    }
    public Task<IList<Book>> GetTopBooksAsync(int noofBooks)
    {
        return Task.Run(() =>
            {
                IList<Book> orderedBooks = allBooks.OrderByDescending(b => b.Rating).ToList().Take(noofBooks).ToList();
                return orderedBooks;
            });
    }
}


We have created a book class with basic properties. Then we created an interface for repository and implemented the same in BookRepository which implements the GetTopBooks method. As mentioned earlier the data is hard-coded here. So we have created our Repository, we need to add it in our application. ASP.Net Core is DI (Dependency Injection) friendly and provides a basic DI container. To add this repository, we need to write the below code in ConfiguerServices method at startup.cs

services.AddTransient<IBookRepository, BookRepository>();

It injects a new instance of service every time it is used in the application. There are few other options based on the scope and life time of instance. To learn more about dependency injection ASP.NET MVC core refer my previous post.

Till now we have written code for supplying data to our component. Now it’s time to write the View Component.

Step 1:

First, we will create a Class which derives from ViewComponent. I have created a class name TopBooks and here we need to implement InvokeAsync method which can take multiple parameters based on the need which is passed while invoking the view component. My View Component class looks like

public class TopBooks : ViewComponent
{
    IBookRepository bookRepostiroty;
    public TopBooks(IBookRepository repository)
    {
        this.bookRepostiroty = repository;
    }
    public async Task<IViewComponentResult> InvokeAsync(int noOfBooks)
    {
        var items = await bookRepostiroty.GetTopBooksAsync(noOfBooks);

        items.ToList().ForEach(b => b.Image = @"/images/" + b.Image);

        return View(items);
    }
}

In the InvokeAsync method, we are passing a parameter noOfBooks and based on that we are fetching the books from bookRepository. The items return by the repository contains the book image name which is getting converted in the complete URL so that it can be directly used on UI. Then we are passing these items in the view. We didn’t provide a view name, it finds a view with name Default.cshtml but we can also provide any other name as well. For that we need to pass the view name in string format, similar to normal controller returning named view. We can see that here the return type of this method is IViewComponentResult which is specific for View Component.

This class looks like mini controller as it returns the view. It has only one method InvokeAsync which can be called from the specific View Component, not from the browser. It also provides another method InvokeAsync which can be used instead for async handling.

We used here our BookRepository that we created and it got injected via constructor. Now we will move to step 2.

Step 2:

We need to create the UI for View Component. It is normal razor view and we will create a new view with name as Default.cshtml. We cannot put this view anywhere but one of the following locations

  • ~/Views/Shared/Components/[ViewComponentName]/Default.cshtml
  • ~/Views/[ControllerName]/Components/[ViewComponentName]/Default.cshtml

If we want to use the View Component across multiple controllers, then first location should be used. On the other hand, if we want to restrict its usage to the same controller then second option should be chosen.

Also as we choose the name of the view component as default.cshtml but we can use any other name. When we have any other name like Foo.cshtml then we need to pass the name of View from our mini controller as

 return View("Foo", items);

It is similar like Views in the normal controller where if the action name and view name is same then we do not need to pass the view name else need to provide.
Let’s see our View (Default.cshtml)

@model IEnumerable<ViewComponentBlog.Models.Book>
<table class="table table-condensed table-hover">   
<tr> 
<th> </th>
<th> Title</th>
<th> Author</th>
<th> Rating</th>
    </tr>
    @foreach (var b in Model)
    { 
<tr>  
<td> <img src=@b.Image /></td>
<td> @b.Title</td>
<td> @b.Author </td>
<td> @b.Rating</td>
        </tr>
    }
</table>

It is like normal view where we have the list of books as in the model and we iterated that to display on the screen.

Step 3:

Now the final step is to use the View Component in any view. It is very simple, we can invoke it at any place.

@await Component.InvokeAsync("TopBooks", 3)

Here the first parameter is the name of the view component and then the parameters that we used while implementation of InvokeAsync method. We used only one parameter number of books so here we are passing the same. Other important thing as we are using asynchronous method, we need to use await keyword as in the example else it won’t be rendered on the screen.

Now we are done and now let’s run the application

So we can see that our view component is displayed as expected.

View Component can also be used in MVC Controller as well. For that we need to use ViewComponetResult class as

public IActionResult IndexVC()
{
    return ViewComponent("TopBooks", 3);
}

How to use ViewComponent as TagHelper 

View Component also can be used tag helpers in ASP.NET Core 1.1. Tag Helpers are also a new feature introduced with ASP.NET Core which provides us to render a server side code in the razor mark up in a HTML friendly way and allows us to write robust, reliable and maintainable. I wrote a post on it long back on it, you can have a look here. As newer version of ASP.NET Core got released, there may be slight changes in it. To use it as TagHelper we need to first include TagHelper library in project.json (Microsoft.AspNetCore.Mvc.TagHelpers) then add it in the Views as

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
@addTagHelper *, ViewComponentBlog

Here ViewComponentBlog is the project name. Now it can be used on the view as

<vc:top-books no-of-books="2" >
</vc:top-books>

It looks simpler and cleaner. To use any View Component, the tag should start with vc and the view component and parameter name are converted on lower kebab case (means all in lower case and if there is case change then a hyphen is inserted like TopBooks changed to top-books), it would not work if the naming is not properly used. So now you have the beauty of TagHelper with ViewCompoenent.

Conclusion

In this post, we have seen that View Component is powerful feature of ASP.NET Core which can be used to write complex reusable controls for the application. It is loaded with separation of concern, async and testability features and similar to normal controller and view.  We created a sample view component in step by step approach and used that in a view and used the power of DI for injecting Repository in our control.  Also, we saw that in ASP.NET Core 1.1, it can be written as TagHelper which makes sure, we are not forced tow write C# code in the view J. Complete code is attached with the post.

Advertisements

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.

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