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

 

Learning ASP.NET Web API [Packt] – ASP.NET Core RC1 to ASP.NET Core 1.0

Hello All,

Recently, I published a video course on ASP.NET Web API using ASP.NET Core framework. Initially, when I started working on it, it was named as ASP.NET 5 and as it progressed till last section, Microsoft announced to rename it as ASP.NET Core and RC2 got released few days prior to the course release date. Microsoft took more than six months to release the next RC version. Although the concept was same but there were major changes in tooling and libraries. As it was not the final version so we decided to provide the details of differences once RTM releases.

In this post, I will be putting the details of the changes by section and also update the sample which will be available with the course. As ASP.NET 5 got renamed to ASP.NET Core, accordingly the names of libraries got changed to ASP.NET Core as well so we need to change all the references. The following table contains the details of packages that we need to update in our sample with the video number.

ASP.NET 5 Reference (Earlier) ASP.NET Core 1.0 Reference (Current) Video#
Microsoft.AspNet.Mvc Microsoft.ASPNETCore.MVC 2.2
EntityFramework.Core
EntityFramework.MicrosoftSqlServer
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
2.3
Microsoft.AspNet.Mvc.Formatters.Xml Microsoft.AspNetCore.Mvc.Formatters.Xml 2.4
Microsoft.AspNet.Mvc.TagHelpers
Microsoft.AspNet.Identity.EntityFramework
Microsoft.AspNetCore.Mvc.TagHelpers
Microsoft.AspNetCore.Identity.EntityFrameworkCore
5.4

Updated sample is available with the course and refer the project.json to find the updated references. Also, Microsoft again reintroduced web.config file but it will be there along with Project.json.

Note – The details will be included for only for those those sections where changes are required.

Section 2   
As this was the first section where we started our discussion on ASP.NET Core Web API and started working on our sample so this section got many changes. Let’s discuss each

Video 2.1 : There are changes in the project creation flow. Earlier when we used to create ASP.NET Core project, it used to have two sets of references – one for core and other for full framework. We either used to build the application using both the framework and remove one based on our needs. As there were many libraries which could not work in both the platforms so Microsoft has provided the option to select the framework upfront while creating the project which makes the complete process simpler. Also, in real world scenarios, there are rare chances when we need to build the binaries for both the framework.  Now when we open New Project window and select web from left side then we get three options as
2.1Here we have three options.

  1. First one is a traditional ASP.NET application using .NET 4.6.
  2. Selected one is ASP.NET Core application using .NET Core framework and the same is used in our course.
  3. Third one is again ASP.NET Core application but uses the standard .NET framework.

When we select the second option, it shows the following dialog

2.1_2

It also got revamped completely. We had earlier lots of options which included standard ASP.NET templates. Now it contains only ASP.NET Core templates and has three options same as earlier ASP.NET 5 templates and we used the empty template for our course. Just to mention again, now we have just one bag named as .NETCoreApp,Version=v1.0  which contains set of libraries for .NET Core only in the references.

There are some more changes as below

a) Earlier we used to have following line in startup.cs which was entry point for the web application

// Entry point for the application.
public static void Main(string[] args) => WebApplication.Run<Startup>(args);

but now we have a new file program.cs which contains the main method which is now entry point for the web application.

b) Configuration file web.config is also back in project file and available along with project.json although all the settings should be put in project.json and we will be using the same.

c) There are also changes in the number of the parameters in ConfigureServices method in startup.cs which includes Hosting Environment and logger factory but we will not be using it in our sample.

Video 2.2: Only change in the references. Refer the initial table.

Video 2.3: In this video, we discussed that there are four options to register a service but we found AddSingleton and AddInstance very similar, both allows single instance across multiple requests with a difference in the instance creation. Now we have only AddSingleton where either we can pass the instance or provide the type info as.

To register via interface and class name

services.AddSingleton<IBookStoreRepository, BookStoreRepository>();

and to register an instance

