Read Excel sheet in Powershell

Question

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

Answer 1

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

Answer 2

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