单元格填充效果

概述本 Demo 展示了如何使用 SpreadJS 的单元格填充效果功能,包括渐变填充、路径渐变填充和图案填充。Demo 在初始加载时预设了多种填充样式,并提供交互面板让用户选择单元格后自定义填充参数。实现思路初始化工作表并加载销售数据和货币汇率表使用 backColor 属性为不同单元格区域设置图案填充和渐变路径填充创建条件格式规则,对大于 250000 的数值应用渐变填充样式为表格创建自定义主题,使用渐变填充作为交替行样式绑定按钮事件,允许用户选择单元格后通过右侧面板设置自定义填充代码解析设置图案填充图案填充使用 IPatternFill 接口,需要指定三个参数: type: 图案类型,使用 GC.Spread.Sheets.PatternType 枚举(如 darkDown、gray125 等) backgroundColor: 背景颜色 patternColor: 图案线条颜色设置渐变路径填充渐变路径填充使用 IGradientPathFill 接口: type: 固定为字符串 "path" left、top、right、bottom: 定义渐变中心位置(值为 0-1 之间的小数) stops: 颜色断点数组,每个断点包含 position(0-1)和 color 在条件格式中应用渐变填充渐变填充使用 IGradientFill 接口: degree: 渐变角度(0-360 度,90 表示从上到下) stops: 颜色断点数组条件格式的 backColor 属性也支持渐变和图案填充,当单元格值大于 250000 时自动应用渐变样式。为表格样式设置渐变填充表格主题的行样式也支持渐变填充,可以创建视觉效果更丰富的表格。交互式设置填充用户选择单元格后,点击按钮读取输入框中的参数并应用到选中区域。运行效果工作表中已预设多种填充效果:表格左侧的深色下斜图案、灰色图案、渐变路径填充单元格 Cumulative 列中数值大于 250000 的单元格会自动应用从白到灰的渐变填充右侧货币表格使用了渐变填充作为交替行样式在右侧面板中选择单元格,可以自定义三种填充效果: 渐变填充:设置角度和颜色断点渐变路径填充:设置中心位置和颜色断点图案填充:从 18 种图案中选择,设置背景色和图案颜色 API 参考 backColor 属性单元格背景色支持多种类型: string: 纯色填充,如 "red"、"#FF0000" IGradientFill: 线性渐变填充 IGradientPathFill: 路径渐变填充 IPatternFill: 图案填充 IGradientFill 接口 IGradientPathFill 接口 IPatternFill 接口 PatternType 枚举常用图案类型: solid: 纯色 darkGray、mediumGray、lightGray: 不同灰度 darkHorizontal、lightHorizontal: 水平线条 darkVertical、lightVertical: 垂直线条 darkDown、lightDown: 下斜线条 darkUp、lightUp: 上斜线条 darkGrid、lightGrid: 网格 darkTrellis、lightTrellis: 斜纹 gray125、gray0625: 点阵图案

window.onload = initFunction;
function initFunction() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 1});
spread.suspendPaint();
var 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
var cfs = sheet.conditionalFormats;
var 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
var border = new GC.Spread.Sheets.LineBorder();
var 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();
// Here add the event.
document.getElementById('setGradientButton').onclick = function (e) {
var backColor = {};
backColor.degree = parseInt(document.getElementById('gradientDegree').value, 10);
backColor.stops = JSON.parse(document.getElementById('gradientStops').value);
sheet = spread.getActiveSheet();
var selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
};
document.getElementById('setPathButton').onclick = function (e) {
var 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);
sheet = spread.getActiveSheet();
var selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
};
document.getElementById('setPatternButton').onclick = function (e) {
var backColor = {};
backColor.type = GC.Spread.Sheets.PatternType[document.getElementById('patternType').value];
backColor.backgroundColor = document.getElementById('bgColor').value;
backColor.patternColor = document.getElementById('patternColor').value;
sheet = spread.getActiveSheet();
var selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
};
}
function initSheet(sheet) {
var gcns = GC.Spread.Sheets;
var 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);
var 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);
var 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),"###,###")');
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js"
type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<strong class="sp-demo-HeaderTitle">设置：</strong>
<P style="padding:2px 10px">为所选区域设置填充效果：</P>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="gradientDegree">角度：</label>
<input type="text" id="gradientDegree" value="45">
</div>
<div class="option-row">
<label for="gradientStops">断点：</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">设置渐变填充</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="pathLeft">左：</label>
<input type="text" id="pathLeft" value="0.5">
</div>
<div class="option-row">
<label for="pathRight">右：</label>
<input type="text" id="pathRight" value="0.9">
</div>
<div class="option-row">
<label for="pathTop">上：</label>
<input type="text" id="pathTop" value="0.5">
</div>
<div class="option-row">
<label for="pathBottom">下：</label>
<input type="text" id="pathBottom" value="0.7">
</div>
<div class="option-row">
<label for="pathStops">断点：</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">设置渐变路径填充</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="patternType">图案类型：</label>
<select id="patternType">
<option value="solid">纯色</option>
<option value="darkGray">深灰色</option>
<option value="mediumGray">中灰色</option>
<option value="lightGray">浅灰色</option>
<option value="gray125">灰色125</option>
<option value="gray0615">灰色0615</option>
<option value="darkHorizontal">深色水平</option>
<option value="darkVertical">深色垂直</option>
<option value="darkDown">深色下斜</option>
<option value="darkUp" selected="selected">深色上斜</option>
<option value="darkGrid">深色网格</option>
<option value="darkTrellis">深色斜纹</option>
<option value="lightHorizontal">浅色水平</option>
<option value="lightVertical">浅色垂直</option>
<option value="lightDown">浅色下斜</option>
<option value="lightUp">浅色上斜</option>
<option value="lightGrid">浅色网格</option>
<option value="lightTrellis">浅色斜纹</option>
</select>
</div>
<div class="option-row">
<label for="bgColor">背景颜色：</label>
<input type="text" id="bgColor" value="white">
</div>
<div class="option-row">
<label for="patternColor">图案颜色：</label>
<input type="text" id="patternColor" value="#C3C3C3">
</div>
<div class="option-row">
<button id="setPatternButton">设置图案填充</button>
</div>
</div>
</fieldset>
</div>
</div>
</body>
</html>

.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;
}

SpreadJS 在线示例