シェイプでは、ビルトインシェイプのほかにカスタムシェイプを作成することができます。カスタムシェイプでは、セル内のデータに基づいて形状を動的に生成できます。
var cmdMap = {
moveTo: 'M',
lineTo: 'L',
bezierCurveTo: 'B',
quadraticCurveTo: 'Q',
arc: 'A',
arcTo: 'AT',
closePath: 'Z'
};
var spread;
var sheet;
var sheetData;
var selectedSeatsArray = [];
// convert svg points to shape path
function pointsToPath(points) {
var ps = points.split(' ');
var mx = 10000; // shift the shape to (0, 0)
var my = 10000;
ps = ps.map(function (p) {
var t = p.split(',');
if (parseFloat(t[0]) < mx) {
mx = parseFloat(t[0]);
}
if (parseFloat(t[1]) < my) {
my = parseFloat(t[1]);
}
return { x: parseFloat(t[0]), y: parseFloat(t[1]) };
});
var cmds = [];
cmds.push(['M', ps[0].x - mx, ps[0].y - my]);
for (var i = 1; i < ps.length; i++) {
cmds.push(['L', ps[i].x - mx, ps[i].y - my]);
}
cmds.push(['Z']);
return [cmds];
}
// convert svg path to shape path
function convertPath(d) {
var cmds = parser.parse(d);
var mx = 10000; // shift the shape to (0, 0)
var my = 10000;
var pathCommands = cmds.map(function (cmd) {
for (var i = 0; i < cmd.args.length; i = i + 2) {
if (cmd.args[i] < mx) {
mx = cmd.args[i];
}
if (cmd.args[i + 1] < my) {
my = cmd.args[i + 1];
}
}
return [cmdMap[cmd.type]].concat(cmd.args);
});
var ret = [];
var t = [];
for (var i = 0; i < pathCommands.length; i++) {
var cmd = pathCommands[i];
for (var j = 1; j < cmd.length; j = j + 2) {
cmd[j] = cmd[j] - mx;
cmd[j + 1] = cmd[j + 1] - my;
}
t.push(cmd);
if (cmd[0] == 'Z') {
ret.push(t);
t = [];
}
}
return ret;
}
function convertStatus(seatStatus) {
var ret = -1
//O: Open: green
//1: Reserved: red
//2: Upgrade: orange
switch (seatStatus) {
case 0:
ret = "green";
break;
case 1:
ret = "red";
break;
case 2:
ret = "orange";
break;
}
return ret
}
function convertNumbertoLetter(ssCol) {
var ret = ""
switch (ssCol) {
case 0:
ret = "A";
break;
case 1:
ret = "B";
break;
case 2:
ret = "C";
break;
case 3:
ret = "D";
break;
case 4:
ret = "E";
break;
case 5:
ret = "F";
break;
}
return ret
}
var shapeFormula = "=CHOOSE(VLOOKUP(name,Sheet2!A1:B186,2,False)+1, \"green\", \"red\", \"orange\")";
function createShape(serverpathCommands, sleft, stop, swidth, sheight, shapename) {
var servermodel = {
name: shapename,
left: sleft,
top: stop,
width: swidth,
height: sheight,
angle: 0,
options: {
fill: {
type: GC.Spread.Sheets.Shapes.ShapeFillType.solid,
color: shapeFormula,
width: 1
},
stroke: {
type: GC.Spread.Sheets.Shapes.ShapeFillType.solid,
color: shapeFormula
},
textFormatOptions: {
allowTextToOverflowShape: false,
wrapTextInShape: false,
font: '="11px Arial"',
fill: {
type: GC.Spread.Sheets.Shapes.ShapeFillType.solid,
color: 'black'
}
}
},
path: serverpathCommands
};
return servermodel;
}
function addData(sheet) {
//O: Open: green
//1: Reserved: red
//2: Upgrade: orange
sheet.setValue(2, 5, "Available Seats:")
sheet.getCell(2, 7).backColor("green");
sheet.setValue(4, 5, "Reserved Seats:")
sheet.getCell(4, 7).backColor("red");
sheet.setValue(6, 5, "Premium Seats")
sheet.getCell(6, 7).backColor("orange");
sheet.setValue(11, 5, "You have Selected:");
sheet.addSpan(11, 5, 1, 3);
sheet.options.protectionOptions.allowEditObjects = false;
sheet.setColumnWidth(7, 20);
sheet.options.colHeaderVisible = false;
sheet.options.rowHeaderVisible = false;
sheet.options.selectionBorderColor = "Transparent";
}
window.onload = initFunction;
function initFunction() {
var constleft = 137.5;
var consttop = 230;
var aislespacer = 25;
var rowspacer = 32.8;
var shapewidth = 25;
var shapeheight = 25;
var shapeleft = constleft;
var shapetop = consttop;
var i;
var j;
var sheetDataRow = 0; //counter for Seats on Data sheet
var sheetDataSeatRow = 2; //seat number counter for display - 2A, 2B, 3A, etc
var seatNum = "" //seat number as shown on ticket- 2A, 2B, 3A, etc
var selectedSeat = "";
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 });
spread.setSheetCount(2);
spread.suspendPaint();
spread.suspendCalcService();
sheet = spread.getSheet(0);
sheet.setRowCount(100);
sheetData = spread.getSheet(1);
sheetData.setRowCount(200);
sheet.setColumnWidth(0, 200);
sheet.addSpan(0, 0, 80, 5);
sheet.getCell(0, 0).backgroundImage("$DEMOROOT$/spread/source/images/airplane.png");
sheet.options.isProtected = true;
sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
sheet.setColumnResizable(-1, true, GC.Spread.Sheets.SheetArea.colHeader);
sheet.setRowResizable(-1, true, GC.Spread.Sheets.SheetArea.rowHeader);
addData(sheet);
var airseat1 = "M 151.917 691.735 h -14.676 c -0.039 0 -3.689 -0.066 -3.689 -4.245 l 0.004 -17.254 c 0.096 -0.683 0.735 -1.976 2.47 -1.976 c 1.552 0 2.339 0.682 2.339 2.029 v 14.269 c 0.153 0.357 1.246 2.427 5.952 2.427 c 5.117 0 6.417 -2.265 6.522 -2.473 V 670.29 c 0.031 -0.713 0.52 -2.029 2.149 -2.029 c 1.149 0 1.909 0.606 2.257 1.802 c 0.009 0.029 0.015 0.051 0.021 0.067 l -0.015 0.005 c 0.103 0.431 0.113 2.671 0.052 17.723 c 0.005 0.042 0.126 1.774 -0.934 2.918 C 153.781 691.412 152.956 691.735 151.917 691.735 Z M 134.324 670.321 v 17.169 c 0 3.4 2.802 3.473 2.921 3.474 h 14.672 c 0.814 0 1.45 -0.24 1.888 -0.713 c 0.832 -0.9 0.73 -2.352 0.729 -2.366 c 0.026 -6.665 0.05 -16.629 0.007 -17.496 c -0.012 -0.034 -0.022 -0.071 -0.034 -0.112 c -0.17 -0.581 -0.496 -1.247 -1.518 -1.247 c -1.308 0 -1.377 1.224 -1.379 1.275 l 0 14.253 c 0 0.65 -1.903 3.197 -7.293 3.197 c -5.646 0 -6.663 -2.882 -6.704 -3.005 l -0.02 -0.058 V 670.29 c 0 -0.44 0 -1.258 -1.568 -1.258 C 134.629 669.031 134.363 670.107 134.324 670.321 Z M 135.261 669.105 c -0.002 -0.047 -0.046 -1.17 0.788 -2.047 c 0.627 -0.657 1.56 -0.99 2.773 -0.99 l 12.006 0.005 c 0.969 0.16 2.8 0.996 2.8 3.014 h -0.771 c 0 -1.779 -1.86 -2.197 -2.126 -2.248 h -11.91 c -0.995 0 -1.74 0.252 -2.215 0.751 c -0.6 0.629 -0.577 1.469 -0.577 1.477 L 135.261 669.105 Z";
//Get the seat shape
var s1commands = convertPath(airseat1);
//Rows loop
//USE 30
for (var r = 0; r <= 185; r++) {
sheetData.setValue(r, 1, Math.floor(Math.random() * Math.floor(3)));
}
for (i = 0; i < 31; i++) {
for (j = 0; j < 6; j++) {
seatNum = sheetDataSeatRow + "" + convertNumbertoLetter(j)
//airline Seat
var s1shape = createShape(s1commands, shapeleft, shapetop, shapewidth, shapeheight, seatNum);
//Add Name
var ret2 = sheet.shapes.add(seatNum, s1shape);
ret2.allowMove(false);
ret2.allowResize(false);
ret2.dynamicMove(false);
ret2.dynamicSize(false);
ret2.isLocked(true);
//Add the SeatNumber to first column
sheetData.setValue(sheetDataRow, 0, seatNum);
sheetDataRow = sheetDataRow + 1;
if (j == 5) {
sheetDataSeatRow = sheetDataSeatRow + 1;
}
//increment
shapeleft = shapeleft + shapewidth;
if (j == 2) {
// D,E,F seats. Add aisle spacer
shapeleft = shapeleft + aislespacer;
}
}
//reset left and top to start new rows
shapeleft = constleft;
shapetop = shapetop + rowspacer;
if (i == 12) {
shapetop = shapetop + 10;
}
if (i == 13) {
shapetop = shapetop + 10;
}
if (i == 22) {
shapetop = shapetop - 3;
}
}
spread.resumeCalcService();
spread.resumePaint();
//handle spread div click event for shapes
document.getElementById("ss").addEventListener("click", function (e) {
var sp = document.getElementById("ss");
var x = e.pageX - sp.offsetLeft;
var y = e.pageY - sp.offsetTop;
var target = spread.getActiveSheet().hitTest(x, y);
if (target.shapeHitInfo == null && x > 700) {
y = y - 300;
target = spread.getActiveSheet().hitTest(x, y);
}
var preselectionseatval = "";
if (target.shapeHitInfo) {
var shape = target.shapeHitInfo.shape;
// var preselectionseatval =
if (shape) {
spread.suspendPaint();
if (selectedSeat == "") {
var searchCondition = new GC.Spread.Sheets.Search.SearchCondition();
searchCondition.searchString = shape.name();
searchCondition.startSheetIndex = 1;
searchCondition.endSheetIndex = 1;
searchCondition.searchOrder = GC.Spread.Sheets.Search.SearchOrder.nOrder;
searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellText;
searchCondition.searchFlags = GC.Spread.Sheets.Search.SearchFlags.ignoreCase | GC.Spread.Sheets.Search.SearchFlags.useWildCards;
var searchresult = spread.search(searchCondition);
var val = sheetData.getValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1);
if (val == 0) {
sheetData.setValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1, 1, GC.Spread.Sheets.SheetArea.viewport, false);
selectedSeat = shape.name();
sheet.setValue(11, 8, selectedSeat);
preselectionseatval = val;
}
else if (val == 2) {
if (confirm("これはプレミアムシートです。アップグレードしてもよろしいですか?")) {
sheetData.setValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1, 1, GC.Spread.Sheets.SheetArea.viewport, false);
selectedSeat = shape.name();
sheet.setValue(11, 8, selectedSeat);
preselectionseatval = val;
}
} else {
alert("この席はすでに予約されています");
}
}
else {
sheetData.suspendPaint();
var searchCondition = new GC.Spread.Sheets.Search.SearchCondition();
searchCondition.searchString = selectedSeat;
searchCondition.startSheetIndex = 1;
searchCondition.endSheetIndex = 1;
searchCondition.searchOrder = GC.Spread.Sheets.Search.SearchOrder.nOrder;
searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellText;
searchCondition.searchFlags = GC.Spread.Sheets.Search.SearchFlags.ignoreCase | GC.Spread.Sheets.Search.SearchFlags.useWildCards;
var searchresult = spread.search(searchCondition);
var selectedseatval = sheetData.getValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1);
var searchCondition1 = new GC.Spread.Sheets.Search.SearchCondition();
searchCondition1.searchString = shape.name();
searchCondition1.startSheetIndex = 1;
searchCondition1.endSheetIndex = 1;
searchCondition1.searchOrder = GC.Spread.Sheets.Search.SearchOrder.nOrder;
searchCondition1.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellText;
searchCondition1.searchFlags = GC.Spread.Sheets.Search.SearchFlags.ignoreCase | GC.Spread.Sheets.Search.SearchFlags.useWildCards;
var searchresult1 = spread.search(searchCondition1);
var val = sheetData.getValue(searchresult1.foundRowIndex, searchresult1.foundColumnIndex + 1);
if (val == 1) {
alert("この席はすでに予約されています");
}
else if (val == 0) {
sheetData.setValue(searchresult1.foundRowIndex, searchresult1.foundColumnIndex + 1, 1, GC.Spread.Sheets.SheetArea.viewport, false);
sheetData.setValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1, 0, GC.Spread.Sheets.SheetArea.viewport, false);
selectedSeat = shape.name();
sheet.setValue(11, 8, selectedSeat);
}
else if (val == 2) {
if (confirm("これはプレミアムシートです。アップグレードしてもよろしいですか?")) {
sheetData.setValue(searchresult1.foundRowIndex, searchresult1.foundColumnIndex + 1, 1, GC.Spread.Sheets.SheetArea.viewport, false);
sheetData.setValue(searchresult.foundRowIndex, searchresult.foundColumnIndex + 1, 0, GC.Spread.Sheets.SheetArea.viewport, false);
selectedSeat = shape.name();
sheet.setValue(11, 8, selectedSeat);
}
}
sheetData.resumePaint();
}
spread.resumePaint();
}
}
}, false);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/svgpath.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" style="width:100%;height:100%"></div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