IBookStoreRepository bookStoreRepository = new BookStoreRepository(new BookStoreContext());
services.AddSingleton(bookStoreRepository);

Video 2.4: Only change in the references. Refer the table.

Section 3
In this section we added CRUD operations to our sample using  HTTP verbs. Only few changes are required in the section as mentioned below.

Video 3.2: Microsoft did an awesome change with it by making the JSON response in camel case by default. In our sample we added serialize settings in statrtup.cs as

options.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();

It is no more required.

Video 3.3 :  We returned various status codes from the action methods based on the request.The classes that we used also got renamed as

ASP.NET 5 ASP.NET Core 1.0
HttpUnauthorized Unauthorized
HttpNotFound (and its overloads) NotFound
HttpBadRequest (and its overloads) BadRequest

The above changes has taken place in all the action methods wherever used.

Section 4 
In this section, we added few more features to our sample like sorting, paging etc. Here there is a change in the way we access URL Helper as discussed below.

Video 4.3: We used URL helper for generating the URLs and it was injected via constructor without any other changes as it was by default available which is not the case now.

Now we need to access it via UrlHelperFactory and action context. ActionContext is accessed via ActionContextAccessor, for that we need to register it in startup.cs as

services.AddSingleton<IActionContextAccessor, ActionContextAccessor>();

and we need to inject the UrlHelperFactory and ActionContextAccessor in constructor to get the URL helper as

  public BooksController(IBookStoreRepository _repository, IUrlHelperFactory urlHelperFactory, IActionContextAccessor actionContextAccessor)
        {
            this.repository = _repository;
            this.urlHelper = urlHelperFactory.GetUrlHelper(actionContextAccessor.ActionContext);
        }

Rest of the code would be same as we got the similar URL helper.

Section 5
In this section we talked about security implementation. In it we have only few changes as below.

Video 5.2 : We created a custom authorization attribute RequireHttpsAttribute which inherited from AuthorizationFilterAttribute where we checked the scheme from the URL. Now RequireHttpsAttribute is itself available under the namespace Microsoft.AspNetCore.Mvc so we can use the same to enforce the SSL in complete application or any specific controller/action.

Video 5.4 : Only few references changes. Refer the table

Section 6 
In this section, we discussed some advanced topics like DI, CORS etc and there are few changes in DI. Let’s see that

Video 6.1:
a) As discussed earlier that now we don’t have AddInstance option for registering the service, same can be achieved via AddSingleton.

b) We discussed that we can also inject services via property using FromServices attribute but it is not available any more as it was creating confusion and issues. Constructor injection is always preferable. From services is available and can be used for Action Injection as

public IEnumerable<Book> Get([FromServices] IBookStoreRepository repository)
{
...
}

These are the key changes that took place specific to Web API and our course. I created again the sample from scratch using the ASP.NET Core 1.0 which can be downloaded from the course.

Thanks a lot for your support and feedback.

Cheers
Brij

Getting Started with TypeScript: Angular 2 (1/N)

Hello All,

Thanks a lot to all of you for liking my first AngularJS 1.X series and I am very glad to receive thousands of responses and feedback. I was planning to write the similar series on Angular 2 but got bit busy with various things. Also I was working on a course on ASP.NET Web API using ASP.NET Core which you can find at Learning ASP.NET Web API. In the mean time so many blog readers requested me for the same. Today I am going to write first post on Angular 2, specifically Typescript.

Download the pdf version of my AngularJS 1.X series from here.

As Angular 2 was first announced by Google around two years back in 2014 since then various changes has taken place in the framework and continuing. The final release is yet to be out. Due to the kind of breaking changes with previous version it is introducing, initially there were lots of apprehensions in the community around it. But the Angular team has successfully addressed most of the concerns as the times passed. Now it is approaching to final release and we already have couple of RC releases so we are not too late with it. But it is high time to start.

Why Angular 2?

