handlebars.jsxlsxjsreport

jsreport handlebars engine and xlsx recipe: how to fill cell with color


I'm trying to fill a cell with "red" or "green" depending on its value; This is what I tried so far:

{{#xlsxAdd 'xl/worksheets/sheet1.xml' "worksheet.conditionalFormatting"}}
    <conditionalFormatting sqref="G7:M7">
        <cfRule type="cellIs" dxfId="0" priority="1" operator="notBetween">
            <formula>{{productObj.min}}</formula>
            <formula>{{productObj.max}}</formula>
        </cfRule>
    </conditionalFormatting>
{{/xlsxAdd}}
{{#xlsxAdd 'xl/worksheets/sheet1.xml' "worksheet.conditionalFormatting"}}
    <conditionalFormatting sqref="G7:M7">
        <cfRule type="cellIs" dxfId="2" priority="1" operator="between">
            <formula>{{productObj.min}}</formula>
            <formula>{{productObj.max}}</formula>
        </cfRule>
    </conditionalFormatting>
{{/xlsxAdd}}

but it seems that the condition notBetween always matches the cell, but after I download the file then change the value of the cell, the conditions start to work correctly.

I don't know if reportjs is considering my numbers as strings since changing the value after downloading the file works perfectly.

-- UPDATE ---

Found this issue: https://github.com/jsreport/jsreport-html-to-xlsx/issues/7

It was a problem with jsreport it's self for adding ' before my numbers which caused the conditional rendering to fail. but still, I would appreciate a proper way to fill a cell "NOT" with the conditional formatting so I'll leave the question

--- END UPDATE ---

I also tried to fill with static color this but it didn't work:

 {{#xlsxReplace "xl/styles.xml" "styleSheet.fills"}}
    <fills count="5">
        <fill>
            <patternFill patternType="none"/>
        </fill>
        <fill>
            <patternFill patternType="lightGray"/>
        </fill>
        <fill>
            <patternFill patternType="solid">
                <fgColor rgb="FFBFBFBF"/>
                <bgColor rgb="FFBFBFBF"/>
            </patternFill>
        </fill>
        <fill>
            <patternFill patternType="solid">
                <fgColor rgb="FFFF0000"/>
                <bgColor rgb="FFFF0000"/>
            </patternFill>
        </fill>
        <fill>
            <patternFill patternType="solid">
                <fgColor rgb="FF00FF00"/>
                <bgColor rgb="FF00FF00"/>
            </patternFill>
        </fill>
    </fills>
{{/xlsxReplace}}
// then adding the cell like this:
 <c t="inlineStr" fillId="3" ><is><t>{{val}}</t></is></c>

but still not doing the job, I even tried to add dxfs instead of fills, if anyone can help me find out what am I doing wrong?!


Solution

  • its (zero-based index) so s="1" will be the 2nd element <cellXfs>

    just remember the zero-based index and the counts attribute will be count of elements inside

    in the main jsreport file add

    {{#xlsxReplace "xl/styles.xml"}}
        {#asset style.xml}}
    {{/xlsxReplace}} 
    

    create style.xml and in it add

        <?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" x14ac:knownFonts="2">
            <font>
                <sz val="12" />
                <name val="Calibri" />
                <b />
            </font>
            <font>
                <sz val="12" />
                <name val="Calibri" />
            </font>        
        </fonts>
        <fills count="5">
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="bfbfbf" />
                </patternFill>
            </fill>
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="bfbfbf" />
                </patternFill>
            </fill>            
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="bfbfbf" />
                </patternFill>
            </fill>    
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="FFFF00" />
                </patternFill>
            </fill>
            <fill>
                <patternFill patternType="solid">
                    <fgColor rgb="FF0000" />
                </patternFill>
            </fill>      
        </fills>
        <borders count="1">
            <border>
                <left/>
                <right/>
                <top/>
                <bottom/>
                <diagonal/>
            </border>
        </borders>
        <cellXfs count="7">
            <xf />
            <xf fontId="0" fillId="2">
                <alignment horizontal="center" vertical="center" />
            </xf>
            <xf fontId="0" fillId="3">
                <alignment horizontal="center" vertical="center" />
            </xf>
            <xf fontId="0" fillId="4">
                <alignment horizontal="center" vertical="center" />
            </xf>
            <xf fontId="1">
                <alignment horizontal="center" vertical="center" />
            </xf>
            <xf fontId="1" fillId="3">
                <alignment horizontal="center" vertical="center" />
            </xf>
            <xf fontId="1" fillId="4">
                <alignment horizontal="center" vertical="center" />
            </xf>
        </cellXfs>   
    </styleSheet>