Прочтите лист Excel в Powershell

Ниже script читаются имена листов документа Excel.

Как я мог улучшить его, чтобы он мог извлечь все содержимое столбца B (начиная с строки 5, так что строка 1-4 игнорируется) на каждом листе и создавать объект?

например. если столбец B на листе 1 (называемый Лондоном) имеет следующие значения:

Marleybone
Paddington
Victoria
Hammersmith

и столбец C на листе 2 (называемый) Nottingham имеет следующие значения:

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

Ответ 1

Это предполагает, что содержимое находится в столбце 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()

Ответ 2

Жаль, что я знаю, что это старый, но все равно хотелось помочь ^ _ ^

Возможно, так, как я это читал, но предполагая, что листок Excel 1 называется "Лондон" и имеет эту информацию; B5 = "Марлибон" B6 = "Паддингтон" B7 = "Виктория" B8 = "Хаммерсмит". И лист excel 2 называется "Ноттингем" и имеет эту информацию; C5 = "Alverton" C6 = "Annesley" C7 = "Arnold" C8 = "Askham". Тогда я думаю, что этот код ниже будет работать. ^ _ ^

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

Это должен быть выход (без запятых):

Город, Площадь
---- ----
Лондон, Марлибон
Лондон, Паддингтон
Лондон, Виктория
Лондон, Хаммерсмит
Ноттингем, Альвертон, Ноттингем, Аннсли
Ноттингем, Арнольд
Ноттингем, Асхам

Ответ 3

Это было чрезвычайно полезно для меня, когда я пытался автоматизировать настройку SIP-телефона Cisco с использованием электронной таблицы Excel в качестве источника. Моя единственная проблема заключалась в том, что я пытался создать массив и заполнить его, используя $array | Add-Member ..., так как позже мне понадобилось использовать его для создания файла конфигурации. Просто определив массив и сделав его циклом for, он сможет правильно храниться.

$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++
            }

Раньше у меня не было проблем с добавлением информации в массив с помощью Add-Member, но это было в PSv2/3, и я некоторое время от нее не уходил. Хотя простое решение избавило меня от ручной настройки 100+ телефонов и добавочных номеров, что никто не хочет делать.

Ответ 4

Есть возможность сделать что-то действительно классное!

# 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 "," }

Ответ 5

У меня есть сложный файл Excel, может ли кто-нибудь помочь мне прочитать данные из него?