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)

}

Comments (5)

  1. Vaibhav Shete says:

    Hello Thanks for the good input.

    I am trying to get some info on win32_logicaldisk from my machines for C: and D: drive but the only thing that is giving me an output is c:  nothing comes up for D:  .. Need you help to see the data for D:

    ##### Here is the Code############

    $Excel = New-Object -Com Excel.Application

    $Excel.visible = $True

    $Excel = $Excel.Workbooks.Add()

    $Excel.WorkSheets.Item(1).Name = "JCC"

    $Sheet = $Excel.WorkSheets.Item(1)

    $Sheet.Cells.Item(1,1) = “Server Name”

    $Sheet.Cells.Item(1,2) = “Drive Letter”

    $Sheet.Cells.Item(1,3) = “FileSystem”

    $Sheet.Cells.Item(1,4) = “Size(GB)”

    $Sheet.Cells.Item(1,5) = “FreeSpace(GB)”

    $Sheet.Cells.Item(1,6) = “FreeSpace(%)”

    $WorkBook = $Sheet.UsedRange

    $WorkBook.Interior.ColorIndex = 8

    $WorkBook.Font.ColorIndex = 11

    $WorkBook.Font.Bold = $True

    $intRow = 2

    $machines = get-content -path "c:tempscriptscomputers.txt"

    Foreach ($server in $machines)

    {

    $ping = Test-Connection $server -Quiet

    if ($ping = "True") {

    $colItems = Get-wmiObject Win32_LogicalDisk -computername $server | where-object {$_.DeviceID -eq "C:"}

    $Sheet.Cells.Item($intRow,1) = $colItems.SystemName

    $Sheet.Cells.Item($intRow,2) = $colItems.DeviceID

    $Sheet.Cells.Item($intRow,3) = $colItems.FileSystem

    $Sheet.Cells.Item($intRow,4) = [MATH]::Round(($colItems.Size / 1GB),2)

    $Sheet.Cells.Item($intRow,5) = [MATH]::Round(($colItems.FreeSpace / 1GB),2)

    $Sheet.Cells.Item($intRow,6) = "{0:P2}" -f ($colItems.FreeSpace / $colItems.Size)

    $data = [MATH]::Round(($colItems.FreeSpace * 100 / $colItems.Size ),2)

    If ($data -lt "25"){$Sheet.Cells.Item($intRow,6).Interior.ColorIndex = 3

    {

    $colItemsD = Get-wmiObject Win32_LogicalDisk -computername $server | where-object {$_.DeviceID -eq "D:"}

    $Sheet.Cells.Item($intRow++,1) = $colItemsD.SystemName

    $Sheet.Cells.Item($intRow++,2) = $colItemsD.DeviceID

    $Sheet.Cells.Item($intRow++,3) = $colItemsD.FileSystem

    $Sheet.Cells.Item($intRow++,4) = [MATH]::Round(($colItemsD.Size / 1GB),2)

    $Sheet.Cells.Item($intRow++,5) = [MATH]::Round(($colItemsD.FreeSpace / 1GB),2)

    $Sheet.Cells.Item($intRow++,6) = "{0:P2}" -f ($colItemsD.FreeSpace / $colItemsD.Size)

    $data = [MATH]::Round(($colItemsD.FreeSpace * 100 / $colItemsD.Size ),2)

    If ($data -lt "25"){$Sheet.Cells.Item($intRow+1,6).Interior.ColorIndex = 3}

    }

    }

    $intRow++

    }

    }

    $WorkBook.EntireColumn.AutoFit()

  2. Tom Anderson says:

    This is superb code! Works great for my purpose (reading data from an XLSX).

    Thank you.

    Tom

  3. Jarmo Haaranen says:

    Vaibhav you are filtering only C: drive in this line:

    $colItems = Get-wmiObject Win32_LogicalDisk -computername $server | where-object {$_.DeviceID -eq "C:"}

    It should be:

    $colItems = Get-wmiObject Win32_LogicalDisk -computername $server | where-object {($_.DeviceID-eq "C:")

    -or ($_.DeviceID -eq "D:")}

  4. papa says:

    Ok I need to try your code…I need to read each row or cell (A1) and place it in memory.

    then I want to process a logic based on that data.

    thanks

  5. Orlando says:

    Hi,

    Thanks for the code shared, I have the problem that the WorkBooks member in my COM object is null 🙁 therefore when I want to access it, powershell trows the following exception:

    "You cannot call a method on a null-valued expression."

    Any ideas how can I prevent this? Note that the object itself is not null. i.e. if I test: $xls -eq $null

    I get False as response.

    Thanks for the assistance.

Skip to main content