Powershell – Import Excel – How to get column count of a row

I have loaded this module for PowerShell (https://github.com/dfinke/ImportExcel) that adds a lot of customization for editing excel files.

Currently I am having an issue where I am unable to get the column count of the first row. The Excel that I am loading is dynamically generated through SQL, so the ending column could be N, AZ, V, BW, etc.

From my looking around, it looks like I need to use .psobject.properties in order to get the correct count, but every time I attempt to do this I get a count of 0, 1, or something greater than what I was expecting. I found this thread that mentions it this, but have not been able to replicate these. Count columns of an imported Excel workbook – count a given object's properties

Is there a way I could look at the first row and see what column the data ends in?

Excel has 14 rows, when running I expect to see 14 as the count, but most of the time I only see 1.

Some code I have tried

$columns = $sheet1.Row(1).Count
$columns = (($columns[0].psobject.properties).Count).Count
$columns = @($columns.psobject.properties).Count
$columns = $sheet1.UsedRange.Rows(1).Columns.Count

>Solution :

Use Open-ExcelPackage to determine this:

$package = Open-ExcelPackage -Path path\to\myExcel.xlsx
# has the column count for this worksheet
$package.Workbook.Worksheets['MyWorkSheet'].Dimension.Columns
# has the row count for this worksheet
$package.Workbook.Worksheets['MyWorkSheet'].Dimension.Rows
Close-ExcelPackage $package

If your file has many Worksheets you can also enumerate them to get a column and row count per each:

$package.Workbook.Worksheets | ForEach-Object {
    [pscustomobject]@{
        WorksheetName = $_.Name
        ColumnCount   = $_.Dimension.Columns
        RowCount      = $_.Dimension.Rows
    }
}