I modified data tables xlsx export to generate tables with my custom styles. Primarily for the background colors. Mine is a mess, but it works. It generates the xlsx file and in LibreOffice it looks exactly like it should. But in Excel, the cells with Style #3 (FFAAAA) are not filled with solid yellow background but with a dotted gray background.
The ones with red or white background just work fine everywhere.
The whole xml was reverse engineered from other exports. Any idea what Excel expects to be different?
<?xml version="1.0" encoding="UTF-8"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
<numFmts count="7">
<numFmt numFmtId="0" formatCode=""/>
<numFmt numFmtId="1" formatCode="#,##0.00_-\ [$$-45C]"/>
<numFmt numFmtId="2" formatCode=""£"#,##0.00"/>
<numFmt numFmtId="3" formatCode="[$€-2]\ #,##0.00"/>
<numFmt numFmtId="4" formatCode="0.0%"/>
<numFmt numFmtId="5" formatCode="#,##0;(#,##0)"/>
<numFmt numFmtId="6" formatCode="#,##0.00;(#,##0.00)"/>
</numFmts>
<fonts count="2" x14ac:knownFonts="1">
<font>
<sz val="11" />
<name val="undefined" />
<color rgb="FF000000" />
</font>
<font>
<sz val="11" />
<name val="Calibri" />
<color rgb="FF000000" />
<b />
</font>
</fonts>
<fills count="4">
<fill>
<patternFill patternType="none" />
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFffeeaa" />
<bgColor indexed="64" />
</patternFill>
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFffaaaa" />
<bgColor indexed="65" />
</patternFill>
</fill>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFffffff" />
<bgColor indexed="66" />
</patternFill>
</fill>
</fills>
<borders count="2">
<border> <left /> <right /> <top /> <bottom /> <diagonal /> </border>
<border diagonalUp="false" diagonalDown="false"> <left style="thin"> <color auto="1" /> </left> <right style="thin"> <color auto="1" /> </right> <top style="thin"> <color auto="1" /> </top> <bottom style="thin"> <color auto="1" /> </bottom> <diagonal /> </border>
</borders>
<cellStyleXfs count="1">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" />
</cellStyleXfs>
<cellXfs count="5">
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>
<xf numFmtId="0" fontId="1" fillId="0" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>
<xf numFmtId="0" fontId="1" fillId="1" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>
<xf numFmtId="0" fontId="1" fillId="2" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>
<xf numFmtId="0" fontId="1" fillId="3" borderId="1" applyFont="1" applyFill="1" applyBorder="1"/>
</cellXfs>
<cellStyles count="1">
<cellStyle name="Normal" xfId="0" builtinId="0" />
</cellStyles>
<dxfs count="0" />
<tableStyles count="0" defaultTableStyle="TableStyleMedium9" defaultPivotStyle="PivotStyleMedium4" />
</styleSheet>
It seems excel always overwrites the second with patternType="gray125". I just keep
<fill>
<patternFill patternType="gray125">
<fgColor rgb="FFffffff" />
<bgColor rgb="FFffffff" />
</patternFill>
</fill>
as second , regardless if i actually use it in any style and add the i need after this. Now it works in Libre Office Calc and MS Excel. I hope that helps others aswell.