[Powershell] export info to Excel
There is no cmdlet available to export content directly to Excel. Sure you can use the ‘Export-csv’ cmdlet exporting the object(s) to a comma delimited file and use the generated file as a base.
But nevertheless you can address Excel via the com interface. Powershell facilitates the use of com objects via the ‘new-object’ cmdlet in conjunction with the –-ComObject parameter.
1: $excel = New-Object -comobject Excel.Application
Once we have our Excel object, we can start adding a workbook and worksheets to the newly created workbook accordingly.
1: #Add a workbook
2: $workbooks = $excel.Workbooks.Add()
3: #Select the Worksheet collection in the workbook (standard default = 3)
4: $worksheets = $workbooks.Worksheets
5: #Select the first worksheet
6: $worksheet = $worksheets.Item(1)
Now that we have an active sheet, we can start adding data. lets get all the running processes and dump the information gathered into the sheet.
1: #Now collect the data we want to export
2: $processes = Get-Process
3: $lastcell = $processes.Count
4: for ($index = 0; $index -lt $lastcell; $index++) {
5: $worksheet.Cells.Item($index+1,1) = $processes[$index].Id
6: $worksheet.Cells.Item($index+1,2) = $processes[$index].ProcessName
7: }
As we are not limited to inserting data, we can easily add some calculations to the sheet as well using the excel functions.
1: $cell = $worksheet.Cells.Item(3,3)
2: $cell.Formula = "=TODAY()"
As we all now, graphs pretty things up and management is fond of them.
So let’s add a simple column chart to the current Workbook to visualize the data.
1: #Lets select our data range
2: $range = $worksheet.usedRange
3: #Now add our charts to the workbook
4: $workbooks.Charts.Add() | out-null
5: #and select a chart type
6: $workbooks.ActiveChart.chartType = [microsoft.office.interop.excel.xlChartType]::xlConeCol
7: #Set the data source
8: $workbooks.ActiveChart.SetSourceData($range)
For more information regarding the Charttypes, see MSDN Excel Charttype Enumeration.
Now lets use a template excel file and dumb the running process data into the predefined range.
1: #Create the Excel com object
2: $excel = New-Object -comobject Excel.Application
3: #Open the workbook
4: $workbooks = $excel.Workbooks.Open("C:\Users\StijnC\Documents\Test.xlsx")
5: #get a hold of all the worksheets
6: $worksheets = $workbooks.Worksheets
7: #Select the first worksheet
8: $worksheet = $worksheets.Item(1)
9: #Now collect the data we want to export
10: $processes = Get-Process
11: $lastcell = $processes.Count
12: for ($index = 0; $index -lt $lastcell; $index++) {
13: $worksheet.Cells.Item($index+1,1) = $processes[$index].Id
14: $worksheet.Cells.Item($index+1,2) = $processes[$index].ProcessName
15: }
The last action that still remains is saving the excel file and disposing the objects.
1: #close workbook and dispose the objects
2: $workbooks.Save()
3: $excel.Quit()
4: $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet)
5: $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheets)
6: $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbooks)
7: $dummy = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
For some reason the Excel process keeps on running.So we kill all excel processes as well.
1: #if any processes left, kill them
2: if (ps excel) { kill -name excel}
So its really up to you to define all formatting in a template or create everything from scratch.
Now, this all is quite easy when using USA language settings, try using different settings and all this breaks.
Even setting the language settings explicit does not solve the problem. You need to define the language settings every time.
The previously defined settings are only valid for the current thread. As Powershell uses a different thread for each command line, this solution is not really feasible.
You could go for a superb function “Using-Culture”, more info can be found on the Powershell Team Blog.
Or we could make sure that all our Excel programming runs in the same thread.
Welcome “script blocks”!
1: & {
2: #Create a Culture
3: $enUS = [System.Globalization.CultureInfo]::CreateSpecificCulture("en-US")
4: #Set the Current thread Culture to en-US
5: [System.Threading.Thread]::CurrentThread.CurrentCulture = $enUS
6: #Create the Excel com object
7: $excel = New-Object -comobject Excel.Application
8: #Open the workbook
9: $workbooks = $excel.Workbooks.Add()
10: $worksheets = $workbooks.Worksheets
11: #Select the first worksheet
12: $worksheet = $worksheets.Item(1)
13: #Now collect the data we want to export
14: $processes = Get-Process
15: $lastcell = $processes.Count
16: for ($index = 0; $index -lt $lastcell; $index++) {
17: $worksheet.Cells.Item($index+1,1) = $processes[$index].ProcessName
18: $worksheet.Cells.Item($index+1,2) = $processes[$index].WS
19: }
20: #Lets select our data range
21: $range = $worksheet.usedRange
22: #Now add our charts to the workbook
23: $workbooks.Charts.Add() | out-null
24: #and select a chart type
25: $workbooks.ActiveChart.chartType = [microsoft.office.interop.excel.xlChartType]::xlConeCol
26: #Set the data source
27: $workbooks.ActiveChart.SetSourceData($range)
28: $workbooks.SaveAs('C:\Users\StijnC\Documents\Process.xlsx')
29: $excel.Quit()
30: }
we define the full script as a “script block” and all the magic just works. once we defined the locale.
