파워쉘-엑셀파일 읽기

 

 

$fileName = 'c:\powershell\test.xlsx'

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($fileName)
$workSheet = $Workbook.Sheets.Item(1)

Write-Host $workSheet.Cells.Item(1,1).Text
Write-Host $workSheet.Cells.Item(1,2).Text
Write-Host $workSheet.Cells.Item(1,3).Text

Write-Host $workSheet.Cells.Item(2,1).Text
Write-Host $workSheet.Cells.Item(2,2).Text
Write-Host $workSheet.Cells.Item(2,3).Text

$objExcel.Quit()


실행결과
a1
b1
c1
a2
b2
c2

 

 

 

데이터 열 개수 만큼 반복문 실행

$fileName = 'c:\powershell\temp\test.xlsx'
$arrRow = 1,2
$arrCol = 1..3

$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open($fileName)
$workSheet = $Workbook.Sheets.Item(1)

$rows = ($workSheet.UsedRange.Rows).Count

for ($i = 1; $i -le $rows; $i++){
    $workSheet.Cells.Item($i,1).Text
    $workSheet.Cells.Item($i,2).Text
} 

$objExcel.Quit()