Saturday, May 12, 2018

Read Excel file data using PowerShell script

We quite often need to read data from Excel file (.xlsx) and perform some action on it. In this article I’ll explain how to open Excel file in PowerShell and read data from it.

I want read below data available in my sample excel file using PowerShell script.

Let’s have a look on below PowerShell script which reads data from sample excel file.

PS Script - 

#select excel file you want to read
$file = "C:\PowerShell\MyContacts.xlsx"
$sheetName = "Sheet1"

#create new excel COM object
$excel = New-Object -com Excel.Application

#open excel file
$wb = $$file)

#select excel sheet to read data
$sheet = $wb.Worksheets.Item($sheetname)

#select total rows
$rowMax = ($sheet.UsedRange.Rows).Count

#create new object with Name, Address, Email properties.
$myData = New-Object -TypeName psobject
$myData | Add-Member -MemberType NoteProperty -Name Name -Value $null
$myData | Add-Member -MemberType NoteProperty -Name Address -Value $null
$myData | Add-Member -MemberType NoteProperty -Name Email -Value $null

#create empty arraylist
$myArray = @()

for ($i = 2; $i -le $rowMax; $i++)
    $objTemp = $myData | Select-Object *
    #read data from each cell
    $objTemp.Name = $sheet.Cells.Item($i,1).Text
    $objTemp.Address = $sheet.Cells.Item($i,2).Text
    $objTemp.Email = $sheet.Cells.Item($i,3).Text
    #Write-Host 'Name-' $objTemp.Name 'Address-' $objTemp.Address 'Email-' $objTemp.Email
    $myArray += $objTemp
#print $myarry object
#print $myarry object with foreach loop
foreach ($x in $myArray)
    Echo $x


#force stop Excel process
Stop-Process -Name EXCEL -Force

Output –

You can download code from Gist.

Thank you for reading this article. Please leave your feedback in comments below.

Reference –

See Also –

Sunday, May 6, 2018

Memento Pattern

Memento design pattern allows you to save state of object and restore it back from historical states. Memento Pattern falls under behavioral pattern of GOF (Gang of Four) patterns.

When to use –

Memento pattern can be used to save sate of an object which can be restored back to object later without violating encapsulation. In your application, you may need to restore previously set checkpoint or state or data in such scenarios you can consider using Memento design pattern. 

The best example of Memento Pattern is undo or rollback operation. In this article, I have used Backup application example in which I store state of multiple objects as backup and restore them later.

Major components of Memento pattern –

Client – This class creates multiple instances of originator class and uses caretaker class to store/restore state of an object.
Memento – The Memento class stores internal state of the Originator class and protect against access by objects other than originator class.
Originator – Creates an instance of Memento class and uses it for creating and restoring state of an object.
Caretaker class – This class manages list of Memento.

Let’s have a look on below example of Memento pattern.

Code –
//Memento class
public class Memento
    public Backup b;

    public void SetBackup(string name, DateTime date)
        b = new Backup(name, date);
    public Backup RestoreBackup()
        return b;
//Originator class
public class Backup
    public string BackupName { get; set; }
    public DateTime BackupDate { get; set; }
    public Backup(string name, DateTime date)
        BackupName = name;
        BackupDate = date;
    public Memento CreateBackup()
        Memento m = new Memento();
        m.SetBackup(BackupName, BackupDate);
        return m;
    public void RestoreBackup(Memento m)
        Backup b = m.RestoreBackup();
        BackupName = b.BackupName;
        BackupDate = b.BackupDate;
    public override string ToString()
        return string.Format("Backup Name - {0}, Date - {1}", BackupName, BackupDate.ToString("dd-MM-yyyy"));
//caretaker class
public class BackupManager
    List<Memento> mementoList = new List<Memento>();

    public void CreateBackup(Backup b)
        Console.WriteLine("{0} created.", b.BackupName);
    public void RestoreBackup(string backupName, Backup b)
        b.RestoreBackup(mementoList.Find(x => x.b.BackupName.Equals(backupName)));
        Console.WriteLine("{0} restored.", b.BackupName);
    public void ListAllBackups()
        Console.WriteLine("\nBackups available for restore - {0}\n", string.Concat(mementoList.Select(x => x.b.BackupName.ToString() + ",")));
class Program
    //Client entry point
    static void Main(string[] args)
        BackupManager bm = new BackupManager();

        Backup b = new Backup("Backup1", DateTime.Now.AddDays(-10));

        Backup b1 = new Backup("Backup2", DateTime.Now.AddDays(-8));

        Backup b2 = new Backup("Backup3", DateTime.Now.AddDays(-5));

        Backup b3 = new Backup("Backup4", DateTime.Now.AddDays(-2));



        bm.RestoreBackup("Backup1", b2);


Output –

As you can see in this example, Memento class stores internal structure and data of Backup class (Originator class). The Backup class uses Memento object to create and restore backup (state of an object). The BackupManager class holds list of Memento and manages to create and restore of backups. The client creates an instance of Backup and BackupManager classes and uses backup manager instances to create and restore state of objects.

You can download full code from Gist.

I hope this article helps you to know more about Memento Design Pattern. Please leave your feedback in comments section below.

References –

See Also –

Tuesday, April 17, 2018

Get-Process cmdlet in PowerShell

In this article I’ll explain some cmdlets around how to get all active processes running on your computer and start/stop process using PowerShell.

