Powershell에서 Excel 시트 읽기

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

오래된 것인 건 알지만 그래도 도와주고 싶은 마음이 들어요 ^_^

아마 제가 읽은 방식일 수도 있지만 엑셀 시트 1이 "런던"이라고 불리고 이러한 정보를 가지고 있다고 가정하면 B5="Marleybone" B6="Paddington" B7="Victoria" B8="Hammersmith"입니다.그리고 엑셀 시트 2는 "노팅엄"이라고 불리며 이 정보를 가지고 있습니다; C5="앨버턴" 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()

쉼표를 제외한 출력은 다음과 같습니다.

도시, 면적
---- ----
런던, 말리본
런던, 패딩턴
런던, 빅토리아
런던, 해머스미스
노팅엄, 앨버턴
노팅엄, 앤즐리
노팅엄, 아놀드
노팅엄, 애스컴

이것은 Excel 스프레드시트를 소스로 사용하여 Cisco SIP 전화기 구성을 자동화할 때 매우 유용했습니다.유일한 문제는 어레이를 만들고 다음을 사용하여 채우려고 했을 때였습니다.$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개 이상의 전화기와 내선을 수동으로 구성할 수 있었지만 아무도 원하지 않았습니다.

정말 더 멋진 것을 만들 수 있는 가능성이 있습니다!

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

OfficeOpenXml을 사용하는 ImportExcel을 사용할 수 있습니다.

언급URL : https://stackoverflow.com/questions/19211632/read-excel-sheet-in-powershell