Where is the Normal Style Font defined in an XLSX file when file uses multiple fonts

Question

Hi,

I am working on a project that needs to extract data from the internal xml files stored within an xlsx file.

The part I am currently working on involves trying to extract the xlsx files "Normal font" information. As I understand it, this information is stored in the internal file xl/styles.xml.

This information is easy to extract when the file/sheet contains a single font. However I don't know how to extract the correct "Normal font" info if the xl/styles.xml file contains multiple font definitions (i.e. when the <fonts> tag contains more than one <font> child element).

I originally thought the "Normal font" was always the first font defined but have found xlsx files where this is not the case.

So, my question: How can I extract the "Normal font" information for the xlsx file from the internal file xl/styles.xml when the <fonts> tag contains more than one <font> child ?

P.S. I have found the following related question on this forum:
https://social.msdn.microsoft.com/Forums/SECURITY/en-US/0af5e410-493a-4478-8b84-5b7d92755561/where-is-the-normal-style-font-defined-in-an-xlsx-file?forum=os_binaryfile

However, it does not explain how to find the "Normal font" when multiple fonts are defined for the xlsx file (in xl/styles.xml)

Many thanks in advance.

Answer 1

Hi @Chazg ,

You should take a look at ISO 29500-1:2016 where styles are defined for SpreadsheetML and workbook packages created by Excel. The standard can be downloaded here:

ISO/IEC 29500-1:2016

In section 18.8.7 cellStyle (Cell Style), you will find attributes on the cellStyle tags used in the xl/styles.xml part. In particular xfId is used to identify which formatting record is used by a given named style like "Normal". This xf formatting record in the cellStylesXfs block will contain a fontId attribute that specifies the index into the fonts block of the correct font to be used.

For example, you will see something like this in xl/styles.xml:

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac x16r2 xr" 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">  
    <fonts count="2" x14ac:knownFonts="1">  
        <font>  
            <sz val="11"/>  
            <color theme="1"/>  
            <name val="Calibri"/>  
            <family val="2"/>  
            <scheme val="minor"/>  
        </font>  
        <font>  
            <sz val="11"/>  
            <color theme="1"/>  
            <name val="Envy Code R"/>  
            <family val="3"/>  
        </font>  
    </fonts>  
    <cellStyleXfs count="1">  
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>  
    </cellStyleXfs>  
    <cellXfs count="2">  
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>  
        <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/>  
    </cellXfs>  
    <cellStyles count="1">  
        <cellStyle name="Normal" xfId="0" builtinId="0"/>  
    </cellStyles>  

In this example, the first font (line 3 above) is index 0 in the font list and specifies Calibri.

Normal (line 25) specifies an xfId of 0 which points to the first and only indexed xf record in cellStyleXfs (line 18). Normal is a built-in style which means it will be specified and have the attribute builtinId set to 0 (which is for Normal). If the author of the workbook modifies the Normal style, it will have the customBuiltin attribute set to 1 for true like this:

<cellStyle name="Normal" xfId="0" builtinId="0" customBuiltin="1"/>  

I hope this helps, let me know if you have more questions.

Best regards,
Tom Jebo
Microsoft Open Specifications Support

Answer 2

Hi @Tom Jebo ,

Many thanks for your reply.

If I understand correctly, the following procedure needs to be followed in order to identify the 'Normal font' of an xlsx/xlsm/etc. file:

STEP 1:
Open the internal xml file xl/styles.xml

STEP 2:
Locate the <cellStyles> tag and find the child tag <cellStyle> that contains the attribute builtinId="0". For this child tag make a note of the value of attribute "xfId".

STEP 3:
Now locate the <cellStyleXfs> tag. Find the nth child tag <cellStyleXf> where n is the value of the "xfId" attribute found in STEP 2 (n is zero based). For this child tag make
a note of the value of the attribute "fontId"

STEP 4:
Now locate the <fonts> tag. Find the mth child tag <font> where m is the value of the "fontId" attribute found in STEP 3 (m is, again, zero based).
This <font> tag information is used by the xlsx/xlsm/etc. file for the "Normal font".

Is this correct ?