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?!
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>