發表者討論內容

冷日
(冷日) 發表時間：2015/12/18 3:52

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15773

[轉貼]Get the Number of Rows in Column of a worksheet

Get the Number of Rows in Column of a worksheet

I have an Excel sheet as follows:

Id   Name   Address
-------------------
1    t1     Add1
2           Add2
3

I want to get the count of non-empty cells in each column, i.e.:

    Column ID → 3 rows
    Name → 1 row
    Address → 2 rows

How do I do this in power shell? I tried various things but they don't seem to work.

I am new to PowerShell. Is there a way to get the number of rows for a column directly?

$SheetName = "Sheet1"
$Excel = New-Object -ComObject "Excel.Application"

$Workbook = $Excel.workbooks.open($XLSDoc)
$Sheet = $Workbook.Worksheets.Item($SheetName)
$objRange = $Sheet.UsedRange
$RowCount = $objRange.Rows.Count
$ColumnCount = $objRange.Columns.Count
Write-Host "RowCount:" $RowCount
Write-Host "ColumnCount" $ColumnCount
$Range1 = $Sheet.Range("A1:A5")
$TestCount = $Range1.Rows.Count
Write-Host $TestCount

Try something like this:

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.Open("C:\path\to\your.xlsx")
$ws = $wb.Sheets.Item(1)

$rows = $ws.UsedRange.Rows.Count

foreach ( $col in "A", "B", "C" ) {
  $xl.WorksheetFunction.CountIf($ws.Range($col + "1:" + $col + $rows), "<>") - 1
}

$wb.Close()
$xl.Quit()

原文出處：powershell - Get the Number of Rows in Column of a worksheet - Stack Overflow

回覆

平面展開

前一個主題 | 下一個主題 | |

| |

討論串

[轉貼]PowerShell - Read an Excel file using COM Interface (冷日
(冷日), 2015/12/18 2:36)
[轉貼]Read Excel sheet in Powershell (冷日
(冷日), 2015/12/18 2:51)
[轉貼]How Can I Read from Excel Without Using Excel? (冷日
(冷日), 2015/12/18 2:58)
» [轉貼]Get the Number of Rows in Column of a worksheet (冷日
(冷日), 2015/12/18 3:52)

發表新主題

微軟帝國 : [轉貼]PowerShell - Read an Excel file using COM Interface

討論串