It is one of the most asked questions about Angular 2. As we have already a robust framework (AngularJS 1.X) which is serving the purpose quite beautifully for small to large enterprises, do we need this kind of changes at all?

The answer is Yes. There are quite a few reasons. Let’s see that

  1. There are major performance improvements in all the areas whether it is bootstrapping, Data binding, event handling etc. Leveraging Web workers and using other libraries also got simplified.
  2. As this is the age of mobile, Angular 2 has been developed by keeping it mind since beginning. The same code renders differently based on browser or mobile app.
  3. One of the hardest things to understand in Angular 1 was $scope which is no more in Angular 2. Angular 2 uses zone.js for the change detection.
  4. No more controllers and we will deal with Components here
  5. Write less code with Typescript and leverage the OOPs concept.

So we have seen some of the key benefits of Angular 2 but it does not mean you just upgrade the Angular 1 to 2. As it is completely new framework with little commonalities with previous one, it is advised to use it with new applications while old one can happily use Angular 1 without any issue.

How to start with Angular 2?

In AngularJS 1.X, we have used plain JavaScript for writing the code but as we are seeing for long, writing JavaScript code for enterprise level applications is quite tough and become unmanageable as the time passes. Even for small applications as they grow, it becomes tough to manage. Angular 1.X resolves this problem a bit by structuring the code and writing loosely coupled system. But JavaScript itself is not a type-safe language and does not allow features of OOPs. To overcome these problems, Angular Team announced AtScript at ngEurope conference in Sep 2014 and shared the new (Angular 2) framework is developed using it. Microsoft was also working on similar language TypeScript so both got together and in March 2015, they announced that the features of AtScript will be available in TypeScript 1.5 and the complete Angular 2 framework got built using TypeScript.

ECMA which defines the standards for JavaScript also understood the challenges of it and came ECMA 6 (June 2015) which includes the concept of classes, modules etc. and ECMA 7 in June 2016. Lets see how does all fit together.

Typescript

We can see that TypeScript is super set of all, it means we can leverage the features of all the standards and languages. But currently most of the browsers supports the ECMA 5 only, it means our code cannot run as is. For that we have few compilers like tsc, webpack which compiles the typescript code to normal JavaScript so that it can run smoothly in all browsers.

As Typescript and AngularJS looks made for each other so we will use TypeScript for writing Angular 2 code and in our initial posts we will discuss some core concepts of Typescript and then introduce Angular 2. After learning basics of TypeScript, learning Angular would be very easy.

We will start learning the Class. Yes you have read it correctly. Class is new construct that got introduced with ECMA 6 and further more features are added via TypeScript.

Choosing Editor

So what editor should we use for the same. There are various options and some of them mostly used are

  1. Visual Studio 2013/2015,
  2. Visual Studio Code/Community (free)
  3. Sublime
  4. WebStorm and many more

You can use any of it. You can go for Sublime if never used Visual Studio or you can try Visual Studio community version which is free. For more details and how to use these editors, refer here.

I will be using Visual Studio and for that you just need to install a plugin and it provides very rich editor with all intellisense support and compile time errors.

Creating Class

Classes in typescript is almost similar to C# but here it is more flexible and requires less code. So let’s our first class

class Person {
    // Three properties which is by default public.
    // Types are written after the variable using the separator :
    firstName: string;
    middleName: string;
    lastName: string;

    // Class constructor accepting three parameters. 
    constructor(firstName: string, middleName: string, lastName: string) {
        this.firstName = firstName;
        this.middleName = middleName;
        this.lastName = lastName;
    }

    // Member function of the class
    getFullName() {
        let fullName = this.firstName + ' ' + this.middleName + ' ' + this.lastName;
        return fullName;
    }
}

We have written a class Person which has three properties firstName, middleName and lastName. In this type of variables are provided after the writing itsname with a sperator “:”. All the members are by default public so these can be accessed and updated using class instance. Then we wrote constructor using the keyword constructor which accepts three parameters and initialize the internal properties. Here you can see the similar this keyword as C# which refers to current instance of the class. Also I added another method getFullName which just concatenates all the three properties and returns full name. Here you can see that we don’t need to write any keyword like function etc so we can see that it requires a lot less code and more contextual keywords.

