none
how to understand the process of cell formatting ? RRS feed

  • Question

  • Hi dear all,

    I'm reading the style part of ECMA-376 and get confused because I can't verify the descriptions in the standard. In my understanding , cell formatting is divided into 2 steps :

    The first step is so-called direct formatting which take formatting information(numFmt,font etc.) from xf record of CellXfs collection .

    The second step(cell style formatting) , if the xf record in the first step is  associated with another xf record which is defined in CellStyleXfs , that xf record will be searched and apply to the cell .

    Could someone clarify this for me ? Many thanks!!!


    nice done!


    • Edited by yushang Wednesday, October 3, 2012 5:50 PM
    Wednesday, October 3, 2012 5:43 PM

Answers

  • Hi yushang,

    That's basically correct but here is an example that might help to clarify the process.

    In sheet1.xml, we have this excerpt:

        <sheetData>

            <row r="1" spans="1:5" x14ac:dyDescent="0.3">

                <c r="A1" t="s">

                    <v>0</v>

                </c>

            </row>

            <row r="6" spans="1:5" x14ac:dyDescent="0.3">

                <c r="B6" s="2" t="s">

                    <v>2</v>

                </c>

                <c r="E6" s="1" t="s">

                    <v>1</v>

                </c>

            </row>

        </sheetData>

                  

    What this says is that we have three cells.  A1 has no style (<c ... s=x> attribute) and therefore will get the default style or Normal.  E6 (the second cell listed in row 6) is assigned style 1 (<c ... s="1">) and B6 is assigned style 2. 

    Now let's look at the styles.xml.  Here are the font and style related elements:

        <fonts count="3" x14ac:knownFonts="1">

            <font>

                <sz val="11"/>

                <color theme="1"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

            <font>

                <b/>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

            <font>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

        </fonts>

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/>

        </cellStyleXfs>

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>

            <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>

        </cellXfs>

        <cellStyles count="2">

            <cellStyle name="60% - Accent2" xfId="1" builtinId="36"/>

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

        </cellStyles>

    Cell A1 is assigned "Normal" as the default. The "Normal" cellStyle entry, as found in the cellStyles block, has an xfId of "0" which is an index into the cellStyleXfs indexed entry:

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

    Which identifies (among other things) the font index of "0" so that we end up with:

            <font>

                <sz val="11"/>

                <color theme="1"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

    Now let's take a look at cell E6.  This specifies style "1" (<c ... s="1">).  The s="1" refers to the following indexed entry in the cellXfs block:

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>  <== THIS ONE!

                                  <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>

                                 

    It too has an xfId, pointing to the "1" indexed entry in the cellStyleXfs block:

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/> <== THIS ONE!

                                 

    Which identifies the font as index "2" or this entry:

            <font>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

                                 

    The only difference between this font and the one used by "Normal" is the color theme.  In this last example of cell E6, we can see how it's necessary to traverse cellXfs first and then cellStyleXfs.  Now let's take a look at cell B6.  Referring back to the sheet1.xml, B6 specifies style "2" (<c ... s="2">).  Going through the same process as E6, you will see the following progression:

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>

            <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>  <== THIS ONE!

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/> <== THIS ONE!

                                 

    Notice in this case that the direct formatting of cellXfs index "2" applies the fontId="1" first and also overrides the style formatting done in cellStyleXfs indexed entry "1".  Looking at the font index "1":

            <font>

                <b/>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

                                 

    We see that there's a <b\> element indicating that this font is displayed bold.  This is the direct formatting that was applied to this cell.

    Here is what the cells look like so you can match the formatting to the SpreadsheetML:

    I hope this example helps to give you a better understanding of the process used in applying cell formatting.

    Best regards,
    Tom Jebo
    Escalation Engineer
    Microsoft Open Specifications

    Thursday, October 4, 2012 4:19 PM
    Moderator
  • Hey shinejin, 

    Perhaps this will help you. In [MS-OI29500] 2.1.720 it says: 

    --------------------------------

    d. The standard does not define different behaviors for @applyFont depending on whether the xf is in the CellXfs element or the CellStyleXfs element.

    For the cellStyleXfs element, this boolean value indicates whether the font formatting specified for this xf is applied. The default value for applyFont in cellStyleXfs is true.

    For the cellXfs element, this boolean value indicates whether the font formatting in the xf is overwritten by changes to the cellStyleXfs referenced by xfId. If applyFont is false and the font formatting in the cellStyleXfs changes, the new font formatting is set in the xf as well. If applyFont is true, then the xf will retain its existing font formatting. If the font formatting in the cellXf is different than the font formatting in the parent cellStyleXf, applyFont shall be true. The default value for applyFont in cellXfs element is false.

    --------------------------------

    Based on this, because cellStyleXfs (the named style properties) didn't change, the fontId in the cellXfs' xf record will be applied.

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Saturday, May 23, 2020 2:23 AM
    Moderator

