Theme confusion in SpreadsheetML

Question

I've been fighting this all day. Inside my styles.xml file I have color information given like so:

<fgColor theme="0" tint="-0.249977111117893" />

ECMA 376 defines a theme color reference as:

Index into the <clrScheme> collection, referencing a particular <sysClr> or <srgbClr> value expressed in the Theme part.

Ok, that sounds easy. Here is an excerpt from my clrScheme xml:

<a:clrScheme name="Office">
<a:dk1>
<a:sysClr val="windowText" lastClr="000000" />
</a:dk1>
<a:lt1>
<a:sysClr val="window" lastClr="FFFFFF" />
</a:lt1>

Index zero is black, and they are wanting to darken it? I can tell you that after the tint is applied, the color should be #F2F2F2.

My confusion is what does theme="0" really mean? It can't possible mean to darken #000000. Checking MSDN only confuses me even more. From http://msdn.microsoft.com/en-us/library/dd560821.aspx

note that the theme color integer begins counting from left to right in the palette starting with zero. Theme color 3 is the dark 2 text/background color.

Actually, if you start counting at zero the third entry is Light 2. Dark 2 is the second one. Can anyone here shed some light on this subject for me? What does theme="0" really mean?

Here is the VB6 code I have been working with to apply the tint. You can paste it into your vba editor and run the test sub.

Public Type tRGB
    R As Byte
    G As Byte
    B As Byte
End Type

Public Type tHSL
    H As Double
    S As Double
    L As Double
End Type

Sub TestRgbTint()
    Dim c As tRGB
    RGB_Hex2Type "ffffff", c
    RGB_ApplyTint c, -0.249977111117893
    Debug.Print Hex(c.R) & Hex(c.G) & Hex(c.B)
End Sub

Public Sub RGB_Hex2Type(ByVal HexString As String, RGB As tRGB)
    'Remove the alpha channel if it exists
    If Len(HexString) = 8 Then
        HexString = mID(HexString, 3)
    End If

    RGB.R = CByte("&H" & Left(HexString, 2))
    RGB.G = CByte("&H" & mID(HexString, 3, 2))
    RGB.B = CByte("&H" & Right(HexString, 2))
End Sub

Public Sub RGB_ApplyTint(RGB As tRGB, tint As Double)
    Const HLSMAX = 1#
    Dim HSL As tHSL

    If tint = 0 Then Exit Sub

    RGB2HSL RGB, HSL

    If tint < 0 Then
        HSL.L = HSL.L * (1# + tint)
    Else
        HSL.L = HSL.L * (1# - tint) + (HLSMAX - HLSMAX * (1# - tint))
    End If

    HSL2RGB HSL, RGB
End Sub

Public Sub HSL2RGB(HSL As tHSL, RGB As tRGB)
    HSL2RGB_ByVal HSL.H, HSL.S, HSL.L, RGB
End Sub

Private Sub HSL2RGB_ByVal(ByVal H As Double, ByVal S As Double, ByVal L As Double, RGB As tRGB)
    Dim v As Double
    Dim R As Double, G As Double, B As Double

    'Default color to gray
    R = L
    G = L
    B = L
    If L < 0.5 Then
        v = L * (1# + S)
    Else
        v = L + S - L * S
    End If
    If v > 0 Then
        Dim m As Double, sv As Double
        Dim sextant As Integer
        Dim fract As Double, vsf As Double, mid1 As Double, mid2 As Double
        m = L + L - v
        sv = (v - m) / v
        H = H * 6#
        sextant = Int(H)
        fract = H - sextant
        vsf = v * sv * fract
        mid1 = m + vsf
        mid2 = v - vsf
        Select Case sextant
            Case 0
                R = v
                G = mid1
                B = m
            Case 1
                R = mid2
                G = v
                B = m
            Case 2
                R = m
                G = v
                B = mid1
            Case 3
                R = m
                G = mid2
                B = v
            Case 4
                R = mid1
                G = m
                B = v
            Case 5
                R = v
                G = m
                B = mid2
        End Select
    End If

    RGB.R = R * 255#
    RGB.G = G * 255#
    RGB.B = B * 255#
End Sub

Public Sub RGB2HSL(RGB As tRGB, HSL As tHSL)
    Dim R As Double, G As Double, B As Double
    Dim v As Double, m As Double, vm As Double
    Dim r2 As Double, g2 As Double, b2 As Double

    R = RGB.R / 255#
    G = RGB.G / 255#
    B = RGB.B / 255#

    'Default to black
    HSL.H = 0
    HSL.S = 0
    HSL.L = 0
    v = IIf(R > G, R, G)
    v = IIf(v > B, v, B)
    m = IIf(R < G, R, G)
    m = IIf(m < B, m, B)
    HSL.L = (m + v) / 2#
    If HSL.L < 0 Then
        Exit Sub
    End If
    vm = v - m
    HSL.S = vm
    If HSL.S > 0 Then
        If HSL.L <= 0.5 Then
            HSL.S = HSL.S / (v + m)
        Else
            HSL.S = HSL.S / (2# - v - m)
        End If
    Else
        Exit Sub
    End If
    r2 = (v - R) / vm
    g2 = (v - G) / vm
    b2 = (v - B) / vm
    If R = v Then
        If G = m Then
            HSL.H = 5# + b2
        Else
            HSL.H = 1# - g2
        End If
    ElseIf G = v Then
        If B = m Then
            HSL.H = 1# + r2
        Else
            HSL.H = 3# - b2
        End If
    Else
        If R = m Then
            HSL.H = 3# + g2
        Else
            HSL.H = 5# - r2
        End If
    End If
    HSL.H = HSL.H / 6#
End Sub

Answer 1

I checked out the xsl from odf-converter.sourceforge.net and it looks like 0 and 1 are switched and 2 and 3 are switched. Here is the xsl part:

    <xsl:variable name="theme">
      <xsl:choose>
        <xsl:when test="@theme = 0">
          <xsl:text>1</xsl:text>
        </xsl:when>
        <xsl:when test="@theme = 1">
          <xsl:text>0</xsl:text>
        </xsl:when>
        <xsl:when test="@theme = 2">
          <xsl:text>3</xsl:text>
        </xsl:when>
        <xsl:when test="@theme = 3">
          <xsl:text>2</xsl:text>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="@theme"/>
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>

Everything is matching up now. Notice the 0=1, 1=0, 2=3, 3=2 switch.

Answer 2

Yeah, it's the index of the theme color. 0 being "the first one", which means 1. Looks like you're doing Excel. For PowerPoint, note that the tint/shade algorithm is different - it's not based on HSL but rather Linear RGB.