let person = new Person("Brij", "Bhushan", "Mishra");
console.log("Mr " + person.lastName);
console.log(person.getFullName());

Now creating instance of the class is similar to C# using new keyword with passing all the parameters in constructor. As we have just one constructor we would not able to create instance by parameter less constructor. We also see another keyword let which I am using instead of var, we will discuss the differences in some later post but let should be preferred. Calling method is simple and here we just printed values in console.

Using Inheritance

Inheritance is well supported in TypeScript so we can easily extends any class. Here we need to extens keyword instead ‘:’ used in C#. Let’s extends our person class and create Employee class

class Employee extends Person {
    // Two more properties added
    employeeId: string;
    salary: number;

    // Constructor takes five parameters now. Three from base class and two derived class
    constructor(firstName: string, middleName: string, lastName: string, 
                employeeId: string, salary: number)
    {
        super(firstName, middleName, lastName) 
        this.employeeId = employeeId;
        this.salary = salary;
    }

    getSalary() {
        return this.salary;
    }
}

As mentioned earlier, extends keyword got used for for deriving a new class. In the constructor, we used another keyword Super to call the base class constructor and that should be called inside the constructor, not after the colon (:) as C#. There we also required to use base keyword for the same.

Implementing Interface

We can also create interface and that may be implemented in a class. Let’s create an interface IBonusCalculator as

interface IBonusCalculater {
    getBonus()  
}

It is similar to C# and have just one method getBonus. To implement it, we need to use implements keyword. Let’s implement it in our Employee class

class Employee extends Person implements IBonusCalculater {
    // Two more properties added
    employeeId: string;
    salary: number;

    // Constructor takes five parameters now. Three from base class and two derived class
    constructor(firstName: string, middleName: string, lastName: string, employeeId: string, salary: number)
    {
        super(firstName, middleName, lastName) 
        this.employeeId = employeeId;
        this.salary = salary;
    }

    getSalary() {
        return this.salary;
    }

    // Implementing IBonusCalculater's method
    getBonus() {
        return this.salary * 0.3;
    }
}

So we have seen that Class in typescript provides all the key features of OOPs and uses more relevant keywords like extends, implements, constructor etc than C# which makes it more developer friendly and readable.

Now I am going the last topic which will make you excited.  Say we have two classes and there instances

class Book {
    name: string;  
}
class Video {
    name: string;
}

Are both different type. Let’s see this

let b = new Book();
b.name = "HTML and CSS";

let v = new Video();
v.name = "Learning ASP.NET Web API";

function PrintName(b : Book) {
    console.log(b.name);
 
}

PrintName(b);
PrintName(v);

Will it compile?

Yes and run without any error. Surprised.

It will print the book and video name accordingly. Typescript checks the property names for Type comparison. I find it more useful as although they are different classes but behind the scene both are same. If we add some another property in Book only then it will throw an error because the type of parameter is Book and when video does not have the property while vice versa works but we will not be access the new property of video.

Conclusion

In this post, We have started with little bit history of Angular 2 and discussed that how TypeScript became the primary language for Angular 2. We saw that TypeScript is superset of JavaScript including ECMA 6, ECMA 7 standards and ultimately it is converted in normal JavaScript file which is compatible with every browser. We discussed the first key concept Class with example and later we saw the most important thing that two different class with same properties are considered similar here. In the next post, we will discuss about Decorator and Components.

Cheers,
Brij

Received Microsoft MVP award sixth time in a Row

Hello All,

MVP_Logo_Horizontal_Preferred_Cyan300_RGB_300ppiAgain, with all of your support and feedback, I have been awarded Microsoft Most Valuable Professional award sixth time consecutively. Its really a great achievement for me and all the kudos goes to my readers, conference audiences and supporters.

