Freigeben über

Excel with PowerShell

  • Artikel

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

  • Anonymous
    October 20, 2013
    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()

  • Anonymous
    October 24, 2013
    This is superb code! Works great for my purpose (reading data from an XLSX). Thank you. Tom

  • Anonymous
    November 13, 2013
    The comment has been removed

  • Anonymous
    September 19, 2014
    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

  • Anonymous
    May 21, 2015
    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.