Export-Excel accepts too long worksheetnames, leading to problems later on

[ildjarnisdead]

Excel only supports worksheets up to 31 characters, but Export-Excel allows longer worksheetnames. The first time this happens, a warning is given, but if, in another call, a worksheetname is used which results in the same abbreviated name, an error occurs.

This could be prevented by disallowing WorksheetNames longer than 31 characters.

$blah | Export-Excel -Path c:\temp\blah.xlsx -WorksheetName ('blah'*8) -AutoSize -AutoFilter -FreezeTopRow -NoNumberConversion * -BoldTopRow

This results in:

WARNING: The Worksheet name has been changed from blahblahblahblahblahblahblahblah to blahblahblahblahblahblahblahbla, this may cause errors later.

But when I then do:

$blah | Export-Excel -Path c:\temp\blah.xlsx -WorksheetName ('blah'*9) -AutoSize -AutoFilter -FreezeTopRow -NoNumberConversion * -BoldTopRow

this results in an Error that does not show the actual problem:

Could not get worksheet blahblahblahblahblahblahblahblahblah
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.10\Public\Export-Excel.ps1:137 char:17
+         catch { throw "Could not get worksheet $WorksheetName" }
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Could not get w...lahblahblahblah:String) [], RuntimeException
    + FullyQualifiedErrorId : Could not get worksheet blahblahblahblahblahblahblahblah

[dfinke]

Could you provide a small script, data that repros this?

[ildjarnisdead]

$ErrorActionPreference = 'Continue'
$test = [pscustomobject]@{
    column1 = "Data"
}

Remove-Item C:\Temp\test.xlsx -ErrorAction SilentlyContinue
try { $WSName = "test"*8; $test | Export-Excel -Path c:\temp\test.xlsx -WorksheetName $WSName } catch { "Worksheet $WSName not created"; $error[0] }
try { $WSName = "test"*8; $test | Export-Excel -Path c:\temp\test.xlsx -WorksheetName $WSName } catch { "Worksheet $WSName not created"; $error[0] }
try { $WSName = "test"*9; $test | Export-Excel -Path c:\temp\test.xlsx -WorksheetName $WSName } catch { "Worksheet $WSName not created"; $error[0] }
try { $WSName = "blah"*8; $test | Export-Excel -Path c:\temp\test.xlsx -WorksheetName $WSName } catch { "Worksheet $WSName not created"; $error[0] }

First export will create the Excel file with a warning
Second export fails with an error 'Could nog get worksheet...'
Third export fails with an error 'Could not get worksheet...'
Fourth export will update the excel-file with a new worksheet, and with a warning

Output:

WARNING: The Worksheet name has been changed from testtesttesttesttesttesttesttest to testtesttesttesttesttesttesttes, this may cause errors later.
Worksheet testtesttesttesttesttesttesttest not created
Could not get worksheet testtesttesttesttesttesttesttest
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.10\Public\Export-Excel.ps1:137 char:17
+         catch { throw "Could not get worksheet $WorksheetName" }
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Could not get w...esttesttesttest:String) [], RuntimeException
    + FullyQualifiedErrorId : Could not get worksheet testtesttesttesttesttesttesttest
 
Worksheet testtesttesttesttesttesttesttesttest not created
Could not get worksheet testtesttesttesttesttesttesttesttest
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.10\Public\Export-Excel.ps1:137 char:17
+         catch { throw "Could not get worksheet $WorksheetName" }
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Could not get w...esttesttesttest:String) [], RuntimeException
    + FullyQualifiedErrorId : Could not get worksheet testtesttesttesttesttesttesttesttest
 
WARNING: The Worksheet name has been changed from blahblahblahblahblahblahblahblah to blahblahblahblahblahblahblahbla, this may cause errors later.

[dfinke]

@ildjarnisdead thanks for the repro.

I would add a pre-step to handle the size of the worksheet name so it is of correct length.

I'll take a look and suspect this may be a problematic change.

[ildjarnisdead]

Some people might say that it's already solved with the warning text, but a better error instead of 'could not get worksheetname' would be welcome.

[dfinke]

If you can isolate in the code where that would be and let me know if it doesn't break things, I'll consider it.

You're the first person to report that you hit this issue.