I received my first award on July 2011 in ASP.NET/IIS category for the first five years. Now it got merged with other related categories and renamed as Visual Studio and Development Technologies. ASP.NET has always been close to me and if you have been following the latest changes in the framework which is ASP.NET Core then you will find it totally awesome. It’s modular, leaner, completely customizable and blazing fast which makes it closer to me. Last month, Packt published a Video Course on ASP.NET Web API using ASP.NET Core framework. I would encourage you to have a look on it.

I have been receiving lots of queries regarding Microsoft MVP program specially last couple of months so If you have any query or want any specific detail, do ask in the comment. I will answer there, so it will be useful to everybody.

Again thanks a lot to all of you. Do keep sharing your feedback and ask any specific question regarding MVP program here.

Cheers,
Brij

My Video Course : Learning ASP.NET Web API

Hello All,

9781785885945I am very happy to announce that my video course  Learning ASP.NET Web API using ASP.NET Core 1.0 got published. This course is for all the ASP.NET Developers who have little or no experience to Web API as it starts from basics and covers all the key aspects of Rest-ful services with real world examples. It will be similarly helpful to experienced Web API devs because developing it using ASP.NET Core is completely different and many old concepts cannot be used as it is.

The vision of the course is that the viewers understand that how to develop a RESTful web API using the ASP.NET Web API service, and be able to implement a service with a range of data transfer and handling operations. They will understand RESTful service architectures and how different types of application data can be exposed with a web API, to different client endpoints.Let me give overview of the course.

The key features of the course are

  • Develop a complete enterprise level REST based HTTP services from scratch using ASP.NET Core Web API with latest standards.
  • An example driven course from starting from basics of REST with brief description of new ASP.NET framework ASP.NET Core.
  •  Discusses all the building blocks: Routing, Controllers, Content Negotiations and security with advance topics like versioning, dependency Injection, caching etc.
  • Includes the tips and best practices for making robust and scalable APIs.
  • Full of pictorial presentations for core concepts, practical examples and clear step by step instructions with tips and best practices.

What you will learn from this course.

  • Understand REST basics and its constraints with real life examples.
  • ASP.NET Core framework changes and its impact of ASP.NET Web API.
  • Building blocks of ASP.NET Web API : Routing Controllers, HTTP verbs with scenario bases example.
  • Implement various data transfer operation and Content negotiation.
  • Various security implementations options including OAuth, CORS.
  • A good hand holding of Advance topics like dependency Injection, API versioning, HTTP Caching etc.
  • Consuming Web API end points using a real world client.
  • Tips and best practices of writing better, highly scalable and performant Web API service.

To find more about the video course and watch the sample video Click here.

Thanks a lot to all of you for your continuous support and feedback. Thanks a lot to Packt publishing for providing me the opportunity for this course and being supportive throughout the journey. Thanks a lot to my friends specially Arun for his time, support, feedback and reviews. Also I cannot forget to thanks my wife Renu for being so supportive and taking care of my son Anvit during my late night hours and weekends. Without their support it would not have been possible.

This book is based on my personal learning via blogs, samples, white papers, projects, POCs and many other sources. It has no relations with any of my current and previous employers. As this course is built on ASP.NET Core 1.0 which is not yet completely out and you may expect some changes in the final version. If you find any mistakes or have some constructive feedback, you can share it with me on my mail id – brij.mishra@outlook.com  I will try my best to address that at earliest.

Again, thanks a lot to all of you for your support and feedback. I am sure you will find it useful and will be very happy to see your valuable feedback and comments.

Cheers,
Brij

9 Tips for Writing Secure Applications in ASP.NET

Security is one of the most important aspects of any application and when we talk about security, particularly in ASP.NET applications, it is not limited to development. A secure app involves multiple layers of security in the configuration, framework, web server, database server, and more. In this post, we’ll take a look at the top nine tips for writing secure applications in ASP.NET… Read complete post on Infragistics blog