none
Recognising a date format in an worksheet.xml RRS feed

  • Question

  • I am currently trying to read an excel spreedsheet into an xml model that we have created.  I am reading the worksheet.xml fine and I can determine when the value in the cell is in a boolean or string format. I cannot easily however find a programatic way to distinguish between whether a value is a serialized date or a number. 

     

    My first thoughts was that you could do this through the style.xml page and that it has something to do with the field 'numFmtId' in the node 'cellXfs '.  If so is there a standard list of all the available numFmtId allowed? Can anyone please help. 

     

    Thanks

     

    Steve

    Friday, August 17, 2007 4:04 PM

All replies

  • Did you see the attribute "s" into the cell node?

    This is a date: 

         <c r="E1" s="1">
            <v>40000</v>
          </c>

    ==> (s="1")

     

    Saturday, August 18, 2007 2:16 PM
  •  scifigooner wrote:
     

    My first thoughts was that you could do this through the style.xml page and that it has something to do with the field 'numFmtId' in the node 'cellXfs '.  If so is there a standard list of all the available numFmtId allowed? Can anyone please help. 



    You're actually on the right track.   Check out section 8 in this post <http://ooxmlisdefectivebydesign.blogspot.com/2007/08/microsoft-office-xml-formats-defective.html> which details the trail you need to follow.  However, the 'id' attribute in the 'xf' element did not exist for me when I was looking for it.

    Also in my experience, a date cell doesn't always have s="1", the spreadsheet that I'm attempting to work with at the moment has s="7".  So I wouldn't rely on the 's' attribute to determine the cell type.
    Monday, March 3, 2008 6:15 AM
  • the ID attribute in the xf element refers to the id in the numFmt element.
    However, some of them seem to be a built in format, as such they use the built-in ID.
    I have no idea how to derive those built-in ID.

    Btw, thanks for the link to the blog.
    Is it right to say that it is difficult to derived whether a value is a number or a date?
    basically we need to know whether the style is referring to a date style.
    Wednesday, July 16, 2008 1:53 PM
  • just want to add the the value of the s attribute is based on the order of the xf elements in the cellXfs element.
    It is the index (start from 0) that point to the its location in the cellXfs element.

    Hope this help.
    Wednesday, July 16, 2008 1:58 PM
  • go to the ECMA website and search for standard index 376. Part 4 of this standards gives detailed insight into the markup language reference. There the numFmtID is epxlained in detail, and numFmt = 14 refers to Short Date data type.

     

    Regards,

    Kalpana

     

    Thursday, August 14, 2008 5:04 PM