Add stylesheet to openxml excel file

Question

I'm trying to insert some styles in a excel file. Right now the data part works perfectly, but the style throws an error when I open the excel. It complains that the stylesheet file has an error in line 19 of the xml. I don't know what is wrong with that line. It is where the font collection node start. Any ideas why... This is the xml for the style

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" mc:Ignorable="x14ac">
    <x:borders count="1">
        <x:border>
            <x:left style="thin">
                <x:color indexed="64" rgb="000000" />
            </x:left>
            <x:right style="thin">
                <x:color indexed="64" rgb="000000" />
            </x:right>
            <x:top style="thin">
                <x:color indexed="64" rgb="000000" />
            </x:top>
            <x:bottom style="thin">
                <x:color indexed="64" rgb="000000" />
            </x:bottom>
        </x:border>
    </x:borders>
    <x:fonts count="1">
        <x:font>
            <x:b />
            <x:sz val="12" />
            <x:color rgb="000000" />
            <x:name val="Arial" />
        </x:font>
    </x:fonts>
    <x:cellXfs>
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf fontId="0" borderId="0" applyFont="1" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
        <x:xf borderId="0" applyBorder="1" />
    </x:cellXfs>
</x:styleSheet>

UPDATE

After some testing, googling, head banging.... I came up with the following stylesheet xml

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" mc:Ignorable="x14ac x16r2 xr">
    <x:fonts count="2" x14ac:knownFonts="1">
        <x:font />
        <x:font>
            <x:b />
            <x:sz val="12" />
            <x:color rgb="FF000000" />
            <x:name val="Arial" />
            <x:family val="2" />
            <x:scheme val="minor" />
        </x:font>
    </x:fonts>
    <x:borders count="2">
        <x:border />
        <x:border>
            <x:left style="thin">
                <x:color rgb="FF000000" />
            </x:left>
            <x:right style="thin">
                <x:color rgb="FF000000" />
            </x:right>
            <x:top style="thin">
                <x:color rgb="FF000000" />
            </x:top>
            <x:bottom style="thin">
                <x:color rgb="FF000000" />
            </x:bottom>
        </x:border>
    </x:borders>
    <x:cellXfs count="3">
        <x:xf />
        <x:xf fontId="1" borderId="1" applyFont="1" applyBorder="1" />
        <x:xf borderId="1" applyBorder="1" />
    </x:cellXfs>
</x:styleSheet>

Still excel complains that the file is corrupt after recovering the file it displays the styles applied to each cell gives the error "Repaired Records: Format from /xl/styles.xml part (Styles)" . LibreOffice opens the file correctly, OPEN XML SDK 2.0 Productivity Tool validates the file as correct...Why excel complains.....

UPDATE After playing around with the code I solved the problem. This two questions also helped Stylesheet 1 Stylesheet 2

It seems that there is an order to create the nodes, specially the cellStyleXfs and cellXfs, the cellStyleXfs as to come before the cellXfs

The working XML

    <?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" mc:Ignorable="x14ac x16r2 xr">
    <x:fonts count="3" x14ac:knownFonts="1">
        <x:font />
        <x:font>
            <x:b />
            <x:sz val="12" />
            <x:color rgb="FF000000" />
            <x:name val="Arial" />
            <x:family val="2" />
            <x:scheme val="minor" />
        </x:font>
        <x:font>
            <x:b />
            <x:sz val="12" />
            <x:color rgb="FFFF0000" />
            <x:name val="Arial" />
            <x:family val="2" />
            <x:scheme val="minor" />
        </x:font>
    </x:fonts>
    <x:fills count="1">
        <x:fill />
    </x:fills>
    <x:borders count="2">
        <x:border />
        <x:border>
            <x:left style="thin">
                <x:color rgb="FF000000" />
            </x:left>
            <x:right style="thin">
                <x:color rgb="FF000000" />
            </x:right>
            <x:top style="thin">
                <x:color rgb="FF000000" />
            </x:top>
            <x:bottom style="thin">
                <x:color rgb="FF000000" />
            </x:bottom>
            <x:diagonal />
        </x:border>
    </x:borders>
    <x:cellStyleXfs count="1">
        <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
    </x:cellStyleXfs>
    <x:cellXfs count="4">
        <x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
        <x:xf numFmtId="0" fontId="1" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" />
        <x:xf numFmtId="0" fontId="2" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" />
        <x:xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" />
    </x:cellXfs>
</x:styleSheet>

FortyTwo · Answer 1 · 2018-03-27T14:26:21.523

Excel is limited to a palette of 56 colors. The indexes are stored rather than the actual RGB value as you have provided. I don't know how you are generating the xmls, but your font node should look something like this:

<x:fonts count="1">
    <x:font>
        <x:b />
        <x:sz val="12" />
        <x:color theme="1" />
        <x:name val="Arial" />
    </x:font>
</x:fonts>

If you use C# or VBA then you can set Colors to your liking by accessing Workbook.Colors Property

EDIT

Like for fonts, borders need to store indexes rather than the actual RGB value as you have provided. Your border node should be something like:

<x:left style="thin">
    <x:color indexed="64" />
</x:left>

also note, borders need to defined in the correct order: left, right, top, bottom, diagonal. Seems correct in your xml but nevertheless thought of mentioning it here.

This is a good example

Thanks for the reply, but the problem is not fonts nodes, I removed the fonts node and the error still happens, it is an error on the style xml, I still just can't figure out what it is — Ruben Monteiro, Mar 27 '18 at 13:59
I also though that I needed the index for the color but it seems that you can either do both... — Ruben Monteiro, Mar 28 '18 at 12:34

Add stylesheet to openxml excel file

1 Answers1