對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2015/12/18 2:51:25
Read Excel sheet in Powershell
The below script reads the sheet names of an Excel document....
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?
E.g. if column B in worksheet 1 (called London) has the following values:
and column C in worksheet 2 (called) Nottingham has the following values:
I'd want to create a object that from that looks like this:
This is my code so far:
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.
冷日說明：
這個看起來值得學習，回覆者雖然用預定義[coee]
$xlCellTypeLastCell = 11
$startRow,$col=5,2[/code]
來解決提問者的問題。
但事實上，只要能知道 Row、Col，或是取得 RowMax 以及 ColMax 的話，就可以用 for 迴圈下去做！
剩下的控制冷日就不多說啦，看你要撈哪個欄位囉...
原文出處：Read Excel sheet in Powershell - Stack Overflow/
The below script reads the sheet names of an Excel document....
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?
E.g. if column B in worksheet 1 (called London) has the following values:
Marleybone
Paddington
Victoria
Hammersmith
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
This is my code so far:
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
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,$col=5,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=$_}
}
}
冷日說明：
這個看起來值得學習，回覆者雖然用預定義[coee]
$xlCellTypeLastCell = 11
$startRow,$col=5,2[/code]
來解決提問者的問題。
但事實上，只要能知道 Row、Col，或是取得 RowMax 以及 ColMax 的話，就可以用 for 迴圈下去做！
$WorkSheet = $WorkBook.sheets.item($SheetName)
$objRange = $WorkSheet.UsedRange
$RowMax = $objRange.Rows.Count
$ColMax = $objRange.Columns.Count
剩下的控制冷日就不多說啦，看你要撈哪個欄位囉...
原文出處：Read Excel sheet in Powershell - Stack Overflow/