none
what's the difference between "ApplyFill=true" and "ApplyFill=false" RRS feed

  • Question

  • hi,

    In the excel document,  the following three seems the same, what is the difference?

    1) <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="1">...  //CellFormat.ApplyFill=true

    2) <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" >...                         //CellFormat.ApplyFill=null

    3) <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="0">...  //CellFormat.ApplyFill=false

    thanks,

    Jialinhai

    Monday, December 22, 2014 2:16 AM

Answers

  • Hi jialinhai,

    It's better to write in English as only English post is allowed in MSDN English forums. If you want to use Chines, please open up a new thread in Chinese forum.

    The documentation in MSDN doesn't clearly explain what the applyFill property does, and seems that the Excel application ignore this property. I tested with Excel 2007,2010,2013, all of them ignore this property. I think it might be a bug.

    As a workaround, you could change the fillId instead to change the style of the cell, for example, the Excel application reserves two fills:

    <fill>
           <patternFill patternType="none"/>
         </fill>
         <fill>
           <patternFill patternType="gray125"/>
         </fill>

    You could change the fillId to 0 to remove the fill temporarily.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by CaillenModerator Monday, December 29, 2014 6:55 AM
    • Marked as answer by jialinhai Monday, December 29, 2014 7:05 AM
    Monday, December 29, 2014 6:48 AM
    Moderator

All replies

  • Hi Jialinhai,

    The CellFormat.ApplyFill property represents for the applyFill attribute in the Open XML schema. It's of type boolean. If you set it to 1, it'll apply the Fill whose id equals the fillId attribute value. If there's no difference between the three values, it should be the problem with the Fill with fillId="2". The Fill may have no background or foreground which can be recognized by our eyes. Set a background or foreground for the fill, you'll see the difference.

    Check this sample code which apply fill for the cell:

    var styleSheet = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
    Fill fill = new Fill() { PatternFill = new PatternFill { ForegroundColor = new ForegroundColor { Rgb = fgcolor }, PatternType = PatternValues.Solid } };
    styleSheet.Fills.Append(fill);
    //or use the method Count(),not need to plus 1,but slowly.
    var fid = styleSheet.Fills.Count++;
    cellFormat.FillId = (UInt32)fid;
    By the way, if it's all about the Office Open XML specification, a better forum is Office XML, ODF, and Binary File Formats.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 25, 2014 7:21 AM
    Moderator
  • 多谢!

    看到您解答了不少中文的问题,还是用中文吧,描述能准确一些:)

    我做了一个简单的测试,手工创建了一个Excel 2007文档,将单元格A1设置了前景色和背景色。

    然后手工修改了styles.xml文档中的applyFill="0",其他不变,再次打开该文档后,看到填充效果是没变的。

    和我理解的不太一样,我的理解如下,您看看是否正确:

    ApplyFill="0"时,FillId是不起作用的,应该是默认没有填充的效果。

    ApplyFill="1"时,FillId起作用,显示对应的填充效果。

    PS:没显式写ApplyFill属性,等效于ApplyFill="0"还是ApplyFill="1"呢?在我的测试中,去掉该字段,填充效果仍然是起做用的。

    相关styles.xml如下,只修改了ApplyFill为0:

    <fills count="3">
        <fill>
          <patternFill patternType="none"/>
        </fill>
        <fill>
          <patternFill patternType="gray125"/>
        </fill>
        <fill>
          <patternFill patternType="gray0625">
            <fgColor theme="3" tint="0.39994506668294322"/>
            <bgColor rgb="FFFFFF00"/>
          </patternFill>
        </fill>
      </fills>
      <borders count="1">
        <border>
          <left/>
          <right/>
          <top/>
          <bottom/>
          <diagonal/>
        </border>
      </borders>
      <cellStyleXfs count="1">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0">
          <alignment vertical="center"/>
        </xf>
      </cellStyleXfs>
      <cellXfs count="2">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
          <alignment vertical="center"/>
        </xf>
        <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="0">
          <alignment vertical="center"/>
        </xf>
      </cellXfs>



    • Edited by jialinhai Friday, December 26, 2014 9:30 AM
    Friday, December 26, 2014 9:13 AM
  • Hi jialinhai,

    It's better to write in English as only English post is allowed in MSDN English forums. If you want to use Chines, please open up a new thread in Chinese forum.

    The documentation in MSDN doesn't clearly explain what the applyFill property does, and seems that the Excel application ignore this property. I tested with Excel 2007,2010,2013, all of them ignore this property. I think it might be a bug.

    As a workaround, you could change the fillId instead to change the style of the cell, for example, the Excel application reserves two fills:

    <fill>
           <patternFill patternType="none"/>
         </fill>
         <fill>
           <patternFill patternType="gray125"/>
         </fill>

    You could change the fillId to 0 to remove the fill temporarily.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by CaillenModerator Monday, December 29, 2014 6:55 AM
    • Marked as answer by jialinhai Monday, December 29, 2014 7:05 AM
    Monday, December 29, 2014 6:48 AM
    Moderator