Exporting with default theme now renders as purple after recent office update? about importexcel HOT 11 OPEN

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

from importexcel.

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

        }}

from importexcel.

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.

from importexcel.

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

Going to try and triangulate on this.

from importexcel.

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

from importexcel.

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.

from importexcel.

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

from importexcel.

Thanks for that fix @bobbynog.

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

from importexcel.

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

from importexcel.

@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

from importexcel.

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

from importexcel.