Premium plugin : Conditional Style

JSpreadsheet Plugin : Conditional Style

For JSpreadsheet v7

You can buy this plugin on Repo plugin conditionalStyle Demo is available on demo

For JSpreadsheet v8 and v9:

Plugin available on Repo plugin conditionalStyle for JSS v8+

Dependencies

• for v1.x JSpreadsheet Pro v7
• for v2.x JSpreadsheet Pro v8 or JSpreadsheet Pro v9

Options of plugin

Option name	Description	Type	Default Value
allUpdateAlways	Update all cell on all refresh, when this property is set to false, refresh only cell changed and impacted (linked by formula)	Boolean	false
rules	Define all rules in order of apply	Array of rules	[]
pathClassName	Path css class name. When you apply style, this plugin create a new class CSS, and this path can specific spreadsheet to apply	String	.jss tbody tr td

Options of Rules

Option name	Description	Type	Example
Range (optional)	Range of cells. Can be add multiple ranges separate by ;. If not defined, by default is all cells of sheet Other syntax available : 1:1 = All data of row 1 A:A = All data of column A	String	{range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert"},
criteria (optional)	Is condition for apply rules. Critera is directly a value or a formula. If not defined, by default is apply to all cells of sheet	String / Int / Float / Boolean / Function	{criteria: "=IF(MOD(ROW(),2)==1, true, false)", style:{"background-color": "lightblue"}, stopIfTrue:true},
style (optional)	Style apply on cell if result of criteria is right	String	{criteria: "=IF(MOD(ROW(),2)==1, true, false)", style:{"background-color": "lightblue"}},
class (optional)	Class CSS apply on cell if result of critera is right	String	{range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert"},
stopIfTrue (optional)	this option like Excel, if rule is apply, stop apply conditional style for this cell (no go check next rule). By Default is false	Boolean	{range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert", stopIfTrue:true},
applyOn (optional) - only on v8	Defined where this rules is applied. of worksheet Id. By Default is null	Array/String	{range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert", applyOn:["worksheet_1", "worksheet_2"]},
criteriaOnProcessedValue (optional) - only on v8	Make a criteria on processed value, by default is on value and formula executed. By Default is false	Boolean	{range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert", criteriaOnProcessedValue:true},

Methods of plugin

Method	Description	Example
addRule(Range, Criteria, Style, Class, Position) -> Void	Create new rule	jspreadsheet.current.plugins.conditionalstyle.addRule("B:B", ">3000", "color: red;", null, 1);
editRule(Position, Range, Criteria, Style, Class) -> Void	Edit rule on specific position	jspreadsheet.current.plugins.conditionalstyle.editRule(1, "B:B", "<3000", "color: red;", null);
getCSS() -> String	Get all CSS of styles. Use for example with print plugin	{ name:'print', plugin:jss_print, options:{style:function(obj) { return obj.plugins.conditionalstyle.getCSS(); }} },
moveRulePosition(Position, NewPosition) -> Void	Move rule to specific position	jspreadsheet.current.plugins.conditionalstyle.moveRulePosition(1, 3);
removeRule(Position) -> Void	Remove rule on specific position	jspreadsheet.current.plugins.conditionalstyle.removeRule(3);

Get started

Header on page

<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jspreadsheet.com/v7/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />

<script src="/path/to/jss.conditionalstyle.js"></script>

Initialize plugin on JSpreadsheet

jspreadsheet(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
      { name:'conditionalstyle', plugin:jss_conditionalstyle},
      ...  
    ],
    ...
});

Example

Header on page

<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jspreadsheet.com/v7/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />

<script src="/path/to/jspreadsheet.conditionalstyle.js"></script>

<style>    
    .jss tbody tr td.cellAlert {
        background-color: #f46e42!important;
        color: #ffffff;
    }    
</style>

Initialize plugin on JSpreadsheet

jspreadsheet(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
   		{ name:'conditionalstyle', plugin:jss_conditionalstyle, options:{rules:[
   			// #Rules 1 : For Range B1:B10 and Row 1, cell = Honda use ClassCss cellAlert
	                {range:"B1:B10;1:1", criteria: "Honda", class:"cellAlert"}, 
	                // #Rule 2 : All data of Column G if value > 3000, apply this style and stop here (no check next rules if true)
	                {range:"G:G", criteria: ">3000", style:{"color": "red", "font-weight":"bold", "background-color": "LightPink"}, stopIfTrue:true}, 
	                // #Rule 3 : All data of Column F, if value = true (checkbox), apply style and stop here (no check next rules if true)
	                {range:"F:F", criteria: true, style:"background-color:green", stopIfTrue:true}, 
	                // #Rule 4 All sheet, If rule is even, apply style                      
	                {criteria: "=IF(MOD(ROW(),2)==1, true, false)", style:{"background-color": "lightblue"}},  
        	]}},
      ...  
    ],
    ...
});

Other example with function and relative cell:

jspreadsheet(document.getElementById('spreadsheet), {
    plugins:[
        { name:'conditionalstyle', plugin:jss_conditionalstyle, options:{rules:[
                     // Criteria with formula relation position
                     {criteria: "=IF(VALUE(COL()-1,ROW(),TRUE)==1, true, false)", style:{"background-color": "lightblue"}, stopIfTrue:true},
                     // Criteria with function        
                     {range:"1:1", criteria: function(worksheet, cellEl, col, row, val) {return ((col+1) % 2==0 && val=="OK")}, class:"cellAlert"}, 
                     // Criteria with function name
                     {range:"F:F", criteria: checkCell, style:{"color": "red", "font-weight":"bold", "background-color": "LightPink"}},
                     // Criteria with bool
                     {range:"E:E", criteria: true, style:"background-color:green"},
             ]}
         }, 
     ],
});

function checkCell(worksheet, cellEl, col, row, val) {
    // get value relative cell
    var value = worksheet.getValueFromCoords(col-1, row, true); // get processed value from cell to left
    if(value == "OK") {
         return true;
    }
    return false;
}

Copyright and license

Copyright GBonnaire.fr and Code released under the commercial License. This plugin requieres license of Repo.gbonnaire.fr