How to export hash table Name and value into Excel columns?

Question

I have a hash table which consists of Component name and Codecount like this:

Name            Value
-----           ------
Comp1           2000
Comp2           3000

If it is exported into Excel, it will be easy to present.

How do I export this hash table from PowerShell into Excel?

score 12 · Accepted Answer · answered Feb 22 '12 at 12:04

12

Another vote for export-csv

&{$hash.getenumerator() |
  foreach {new-object psobject -Property @{Component = $_.name;Codecount=$_.value}}
 } | export-csv codecounts.csv -notype

answered Feb 22 '12 at 12:04

mjolinor

66,130
7
114
135

`Export-CSV` converts to one column and not several. One should use `export-excel` or a solution from @cb. – Timo Jun 04 '20 at 07:31

score 6 · Answer 2 · edited Sep 14 '13 at 07:35

To create an Excel file use something like this:

$ht = @{"comp1"="2000";"comp2"="3000"}
$excel = new-Object -comobject Excel.Application
$excel.visible = $true # set it to $false if you don't need monitoring the actions...
$workBook = $excel.Workbooks.Add()
$sheet =  $workBook.Sheets.Item(1)
$sheet.Name = "Computers List"
$sheet.Range("A1","A2").ColumnWidth = 40
$sheet.range('A:A').VerticalAlignment = -4160 #align is center (TOP -4108 Bottom -4107 Normal)

$sheet.Cells.Item(1,1) = "Name"
$sheet.cells.Item(1,2) = "Value"

$index = 2

$ht.keys | % {  

    $sheet.Cells.Item($index,1) = $_
    $sheet.Cells.Item($index,2) = $ht.item($_)
    $index++
}

$workBook.SaveAs("C:\mylist.xls")
$excel.Quit()

Remember that the Excel process need to be killed in task manager or use this function:

function Release-Ref ($ref) {

    [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$ref) | out-null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}

And modify last script's lines like this:

Release-Ref $workbook
Release-Ref $sheet

$excel.Quit()

release-Ref $excel

Why has the excel process to be deleted? – Timo Jun 04 '20 at 08:03 — Timo, Jun 04 '20 at 08:03

score 1 · Answer 3 · answered Feb 22 '12 at 10:06

1

Use Export-CSV and open that CSV file with Excel

answered Feb 22 '12 at 10:06

Ocaso Protal

19,362
8
76
83

The question is about exporting a hashtable and I would have expected to get info about the export of this specific data structure. `Export-CSV` converts to one column and not several. – Timo Jun 04 '20 at 07:29

score 1 · Answer 4 · answered Feb 22 '12 at 10:12

1

Consider a hashtable:

$ht = @{"c1" = 100; "c2" = 200}

Iterate all the keys and add hashtable key and value into a file:

$ht.keys | % { add-content -path myFile.csv -value $("{0},{1}" -f $_, $ht.Item($_)) }

answered Feb 22 '12 at 10:12

vonPryz

22,996
7
54
65

1

You will get problems with this solution when your hashtable keys contain a "," – Ocaso Protal Feb 22 '12 at 10:33
`*.CSV` converts to one column and not several. – Timo Jun 04 '20 at 07:30

How to export hash table Name and value into Excel columns?

4 Answers4