Comments (4)
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
}
from importexcel.
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
}
from importexcel.
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
from importexcel.
Thanks @PakoGomezG for raising this and @pauljnav for the scenarios.
Will keep an eye on this thread. 👀
from importexcel.
Related Issues (20)
- Issue with empty values in generated Excel. HOT 1
- Issue with user-defined number format HOT 5
- Export-Excel's -CellStyleSB switch doesn't seem to parse dimensions.address correctly for use with Set-CellStyle HOT 2
- Can't get 'Close-ExcelPackage' to work properly HOT 5
- Exporting hashtable to new (empty) file doesn't transfer as expected HOT 4
- Exporting with default theme now renders as purple after recent office update? HOT 11
- New-ConditionalText Question HOT 1
- Exporting worksheet to csv while keeping excel formats (where applicable) HOT 9
- Set-ExcelRange -Formula issue HOT 1
- KillExcel fails
- Feature Request: Set the default worksheet when the workbook is opened HOT 3
- Close-ExcelPackage -Show broken on MacOS due to PowerShell issue HOT 1
- Add -Calculate to Open-ExcelPackage and Import-Excel
- Help with getting a Table without Headers HOT 6
- Exception calling "Save" with "0" argument(s) HOT 7
- Faulty Hyperlink to web site using OfficeOpenXml.ExcelHyperLink HOT 2
- Question: How to colorize header columns HOT 3
- Question: How to match a keyword (stored in a variable) in a specific column and color a range? HOT 2
- Formatting OutPut HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from importexcel.