You can get all active and running processes on your computer using below command.

Get-Process | more

You can specify names or ID of processes you want to get information about.

Get-Process -Name Chrome, explorer, powershell

Get-Process -Id 200,1692,7368,6988

You can use wildcard characters to search processes.

Get-Process -Name w*

You can specify local or remote computer name to get processes running on local or remote computer.

Get-Process -ComputerName Mitesh-PC -Name chrome,powershell

You can also use below command to check count of how many processes of single process is running using below command. You can use Group-Object cmdlets to group on property. Before you use group you need to sort data using Sort-Object cmdlet.

Get-Process | Sort-Object Name | Group-Object -Property Name

You can send result to output text file using Out-file cmdlets.

Get-Process | Sort-Object Name | Group-Object -Property Name | Select count, name | Out-File -FilePath C:\PowerShell\Processes.txt
Get-ChildItem process*.txt

You can find a specific process running on computer using where object and you can stop that processes same time via below cmdlets. In below example, I search for processes using CPU more than 50 and name starts with ‘A’ and stop all the processes matching this condition.

Get-Process | Where-Object {$_.CPU -ge 50 -and $_.Name -like 'A*'} | Stop-Process -PassThru


You can start process on your local computer using Start-Process cmdlets.

Start-Process notepad 

You can also specify additional parameters along with start-process.

Start-Process notepad -WindowStyle Maximized


You can stop any running processes using stop-process cmdlets.

Stop-Process -Name notepad
Stop-Process -ID 9964


This cmdlets wait for process to be stopped. If you execute this cmdlets for specific process, PowerShell prompt will waits until give process stopped manually.


This cmdlets debug one or more processes running on local computer. This cmdlets attaches the debugger to running process.

Get-Process powershell
Debug-Process powershell

I hope you have some knowledge about how to get processes and perform some actions around it using various PowerShell cmdlets.

Thank you for reading this article. Please leave your feedback below.

Reference –

See Also –

Monday, April 9, 2018

Mediator Pattern

Mediator design pattern promotes loose coupling between objects and communicate between them with the help of mediator object. Mediator Pattern falls under behavioral pattern of GOF (Gang of Four) patterns.

When to use –

Mediator pattern provides a way to communicate between objects indirectly via mediator object. The mediator object facilitates the communication between objects. When you have large number of objects in your application, which is very hard to maintain you should consider using Mediator pattern.  Mediator pattern avoids direct communication between objects and it uses mediator object that is responsible to communicate between source and destination object.

There are so many practical and real life examples available for Mediator Pattern. In this article, I have used simple Chat application example in which so many participants can send their messages to other participants via mediator.

Major components of Mediator pattern –

Client – This class creates an instance of multiple participants and sends messages between them.
Mediator – The Mediator interface for communicating with different participants (colleagues).
ConcreteMediator – This class implements the Mediator interface and maintain participants.
Participant (Colleague) class – This class has reference of Mediator object and uses this object to communicate with other participants.

Let’s have a look on below example of Mediator pattern.

Code –
public interface IChatRoom
    void Register(Participant participant);
    void SendMessage(string from, string to, string message);

//Concrete Mediator
public class ChatRoom : IChatRoom
    private Dictionary<string, Participant> participants =
        new Dictionary<string, Participant>();
    public void Register(Participant participant)
        if (!participants.ContainsValue(participant))
            participants.Add(participant.Name, participant);
        participant.ChatRoom = this;

    public void SendMessage(string from, string to, string message)
        if (participants.Keys.Contains(to))
            Participant participant = participants[to];
            participant.Receive(from, message);
            Console.WriteLine("Participant not registered.");
//Participant class (Colleague class)
public class Participant
    public Participant(string name)
        Name = name;
    public ChatRoom ChatRoom { get; set; }
    public string Name { get; set; }
    public void Send(string to, string message)
        ChatRoom.SendMessage(Name, to, message);
    public void Receive(string from, string message)
        Console.WriteLine("{0} to {1} - '{2}'",
            from, Name, message);
//Client class (entry point)
class Program
    static void Main(string[] args)
        ChatRoom chatroom = new ChatRoom();
        List<string> people = new List<string>()
        { "Mitesh", "Sandeep", "Vikram", "Amit" };
        List<Participant> participants = new List<Participant>();
        foreach (string s in people)
            participants.Add(new Participant(s));
        foreach (Participant p in participants)

        participants.Find(x => x.Name.Equals("Mitesh")).
            Send("Sandeep", "Hey Sandeep, How are you?");
        participants.Find(x => x.Name.Equals("Vikram")).
            Send("Mitesh", "Where are you?");
        participants.Find(x => x.Name.Equals("Sandeep")).
            Send("Mitesh", "Hi Mitesh, i am good. Thanks");
        participants.Find(x => x.Name.Equals("Mitesh")).
            Send("Vikram", "I am at home");
        participants.Find(x => x.Name.Equals("Amit")).
            Send("Vikram", "Hi");


Output –

As you can see in this example, Participant class has reference to mediator object (ChatRoom). ChatRoom object knows about different participants and facilitates communication between them. Client creates various participants and register them with mediator object and sends messages between different participants. The sender participant does not have direct reference to receiver participant and it communicates via Mediator object.

You can download full code from Gist.

I hope this article helps you to know more about Mediator Design Pattern. Please leave your feedback in comments section below.

References –

See Also –