Excel with PowerShell

Reading data from Excel with PowerShell:

Microsoft Excel has a very complex COM object model that we can interact with in Windows PowerShell. We can read from .xlsx files as well as.csv files

In this blog i am focusing completely on script with out focussing on theory. Comments are given for the code where ever required.

PowerShell script to read data from .xlsx file:

# Adding PS Snapin

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}

if ($snapin -eq $null)

{

   Write-Host "Loading SharePoint Powershell Snapin" -ForegroundColor Green

    Add-PSSnapin "Microsoft.SharePoint.Powershell"

}

# This is a common function i am using which will release excel objects

function Release-Ref ($ref) {

([System.Runtime.InteropServices.Marshal]::ReleaseComObject(

[System.__ComObject]$ref) -gt 0)

[System.GC]::Collect()

[System.GC]::WaitForPendingFinalizers()

}

 

# Creating excel object

$objExcel = new-object -comobject excel.application 

$objExcel.Visible = $True 

 # Directory location where we have our excel files

$ExcelFilesLocation = "D:\Users\"

 # Open our excel file

$UserWorkBook = $objExcel.Workbooks.Open($ExcelFilesLocation + "Users.xlsx") 

# Here Item(1) refers to sheet 1 of of the workbook. If we want to access sheet 10, we have to modifythe code to Item(10)

$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

 # This is counter which will help to iterrate trough the loop. This is simply row count

# I am taking row count as 2, because the first row in my case is header. So we dont need to read the header data

$intRow = 2

Do {

 # Reading the first column of the current row

 $FirstName = $UserWorksheet.Cells.Item($intRow, 1).Value()

 # Reading the second column of the current row

 $SecondName = $UserWorksheet.Cells.Item($intRow, 2).Value()

 # Reading the first column of the current row. I am assuming that this is an email id field

 $userLogOnEmail = $UserWorksheet.Cells.Item($intRow, 3).Value()

 # Read only login name from the above email id

 $pos = $userLogOnEmail.IndexOf("@")

 $userLogOnName = $userLogOnEmail.Substring(0, $pos)

          "First Name: " + $FirstName

          "Second Name: " + $SecondName

          "Log on email Name: " + $userLogOnEmail

          "Only alias truncated upto @: " + $userLogOnEmail

           # Move to next row

           $intRow++

           } While ($UserWorksheet.Cells.Item($intRow,1).Value() -ne $null)

 

# Exiting the excel object

$objExcel.Quit()

 #Release all the objects used above

$a = Release-Ref($UserWorksheet)

$a = Release-Ref($UserWorkBook) 

$a = Release-Ref($objExcel)

 

PowerShell script to read data from .csv file:

This is quite easily doable compared to .xlsx file .Very less code is not required for this. we don't need to create excel object for reading .csv files.

# All the date in the user excel will be imported

$UserList = IMPORT-CSV D:\Users\users.csv

# This foreach will loop all the rows in the above userlist

FOREACH ($User in $UserList) {

# "FirstName" is the header name of the column in the excel sheet

$UserFirstName = $OrmUser.FirstName

# "SecondName" is the header name of the column in the excel sheet

$UserSecondName = $OrmUser.SecondName

# "UserLogonName" is the header name of the column in the excel sheet

$UserLogOnMail = $User.UserLogonName

# Read only login name

$pos = $OrmUserLogOnMail.IndexOf("@")

if($pos -ge 0)

{

$userLogOnName = $OrmUserLogOnMail.Substring(0, $pos)

 }

}

 

Reading .xlsx vs reading .csv files:

It is easy to read data from .csv files compared to .xlsx files. if we have .xlsx files with us, we can just "save as" them to .csv files (What i did in my case :)).

The following are some of the advantages of reading data from .csv files compared to other:

1. No need to create excel object while reading from .csv files

2. Using Import-CSV, the execution speed will be increased

3. No need to create objects for each worksheet and workbook

 

Saving data inExcel File:

The following is the simple code that can be used to save the data in excel file

$objExcel = new-object -comobject excel.application 

$objExcel.Visible = $True 

$FinalExcelLocation = "D:\Users\FinalUsersList.xlsx"

# Create final worksheet

if (Test-Path $FinalExcelLocation) 

{ 

    # Open the document 

    $finalWorkBook = $objExcel.WorkBooks.Open($FinalExcelLocation) 

    $finalWorkSheet = $finalWorkBook.Worksheets.Item(1) 

}

else { 

    # Create It 

    $finalWorkBook = $objExcel.Workbooks.Add() 

    $finalWorkSheet = $finalWorkBook.Worksheets.Item(1)

}

# Add Header

$finalWorkSheet.Cells.Item(1,1) = "User Name";

$finalWorkSheet.Cells.Item(1,1).Font.Bold = $True 

$finalWorkSheet.Cells.Item(1,2) = "Email"

$finalWorkSheet.Cells.Item(1,2).Font.Bold = $True 

# As the first row is already filled with header, the row count will start from 2

$FinalExcelRow = 2   

Do {

$finalWorkSheet.Cells.Item($FinalExcelRow,1) = "Sample"

$finalWorkSheet.Cells.Item($FinalExcelRow,2) = Someone@sample.com

$FinalExcelRow++

  }

# To wrap the text           

$d = $finalWorkSheet.UsedRange 

$null = $d.EntireColumn.AutoFit()

if (Test-Path $FinalExcelLocation) 

{

    # If already existing file is opned, save the file

    $finalWorkBook.Save()

}

else

{

    # If a new file is created, save the file with the given name

    $finalWorkBook.SaveAs($FinalExcelLocation)

}