Using the Data Bar Rule

SpreadJS supports the data bar rule.

The data bar rule uses a bar that is displayed as the background for each cell. The length of the bar corresponds to the size of the data relative to the other data in the sheet. The longer the bar, the greater the value in the cell.

You can specify the minimum and maximum type and the value to compare in the conditional format. The minimumType method can be set to Number, LowestValue, HighestValue, Percent, Formula, Percentile, and Automin, or Automax.

You can use the addDataBarRule method to create a rule and add it to the collection. The BarDirection and DataBarAxisPosition types can be used to specify the data bar direction and axis position.

Using Code

This example creates a data bar rule and uses the addRule method to add the rule.

JavaScript	Copy Code
activeSheet.setValue(0,0,1,3); activeSheet.setValue(1,0,15,3); activeSheet.setValue(2,0,25,3); activeSheet.setValue(3,0,-1,3); var cfs = activeSheet.getConditionalFormats(); var dataBarRule = new GcSpread.Sheets.DataBarRule(); dataBarRule.ranges=[new GcSpread.Sheets.Range(0,0,4,1)]; dataBarRule.minimumType(GcSpread.Sheets.ScaleValueType.Number); dataBarRule.minimumValue(-1); dataBarRule.maximumType(GcSpread.Sheets.ScaleValueType.Number); dataBarRule.maximumValue(40); dataBarRule.color("green"); dataBarRule.showBorder(true); dataBarRule.borderColor("orange"); dataBarRule.dataBarDirection(GcSpread.Sheets.BarDirection.LeftToRight); dataBarRule.negativeFillColor("yellow"); dataBarRule.useNegativeFillColor(true); dataBarRule.negativeBorderColor("red"); dataBarRule.useNegativeBorderColor(true); dataBarRule.axisPosition(GcSpread.Sheets.DataBarAxisPosition.Automatic); dataBarRule.axisColor("blue"); dataBarRule.showBarOnly(false); cfs.addRule(dataBarRule);

JavaScript

Copy Code

activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,15,3);
activeSheet.setValue(2,0,25,3);
activeSheet.setValue(3,0,-1,3);

var cfs = activeSheet.getConditionalFormats();
var dataBarRule = new GcSpread.Sheets.DataBarRule();
dataBarRule.ranges=[new GcSpread.Sheets.Range(0,0,4,1)];
dataBarRule.minimumType(GcSpread.Sheets.ScaleValueType.Number);
dataBarRule.minimumValue(-1);
dataBarRule.maximumType(GcSpread.Sheets.ScaleValueType.Number);
dataBarRule.maximumValue(40);
dataBarRule.color("green");
dataBarRule.showBorder(true);
dataBarRule.borderColor("orange");
dataBarRule.dataBarDirection(GcSpread.Sheets.BarDirection.LeftToRight);
dataBarRule.negativeFillColor("yellow");
dataBarRule.useNegativeFillColor(true);
dataBarRule.negativeBorderColor("red");
dataBarRule.useNegativeBorderColor(true);
dataBarRule.axisPosition(GcSpread.Sheets.DataBarAxisPosition.Automatic);
dataBarRule.axisColor("blue");
dataBarRule.showBarOnly(false);
cfs.addRule(dataBarRule);