WINDOW
语法
参数
描述
window_function
(必须) window 函数
[partitionby_function]
(可选) 将行分割为分区。
[orderby_function]
(可选) 定义每个分区内行的逻辑顺序。
[frame_function]
(可选) 指定开始和结束点，将行组合为与当前行相对的分区内的window。
用例语法
WINDOW 只能与window函数一起使用，将整个行集视为一个window，并通过 PARTITIONBY, ORDERBY 影响行的顺序。
PARTITIONBY
语法
参数
描述
field_function
(必须) 要按字段名称或公式进行分区。
用例语法
PARTITIONBY 将行分割为分区，window函数将在每个分区中单独应用。它应该有1个或多个参数，例如：
ORDERBY
语法
参数
描述
field_function
(必须) 按字段名称或公式排序。
参数
描述
field_function
(必须) 按字段名称或公式进行排序。
用例语法
ORDERBY 定义每个分区内行的逻辑顺序。它会影响指定的window和window函数的计算，并且应该有1个或多个参数。使用 ORDERASC 和 ORDERDESC 来指示升序或降序的排序顺序。默认的排序顺序是 ORDERASC，例如：
FRAMEROWS
语法
参数
描述
beginning_function
(必选) 从当前行开始的行计数。
[ending_function]
(可选) 当前行结束时的行计数。
[exclude_mode]
(可选) 指定排除模式：0 - 这是默认情况，不排除任何行。1 - 排除当前行，当前行的其他同行保留用于 FRAMEGROUPS 和 FRAMERANGE。2 - 排除当前行和同行。3 - 保留当前行，排除其他同行。
用例语法
FRAMEROWS 通过在当前行之前或之后指定非负整数行数来限制窗口的行集。第一个参数表示当前行之前的行数，并接受 -1、[@-n]、[@+n] 或 [@]，第二个参数表示当前行之后的行数，并接受 -1、[@-n]、[@+n] 或 [@]。-1 表示当前分区的开始或结束，[@-n] 或 [@+n] 表示当前行旁边的行数，n 接受表示行数的非负整数，[@] 表示当前行，例如
FRAMERANGE
语法
参数
描述
beginning_function
(必选) 当前行之前的距离。
[ending_function]
(可选) 当前行之后的距离。
[exclude_mode]
(可选) 指定排除特殊行的模式，与FRAMEROWS相同。
用例语法
FRAMERANGE 通过指定非负数作为距离，限制window的范围，该距离是从具有相同值的对等行中的一些值到当前行之间的距离，这些对等行由ORDERBY列组成。第一个参数表示当前行之前对等行的距离，可以接受 -1、[@-n]（如果顺序是降序，则应为[@+n]）或[@]。第二个参数表示当前行之后对等行的距离，可以接受 -1、[@+n]（如果顺序是降序，则应为[@-n]）或[@]。-1 表示当前分区的边界，n 接受非负整数，表示距离，[@] 表示具有相同值的对等行。该范围是一个完全闭合的区间，并且需要ORDERBY提供具有数值数据类型的第一列。如果有多个有序列，则只接受-1和[@]。例如：
FRAMEGROUPS
语法
参数
描述
beginning_function
(必须) 从当前组开始进行分组计数。
[ending_function]
(可选) 当前组结束的分组计数。
[exclude_mode]
(可选) 指定排除特殊行的模式，与FRAMEROWS相同。
用例语法
FRAMEGROUPS 意味着起始和结束边界是通过相对于当前组的“组”数量来确定的。“组”是一系列行，所有行的值都相同，这些值受到window ORDERBY 的影响。第一个参数表示相对于当前组的组计数开始，接受 -1、[@-n]、[@+n] 或 [@]，第二个参数表示相对于当前组的组计数结束，接受 -1、[@-n]、[@+n] 或 [@]。-1 表示当前分区的开始或结束，[@-n] 或 [@+n] 表示当前组旁边的组计数，n 接受一个非负整数，表示组计数，[@] 表示当前组。例如：
Window Chaining
语法
用例语法
window链是一种简写形式，允许一个window提前定义并被新window重用，新window隐式指定了PARTITIONBY、ORDERBY或window Frame。预定义的window中的PARTITIONBY、ORDERBY、window Frame将被新window中的表达式覆盖。使用 WINDOWDEF 在模式中定义基本window：
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
spread.clearSheets();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.options.allowDynamicArray = true;
spread.options.highlightInvalidData = true;
spread.options.calcOnDemand = true;
discountAmount(spread);
revenueTrends(spread);
quantityRevenueTrends(spread);
monthlyRevenueTrends(spread);
spread.resumePaint();
}
function discountAmount(spread) {
//init a data manager
var dataManager = spread.dataManager();
var discountAmountTable = dataManager.addTable("discountAmountTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderPriceQuantityData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Quantity: { dataType: "number" },
Price: { dataType: "number" },
Amount: { dataType: 'formula', value: "=[@Price] * [@Quantity]" },
DiscountAmount: { dataType: 'formula', value: "=IF([@Quantity] > 30, [@Amount] * 0.8, [@Amount])" },
},
window: {
BeginCurrent: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])), FRAMEROWS(-1,[@]))',
BeginEnd: '=WINDOWDEF(PARTITIONBY([Category], [Product], YEAR([@OrderDate])))',
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "Discount Amount", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
discountAmountTable.fetch().then(function () {
var discountHighlightRule = {
ruleType: "formulaRule",
formula: "[@Quantity]>30",
style: {
foreColor: "purple"
}
};
var myView = discountAmountTable.addView("myView", [
{ value: "Category", width: 90 },
{ value: "Product", width: 90 },
{ value: "=YEAR([@OrderDate])", caption: "Year", width: 80 },
{ value: "Amount", caption: "Amount", width: 100, conditionalFormats: [discountHighlightRule] },
// If the quantity exceeds 30, 20% off sales amount
{ value: "DiscountAmount", caption: "Discount Amount", conditionalFormats: [discountHighlightRule], width: 160 },
{ value: "Price", width: 70 },
{ value: "Quantity", width: 90, conditionalFormats: [discountHighlightRule] },
// The cumulative annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginCurrent\")", caption: 'Running Total Quantity', width: 190, style: { backColor: "#D9E1F2" } },
// The total annual sales volume of each category and product
{ value: "=WINDOW(SUM([Quantity]), \"BeginEnd\")", caption: 'Total Quantity', width: 140, style: { backColor: "#D9E1F2" } },
// The cumulative annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginCurrent\")", caption: 'Running Discount Total Amount', width: 240, style: { backColor: "#E2EFDA" } },
// The total annual sales amount of each category and product, if the quantity exceeds 30, 20% off sales amount
{ value: "=WINDOW(SUM([DiscountAmount]), \"BeginEnd\")", caption: 'Discount Total Amount', width: 190, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function revenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var revenueTrendsTable = dataManager.addTable("revenueTrendsTable", {
data: orderYearProductDataSource,
schema: {
type: "csv",
columns: {
Quantity: { dataType: "number" },
Amount: { dataType: "number" },
MovingAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductYear")' },
},
window: {
ProductYear: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Year]), FRAMEROWS([@-2], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(1, "Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
revenueTrendsTable.fetch().then(function () {
var myView = revenueTrendsTable.addView("myView", [
{ value: "Year", width: 150 },
{ value: "Product", width: 105 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// Calculate the 3-year moving average of earned per product
// FRAMEROWS([@-2], [@]): the preceding two years and current
{ value: 'MovingAverageRevenue', caption: 'Moving Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// To show a ratio of increase and decrease between the current and moving average amount
{ value: '=VARISPARKLINE(ROUND(([@Amount] - [@MovingAverageRevenue]) / [@Amount], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function quantityRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var quantityRevenueTrendsTable = dataManager.addTable("quantityRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
AverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductRangeAmount")' },
QuantityOfRevenue: { dataType: 'formula', value: '=WINDOW(COUNT([Amount]), "ProductRangeAmount")' },
},
window: {
ProductRangeAmount: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY([Amount]), FRAMERANGE([@-200], [@+200]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(2, "Quantity Of Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
quantityRevenueTrendsTable.fetch().then(function () {
var myView = quantityRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
// The trend comparison of average selling prices and the quantity of the proximate orders
// When the price is within a certain range, there are more orders
// When the price is higher or lower, the order quantity decreases
// This situation of the quantity of the orders are close to the normal distribution
// The analytic function AVERAGE can obtain the average of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "AverageRevenue", caption: 'Average Revenue', width: 150, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
// Calculating the ratio of the average amount of the proximate orders against max amount in each product to show the bars
{ value: "=HBARSPARKLINE([@AverageRevenue] / WINDOW(MAX([Amount]), PARTITIONBY([Product])), \"#347B98\")", caption: 'Average Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
// The analytic function COUNT can obtain the quantity of proximate selling prices within each product
// FRAMERANGE([@-200], [@+200]): the amount range which minus or plus 200 against current amount will help to retrieve the orders as a window
{ value: "QuantityOfRevenue", caption: 'Quantity Of Revenue', width: 180, style: { backColor: "#E2EFDA" } },
// Using LET to cached the ratio of the number of the proximate orders against the total number of the orders in each product to show the bars which indicates the trends through the ratio and colors
{ value: "=LET(ratio, [@QuantityOfRevenue] / WINDOW(COUNT([Amount]), PARTITIONBY([Product])),color,IF(ratio >= 0.32,\"green\", IF(ratio >= 0.2, \"#66B032\", IF(ratio >= 0.1, \"#B2D732\", \"red\"))), HBARSPARKLINE(ratio, color))", caption: 'Quantity Of Revenue Trends', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
function monthlyRevenueTrends(spread) {
//init a data manager
var dataManager = spread.dataManager();
var monthlyRevenueTrendsTable = dataManager.addTable("monthlyRevenueTrendsTable", {
remote: {
read: {
url: "$DEMOROOT$/spread/source/data/orderAmountData.csv"
}
},
schema: {
type: "csv",
columns: {
OrderDate: { dataType: "date" },
Amount: { dataType: "number" },
MonthlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly")' },
NearlyAverageRevenue: { dataType: 'formula', value: '=WINDOW(AVERAGE([Amount]), "ProductMonthly", FRAMEGROUPS([@-2], [@+2], 2))' },
},
window: {
ProductMonthly: '=WINDOWDEF(PARTITIONBY([Product]), ORDERBY(VALUE(DATEPART([@OrderDate], "M"))), FRAMEGROUPS([@], [@]))'
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(3, "Monthly Revenue Trends", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
monthlyRevenueTrendsTable.fetch().then(function () {
var myView = monthlyRevenueTrendsTable.addView("myView", [
{ value: "Product", width: 150 },
{ value: '=VALUE(DATEPART([@OrderDate], "M"))', caption: 'Month', width: 150 },
{ value: "Amount", caption: "Revenue", width: 100, style: { formatter: "#,##0.00" } },
{ value: 'MonthlyAverageRevenue', caption: 'Monthly Average Revenue', width: 220, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
{ value: 'NearlyAverageRevenue', caption: 'Nearly Average Revenue', width: 200, style: { backColor: "#E2EFDA", formatter: "#,##0.00" } },
{ value: '=VARISPARKLINE(ROUND(([@MonthlyAverageRevenue] - [@NearlyAverageRevenue]) / [@MonthlyAverageRevenue], 2),0,,,,0.2,TRUE)', caption: 'Revenue Trends %', width: 200, style: { backColor: "#E2EFDA" } },
]);
spread.suspendPaint();
sheet.setDataView(myView);
spread.resumePaint();
});
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<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">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<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-tablesheet/dist/gc.spread.sheets.tablesheet.min.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="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/orderYearProductDataSource.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>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
</div>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 210px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 3px;
margin-top: 3px;
}
input {
padding: 4px 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width: 100%;
text-align: center;
}
input[type=text] {
width: 230px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
margin: 0;
}
