none
Excel Color Tint Algorithm Incorrect RRS feed

  • Question

  • In the ECMA-376 Office Open XML File Formats document, and also here: http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.backgroundcolor.aspx, the documentation for the bgColor element's tint attribute indicates that the tint only modifies the luminance of a color when converted to the HSL color space. However, this does not seem to be the case in Excel. If I apply all versions of the Accent1 (X%) colors to the background of a cell, their bgColor elements are all written out with the same theme index and only differ in their tint value. But if I look at the Colors dialog for each cell and view the current color in the HSL color model, I see the following values:

    • H:151, S:116, L:134 - Accent1
    • H:150, S:109, L:231 - Accent1, Lighter 80%
    • H:151, S:114, L:206 - Accent1, Lighter 60%
    • H:151, S:115, L:182 - Accent1, Lighter 40%
    • H:151, S:117, L:100 - Accent1, Darker 25%
    • H:151, S:118, L:067 - Accent1, Darker 50%

    So obviously, the Hue and Saturation values are also affected by the tint. What is the actual tinting algorithm being used? Thank you for your help.

    Wednesday, March 28, 2012 3:33 PM

Answers

All replies

  • Hello Michael Dour,
                               Thank you for your inquiry about Office Open XML file format. One of the Open specifications team member will contact you shortly.

     
    Regards,
    Sreekanth Nadendla
    Microsoft Windows Open specifications

    Wednesday, March 28, 2012 4:12 PM
    Moderator
  • Hi Michael,

    I'll be looking into this and will get back to you shortly.

    Best regards,
    Tom Jebo
    Escalation Engineer
    Microsoft Open Specifications

    Wednesday, March 28, 2012 6:39 PM
    Moderator
  • Hi Michael,

    What version of Excel are you testing?  I don't see the bgColor change you're reporting.

    Tom

    Monday, April 2, 2012 6:18 PM
    Moderator
  • The HSL values I provided in the original post are from Excel 2010. But since you brought it up, here are the values I see when using Excel 2007:
    • H:151, S:116, L:134 - Accent1
    • H:151, S:112, L:230 - Accent1, Lighter 80%
    • H:151, S:114, L:206 - Accent1, Lighter 60%
    • H:151, S:115, L:182 - Accent1, Lighter 40%
    • H:151, S:115, L:100 - Accent1, Darker 25%
    • H:151, S:114, L:067 - Accent1, Darker 50%

    So the Hue value isn't changing, but the Saturation is still being affected by the tint. However, when I look at the values for the Accent2 theme, I see these values in Excel 2007 and the Hue is changing:

    • H:001, S:122, L:135 - Accent2
    • H:002, S:117, L:231 - Accent2, Lighter 80%
    • H:001, S:122, L:207 - Accent2, Lighter 60%
    • H:001, S:120, L:183 - Accent2, Lighter 40%
    • H:001, S:121, L:101 - Accent2, Darker 25%
    • H:001, S:122, L:067 - Accent2, Darker 50%
    Monday, April 2, 2012 7:35 PM
  • Hey Michael,

    Please send me your test xlsx file (send to dochelp at microsoft dot com, referencing this thread and my name) so I can compare the themes.  Mine are not adjusting the bgcolor as yours are. 

    Thanks,

    Tom

    Monday, April 2, 2012 7:44 PM
    Moderator
  • Thanks Tom, I have sent a file showing the issue.
    Monday, April 2, 2012 7:57 PM
  • Thanks Michael, received.  I'll take a look.

    Tom

    Tuesday, April 3, 2012 2:20 PM
    Moderator
  • Hi Mike,  

    Thanks for your patience on this.  Excel does exactly what the specification says, namely, applying the tint value *only* to the lightness or luminosity value.   Where the RGB and HSL values are affected is in the conversion between the two.  The L *and* S values affect the H (hue), and therefore the resulting R, G and B.  So, changing the L value with a tint, indeed *may* affect the S and H.  This is not part of the specification because it is up to the application developer to decide how to implement the conversion from HSL to RGB and vice versa.  Wikipedia discusses the relationship between these values and the coordinate systems in http://en.wikipedia.org/wiki/HSL_and_HSV.  This is not  part of the standard and not needed for interoperability for Excel.   

    Best regards,
    Tom Jebo
    Escalation Engineer
    Microsoft Open Specifications

    Monday, April 9, 2012 6:07 PM
    Moderator
  • Thank you for looking into this Tom, but I do not believe your answer is correct. The color algorithm actually is needed for interoperability for Excel, and here is an example of a situation when it matters: If I have a table in the workbook and I filter some cells in a column of that table based on font color, and the color I choose as the filter color is a tinted theme color, that color must be saved in a DXF record as a resolved RGB value. If it is saved as a tinted theme color or the RGB value is off by one or more for the R, G, or B components, all rows of the table will be hidden if the filters are reapplied. So if you are correct that tinting algorithm really does only affect the L value, then please provide the HSL/RGB conversion algorithms used by Excel because I have tried two separate implements for converting in both directions and nothing gives the correct result.
    Monday, April 9, 2012 6:45 PM
  • Mike,

    Thanks for the explanation, stay tuned.

    Tom

    Tuesday, April 10, 2012 6:38 PM
    Moderator
  • Mike,

    Sorry for the delay in getting this to you.  The correct algorithm is already published in http://support.microsoft.com/kb/29240/n.    For the following define in the code:

     #define  HLSMAX   RANGE /* H,L, and S vary over 0-HLSMAX */

    RANGE is equal to 240 for Excel.

    Please verify this with your code.

    Tom

    Monday, May 7, 2012 4:38 PM
    Moderator
  • Thanks, this seems to work. I did have this algorithm being used, but my HLSMAX value was 255, not 240. Also, the documentation in the original link I posted says HLSMAX is 255, but when using the tinting algorithm, you must also use 240. In addition, something that is hinted at by the example, but not said explicitly in that documentation is that after multiplying by (1.0+tint) or (1.0-tint), the result must be truncated (not rounded) before continuing on with the rest of the equation.
    Monday, May 7, 2012 5:45 PM
  • Thanks for the feedback Mike, we'll review the standard to see if we may need to submit a defect report. 

    Tom

    Monday, May 7, 2012 6:33 PM
    Moderator
  • I am trying to implement this same sort of color resolution for Word now and I notice that this color tinting algorithm does not work for Word like it does for Excel. For example, here are the values for "Accent2, Lighter 60%" in Excel:

    H:001, S:124, L:207 ---- R:230, G:184, B:183

    And here are the values for the same theme color with the same tinting in Word:

    H:001, S:120, L:206 ---- R:229, G:184, B:183

    The base colors for these theme colors are the same in both applications: R:192, G:80, B:77

    So is the same tinting algorithm used with a different HLSMAX, or is it a different algorithm?

    Monday, April 22, 2013 7:42 PM
  • Hi Michael,

    I'll check on this and see what I can find out. 

    Tom

    Monday, April 22, 2013 8:02 PM
    Moderator
  • Hi Michael,

    I'm still looking at this but could you tell me what the interoperability scenario is for this (for example, like the Excel case where it affected filtering on rows)?  I'm not sure if you can do the same kind of filtering in Word.  If it doesn't affect the standard for interoperability, then it would probably need to be discussed on the one of the Office developer forums as opposed to here. 

    Also, for my reference, are you referring to the themeTint or themeFillTint definitions in 17.3.5  Shading Properties (CT_Shd)?

    Tom


    Monday, April 29, 2013 5:59 AM
    Moderator
  • I am currently writing software to read and write Word files. The object model of this software allows a caller to get a resolved color value that would be seen in a UI if tinting or shading is applied to a theme color. Currently, that resolved color is off slightly due to these differences in color resolution. So it is not as severe as the filtering scenario, where the color had to be exact. But I would like to be able to report the correct color that would be seen in Word.
    Monday, April 29, 2013 4:04 PM
  • Hi Michael, 

    Based on the fact that there isn’t a clear interoperability scenario that drives the need to clarify this in the standard or implementation notes, I think it would be better to discuss this question in the Office Developer Forum.

    Tom

    Tuesday, April 30, 2013 5:30 AM
    Moderator