對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2015/12/18 3:52:41
Get the Number of Rows in Column of a worksheet
I have an Excel sheet as follows:
I want to get the count of non-empty cells in each column, i.e.:
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?
Try something like this:
原文出處：powershell - Get the Number of Rows in Column of a worksheet - Stack Overflow
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