Read Excel sheet in Powershell -

the below script reads sheet names of excel document....

how improve extract contents of column b (starting row 5 - row 1-4 ignored) in each worksheet , create object?

e.g. if column b in worksheet 1 (called london) has following values:

marleybone paddington victoria hammersmith 

and column c in worksheet 2 (called) nottingham has following values:

alverton  annesley arnold askham 

i'd want create object looks this:

city,area london,marleybone london,paddington london,victoria london,hammersmith nottingham,alverton  nottingham,annesley nottingham,arnold nottingham,askham 

this code far:

clear  sheetname = @()      $excel=new-object -com excel.application     $wb=$excel.workbooks.open("c:\users\administrator\my_test.xls")     ($i=1; $i -le $wb.sheets.count; $i++)     {       $sheetname+=$wb.sheets.item($i).name;     }  $sheetname 

this assumes content in column b on each sheet (since it's not clear how determine column on each sheet.) , last row of column last row of sheet.

$xlcelltypelastcell = 11  $startrow,$col=5,2   $excel=new-object -com excel.application $wb=$excel.workbooks.open("c:\users\administrator\my_test.xls")  ($i=1; $i -le $wb.sheets.count; $i++){     $sh=$wb.sheets.item($i)     $endrow=$sh.usedrange.specialcells($xlcelltypelastcell).row     $city=$sh.cells.item($startrow,$col).value2     $rangeaddress=$sh.cells.item($startrow+1,$col).address() + ":" +$sh.cells.item($endrow,$col).address()     $sh.range($rangeaddress).value2 | foreach {         new-object psobject -property @{city=$city;area=$_}     } } $excel.workbooks.close()