Exporting Data to Excel using PowerShell

I had to get a quick audit of systems and i needed to know OS Version, SP version, Memory and Disk Details.

One problem is that if you don’t know what disks you have exporting this to excel is difficult. I chose to dynamically create the headers for the excel file based on the drive letter names. This approach is by no means perfect as you rely on the fact that people have built the systems with sequential drive letters. Any drive letter out of sequence will disturb the excel header names.

Example: If you have 3 servers and two have letters C: and D: and the third has C: and E: this approach will not work for you. Luckily I know my servers have sequential drive letters.

Code:

$excel = New-Object -comobject Excel.Application
$excel.visible = $True
$wbook = $excel.Workbooks.Add()
$wsheet = $wbook.Worksheets.Item(1)
$wsheet.Cells.Item(1,1) = "Date"
$wsheet.Cells.Item(1,2) = "Server"
$wsheet.Cells.Item(1,3) = "Memory"
$wsheet.Cells.Item(1,4) = "OSName"
$wsheet.Cells.Item(1,5) = "SPVersion"
$iRow = 2
$InputFile = "C:\Server.txt" #Input File Containing Servers to be Scanned
$Servers = Get-Content $InputFile
ForEach($Server in $Servers) { $wsheet.Cells.Item($iRow,1) = Get-Date
$wsheet.Cells.Item($iRow,2) =$Server
$AllCS = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $Server | Select-Object TotalPhysicalMemory
ForEach($CS In $AllCS){$wsheet.Cells.Item($iRow,3) = $CS.TotalPhysicalMemory/1GB}
$AllOS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Server
ForEach($OS In $AllOS){$OSVersion = $OS.Caption;$SPVersion = $OS.CSDVersion}
$wsheet.Cells.Item($iRow,4) = $OSVersion
$wsheet.Cells.Item($iRow,5) = $SPVersion
$logicalDisk = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $Server -Filter "DriveType=3" | Select-Object Caption,Size
$j = 6
For($i=0;$i -le $LogicalDisk.Length - 1; $i++){ $wsheet.Cells.Item(1,$j) = $LogicalDisk[$i].Caption
$wsheet.Cells.Item($iRow,$j) = $LogicalDisk[$i].Size/1GB $j++} $iRow++ }
$range = $wsheet.UsedRange
$range.Interior.ColorIndex = 19
$range.Font.ColorIndex = 11
$range.Font.Bold = $True
$range.EntireColumn.AutoFilter()
$range.EntireColumn.AutoFit()
$excel.ActiveWorkbook.SaveAs("C:\Server_Data.xls")
$excel.ActiveWorkbook.Close
$excel.Application.Quit
If (ps excel) { kill -name excel}

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

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