Except the back color of solid fill, now SpreadJS support gradient cell fills and pattern cell fills.
The following sample shows set with gradient top to bottom with red-white-blue.
The following sample shows set with gradient inner to outer with red-white-blue.
The following sample shows the lightHorizontal red pattern fill on the white background.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet :autoGenerateColumns="autoGenerateColumns"></gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<strong class="sp-demo-HeaderTitle">Settings:</strong>
<P style="padding:2px 10px">Set the fill effect for the select range:</P>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="gradientDegree">Degree:</label>
<input type="text" id="gradientDegree" value="45">
</div>
<div class="option-row">
<label for="gradientStops">Stops:</label>
<textarea id="gradientStops"
rows="4">[{"position":0,"color":"#838383"},{"position":0.5,"color":"white"},{"position":1,"color":"#82bc00"}]</textarea>
</div>
<div class="option-row">
<button id="setGradientButton" @click="setGradientButtonClicked($event)">Set Gradient Fill</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="pathLeft">Left:</label>
<input type="text" id="pathLeft" value="0.5">
</div>
<div class="option-row">
<label for="pathRight">Right:</label>
<input type="text" id="pathRight" value="0.9">
</div>
<div class="option-row">
<label for="pathTop">Top:</label>
<input type="text" id="pathTop" value="0.5">
</div>
<div class="option-row">
<label for="pathBottom">Bottom:</label>
<input type="text" id="pathBottom" value="0.7">
</div>
<div class="option-row">
<label for="pathStops">Stops:</label>
<textarea id="pathStops"
rows="4">[{"position":0,"color":"#82bc00"},{"position":0.5,"color":"white"},{"position":1,"color":"#838383"}]</textarea>
</div>
<div class="option-row">
<button id="setPathButton" @click="setPathButtonClicked($event)">Set Gradient Path Fill</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="patternType">Pattern Type:</label>
<select id="patternType">
<option value="solid">solid</option>
<option value="darkGray">darkGray</option>
<option value="mediumGray">mediumGray</option>
<option value="lightGray">lightGray</option>
<option value="gray125">gray125</option>
<option value="gray0615">gray0615</option>
<option value="darkHorizontal">darkHorizontal</option>
<option value="darkVertical">darkVertical</option>
<option value="darkDown">darkDown</option>
<option value="darkUp" selected="selected">darkUp</option>
<option value="darkGrid">darkGrid</option>
<option value="darkTrellis">darkTrellis</option>
<option value="lightHorizontal">lightHorizontal</option>
<option value="lightVertical">lightVertical</option>
<option value="lightDown">lightDown</option>
<option value="lightUp">lightUp</option>
<option value="lightGrid">lightGrid</option>
<option value="lightTrellis">lightTrellis</option>
</select>
</div>
<div class="option-row">
<label for="bgColor">Background color:</label>
<input type="text" id="bgColor" value="white">
</div>
<div class="option-row">
<label for="patternColor">Pattern color:</label>
<input type="text" id="patternColor" value="#C3C3C3">
</div>
<div class="option-row">
<button id="setPatternButton" @click="setPatternButtonClicked($event)">Set Pattern Fill</button>
</div>
</div>
</fieldset>
</div>
</div>
</template>
<script setup>
import '@mescius/spread-sheets-vue'
import "@mescius/spread-sheets-io";
import { ref } from "vue";
import GC from "@mescius/spread-sheets";
const spreadRef = ref(null);
const autoGenerateColumns = ref(true);
const initSpread = (spread) => {
spreadRef.value = spread;
spread.suspendPaint();
let sheet = spread.getActiveSheet();
initSheet(sheet);
// Set range with pattern fill
sheet.getRange(3, 2, 3, 2).backColor({ type: GC.Spread.Sheets.PatternType.darkDown, backgroundColor: '#D3D3D3', patternColor: "white" });
sheet.getRange(6, 1, 12, 1).backColor({ type: GC.Spread.Sheets.PatternType.gray125, backgroundColor: '#B3B3B3', patternColor: "white" });
// Set range with gradient path fill
sheet.getRange(1, 3, 1, 1).backColor({ type: "path", left: 0.1, top: 0.3, right: 0.9, bottom: 0.6, stops: [{ position: 0, color: "#C3C3C3" }, { position: 1, color: "white" }] });
// Set conditional format with gradient path fill
let cfs = sheet.conditionalFormats;
let style = new GC.Spread.Sheets.Style();
style.backColor = { degree: 90, stops: [{ position: 0.3, color: "white" }, { position: 1, color: "#C3C3C3" },] };
cfs.addCellValueRule(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan, 250000, 0, style, [new GC.Spread.Sheets.Range(6, 2, 12, 1)]);
// Set table style with gradient fill
let border = new GC.Spread.Sheets.LineBorder();
let tableStyle = new GC.Spread.Sheets.Tables.TableTheme();
tableStyle.name('tableStyle1');
tableStyle.secondRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle({ degree: 90, stops: [{ position: 0, color: "accent 1" }, { position: 0.1, color: "white" }, { position: 0.7, color: "white" }, { position: 1, color: "accent 6" },] }, 'black', '10px arial', border, border, border, border, border, border))
tableStyle.firstRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle({ degree: 90, stops: [{ position: 0, color: "accent 6" }, { position: 0.1, color: "white" }, { position: 0.7, color: "white" }, { position: 1, color: "accent 1" },] }, 'black', '10px arial', border, border, border, border, border, border))
sheet.tables.findByName('cT').style(tableStyle);
spread.resumePaint();
}
function initSheet(sheet) {
let gcns = GC.Spread.Sheets;
let data = [
[, "FY 2019"],
[, "Sales"],
[, "Monthly", "Cumulative"],
["Apr", 188897, 188897],
["May", 208146, 397043],
["Jun", 226196, 623239],
["Jul", 277318, 900557],
["Aug", 263273, 1163830],
["Sep", 259845, 1423675],
["Oct", 241047, 1664722],
["Nov", 256306, 1921028],
["Dec", 195845, 2116873],
["Jan", 204934, 2321808],
["Feb", 257852, 2579660],
["Mar", 227779, 2807439]
];
sheet.setArray(3, 1, data);
sheet.setColumnWidth(2, 110);
sheet.setColumnWidth(3, 110);
sheet.setRowCount(20);
sheet.setColumnCount(9);
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.getRange(3, 1, 15, 3).setBorder(
new gcns.LineBorder("black", gcns.LineStyle.medium),
{ all: true });
sheet.addSpan(3, 2, 1, 2);
sheet.addSpan(4, 2, 1, 2);
sheet.getRange(3, 2, 3, 2).hAlign(gcns.HorizontalAlign.center);
let cMapSource = [
{ "Currency": "USD", "Value": 1, "Symbol": "$" },
{ "Currency": "CNY", "Value": 7.02, "Symbol": "¥" },
{ "Currency": "JPY", "Value": 108.8, "Symbol": "¥" },
{ "Currency": "EURO", "Value": 0.91, "Symbol": "€" },
];
sheet.tables.addFromDataSource('cT', 3, 5, cMapSource);
[5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); });
sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right);
let dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]');
sheet.setDataValidator(1, 3, dv1);
sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD");
sheet.getRange(6, 2, 12, 2)
.hAlign(gcns.HorizontalAlign.center)
.formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&" "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")');
}
function setGradientButtonClicked(e) {
let backColor = {};
backColor.degree = parseInt(document.getElementById('gradientDegree').value, 10);
backColor.stops = JSON.parse(document.getElementById('gradientStops').value);
let sheet = spreadRef.value.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
function setPathButtonClicked(e) {
let backColor = { type: "path" };
backColor.left = parseFloat(document.getElementById('pathLeft').value);
backColor.right = parseFloat(document.getElementById('pathRight').value);
backColor.top = parseFloat(document.getElementById('pathTop').value);
backColor.bottom = parseFloat(document.getElementById('pathBottom').value);
backColor.stops = JSON.parse(document.getElementById('pathStops').value);
let sheet = spreadRef.value.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
function setPatternButtonClicked(e) {
let backColor = {};
backColor.type = GC.Spread.Sheets.PatternType[document.getElementById('patternType').value];
backColor.backgroundColor = document.getElementById('bgColor').value;
backColor.patternColor = document.getElementById('patternColor').value;
let sheet = spreadRef.value.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
</script>
<style scoped>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 1px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.options-container legend {
text-align: center;
}
fieldset {
margin-bottom: 5px;
padding: 2px;
}
.option-row {
font-size: 14px;
padding: 2px;
}
input,
textarea,
select {
display: block;
width: 100%;
margin: 4px 0;
box-sizing: border-box;
}
label,
input,
textarea,
select {
padding: 1px 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
button {
font-weight: bold;
background-color: #ecf3ff;
border-color: #0b93d5;
width: 100%;
height: 24px;
border-radius: 4px;
border-width: thin;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app" style="height: 100%;"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-io': 'npm:@mescius/spread-sheets-io/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
