Exporting with default theme now renders as purple after recent office update?

[rottenbologna]

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

[dfinke]

@rottenbologna Thanks for that! I heard for someone else similar issue.

Going to try and triangulate on this.

[bobbynog]

I have found the same thing. I also looked into the xml contents of an xlsx; the only thing saved is the definition "Medium6", no actual colour codes, so the interpretation of "Medium6" is down to the application opening the file:

Other interesting observation: if I open a file in new Excel created in December last year, "Medium6" still renders blue. It seems to be using another attribute of the file (creation datetime?) to determine whether to use "old colours" or "new colours".

That's manifested in the available "Format as Table" colours are different based on the creation date of the file: December 2023 file (where Medium6 is described as "Blue"):

January 2024 file (where Medium6 is now described as "Plum"):

[bobbynog]

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:

1. /xl/theme folder and file. Excel creates a new theme folder within the xl subfolder; in it is theme1.xml, a copy of which I attach as a .txt as .xml isn't allowed here: theme1.txt

2. /xl/_rels/workbook.xml.rels file. The workbook.xml.rels file needs a reference added to it:

Text value (be careful to increment the rIdNN value so it's not a duplicate of any existing rIds): <Relationship Id="rId12" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>

3. /[Content_Types].xml file. This needs the pointer to themes added as well:

Text value (nothing to worry about incrementing): <Override PartName="/xl/theme/theme1.xml" ContentType="application/vnd.openxmlformats-officedocument.theme+xml"/>

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? ;)

[bobbynog]

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.

[dfinke]

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

