Premium plugin : Conditional Style - GBonnaire/jspreadsheet-plugins-and-editors GitHub Wiki

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

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

All librairies available on https://repo.gbonnaire.fr

⚠️ **GitHub.com Fallback** ⚠️