none
why cell style did not take effect? RRS feed

  • Question

  • Hi dear all,

    I have the following stylesheet and sheet data

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
     <fonts count="2">
      <font>
       <name val="Calibri"/>
       <family val="2"/>
       <color indexed="2"/>
       <scheme val="minor"/>
      </font>
      <font>
       <name val="Calibri"/>
       <family val="2"/>
       <color indexed="4"/>
       <scheme val="minor"/>
      </font>
     </fonts>
     <fills count="1">
      <fill>
       <patternFill patternType="darkGrid">
        <fgColor rgb="00FF0000"/>
        <bgColor rgb="0000FF00"/>
       </patternFill>
      </fill>
     </fills>
     <borders count="1">
      <border>
       <left style="mediumDashed">
        <color indexed="2"/>
       </left>
       <right style="mediumDashed">
        <color indexed="2"/>
       </right>
       <top style="mediumDashed">
        <color indexed="2"/>
       </top>
       <bottom style="mediumDashed">
        <color indexed="2"/>
       </bottom>
       <diagonal>
       </diagonal>
       <vertical>
       </vertical>
       <horizontal>
       </horizontal>
      </border>
     </borders>
     <cellXfs count="2">
      <xf numFmtId="3" fontId="1" fillId="0" borderId="0">
      </xf>
      <xf numFmtId="3" fontId="1" fillId="0" borderId="0">
      </xf>
     </cellXfs>
    </styleSheet>

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
     <cols>
      <col min="1" max="2" width="20"/>
     </cols>
     <sheetData>
      <row r="1">
       <c r="A1" s="0">
        <v>1000000001</v>
       </c>
       <c r="B1" s="1">
        <v>1000000001</v>
       </c>
      </row>
     </sheetData>
    </worksheet>

    B1 was displayed as expected : blue font and thounds seperator , but A1 did not display the expected thounds seperator and the font color is incorrect (it should be blue) , it seems the cell format 0 did not take effect on A1 at all ! How did this happen ? Many thanks!


    nice done!



    • Edited by yushang Saturday, October 6, 2012 8:14 AM
    Saturday, October 6, 2012 8:05 AM

Answers

  • Hi yushang, in addition to what I mentioned previously,  if no cell styles are defined, Excel will define a cell style to represent the normal style, and set it up with default settings. It will use xfid 0 in creating this new cell style. The solution to this is to do what I mentioned in my previous post. I have filed a request to have this information added to MS-OI29500 which should be included in a future release of the document.

     

    Please let me know if you have any other questions. Thank you.


    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Monday, November 5, 2012 3:29 PM
    Moderator

All replies

  • Hi yushang,

    Thanks for your question.

    Someone from our team will get in touch with your shortly.

    Regards,


    SEBASTIAN CANEVARI - MSFT Escalation Engineer Protocol Documentation Team

    Sunday, October 7, 2012 1:47 AM
  • Hi yushang, I am the engineer who will be working with you on this issue. I am currently researching the problem and will provide you with an update soon. Thank you for your patience.

    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Monday, October 8, 2012 3:16 PM
    Moderator
  • Hi yushang, please take a look at the second section of XML that you provided and notice that each cell has a different value for the 's' attribute, which is the Style Index. Cell A1 has a value of 0 and cell B1 has a value of 1. If you expect both cells to be formatted the same they should have matching Style Index values.

    You can find more information about that in ISO/IEC 29500, Part 1, section 18.3.1.4. Please let me know if you have any other questions.


    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Monday, October 8, 2012 5:43 PM
    Moderator
  • Hi Josh , I think A1 shoud have the thounds separator , but it did not . In fact , xf 0 and 1 are the same . Thanks .

    nice done!

    Tuesday, October 9, 2012 1:02 AM
  • Hi yushang, what version of Excel did you use to create that file? The XML from my test file looks quite different. Could you send me your test file to examine? You can send that to dochelp(at)microsoft(dot)com and reference this thread.

    Thanks.


    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Tuesday, October 9, 2012 2:27 PM
    Moderator
  • Hi Josh,

    I've sent you the file ooxml_msdn2.xlsx , the subject is to Josh Curry . I created this file with notepad . Many thanks! BTW , the file may be a slightly different from what I posted here . But the semantic is the same.


    nice done!


    • Edited by yushang Wednesday, October 10, 2012 2:51 PM
    Wednesday, October 10, 2012 2:48 PM
  • Hi yushang, Thank you for sending me the file. I have been able to verify the behavior that you described.  I am still looking into this issue. I hope to have more information for you soon. Your patience is greatly appreciated.


    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Thursday, October 11, 2012 7:04 PM
    Moderator
  • Hi Josh,

    Any update please ?


    nice done!

    Tuesday, October 30, 2012 1:18 PM
  • Hi yushang, I have been able to make some progress on this. I discovered that you can 'fix' the problem by adding a CellStyle with an xfId of 0. However, I am still looking into why that fixes it. There is nothing in the ISO/IEC 29500 specification or MS-OI29500 that explains the behavior. I will let you know when I have more information. Your patience is greatly appreciated.

    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Tuesday, October 30, 2012 6:06 PM
    Moderator
  • Hi yushang, in addition to what I mentioned previously,  if no cell styles are defined, Excel will define a cell style to represent the normal style, and set it up with default settings. It will use xfid 0 in creating this new cell style. The solution to this is to do what I mentioned in my previous post. I have filed a request to have this information added to MS-OI29500 which should be included in a future release of the document.

     

    Please let me know if you have any other questions. Thank you.


    Josh Curry (jcurry) | Escalation Engineer | Open Specifications Support Team

    Monday, November 5, 2012 3:29 PM
    Moderator
  • Thanks . I'll try it.

    nice done!

    • Marked as answer by yushang Tuesday, November 13, 2012 5:45 AM
    • Unmarked as answer by yushang Tuesday, November 13, 2012 5:45 AM
    Tuesday, November 13, 2012 5:45 AM