Export-Excel -Style work wrong with multiple sheets

[PakoGomezG]

Hello,

If you want to use the same Excel Style in multiple Export-Excel, fails applying the first excel/sheet range afert first execution. For example:

$defaultStyle = New-ExcelStyle -AutoSize -HorizontalAlignment Center
foreach ($sheet in $sheets) {
$data | Export-Excel -Path $excelFile -WorksheetName $sheet -Style $defaultStyle
}

After the first 'Export-Excel', $defaultStyle variable have a range property with the dimension of the sheet.

This occurs in Export-Excel.ps1, line 659

        foreach ($s in $Style) {
            if (-not $s.Range) { $s["Range"] = $ws.Dimension.Address }
            Set-ExcelRange -Worksheet $ws @s
        }

[pauljnav]

Hello. I performed a test of this today , and I'm observing no issue.
My observation; As mentioned by PakoGomezG: after the first 'Export-Excel' $defaultStyle [PSBoundParametersDictionary] has "Range" property with the dimension of the sheet ("A1:BO11" in my test). Those dimensions are static for all 3x iterations of the foreach loop, and the styling (HorizontalAlignment Center) is applied as expected to all 3x worksheets.

Versions used in test :

PSVersion 5.1.19041.4046 with ImportExcel 7.8.6
PSVersion 7.3.2 with ImportExcel 7.8.6

Code sample:

$defaultStyle = New-ExcelStyle -AutoSize -HorizontalAlignment Center

$sheets = 'firstSheet','secondSheet','thirdSheet'

$data = Get-Process | Select-Object -First 10

$excelFile = "C:\Temp\testFile1.xlsx"

foreach ($sheet in $sheets) {
    $data | Export-Excel -Path $excelFile -WorksheetName $sheet -Style $defaultStyle
    $defaultStyle | Out-String | Write-Host # Lets observe $defaultStyle using Write-Host
}

[PakoGomezG]

Hello,

In your case is working fine because you are exporting the same data with the same range of table, but usually different data is exported with a different range.

For example:

$computers = @('cpt1','cpt2','cpt3')
$defaultStyle = New-ExcelStyle -AutoSize -HorizontalAlignment Center

foreach ($cpt in $computers) {
      $data = Get-Process -ComputerName $cpt
      $data | Export-Excel -Path $excelFile -WorksheetName $cpt -Style $defaultStyle
}

In this case the DefaultStyle variable will have the range of the first Export and if the rest of the exports have a higher range, the style will not be applied to all the information.
So I can't use a default styles variable and have to define it before each export.

$computers = @('cpt1','cpt2','cpt3')

foreach ($cpt in $computers) {
      $data = Get-Process -ComputerName $cpt
      $defaultStyle = New-ExcelStyle -AutoSize -HorizontalAlignment Center
      $data | Export-Excel -Path $excelFile -WorksheetName $cpt -Style $defaultStyle
}

[pauljnav]

Hi @PakoGomezG
Yes, my example was not changing data of range. So adjusting to follow your example; Dynamic data, and relocating the New-ExcelStyle command inside the loop, the sample below generates 3 different data sets, and also 3 different range sizes, and the issue is not reproduced. The ranges are adjusting and consequently the styling is correctly applied to the different ranges.
The ranges should change in your case as they do in this example.

$sheets = 'firstSheet','secondSheet','thirdSheet'

$excelFile = "C:\Temp\testFile1.xlsx"

foreach ($sheet in $sheets) {
    # different data per iteration
    $data = Get-Process | Select-Object -First (Get-Random -min 8 -max 20) -Skip (Get-Random -max 6)
    
    $defaultStyle = New-ExcelStyle -AutoSize -HorizontalAlignment Center    
    $data | Export-Excel -Path $excelFile -WorksheetName $sheet -Style $defaultStyle
    
    # Observe $defaultStyle using Write-Host
    $defaultStyle | Out-String | Write-Host
}

Each range is adjusting to the data, and I would expect same for you.

Key                 Value  
---                 -----  
AutoSize            True   
HorizontalAlignment Center 
Range               A1:BO20

Key                 Value  
---                 -----  
AutoSize            True   
HorizontalAlignment Center 
Range               A1:BO15

Key                 Value  
---                 -----  
AutoSize            True   
HorizontalAlignment Center 
Range               A1:BO18

[dfinke]

Thanks @PakoGomezG for raising this and @pauljnav for the scenarios.

Will keep an eye on this thread. 👀