[excel] Read Excel sheet in Powershell

The below script reads the sheet names of an Excel document....

How could I improve it so it could extract all the contents of column B (starting from row 5 - so row 1-4 are ignored) in each worksheet and create an object?

E.g. if column B in worksheet 1 (called London) has the following values:

Marleybone
Paddington
Victoria
Hammersmith

and column C in worksheet 2 (called) Nottingham has the following values:

Alverton 
Annesley
Arnold
Askham

I'd want to create a object that from that looks like this:

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

This is my code so far:

clear all

sheetname = @()

    $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++)
    {
      $sheetname+=$wb.Sheets.Item($i).Name;
    }

$sheetname

This question is related to excel powershell

The answer is


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.


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


There is the possibility of making something really more cool!

# Powershell 
$xl = new-object -ComObject excell.application
$doc=$xl.workbooks.open("Filepath")
$doc.Sheets.item(1).rows |
% { ($_.value2 | Select-Object -first 3 | Select-Object -last 2) -join "," }