概述
本 Demo 展示了如何使用公式控制形状属性,包括内置形状和自定义形状两种场景。第一个工作表展示了如何为内置形状(直角三角形)设置公式,第二个工作表展示了如何在自定义形状模型中使用公式实现更复杂的属性绑定。
实现思路
在工作表中设置属性数据表格,包含形状的各项属性值
内置形状使用 sheet.shapes.add() 创建形状,然后通过 setFormula() 方法绑定各个属性到单元格
自定义形状通过 model 对象定义形状结构,在属性值中直接使用公式字符串
支持位置、尺寸、旋转、填充、边框、文本等多种属性的公式绑定
自定义形状还支持路径、控制点和连接点的公式定义
代码解析
内置形状的公式绑定
使用 setFormula() 方法为内置形状绑定属性:
setFormula() 方法的第一个参数是属性路径,支持嵌套属性(如 style.fill.color),第二个参数是公式字符串。
自定义形状的公式绑定
在自定义形状的 model 对象中直接使用公式:
自定义形状支持更复杂的属性结构,包括路径、控制点、连接点、文本区域等,所有属性都可以使用公式。
路径命令说明
自定义形状的 path 属性使用路径命令定义形状轮廓:
"M": 移动到坐标点 (x, y)
"L": 画线到坐标点 (x, y)
"Z": 闭合路径
路径坐标可以使用公式,引用 width、height、controls、variables 等内置变量
运行效果
第一个工作表(BuilInShape)展示内置形状,修改单元格中的属性值可以实时改变形状的位置、大小、旋转角度、颜色和文本
第二个工作表(CustomShape)展示自定义形状,包含更丰富的属性控制,如箭头样式、线条端点、文本对齐等
使用下拉列表可以选择对齐方式、线条样式、箭头样式等枚举值
所有形状属性都与单元格数据联动,实现动态可视化效果
API 参考
setFormula 方法
path: 属性路径,如 "x"、"width"、"style.fill.color" 等
formula: 公式字符串,如 "=Sheet1!B1"
shapes.add 方法
name: 形状名称
type: AutoShapeType 枚举值(内置形状)
model: 自定义形状模型对象
x、y: 形状位置
width、height: 形状尺寸
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
sheetCount: 2
});
initBuildShapeFormula(spread);
initCustomShapeFormula(spread);
};
function initBuildShapeFormula(spread) {
var sheet = spread.getSheet(0);
sheet.name("BuildInShape");
sheet.setArray(0, 0, [
["Properties", "Values"],
["x", 400],
["y", 20],
["width", 200],
["height", 200],
["angle", 0],
["background color", "orange"],
["background tranparency", 0.5],
["border color", "gray"],
["border width", 2],
["shape text", "BUILt-IN Shape"],
["text font", "15px Georgia"],
["text color", "black"],
]);
sheet.setColumnWidth(0, 200);
sheet.setColumnWidth(1, 150);
sheet.getRange(-1,1,0,1).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
var shape1 = sheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.rightTriangle, 400, 20, 200, 200);
shape1.setFormula("x", "=BuildInShape!B2");
shape1.setFormula("y", "=BuildInShape!B3");
shape1.setFormula("width", "=BuildInShape!B4");
shape1.setFormula("height", "=BuildInShape!B5");
shape1.setFormula("rotate", "=BuildInShape!B6");
shape1.setFormula("text", "=BuildInShape!B11");
shape1.setFormula("style.fill.color", "=BuildInShape!B7");
shape1.setFormula("style.fill.transparency", "=BuildInShape!B8");
shape1.setFormula("style.line.color", "=BuildInShape!B9");
shape1.setFormula("style.line.width", "=BuildInShape!B10");
shape1.setFormula("style.textEffect.font", "=BuildInShape!B12");
shape1.setFormula("style.textEffect.color", "=BuildInShape!B13");
sheet.getRange(0, 0,1,2).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white");
}
function createComboCellType(enumType, max) {
var combo = new GC.Spread.Sheets.CellTypes.ComboBox();
var items = [];
for (var name in enumType) {
var value = enumType[name];
if (!max || value <= max) {
items.push({
text: name,
value: value
});
}
}
combo.items(items);
combo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
return combo;
}
function initCustomShapeFormula(spread) {
var sheet = spread.getSheet(1);
sheet.name("CustomShape");
sheet.setArray(0, 0, [
["General Properties"],
["left", 600],
["top", 60],
["width", 400],
["height", 240],
["angle",10],
["background color","#82BC00"],
["background tranparency", 0.3],
["border color", "orange"],
["border width", 3],
["Text Properties"],
["shape text", "CUSTOM Shape"],
["text font", "20px Georgia"],
["text color", "white"],
["margins", 1, 2, 30, 0],
["horizontalAlignment", 1],
["verticalAlignment", 1],
["textDirection", "horizontal"],
["allowTextToOverflowShape", false],
["wrapTextInShape", true],
["Line Properties"],
["line width", 3],
["line style", 5],
["capType", 2],
["joinType", 1],
["beginArrow", 1, 1, 1],
["endArrow", 5, 2, 2],
]);
sheet.setColumnWidth(0, 280);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 70);
sheet.setColumnWidth(3, 70);
sheet.setColumnWidth(4, 70);
sheet.getRange(-1,1,0,5).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
sheet.setCellType(15, 1, createComboCellType(GC.Spread.Sheets.HorizontalAlign, 3));
sheet.setCellType(16, 1, createComboCellType(GC.Spread.Sheets.VerticalAlign,2));
sheet.setCellType(22, 1, createComboCellType(GC.Spread.Sheets.Shapes.PresetLineDashStyle,10));
sheet.setCellType(23, 1, createComboCellType(GC.Spread.Sheets.Shapes.LineCapStyle,2));
sheet.setCellType(24, 1, createComboCellType(GC.Spread.Sheets.Shapes.LineJoinStyle,2));
sheet.setCellType(25, 1, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle,5));
sheet.setCellType(26, 1, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadStyle,5));
sheet.setCellType(25, 2, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength,2));
sheet.setCellType(26, 2, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadLength,2));
sheet.setCellType(25, 3, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth,2));
sheet.setCellType(26, 3, createComboCellType(GC.Spread.Sheets.Shapes.ArrowheadWidth,2));
sheet.getRange(0, 0,1,2).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white");
sheet.getRange(10, 0,1,5).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white");
sheet.getRange(20, 0,1,4).font("17px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center).backColor("gray").foreColor("white");
var model = {
left: "=CustomShape!B2",
top: "=CustomShape!B3",
width: "=CustomShape!B4",
height: "=CustomShape!B5",
angle: "=CustomShape!B6", //"=ROW(BuildInShape!B10)",
options: {
endPoints: {
beginArrow: {
type: "=CustomShape!B26", widthType: "=CustomShape!C26", lengthType: "=CustomShape!D26" },
endArrow: { type: "=CustomShape!B27", widthType: "=CustomShape!C27", lengthType: "=CustomShape!D27" }
},
fill: {
type: 1, // solid fill (now only support solid fill)
color: "=CustomShape!B7",
transparency: "=CustomShape!B8"
},
stroke: {
type: 1, // solid fill (now only support solid fill)
color: "=CustomShape!B9",
transparency: "=CustomShape!B8",
width: "=CustomShape!B10",
lineStyle: "=CustomShape!B23",
capType: "=CustomShape!B24",
joinType: "=CustomShape!B25"
},
textFormatOptions: {
text: "=CustomShape!B12", // "Shape Text",
font: "=CustomShape!B13", // "bold 15px Georgia", // css font, zoom related code should be update to support it
fill: {
type: 1, // solid fill (now only support solid fill)
color: "=CustomShape!B14"
},
margins: {
left: "=CustomShape!B15",
top: "=CustomShape!C15",
right: "=CustomShape!D15",
bottom: "=CustomShape!E15"
},
verticalAlignment: "=CustomShape!B17", // (0: top, 1: center, 2: bottom)
horizontalAlignment: "=CustomShape!B16", // (0: left, 1: center, 2: right)
textDirection: "=CustomShape!B18", //f "vertical", "rotate90", "rotate270"
allowTextToOverflowShape: "=CustomShape!B19",
wrapTextInShape: "=CustomShape!B20"
}
},
variables: {
xOffset: 40,
yOffset: 10
},
path: [[
["M", "=controls.0.x", 0], // M: move to (x, y)
["L", "=width - controls.0.x", 0], // L: line to (x, y)
["L", "=width - 2 * variables.xOffset", "=height"], ["L", "=variables.xOffset", "=height"],
["Z"]], // Z: close path
[
["M", "=width - variables.xOffset", "=variables.yOffset"],
["L", "=width", "=variables.yOffset"],
["L", "=width", "=height - 4 * variables.yOffset"],
["L", "=width - variables.xOffset", "=height"]
]
],
controls: [
{
x: "=BOUND(0.3*width, 0, false, 0, 0.5*width)", // formula used to provide position and range limitation (here default at position (0, 0.2 * width), and the y range from 0 to 0.5*width)
y: 0,
xBehavior: 0, // 0 if adjust in x (horizontal), otherwise 1
yBehavior: 1 // 0 if adjust in y (vertical), otherwise 1
}
],
connectionPoints: [
{
x: "=0.5*width",
y: 0
},
{
x: "=0.5*controls.0.x",
y: "=0.5*height"
},
{
x: "=0.5*width",
y: "=1*height"
},
{
x: "=width-0.5*controls.0.x",
y: "=0.5*height"
}
],
textRect: {left: "=controls.0.x", top: 20, bottom: "=height - 20", right: "=width - variables.xOffset" }
};
sheet.shapes.add('shape2', model);
}
<!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$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.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>
<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">
<div class="option-row">
尝试编辑 SpreadJS 实例中的数据，查看其如何影响形状的属性。
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 5px;
}
