Read Excel sheet in Powershell

This was extremely helpful for me when trying to automate Cisco SIP phone configuration using an Excel spreadsheet as the source. My only issue was when I tried to make an array and populate it using $array | Add-Member ... as I needed to use it later on to generate the config file. Just defining an array and making it the for loop allowed it to store correctly.

$lastCell = 11 
$startRow, $model, $mac, $nOF, $ext = 1, 1, 5, 6, 7

$excel = New-Object -ComObject excel.application
$wb = $excel.workbooks.open("H:\Strike Network\Phones\phones.xlsx")
$sh = $wb.Sheets.Item(1)
$endRow = $sh.UsedRange.SpecialCells($lastCell).Row

$phoneData = for ($i=1; $i -le $endRow; $i++)
            {
                $pModel = $sh.Cells.Item($startRow,$model).Value2
                $pMAC = $sh.Cells.Item($startRow,$mac).Value2
                $nameOnPhone = $sh.Cells.Item($startRow,$nOF).Value2
                $extension = $sh.Cells.Item($startRow,$ext).Value2
                New-Object PSObject -Property @{ Model = $pModel; MAC = $pMAC; NameOnPhone = $nameOnPhone; Extension = $extension }
                $startRow++
            }

I used to have no issues adding information to an array with Add-Member but that was back in PSv2/3, and I've been away from it a while. Though the simple solution saved me manually configuring 100+ phones and extensions - which nobody wants to do.

This assumes that the content is in column B on each sheet (since it's not clear how you determine the column on each sheet.) and the last row of that column is also the last row of the sheet.

$xlCellTypeLastCell = 11 
$startRow = 5 
$col = 2 

$excel = New-Object -Com Excel.Application
$wb = $excel.Workbooks.Open("C:\Users\Administrator\my_test.xls")

for ($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()

Sorry I know this is an old one but still felt like helping out ^_^

Maybe it's the way I read this but assuming the excel sheet 1 is called "London" and has this information; B5="Marleybone" B6="Paddington" B7="Victoria" B8="Hammersmith". And the excel sheet 2 is called "Nottingham" and has this information; C5="Alverton" C6="Annesley" C7="Arnold" C8="Askham". Then I think this code below would work. ^_^

$xlCellTypeLastCell = 11 
$startRow = 5

$excel = new-object -com excel.application
$wb = $excel.workbooks.open("C:\users\administrator\my_test.xls")

for ($i = 1; $i -le $wb.sheets.count; $i++)
    {
        $sh = $wb.Sheets.Item($i)
        $endRow = $sh.UsedRange.SpecialCells($xlCellTypeLastCell).Row
        $col = $col + $i - 1
        $city = $wb.Sheets.Item($i).name
        $rangeAddress = $sh.Cells.Item($startRow, $col).Address() + ":" + $sh.Cells.Item($endRow, $col).Address()
        $sh.Range($rangeAddress).Value2 | foreach{
            New-Object PSObject -Property @{City = $city; Area=$_}
        }
    }

$excel.Workbooks.Close()

This should be the output (without the commas):

City, Area
---- ----
London, Marleybone
London, Paddington
London, Victoria
London, Hammersmith
Nottingham, Alverton
Nottingham, Annesley
Nottingham, Arnold
Nottingham, Askham