Conditional cell formatting

[DerekDomino]

Hello,

I would like to know if/how conditional formatting of cells can be applied with the fantastic js-xslx?

I'm pretty new with conditional formatting in Excel, there's a variety of conditional formatting cases along Excel, so I guess it's no trivial to implement them.

Hi @DerekDomino
try this :

cell = { 
    s: {
        fill: { 
            fgColor : { 
                rgb : myVar == 1 ? "FFFFFFFF" : myVar  == 2 ? "FFF5770F": myVar  == 3 ? "FFD03030" : myVar  == 4 ? "FFD03030" : myVar  == 5 ? "FF51C751" : "FF51C751"
            }
        }
    }
}

[DerekDomino]

Hi @Jbmath,

Thanks, but what you wrote is javascript-side conditional formatting where the colour is set at runtime (js).

What is needed is how to make js-xlsx to create Excel cells that use Excel-defined conditional formatting rules.

Hum... sorry, I didn't understood.

Have you see this documentation?
https://www.npmjs.com/package/xlsx-style

[zbjornson]

@Jbmath he means Excel's Conditional Formatting feature: https://support.office.com/en-us/article/quick-start-apply-conditional-formatting-6b6f7c2a-5d62-45a1-8f67-584a76776d67. (+1 from me)

[SheetJSDev]

We offer this in the Pro compendium. Since companies have paid for the features already, it would be unfair to them if we turned around and made it available as open source. We have a longer comment in a gist.

[alexcodelab]

Spend some hours and wasting alot of cofe and pizza. I have a workaround!
VB (Makro) was the solution!

I hope i helps someone :)

Sub getBF()
 Dim a As Variant, c As Long, ausgabe As Variant, fIndex As Integer, iFormula As Integer, iColorA As Integer, iColorB As Integer, oZelle As Object, sampleRange As Range

 For Each oZelle In Worksheets(1).Range("A29:AT81")
 a = Split(iColor(oZelle, "RGB"), ",")
 oZelle.Interior.Color = RGB(a(0), a(1), a(2))
 Next
 Debug.Print "fertig"
End Sub

Public Function iColor(rng As Range, Optional formatType As String) As Variant
'formatType: Hex for #RRGGBB, RGB for (R, G, B) and IDX for VBA Color Index
    Dim colorVal As Variant
    colorVal = rng.DisplayFormat.Interior.Color
    Select Case UCase(formatType)
        Case "HEX"
            iColor = "#" & Hex(colorVal Mod 256) & Hex((colorVal \ 256) Mod 256) & Hex((colorVal \ 65536))
        Case "RGB"
            iColor = (colorVal Mod 256) & ", " & ((colorVal \ 256) Mod 256) & ", " & (colorVal \ 65536)
        Case "IDX"
            iColor = rng.Interior.ColorIndex
        Case Else
            iColor = colorVal
    End Select
End Function