[bobbynog]

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. :)

    param ([Parameter(Mandatory = $true)]
           [String] $XlsxFile,
           [Parameter(Mandatory = $true)]
           [String] $XlsxTab,
           [Parameter(Mandatory = $false)]
           [String] $ThemeFile)

     #1. Create the specified workbook with a temporary (consisent) tab name
    if (Test-Path $XlsxFile) { Remove-Item $XlsxFile -Force }

    [object] $xl_pkg = Open-ExcelPackage -Path $XlsxFile -Create
    Add-Worksheet -ExcelPackage $xl_pkg -WorksheetName $XlsxTab -MoveToEnd | out-null
    Close-ExcelPackage -ExcelPackage $xl_pkg 
    $xl_pkg.Dispose() | out-null

    #2. Apply the Client's Theme file to spreadsheet if requested
    if ($ThemeFile) {
        if(Test-Path $ThemeFile)     {
        [object] $XlsxFileObject = Get-Item $XlsxFile
        [string] $XlsxFolder = $XlsxFileObject.DirectoryName
        [string] $XlsxName = $XlsxFileObject.Name
        [string] $XlsxName_AsZip = $XlsxName.Replace('.xlsx','.zip')
        [string] $WorkingFolder = "$XlsxFolder\" + $XlsxName.Replace('.xlsx','_xlsx').Replace(' ','_')
        [string] $ContentTypesData_Original
        [string] $ContentTypesData_Updated
        [string] $WorkbookXmlRels_Original
        [string] $WorkbookXmlRels_Updated

        #2.1. Rename it to a ZIP; extract it to a folder
        #   Delete path if it already exists, rename file to ZIP, extract it to working folder
        if (Test-Path $WorkingFolder) { Remove-Item $WorkingFolder -Recurse -Force | out-null} 
        Rename-Item -Path "$XlsxFolder\$XLSXName" -NewName $XlsxName_AsZip 
        Expand-Archive -LiteralPath "$XlsxFolder\$XlsxName_AsZip" -DestinationPath $WorkingFolder
        [object] $ThemeFileObject = Get-Item $ThemeFile

        #2.2. Create Theme folder if it doesn't already exist
        if (-not(Test-Path "$WorkingFolder\xl\theme")) {
            New-Item "$WorkingFolder\xl\theme" -ItemType Directory -Force | out-null}

        #2.3. Copy the client's theme file to the theme folder
        Copy-Item -Path $ThemeFile -Destination "$WorkingFolder\xl\theme\theme1.xml" -Force | out-null

        #2.4. Amend references to [Content_Types].xml
            #a. Get current ContentTypesData
            $ContentTypesData_Original  = Get-Content -LiteralPath "$WorkingFolder\[Content_Types].xml"  -Raw
            $ContentTypesData_Updated = $ContentTypesData_Original

            if (!$ContentTypesData_Updated.Contains("ContentType=`"application/vnd.openxmlformats-officedocument.theme+xml`"")) {
                    $ContentTypesData_Updated = $ContentTypesData_Updated.Replace("</Types>","<Override PartName=`"/xl/theme/theme1.xml`" ContentType=`"application/vnd.openxmlformats-officedocument.theme+xml`"/></Types>")   }

            #b. If Content_Types has changed in the file, write it
            if ($ContentTypesData_Original -ne $ContentTypesData_Updated) {
                            Set-Content -LiteralPath "$WorkingFolder\`[Content_Types`].xml" -Value $ContentTypesData_Updated   | out-null }

        #2.5. Handle update for /xl/_rels/workbook.xml.rels
            #a. Amend references /xl/_rels/workbook.xml.rels
            $WorkbookXmlRels_Original= Get-Content -LiteralPath "$WorkingFolder\xl\_rels\workbook.xml.rels"  -Raw
            $WorkbookXmlRels_Updated = $WorkbookXmlRels_Original

            if (!$WorkbookXmlRels_Updated.Contains("Target=`"theme/theme1.xml`"")) {
                    $WorkbookXmlRels_Updated = $WorkbookXmlRels_Updated.Replace("</Relationships>","<Relationship Id=`"rId98765`" Type=`"http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme`" Target=`"theme/theme1.xml`"/></Relationships>")    }

            #b. If the data has changed in the file, write it
            if ($WorkbookXmlRels_Original -ne $WorkbookXmlRels_Updated) {
                            Set-Content -LiteralPath "$WorkingFolder\xl\_rels\workbook.xml.rels" -Value $WorkbookXmlRels_Updated  | out-null   }            
        #2.6. Overwrite the ZIP
        Compress-Archive -Path "$WorkingFolder\*" -DestinationPath "$XlsxFolder\$XlsxName_AsZip" -Force | out-null

        #2.7. Rename the ZIP to XLSX
        Rename-Item -Path "$XlsxFolder\$XlsxName_AsZip" -NewName $XlsxName | out-null    

        #2.8.  Delete the folder.
        Remove-Item $WorkingFolder -Recurse -Force | out-null

        }}

[dfinke]

Thanks for that fix @bobbynog.

So Microsoft reverted those changes? Seems probable. Folks get used to the formatting of their sheets.

[JustinGrote]

Bumping this, mine started showing purple again after a recent update, so I guess they've re-implemented. There is theme support in EPPlus 5.x which isn't a non-commercial license as I'm sure everyone knows, so yeah this is gonna require workarounds which is a shame.

[dfinke]

@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.

[JustinGrote]

@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. https://www.epplussoftware.com/en/Developers/Themes

[dfinke]

@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.

[An-dir]

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

# Design template extracted from a default XLSX with selected color theme - SourcePath: '*.xlsx\xl\theme\theme1.xml'
$themeTemplatePath = "C:\excel color problem\theme1.xml"

# path to temporary xlsx created by ImportExcel module
$zipPath = "C:\excel color problem\temp.xlsx"

# Path to final xlsx
$outputFile = "C:\excel color problem\final.xlsx"

# open XLSX file as archive
Add-Type -AssemblyName "WindowsBase"
[System.IO.Packaging.Package]::Open($zipPath, [System.IO.FileMode]::Open, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None) | ForEach-Object {
    $package = $_

    # delete 'theme1.xml' if exists
    $themeUri = New-Object System.Uri("/xl/theme/theme1.xml", [System.UriKind]::Relative)
    if ($package.PartExists($themeUri)) {
        $themePart = $package.GetPart($themeUri)
        $themePart.GetStream().Dispose()
        $package.DeletePart($themeUri)
    }

    # add  'theme1.xml'
    $themePart = $package.CreatePart($themeUri, "application/vnd.openxmlformats-officedocument.theme+xml", [System.IO.Packaging.CompressionOption]::Maximum)
    $themeContent = [System.IO.File]::ReadAllBytes($themeTemplatePath)
    $themePart.GetStream().Write($themeContent, 0, $themeContent.Length)

    $package.Flush()
    $package.Close()
}

# save to final file
Copy-Item -Path $zipPath -Destination $outputFile -Force

[An-dir]

New script with some Ideas from @bobbynog and ChatGPT. Removed the requirement to enter a worksheet name

Requirements: Exported theme Files with the colors you like

how I tested:

# create a object for the table
$Table = Dir 

# use ImportExcel module to "Export-Excel" file
$Table | Export-Excel -Path test.xlsx -TableStyle Medium6 -AutoFilter

# integrate Office 2013-2022 theme
.\themeswitcher.ps1 -XlsxFile "c:\issue1553\test.xlsx" -ThemeFile "C:\themeswitcher\theme1.xml"

# make a copy for additional tests
copy c:\issue1553\test.xlsx c:\issue1553\test2.xlsx

# Open first file in Excel to validate changes
start c:\issue1553\test.xlsx

# integrate current Office theme in second file
.\themeswitcher.ps1 -XlsxFile "c:\issue1553\test2.xlsx" -ThemeFile "C:\themeswitcher\theme2.xml"

# Open in Excel to validate changes
start c:\issue1553\test2.xlsx

And it all worked.

Here is the edited script:

param (
    [Parameter(Mandatory = $true)]
    [String] $XlsxFile,
    [Parameter(Mandatory = $true)]
    [String] $ThemeFile
)

Add-Type -AssemblyName "WindowsBase"

# Helper function to write string content to a package part
function Write-ToPackagePart {
    param (
        [System.IO.Packaging.PackagePart] $Part,
        [string] $Content
    )
    $bytes = [System.Text.Encoding]::UTF8.GetBytes($Content)
    $Part.GetStream([System.IO.FileMode]::Create).Write($bytes, 0, $bytes.Length)
}

# Apply the Client's Theme file to the spreadsheet
if (Test-Path $ThemeFile) {
    $package = [System.IO.Packaging.Package]::Open($XlsxFile, [System.IO.FileMode]::Open, [System.IO.FileAccess]::ReadWrite)

    # Create the theme part or overwrite if it already exists
    $themeUri = New-Object System.Uri("/xl/theme/theme1.xml", [System.UriKind]::Relative)
    if ($package.PartExists($themeUri)) {
        $package.DeletePart($themeUri)
    }
    $themePart = $package.CreatePart($themeUri, "application/vnd.openxmlformats-officedocument.theme+xml")

    # Copy the client's theme file to the theme part
    $themeContent = [System.IO.File]::ReadAllBytes($ThemeFile)
    $themePart.GetStream().Write($themeContent, 0, $themeContent.Length)

    # Update [Content_Types].xml
    $contentTypesUri = New-Object System.Uri("/Content_Types.xml", [System.UriKind]::Relative)
    if ($package.PartExists($contentTypesUri)) {
        $contentTypesPart = $package.GetPart($contentTypesUri)
        $contentTypesData = (New-Object System.IO.StreamReader($contentTypesPart.GetStream())).ReadToEnd()
        if ($contentTypesData -notmatch "application/vnd.openxmlformats-officedocument.theme+xml") {
            $contentTypesData = $contentTypesData -replace "</Types>", "<Override PartName=""/xl/theme/theme1.xml"" ContentType=""application/vnd.openxmlformats-officedocument.theme+xml""/></Types>"
            Write-ToPackagePart -Part $contentTypesPart -Content $contentTypesData
        }
    }

    # Update /xl/_rels/workbook.xml.rels
    $workbookRelsUri = New-Object System.Uri("/xl/_rels/workbook.xml.rels", [System.UriKind]::Relative)
    if ($package.PartExists($workbookRelsUri)) {
        $workbookRelsPart = $package.GetPart($workbookRelsUri)
        $workbookRelsData = (New-Object System.IO.StreamReader($workbookRelsPart.GetStream())).ReadToEnd()
        if ($workbookRelsData -notmatch "theme/theme1.xml") {
            $workbookRelsData = $workbookRelsData -replace "</Relationships>", "<Relationship Id=""rId98765"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"" Target=""theme/theme1.xml""/></Relationships>"
            Write-ToPackagePart -Part $workbookRelsPart -Content $workbookRelsData
        }
    }

    # Close the package
    $package.Close()
}

Edit: Tested in PowerShell7 and PowerShell5