-
Notifications
You must be signed in to change notification settings - Fork 393
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Exporting with default theme now renders as purple after recent office update? #1553
Comments
@rottenbologna Thanks for that! I heard for someone else similar issue. Going to try and triangulate on this. |
I've been playing with ImportExcel-created spreadsheets (with "plum" Medium 6"), then in Excel opening them, setting Palette to be Office 2013-2022, then interrogating the file contents to see what I need to do to "force" the ImportExcel spreadsheet into old Office colours. It's not too pretty but also not too complex (haven't figured out how to automate this in PowerShell yet.) Essentially, you rename the .xlsx to .zip, then extract it. Change the file contents in the following 3 ways:
Text value (be careful to increment the rIdNN value so it's not a duplicate of any existing rIds): Text value (nothing to worry about incrementing): Then rezip the file, rename to .xlsx and open, and you now have forced it to the older colour palette. Thank you Microsoft. I suppose 10 years was a long time to not fiddle with and break something like colour schemes. Finally had to scratch the itch, eh? ;) |
One other interesting thing. Inside theme1.xml, at the top are these colour codes: If you overwrite the values with other Hex colour codes (note LT1 and DK1 take system window and window text values, you can overwrite with hardcoded values if needed), you are changing the "base colour" available for different styles. I changed them all to FF5733 (orange): My table styles then looked like: That means, based on what theme1.xml file you throw in to the ZIP, you can control available colour options. |
Thanks @bobbynog! Well, that is unfortunate. Are you going to write some PowerShell to make those changes for your workflow? I don't think I'll be able to fix that in ImportExcel. EPPlus handles the serialization to the xlsx. I suspect they have fixed it in their paid version v5.x |
Thanks, @dfinke. Curiously (and @rottenbologna), it seems Microsoft have backpeddled... the "new" theme colours have disappeared from my O365 Excel application. Is that your experience too? I wrote a PowerShell script to handle it anyway, copied below. This has the added benefit of allowing you to force in whatever theme file you like if you want custom (on-brand) colours in your themes, and removing the dependence on the application to determine colours. This script creates a new workbook with a default empty tab, saves and closes it, then adds the theme file to it. We've done thorough testing on the workbooks created by this little script. Once that "initial" workbook is created, you can use the other ImportExcel functions (SendSQLDataToExcel, Add-Worksheet, etc.), and the theme file is preserved throughout. Doing it this way means you're only have to rename/unzip/edit/rezip/rename once, when the spreadsheet is a tiny little file before any data has been added, so it's fairly quick. Apologies, I'm not a great PowerShell coder, the attached is enough to get us over our hump, and because we execute our PowerShell scripts as Script Tasks in SSIS, failures are caught by SSIS, so no error handling. Lastly, I did some testing using O365 online and also Google Sheets, both read the theme file and apply colours. :)
|
Thanks for that fix @bobbynog. So Microsoft |
@JustinGrote Don't know, didn't look at EPPlus 5+ if they resolve it. It EPPlus 5+ is not a drop in replacement. Plus, they have a paid license process. |
@dfinke they support themes so there's an API to do the work that @bobbynog basically did above, but, as you said, the license change would make it difficult to adopt for this module as lots of importexcel integrations are commercial in nature I'm sure. |
@JustinGrote not just the license change. I did a spike on using their new version a couple years back, they change a boatload of things. I had conversations with the creators about that and the licencsing. It is what it is. Always open to take a PR for additional features. |
After some searching and try&error I came to the following alternative solution that IS NOT FINISHED but I would like to share it as an idea. It uses "System.IO.Packaging.Package" so it might be more compatible. Maybe someone has time to finish or integrate something into ImportExcel
|
New script with some Ideas from @bobbynog and ChatGPT. Requirements: Exported theme Files with the colors you like how I tested:
And it all worked. Here is the edited script:
Edit: Tested in PowerShell7 and PowerShell5 |
Hello, dropping a line for something odd that I see after a recent Office update. (Using 7.8.6 and tested under PS5 5.1.14393.517 and PS7 7.4.0)
Not a bug per-se but wanted to put it on your radar that Excel / Office might have something up with how it treats the default style/theme.
Referencing this article:
https://insider.microsoft365.com/en-us/blog/office-has-a-new-default-theme
In using the default theme when exporting (has been the usual light blue)
I've seen a few machines that started presenting with an odd purple variation today after having gotten an Office update. Like this:
Interesting that for users picked up that newer Office theme, they see this:
If manually toggling an open spreadsheet back to the Office 2013-2022 one, all goes back to normal with the expected blue. It appears that at least on the user's machines I've looked at, the default is the new one that renders as purple.
Specifying any built-in theme works as expected so I simply set one explicitly. (-TableStyle Medium16) Just wanted to put it on your radar.
I saw this behavior on this build:
To be one the safe side, I looked for and installed the latest office update to rule that out. Here's the build I updated to:
I did hear from a few other colleagues outside our agency and some also report seeing the purple manifest as well today.
I had run through the built in styles for export to see if any were the actual blue default one. Maybe useful to have the usual longstanding blue theme added as an embedded one in the TableStyle list so when Microsoft changes stuff the default change wouldn't matter as it could be explicitly set?
Might you know off hand the parameter values to set to match the usual blue default?
If you might need any other info, happy to provide.
Thanks,
Scott
The text was updated successfully, but these errors were encountered: