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 = $excel.workbooks.open($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
#$myArray
#print $myarry object with foreach loop
foreach ($x in $myArray)
{
    Echo $x
}

$excel.Quit()

#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);
        mementoList.Add(b.CreateBackup());
    }
    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));
        Console.WriteLine(b.ToString());
        bm.CreateBackup(b);

        Backup b1 = new Backup("Backup2", DateTime.Now.AddDays(-8));
        Console.WriteLine(b1.ToString());
        bm.CreateBackup(b1);

        Backup b2 = new Backup("Backup3", DateTime.Now.AddDays(-5));
        Console.WriteLine(b2.ToString());
        bm.CreateBackup(b2);

        Backup b3 = new Backup("Backup4", DateTime.Now.AddDays(-2));
        Console.WriteLine(b3.ToString());
        bm.CreateBackup(b3);

        bm.ListAllBackups();

        bm.RestoreBackup("Backup2",b);
        Console.WriteLine(b.ToString());

        bm.RestoreBackup("Backup1", b2);
        Console.WriteLine(b2.ToString());

        Console.Read();
    }
}

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.

Get-Process
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



Start-Process

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





Stop-Process

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

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





Wait-Process

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.


Debug-Process

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 –