none
NumFmtId and formatCode question [Excel 2007] RRS feed

  • Question

  • I have a worksheet where one column "A" can have custom number format: 0"A" (number plus any text). Here's XML content of styles.xml file (some content was removed for brevity):

    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    	<numFmts count="3">
    		<numFmt numFmtId="164" formatCode="0&quot;А&quot;"/>
    		<numFmt numFmtId="165" formatCode="0&quot;N091716&quot;"/>
    		<numFmt numFmtId="166" formatCode="0.0"/>
    	</numFmts>
    	<cellXfs count="21">
    		<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"/>
    		<xf numFmtId="1" fontId="4" fillId="0" borderId="4" xfId="0">
    			<alignment horizontal="right"/>
    		</xf>
    		<xf numFmtId="2" fontId="4" fillId="0" borderId="4" xfId="0">
    			<alignment horizontal="right"/>
    		</xf>
    		<xf numFmtId="164" fontId="4" fillId="0" borderId="4" xfId="0">
    			<alignment horizontal="right"/>
    		</xf>
    		<xf numFmtId="165" fontId="4" fillId="0" borderId="4" xfId="0">
    			<alignment horizontal="right"/>
    		</xf>
    		<xf numFmtId="166" fontId="4" fillId="0" borderId="4" xfId="0">
    			<alignment horizontal="right"/>
    		</xf>
    	</cellXfs>
    </styleSheet>
    

    OOXML Specification ECMA-376 1-st Edition, Part 4: Markup Language Reference, §3.8.30 (page 2127, line 14).

    According to this paragraph, there's a list of formatCodes which are "implied rather than explicitly saved in the file". This means that we won't see the reference to <numFmt>  thru numFmtId attribute of <xf> tags. Hence, all other remained number can be whatever.

    Under "custom format" I mean the custom format IS NOT taken from built-in formats but rather is manually defined by user. In other words, it was hand-type in Format Cells -> Custom -> Type.

    Here's question: So, does it mean that all these hand-typed custom formats' numFmtIds always begin with 164?

     

     


    There is no knowledge that is not power.
    Thursday, December 8, 2011 4:36 AM

Answers

  • Hi Motaro,

     

    Thanks for posting in the MSDN Forum.

     

    It’s based on my experience that we are hard to say that custom formats numFmtIds begin with 164. It’s on my side is 176, and on my colleague side is 164.

     

    I hope it can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by JohnyL Friday, December 9, 2011 12:19 PM
    Friday, December 9, 2011 6:49 AM
    Moderator

All replies

  • Hi Motaro,

     

    Thanks for posting in the MSDN Forum.

     

    It’s based on my experience that we are hard to say that custom formats numFmtIds begin with 164. It’s on my side is 176, and on my colleague side is 164.

     

    I hope it can help you.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by JohnyL Friday, December 9, 2011 12:19 PM
    Friday, December 9, 2011 6:49 AM
    Moderator
  • Hi, Tom!

    Thanks for your answer! If numFmtIds with custom format would begin with 164, then I would avoid extra check. If, as you say, this isn't a case, then I have to traverse all nodes. Thanks once again!


    There is no knowledge that is not power.
    • Edited by JohnyL Friday, December 9, 2011 12:19 PM
    Friday, December 9, 2011 12:19 PM