在 Powershell 中读取 Excel 工作表 [英] Read Excel sheet in Powershell

问题描述

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

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

我该如何改进它才能提取每个工作表中 B 列的所有内容(从第 5 行开始 - 因此忽略第 1-4 行)并创建一个对象?

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?

例如如果工作表 1(称为伦敦)中的 B 列具有以下值:

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

Marleybone
Paddington
Victoria
Hammersmith

和工作表 2(称为)Nottingham 中的 C 列具有以下值:

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

这是我目前的代码:

clear all

sheetname = @()

    $excel=new-object -com excel.application
    $wb=$excel.workbooks.open("c:usersadministratormy_test.xls")
    for ($i=1; $i -le $wb.sheets.count; $i++)
    {
      $sheetname+=$wb.Sheets.Item($i).Name;
    }

$sheetname

推荐答案

这里假设内容在每张工作表的 B 列中(因为不清楚如何确定每张工作表上的列.)和最后一行列也是工作表的最后一行.

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

这篇关于在 Powershell 中读取 Excel 工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！