powershell excel get first row(header) column count

Question

I'm trying to count the cell number of the first row (A1-D1) which is known as header and get that count as the counter.

As all the while find most of them using Usedrange to count the columns:

$headercolcount=($worksheet.UsedRange.Columns).count

But UsedRange will capture maximum count in the whole activesheet, which resulting not identical to the column count in first row if there is extra content data below the header.

I only wish to grab just the first row:

[ Sample excel view ]

Update: For clearer view, here is an example. As 1F & 1G there are no value present, so the answer should be 5 as 1A-1E as it contains data. So how should I grab the 5 correctly?

[ enter image description here ]

Not familiar with PowerShell but you want something like $headercolcount = $worksheet.UsedRange.Rows(1).Columns.Count The UsedRange is always a rectangular area though, so $worksheet.UsedRange.Columns.Count would be equivalent — Tim Williams
– Tim Williams, Commented Oct 12, 2017 at 6:37
yea that what i meaning, i also tried as you proposed $headercolcount = $worksheet.UsedRange.Rows(1).Columns.Count before and it's not working too, it will not counting the column, it resulting in counting the characters i think so as it resulting in large value. — Yong Cai
– Yong Cai, Commented Oct 12, 2017 at 6:40

Snak3d0c · Accepted Answer · 2017-10-12 15:10:24Z

    Get-Process excel | Stop-Process -Force
# Specify the path to the Excel file and the WorkSheet Name
$FilePath = "C:\temp\A_A.xlsx"
$SheetName = "Blad1" # In english this is probably Sheet1
# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
$objExcel.DisplayAlerts = $false
# Open Excel file and in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load WorkSheet 'Blad 1' in variable Worksheet
$WorkSheet = $WorkBook.sheets.item($SheetName)
$xlup = -4162
$lastRow = $WorkSheet.cells.Range("A1048576").End($xlup).row

# get the highest amount of columns
$colMax = ($WorkSheet.UsedRange.Columns).count
# initiatie a counter
$count = $null
# set the column you'd like to count
$row = 1

for ($i = 0; $i -le $colMax; $i++){
    if($worksheet.rows.Item("$row").columns.Item($i+1).text){
        $count++
    }    
}

$count

This should work. It takes the highest amount of columns. It then loops until it reaches that amount. During the loop it checks if the cell on that row is filled or not, if it is, it adds to the counter.

If you have millions of lines, this might not be the best way but this works for me.

I've testes it with an excel file:

With

$row = 1 this will give : 5
$row = 2 this will give : 6
$row = 3 this will give : 7
$row = 4 this will give : 8

Snak3d0c · Accepted Answer · 2017-10-12 07:29:47Z

0

# Specify the path to the Excel file and the WorkSheet Name
$FilePath = "C:\temp\A_A.xlsx"
$SheetName = "Blad1" # In english this is probably Sheet1
# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
$objExcel.DisplayAlerts = $false
# Open Excel file and in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load WorkSheet 'Blad 1' in variable Worksheet
$WorkSheet = $WorkBook.sheets.item($SheetName)
$xlup = -4162
$lastRow = $WorkSheet.cells.Range("A1048576").End($xlup).row
$amountofcolumns = $worksheet.UsedRange.Rows(1).Columns.Count   
#OUTPUT
write-host "Last Used row:" $lastRow 
Write-host "Amount of columns" $amountofcolumns
#show all columnnames
for($i =  1 ; $i -le $amountofcolumns; $i++){
    $worksheet.Cells.Item(1,$i).text
}

This will show you how many rows you have AND will show you all values in the first row , ergo your titles.

answered Oct 12, 2017 at 7:29

Snak3d0c

6264 silver badges12 bronze badges

4 Comments

Yong Cai Over a year ago

Not working, still unable to capture first row number of column that containing the data.

Snak3d0c Over a year ago

Yeah so "not working" -> not very informative. Did you change the $Sheetname into English format? Or in your language if you have Excel in another language.

Yong Cai Over a year ago

Yes, I did named it to Book1. I tried and run your code against the above updated example excel file. The first row that containing data are 5. However your code shows amount of columns = 6 as the code count until 1F i think so. Sorry if there is any poor explanations. Here is result: write-host "Last Used row:" $lastRow Last Used row: 4 Write-host "Amount of columns" $amountofcolumns Amount of columns 6 for($i = 1 ; $i -le $amountofcolumns; $i++){ $worksheet.Cells.Item(1,$i).text } s sd dssd dsdsd 21321

Tim Williams Over a year ago

As I mentioned above, UsedRange is always a regular rectange - it will enclose all of the data on the sheet, and extends to the right as far as the right-most entry on any row. It doesn't have different number of columns per row

Collectives™ on Stack Overflow

powershell excel get first row(header) column count

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related