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.