Bug: Spreadsheet with data above StartRow causes column headers to be duplicated

[dbrennand]

Hey @dfinke

Hope you're keeping well 🙂

I've noticed some odd behaviour using Import-Excel ... -StartRow 3 with an Excel spreadsheet which has the following layout:

Sheet1

There are no rows containing data after the table headers however, when I import this spreadsheet it produces the following output:

Import-Excel -Path "Issue.xlsx" -WorksheetName "Sheet1" -StartRow 3

# Output
System Name          :
RAID Group Name      :
RAID Level           :
# LUNs               :
# Disks              :
Raw Capacity (GB)    :
Usable Capacity (GB) :
Used Capacity (GB)   :
Free Capacity (GB)   :
Throughput (IOPS)    :
Defragmented (%)     :

System Name          : System Name
RAID Group Name      : RAID Group Name
RAID Level           : RAID Level
# LUNs               : # LUNs
# Disks              : # Disks
Raw Capacity (GB)    : Raw Capacity (GB)
Usable Capacity (GB) : Usable Capacity (GB)
Used Capacity (GB)   : Used Capacity (GB)
Free Capacity (GB)   : Free Capacity (GB)
Throughput (IOPS)    : Throughput (IOPS)
Defragmented (%)     : Defragmented (%)

Sheet1 (2)

This is working as expected and the warning is shown:

Import-Excel -Path "Issue.xlsx" -WorksheetName "Sheet1 (2)"
# Output
WARNING: Worksheet 'Sheet1 (2)' in workbook '...\Issue.xlsx' contains no data in the rows after top row '1'

I think this issue is related to there being some data above the StartRow 3 and the logic in this else statement of the Import-Excel cmdlet doesn't account for this case?

This issue occurs on the latest module version:

PS> gmo | where name -eq ImportExcel

ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Script     7.8.6      ImportExcel                         {Add-ConditionalFormatting, Add-ExcelChart, Add-ExcelDataValidationRule, Add-ExcelName...}

[dfinke]

@dbrennand Thanks for using the Excel module.

My guess is the logic when using -StartRow does not pass thru the checks. Highly possible. That is early organic code.

No ETA on this.

[scriptingstudio]

I did not dig enough deep but inserting expression if ($StartRow -eq $EndRow) {$rows = 0} after line 182 would resolve the issue and would give some hints to further exploration.

[dfinke]

Thanks @scriptingstudio for checking that. I'll put this on the list. Need to see if there are tests, doubt it.

[dbrennand]

@scriptingstudio @dfinke - Pushed a fix for this in master...dbrennand:ImportExcel:fix/#1587

With this change the expected warning is now showing:

Import-Excel -Path "Issue.xlsx" -WorksheetName "Sheet1" -StartRow 3
WARNING: Worksheet 'Sheet1' in workbook 'Issue.xlsx' contains no data in the rows after top row '3'

Previous behaviour is also preserved:

Import-Excel -Path "Issue.xlsx" -WorksheetName "Sheet1 (2)"
WARNING: Worksheet 'Sheet1 (2)' in workbook 'Issue.xlsx' contains no data in the rows after top row '1'

[dfinke]

Thanks @dbrennand! I just created a branch to take a look. Were you able to take a look at the tests to see if there were any that tested this code?

[dbrennand]

Thanks @dbrennand! I just created a branch to take a look. Were you able to take a look at the tests to see if there were any that tested this code?

https://github.com/search?q=repo%3Adfinke%2FImportExcel%20path%3A%2F%5E__tests__%5C%2F%2F%20StartRow&type=code

There are a couple of tests which already include -StartRow - I will add the problematic workbook and include a new test for this.

[dfinke]

It needs additional tests.