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.
