What does the "s" attribute signify in a cell tag in XLSX

Question

In the XML of a worksheet in an XLSX file (Excel 2007) cell tags that have a "t" attribute equal to "s" are string types. The value tag inside the c needs to be looked up and converted via the sharedStrings document. But, some cells have s="237" and no t attribute at all. The value tag has an integer like 39448 which does not relate to the sharedStrings document. The value as it appears in Excel is a date 1/1/2008.

What does the s attribute signify in a c tag in XLSX?

Unknown value

<c r="B47" s="237">
    <v>39448</v>
</c>

Shared String value

<c r="C47" t="s">
    <v>7</v>
</c>

score 10 · Accepted Answer · edited Jan 15 '23 at 11:09

10

The s attribute refers to a style. "237" is a style defined in the styles.xml file.

<v>39448</v>

...is most likely a date in double format. And the style 237 tells excel to display 39448 in date format.

You can see an example of how this works here: Link

edited Jan 15 '23 at 11:09

Glorfindel

21,988
13
81
109

answered Jul 14 '10 at 18:43

mscccc

2,190
6
25
39

But where does `237` come from? i'm looking at the blog. He mentions everything is *zero-based*, but he references style `1`, but there is no `0-th` style anywhere. Where does `1` come from? Where does `237` come from? – Ian Boyd Nov 08 '13 at 16:38
It's an identifier for the style in the styles.xml file. It can be any integer. – mscccc Nov 09 '13 at 18:47

score 4 · Answer 2 · answered Jul 21 '17 at 12:33

The s attribute refers that is equal to 237, point to the 237th element found in parent element in the styles.xml file contained in the xlsx file.

If the cell value is a date, the element can be similar to the following code

<xf numFmtId="167" 
    fontId="6" 
    fillId="0" 
    borderId="6" 
    xfId="3" 
    applyNumberFormat="1" 
    applyFont="1" 
    applyFill="1" 
    applyBorder="1" 
    applyAlignment="1">
        <alignment horizontal="center"/>
</xf>

At this point we don't see that this cell represent a date type. To understand that, we must find the <numFmtId> with "167" as key.

This value can be found at begin of styles.xml file

<numFmts count="7">
    <numFmt numFmtId="164" formatCode="[$-409]d\-mmm\-yy;@"/>
    <numFmt numFmtId="165" formatCode="0.000"/>
    <numFmt numFmtId="166" formatCode="0.0"/>
    <numFmt numFmtId="167" formatCode="[$-409]d\-mmm\-yyyy;@"/>
    <numFmt numFmtId="168" formatCode="0.0%"/>
    <numFmt numFmtId="169" formatCode="00000"/>
    <numFmt numFmtId="170" formatCode="0.0000"/>
</numFmts>

The line with numFmtId="167" indicate that the cell's value is a date formatted using following string "[$-409]d-mmm-yyyy;@"

In resume, to find if a cell contains a number or date we must

find the S (=style) attribute of <c> element
find the numFmtId attribute of the <xf> element in styles.xml file in xlsx file.
find the formatCode attribute of <numFmt> that has numFmtId as key
see if format is a date format or a number format

I hope that can help others.

What does the "s" attribute signify in a cell tag in XLSX

2 Answers2

Linked