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)
}