Creating a Simple Excel Sheet
PowerShell is power full enough to create Excel sheets too. Lets Check it out.
What we are going to do ?
Here we are going to create an excel sheet , which contains the list of running process in a Computer with following fields.
- Process Name :- Description of the Process.
- Company:- Company name, Who created the file executing by the process.
- Name:- Actual name of the Process.
- File path:- Path of the executable
- When Started:- Start time of the process.
We use Get-Process cmdlet to get the list of running process in a computer.
PS C:\> Get-Process Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName ------- ------ ----- ----- ----- ------ -- ----------- 209 15 3708 11020 102 1.28 1728 AdobeARM 115 9 1352 6368 ...84 0.05 2708 alg 529 31 28324 22616 ...45 1.69 4356 ApplicationFrameHost 404 17 5232 16640 ...56 0.27 4372 browser_broker 162 10 1652 7296 62 0.09 6676 CAudioFilterAgent64 53 6 1612 296 ...78 0.05 7588 conhost 68 8 2268 6840 ...04 0.30 7804 conhost 355 16 1272 4248 ...07 0.61 584 csrss 531 18 2080 8572 ...06 13.22 1504 csrss 165 9 1596 7384 65 0.03 2096 CxAudMsg64 98 6 908 4564 ...73 0.02 2420 dasHost 163 11 4032 8432 84 0.13 4168 DCSHelper 751 49 73608 34940 ...20 351.70 2196 dwm 946 45 37580 47948 434 2.31 6368 EXCEL 1690 84 53784 89504 ...22 36.19 4976 explorer 198 14 4064 11444 ...69 0.38 3520 FlashUtil_ActiveX 36 8 816 3532 ...08 0.05 856 fontdrvhost 147 10 17432 7492 87 2.53 2068 HWDeviceService64 920 52 57544 72836 446 6.39 8092 HxMail 336 18 5300 17632 119 0.33 3964 HxTsr 0 0 0 4 0 0 Idle 162 14 14404 11868 ...27 1.14 4240 ieetwcollector 921 114 76960 84404 487 38.05 748 iexplore
Above results are the results by default PowerShell gives for Get-Process cmdlet, But we have more
PS C:\> Get-Process|select -First 1|Format-List *
__NounName : Process
Name : AdobeARM
Handles : 209
VM : 106840064
WS : 10862592
PM : 3796992
NPM : 15808
Path : C:\Program Files (x86)\Common Files\Adobe\ARM\1.0\AdobeARM.exe
Company : Adobe Systems Incorporated
CPU : 1.28125
FileVersion : 1.4.7.0
ProductVersion : 1.4.7.0
Description : Adobe Reader and Acrobat Manager
Product : Adobe Reader and Acrobat Manager
Id : 1728
PriorityClass : Normal
HandleCount : 209
WorkingSet : 10862592
PagedMemorySize : 3796992
PrivateMemorySize : 3796992
VirtualMemorySize : 106840064
TotalProcessorTime : 00:00:01.2812500
BasePriority : 8
ExitCode :
HasExited : False
ExitTime :
Handle : 1656
SafeHandle : Microsoft.Win32.SafeHandles.SafeProcessHandle
MachineName : .
MainWindowHandle : 0
MainWindowTitle :
MainModule : System.Diagnostics.ProcessModule (AdobeARM.exe)
MaxWorkingSet : 1413120
MinWorkingSet : 204800
Modules : {System.Diagnostics.ProcessModule (AdobeARM.exe), System.Diagnostics.ProcessModule (ntdll.dll), System.Diagnostics.ProcessModule (wow64.dll),
System.Diagnostics.ProcessModule (wow64win.dll)...}
NonpagedSystemMemorySize : 15808
NonpagedSystemMemorySize64 : 15808
PagedMemorySize64 : 3796992
PagedSystemMemorySize : 210344
PagedSystemMemorySize64 : 210344
PeakPagedMemorySize : 6316032
PeakPagedMemorySize64 : 6316032
PeakWorkingSet : 60264448
PeakWorkingSet64 : 60264448
PeakVirtualMemorySize : 174739456
PeakVirtualMemorySize64 : 174739456
PriorityBoostEnabled : True
PrivateMemorySize64 : 3796992
PrivilegedProcessorTime : 00:00:00.3906250
ProcessName : AdobeARM
ProcessorAffinity : 3
Responding : True
SessionId : 2
StartInfo : System.Diagnostics.ProcessStartInfo
StartTime : 9/20/2015 11:20:57 AM
SynchronizingObject :
Threads : {2508, 2208, 4412, 5816...}
UserProcessorTime : 00:00:00.8906250
VirtualMemorySize64 : 106840064
EnableRaisingEvents : False
StandardInput :
StandardOutput :
StandardError :
WorkingSet64 : 10862592
Site :
Container :
So, Selecting the required Properties and Then creating an Excel. Lets go,
$Excel=New-Object -ComObject 'Excel.Application'
Here we created a ComObject for Microsoft Excel and stored it into a variable $Excel
$Workbook=$Excel.Workbooks.Add()
Adds a work book in The new ComObject $Excel
$WorkSheet=$Workbook.Worksheets.Item(1)
Creates a WorkSheet in The new WorkBook $WorkSheet
$Workbook.saveas("$env:HOMEPATH\Desktop\Process.xlsx")
Adds the save Location For the excel File.
$WorkSheet.Cells.Item(1,1)='Process Name'
Creates Value for the first cell, First Row of First Column ($WorkSheet.Cells.Item(1,1)='Process Name')
$WorkSheet.Cells.Item(1,2)='Company'
$WorkSheet.Cells.Item(1,3)='Name'
$WorkSheet.Cells.Item(1,4)='File Path'
$WorkSheet.Cells.Item(1,5)='When Started'
$ColumnInfo=$worksheet.UsedRange
The above code stores the properties of UsedRanges to $ColumnInfo
$ColumnInfo.Interior.ColorIndex=11
Sets the BackGround color of the UsedCells( Here its the first row of first 5 Columns.)
$ColumnInfo.Font.ColorIndex=20
Sets the ForeGround color of the UsedCells( Here its the first row of first 5 Columns.)
$ColumnInfo.Font.Bold=$true
Sets Font to Bold
$RowNum=2
Sets the Value if the Variable $RowNum to 2(is used as incrementing variable Later and represents Row number)
$Cells=$WorkSheet.Cells
Storing Properties of Cells to the new variable named Cells.
here We Creates a function which is responsible for storing value in respective cells based on conditions and modifies the border of each cell.
This function contains three parameters,
- $Item :- Receives the value for selected the Property for each process.
- $Row :- Receives the Row number.
- $Col :- Receives the Column number.
{
$Cells.Item($Row,$Col)=if ($Item -eq $null){'No Description';$Cells.Item($Row,$Col).font.colorindex=38}
else
{
$Item
}
$Cells.Item($Row,$Col).borders.weight = 4
}
Get-Process|ForEach-Object{
EditCell -Item $_.Description -Row $RowNum -Col 1
Above line Calls the function by passing the parameters.
EditCell -Item $_.Company -Row $RowNum -Col 2
EditCell -Item $_.Name -Row $RowNum -Col 3
EditCell -Item $_.Path -Row $RowNum -Col 4
EditCell -Item $_.StartTime -Row $RowNum -Col 5
$RowNum++
Incremets the row
}
$ColumnInfo.EntireColumn.AutoFit()
Sets entire column to Autofit.
$Workbook.save()
Saves the WorkBook to Predefined Location
$Excel.Quit()
Complete Script,
$Excel=New-Object -ComObject 'Excel.Application'
$Workbook=$Excel.Workbooks.Add()
$WorkSheet=$Workbook.Worksheets.Item(1)
$Workbook.saveas("$env:HOMEPATH\Desktop\Process.xlsx")
# $WorkSheet.Cells.Item(row number,column number)
$WorkSheet.Cells.Item(1,1)='Process Name'
$WorkSheet.Cells.Item(1,2)='Company'
$WorkSheet.Cells.Item(1,3)='Name'
$WorkSheet.Cells.Item(1,4)='File Path'
$WorkSheet.Cells.Item(1,5)='When Started'
$ColumnInfo=$worksheet.UsedRange # All ready used calomns
$ColumnInfo.Interior.ColorIndex=11
$ColumnInfo.Font.ColorIndex=20
$ColumnInfo.Font.Bold=$true
$RowNum=2
$Cells=$WorkSheet.Cells
function EditCell ($Item,$Row,$Col)
{
$Cells.Item($Row,$Col)=if ($Item -eq $null){'No Description';$Cells.Item($Row,$Col).font.colorindex=38} else {$Item}
$Cells.Item($Row,$Col).borders.weight = 4
}# Function that changes cell Color,Adds Cell value based on condition and border
Get-Process|ForEach-Object{
EditCell -Item $_.Description -Row $RowNum -Col 1
EditCell -Item $_.Company -Row $RowNum -Col 2
EditCell -Item $_.Name -Row $RowNum -Col 3
EditCell -Item $_.Path -Row $RowNum -Col 4
EditCell -Item $_.StartTime -Row $RowNum -Col 5
$RowNum++
}
$ColumnInfo.EntireColumn.AutoFit()
$Workbook.save()
$Excel.Quit()
References: Excel Boarder Properties with PowerShell , How Can I Use Windows PowerShell to Automate Microsoft Excel?
No comments:
Post a Comment