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 –