All replies

  • Hi Yushang,

    Thanks for your inquiry.

    Someone from our group will get in touch with you shortly.

    Thanks and regards,

    Sebastian


    SEBASTIAN CANEVARI - MSFT Escalation Engineer Protocol Documentation Team

    Wednesday, October 3, 2012 8:50 PM
  • Hi yushang,

    That's basically correct but here is an example that might help to clarify the process.

    In sheet1.xml, we have this excerpt:

        <sheetData>

            <row r="1" spans="1:5" x14ac:dyDescent="0.3">

                <c r="A1" t="s">

                    <v>0</v>

                </c>

            </row>

            <row r="6" spans="1:5" x14ac:dyDescent="0.3">

                <c r="B6" s="2" t="s">

                    <v>2</v>

                </c>

                <c r="E6" s="1" t="s">

                    <v>1</v>

                </c>

            </row>

        </sheetData>

                  

    What this says is that we have three cells.  A1 has no style (<c ... s=x> attribute) and therefore will get the default style or Normal.  E6 (the second cell listed in row 6) is assigned style 1 (<c ... s="1">) and B6 is assigned style 2. 

    Now let's look at the styles.xml.  Here are the font and style related elements:

        <fonts count="3" x14ac:knownFonts="1">

            <font>

                <sz val="11"/>

                <color theme="1"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

            <font>

                <b/>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

            <font>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

        </fonts>

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/>

        </cellStyleXfs>

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>

            <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>

        </cellXfs>

        <cellStyles count="2">

            <cellStyle name="60% - Accent2" xfId="1" builtinId="36"/>

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

        </cellStyles>

    Cell A1 is assigned "Normal" as the default. The "Normal" cellStyle entry, as found in the cellStyles block, has an xfId of "0" which is an index into the cellStyleXfs indexed entry:

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

    Which identifies (among other things) the font index of "0" so that we end up with:

            <font>

                <sz val="11"/>

                <color theme="1"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

    Now let's take a look at cell E6.  This specifies style "1" (<c ... s="1">).  The s="1" refers to the following indexed entry in the cellXfs block:

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>  <== THIS ONE!

                                  <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>

                                 

    It too has an xfId, pointing to the "1" indexed entry in the cellStyleXfs block:

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/> <== THIS ONE!

                                 

    Which identifies the font as index "2" or this entry:

            <font>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

                                 

    The only difference between this font and the one used by "Normal" is the color theme.  In this last example of cell E6, we can see how it's necessary to traverse cellXfs first and then cellStyleXfs.  Now let's take a look at cell B6.  Referring back to the sheet1.xml, B6 specifies style "2" (<c ... s="2">).  Going through the same process as E6, you will see the following progression:

        <cellXfs count="3">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/>

            <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="1" applyFont="1"/>  <== THIS ONE!

        <cellStyleXfs count="2">

            <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>

            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyNumberFormat="0" applyBorder="0" applyAlignment="0" applyProtection="0"/> <== THIS ONE!

                                 

    Notice in this case that the direct formatting of cellXfs index "2" applies the fontId="1" first and also overrides the style formatting done in cellStyleXfs indexed entry "1".  Looking at the font index "1":

            <font>

                <b/>

                <sz val="11"/>

                <color theme="0"/>

                <name val="Calibri"/>

                <family val="2"/>

                <scheme val="minor"/>

            </font>

                                 

    We see that there's a <b\> element indicating that this font is displayed bold.  This is the direct formatting that was applied to this cell.

    Here is what the cells look like so you can match the formatting to the SpreadsheetML:

    I hope this example helps to give you a better understanding of the process used in applying cell formatting.

    Best regards,
    Tom Jebo
    Escalation Engineer
    Microsoft Open Specifications

    Thursday, October 4, 2012 4:19 PM
    Moderator
  • But I verified in Excel, Cell font always applies "fontId" that defined in <cellXfs><xf  fontId="1" ...></cellXfs> , whatever set "applyFont" and xfId defined font.

    that is the following case are same:

    case1:

    <cellXfs count="3">

    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>

            <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="1" />
            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1" applyFont="1"/>
    </cellXfs>

    case2:

    <cellXfs count="3">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
            <xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="1"/>
            <xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="1"/> <!-- without applyFont and always use fontId="2" -->
    </cellXfs>



    Thursday, May 21, 2020 3:04 AM
  • Hi shinejin,

    Thanks for contributing to this thread (although very old). We can verify your assertion here and get back to you. If you have an example workbook that you are using when you see the behavior and can share it with us, please do so we can also check to see if there is some other reason for your observation. 

    If you can't share on a cloud drive, please send it to dochelp at Microsoft dot com, referencing my name and the URL for this thread.

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Thursday, May 21, 2020 5:32 AM
    Moderator
  • Thanks for your response.

    pls refer to this doc:https://1drv.ms/u/s!AuX5WVT5K94sdhmORTXnQ9BTolE?e=c1lSNU

    <cellStyleXfs count="1">
    	<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
    </cellStyleXfs>
    <cellXfs count="4">
    	<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    	<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" /> <!-- fontId="2" was applied without applyFont -->
    	<xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1"/>
    	<xf numFmtId="0" fontId="3" fillId="0" borderId="0" xfId="0" applyFont="1"/>
    </cellXfs>
    <cellStyles count="1">
    	<cellStyle name="Normal" xfId="0" builtinId="0"/>
    </cellStyles>



    • Edited by shinejin Friday, May 22, 2020 6:41 AM
    Friday, May 22, 2020 6:41 AM
  • Thanks shinejin, I'll take a look.

    Tom

    Saturday, May 23, 2020 12:23 AM
    Moderator
  • Hey shinejin, 

    Perhaps this will help you. In [MS-OI29500] 2.1.720 it says: 

    --------------------------------

    d. The standard does not define different behaviors for @applyFont depending on whether the xf is in the CellXfs element or the CellStyleXfs element.

    For the cellStyleXfs element, this boolean value indicates whether the font formatting specified for this xf is applied. The default value for applyFont in cellStyleXfs is true.

    For the cellXfs element, this boolean value indicates whether the font formatting in the xf is overwritten by changes to the cellStyleXfs referenced by xfId. If applyFont is false and the font formatting in the cellStyleXfs changes, the new font formatting is set in the xf as well. If applyFont is true, then the xf will retain its existing font formatting. If the font formatting in the cellXf is different than the font formatting in the parent cellStyleXf, applyFont shall be true. The default value for applyFont in cellXfs element is false.

    --------------------------------

    Based on this, because cellStyleXfs (the named style properties) didn't change, the fontId in the cellXfs' xf record will be applied.

    Best regards,
    Tom Jebo
    Sr Escalation Engineer
    Microsoft Open Specifications

    Saturday, May 23, 2020 2:23 AM
    Moderator
  • Thanks for the clear explanation.
    Monday, May 25, 2020 1:32 AM