在 Powershell 中读取 Excel 工作表

下面的脚本读取 Excel 文档的工作表名称......

我该如何改进它，以便它可以提取每个工作表中 B 列的所有内容（从第 5 行开始 - 因此第 1-4 行被忽略）并创建一个对象？

例如。如果工作表 1 中的 B 列（称为伦敦）具有以下值：

Marleybone
Paddington
Victoria
Hammersmith

工作表 2 中的 C 列（称为诺丁汉）具有以下值：

Alverton 
Annesley
Arnold
Askham

我想创建一个如下所示的对象：

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

到目前为止，这是我的代码：

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

这假设内容位于每张工作表的 B 列中（因为不清楚如何确定每张工作表上的列。）并且该列的最后一行也是工作表的最后一行。

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