[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
        
(Showing Draft Content)

迭代计算

和Excel一样,SpreadJS支持迭代计算,也称为循环引用。一般来说,迭代计算是表单的重复计算,直到满足一个确定的数值条件。当单元格中的公式在表格中直接或间接地引用它自己的单元格时,使用它重新计算公式,直到找到一个特定的结果。

通过使用 iterativeCalculation 属性,您可以在表单中启用迭代计算。您还可以通过设置 iterativeCalculationMaximumIterations 属性来指定公式需要重新计算的次数。此外,您还可以通过设置 iterativeCalculationMaximumChange 属性来指定两个计算值之间的最大变更量。

在启用迭代计算时,所有循环引用将迭代地计算,直到所有值的变化都小于IterativeCalculationMaximumChange,或者迭代计数为IterativeCalculationMaximumIterations。然而,在禁用迭代计算时,所有循环引用单元格值将变为零,它引用的其他单元格也将变为零。

用例

你可以在以下用例中使用迭代计算:

计算客户未来投资价值

让我们假设一位客户有50,000美元,他想把这笔钱投资到一个每月4.75%的活期存款账户上。为了计算两年后的总价值,我们要计算每个月的本金,然后把利息加到前一个月。在这里,我们将 iterativeCalculation 属性设置为True,以启用迭代计算,并将最大迭代次数设置为24次,以找到24个月(2年)后的利息,即25月底的总现金价值。

这个用例可以使用下面的代码片段来描述:

<script>
$(document).ready(function () {
//初始化spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
//获取活动表
var sheet = spread.getSheet(0);
//设置列宽
sheet.setColumnWidth(0, 30);
sheet.setColumnWidth(1, 110);
sheet.setColumnWidth(2, 130);
sheet.setColumnWidth(3, 30);
sheet.setColumnWidth(4, 30);
sheet.setColumnWidth(5, 230);
sheet.setColumnWidth(6, 130);
/ /启用IterativeCalculation
spread.options.iterativeCalculation = true;
spread.options.iterativeCalculationMaximumIterations = 24;
spread.options.iterativeCalculationMaximumChange = 0.1;
//设置文本和值
sheet.setText(1, 1, "Initial Cash");
sheet.setValue(1, 2, 50000);
sheet.setText(2, 1, "Interest");
sheet.setValue(2, 2, 0.0475);
sheet.setText(4, 1, "Month/ Year");
sheet.setText(4, 2, "Total Cash");
// 创建数组
var items = [
"Jan' 2019", "Feb' 2019", "Mar' 2019", "April' 2019", "May' 2019", "June' 2019", "July' 2019", "Aug' 2019", "Sept' 2019", "Oct' 2019", "Nov' 2019", "Dec' 2019",
"Jan' 2020", "Feb' 2020", "Mar' 2020", "April' 2020", "May' 2020", "June' 2020", "July' 2020", "Aug' 2020", "Sept' 2020", "Oct' 2020", "Nov' 2020", "Dec' 2020", "Jan' 2021"
];
// 设置数组
sheet.setArray(5, 1, items);
// 设置公式
sheet.setFormula(5, 2, '=C2*(1+C3)');
sheet.setFormula(6, 2, '=C6*(1+C3)');
for (var i = 6; i < 30; i++) {
sheet.setFormula(i, 2, '=C' + i + '*(1+C3)');
}
// 创建表格在区域上添加样式
sheet.tables.add("Table1", 4, 1, 26, 2, GC.Spread.Sheets.Tables.TableThemes.medium6);
// 设置值和公式
sheet.setText(1, 5, "Initial Cash");
sheet.setValue(1, 6, 50000);
sheet.setText(2, 5, "Interest");
sheet.setValue(2, 6, 0.0475);
sheet.setText(3, 5, "Total Cash after 2 yrs (as on Jan' 2021)");
sheet.setFormula(3, 6, '=G2*(1+G3)');
// 此公式将生成循环引用
sheet.setFormula(1, 6, '=G4');
// 设置格式
sheet.setFormatter(1, 2, '$#,##0.00');
sheet.setFormatter(2, 2, '0.00%');
sheet.setFormatter(1, 6, '$#,##0.00');
sheet.setFormatter(2, 6, '0.00%');
sheet.setFormatter(3, 6, '$#,##0.00');
sheet.setFormatter(5, 2, '$#,##0.00');
for (var i = 6; i < 30; i++) {
sheet.setFormatter(i, 2, '$#,##0.00');
}
// 为单元格区域设置边框
sheet.getRange("B2:C3", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
sheet.getRange("F2:G4", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
sheet.getRange("B5:C30", GC.Spread.Sheets.SheetArea.viewport).setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { all: true }, 3);
// 为单元格区域设置背景色
sheet.getRange("C2:C3").backColor("#D9E3F2");
sheet.getRange("G2:G4").backColor("#D9E3F2");
});
</script>

向单元格添加时间戳

迭代计算可用于在表单中设置时间戳。例如,您正在管理任务列表,并希望在完成每个任务时在相邻的单元格中添加日期和时间。为了启用时间戳用例的迭代计算,我们将 iterativeCalculation 属性设置为true。在这里,我们使用了 iterativeCalculationMaximumIterations 属性,并将其设置为24,这样它就会告诉SpreadJS它应该运行该公式的最大次数。通过迭代计算,每次我们在“Employee Name”列的新行中输入一个新项目时,“timestamp”列中的一个时间戳将自动更新,直到它到达第23行。

这个用例可以使用下面的代码片段来描述:

<script>
$(document).ready(function () {
//初始化spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
//获取活动表
var sheet = spread.getSheet(0);
sheet.suspendPaint();
//设置列宽
sheet.setColumnWidth(0, 200);
sheet.setColumnWidth(1, 200);
/ /启用IterativeCalculation
spread.options.iterativeCalculation = true;
spread.options.iterativeCalculationMaximumIterations = 24;
spread.options.iterativeCalculationMaximumChange = 0.1;
// 为单元格区域设置背景色
sheet.getRange("A1:B1").backColor("#D9E3F2");
// 在单元格设置文本
sheet.setText(0, 0, "Employee Name");
sheet.setText(0, 1, "TimeStamp");
for (var i = 2; i < spread.options.iterativeCalculationMaximumIterations; i++) {
sheet.setFormula(i - 1, 1, '=IF(A' + i + '<>"",IF(B' + i + '<>"",B' + i + ',NOW()),"")');
sheet.setFormatter(i - 1, 1, 'dd-mm-yyyy hh:mm:ss');
}
// 样本数据
sheet.setText(1, 0, "Andy");
var style = new GC.Spread.Sheets.Style();
style.font = "bold 12px Arial";
style.foreColor = "white";
style.backColor = "#5B9BD5";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
for (var i = 0; i < 2; i++)
sheet.setStyle(0, i, style, GC.Spread.Sheets.SheetArea.viewport);
sheet.resumePaint();
});
</script>