Posted by: Preetam | May 5, 2008

PowerShell and Excel

This is script is contributed to Frank Koch who created Free book on Powershell. I learnt how to push excel information only through his book. You may like to go through this book as well. Million thanks to Frank.

http://blogs.msdn.com/powershell/archive/2007/05/11/free-powershell-book.aspx

=====================================================================

#This cmdlet picks the servers from workstations.txt one by one, and collects IP details of each server and pushes them #into excel sheet

$WKSs=get-content Workstations.txt                                #Workstation/Server list
$ExcelSheet=new-object -comobject Excel.application    #Creates excel com object
$WorkBook=$ExcelSheet.WorkBooks.add(1)                       #when you first open excel it creates workbook
$WorkSheet=$WorkBook.WorkSheets.item(1)                    #Under book create a worksheet 
$WorkSheet.cells.item(1,1)=”HostName”                            #Creates first row basically heading
$WorkSheet.cells.item(1,2)=”IPAddress”                            #like hostname,IPAddress,SubnetMask,
$WorkSheet.cells.item(1,3)=”Subnet”                                #Default gateway,DNSDomain,MAC,Description
$WorkSheet.cells.item(1,4)=”Default Gateway”                 #DNSSuffix
$WorkSheet.cells.item(1,5)=”DNSDomain”
$WorkSheet.cells.item(1,6)=”MAC”
$WorkSheet.cells.item(1,7)=”Description”
$WorkSheet.cells.item(1,7)=”DNS Suffix Order”
$i=2

Foreach($WKS in $WKSs) {
$NICCard=Get-WmiObject win32_networkadapterconfiguration -COMPUTER $WKS
foreach($NIC in $NICCard){
if($NIC.ipenabled -eq $true)
{
Write-host Hostname => $NIC.DNSHostName
$WorkSheet.cells.item($i,1)=$NIC.DNSHostName
$WorkSheet.cells.item($i,2)=$NIC.ipaddress
$WorkSheet.cells.item($i,3)=$NIC.IPSubnet
$WorkSheet.cells.item($i,4)=$NIC.DefaultIPGateway
$WorkSheet.cells.item($i,5)=$NIC.DNSDomain
$WorkSheet.cells.item($i,6)=$NIC.MACAddress
$WorkSheet.cells.item($i,7)=$NIC.Description
$WorkSheet.cells.item($i,8)=$NIC.DNSDomainSuffixSearchOrder
$i=$i+1                                                                                      #Remember here you say go to another column
}
}
}
$ExcelSheet.visible=$true

===========================================================================

OutPut in Excel Looks like  

 

image


Responses

  1. Attempting to put $worksheet.cell.item(2,3) into a variable as integer. How can this be achieved?

    Example: Increment the value of (3,2) by 1, then replace the value on the spreadsheet.

    I was attempting a few
    $Value = $worksheet.cell.item(2,3)
    $Value++
    $worksheet.cell.item(2,3) = $Value

    But it comes out as System.COMObject

  2. I found the answer, but also discovered some more interesting stuff…

    $Value = $worksheet.cell.item(2,3).tointeger()
    $Value++
    $worksheet.cell.item(2,3) = $Value

    Which will output the new incremented result to your excel spreadsheet.

    Then I also found this

    $RandomText = $worksheet.cell.item(1,1).text
    Write-Host “Here’s my text: $RandomText”
    (Output) >> Here’s my text: HostName

    That is different altogether for strings and integers.

    Thought it might help people out there.

  3. The students then choose the project they want to work on. ,

  4. […] found a nice script on the blog post PowerShell and Excel (from the “Powershell,Passion,Persistence and Pursuit” blog).  This script reads a […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: