乐趣区

关于葡萄城开发技术:一篇带你了解如何使用纯前端类Excel表格构建现金流量表

jiezi

现金流量表（Cash Flow Statement），是指反映企业在肯定会计期间现金和现金等价物流入和流出的报表。现金流量表是企业财务报表的三个根本报告之一（另外两个是资产负债表和损益表）。
为了全面系统地揭示企业肯定期间的财务状况、经营成绩和现金流量，财务报表需按财政部会计准则的规范格局设计，因而，财务报表的典型特色是数据更新频繁、剖析维度多、数据起源简单，惯例的报表工具很难同时满足上述所有需要
本博客将带大家理解如何应用类 Excel 的 JavaScript 电子表格在前端创立现金流日历。此日历将宽泛应用以下弱小性能：

  1. 动静数组公式 – 依据一个公式将多个后果返回到一系列单元格。此示例应用 SEQUENCE 和 FILTER 函数。
  2. RANGEBLOCKSPARKLINE(template_range, data_expr) – 此迷你图容许开发人员将单元格范畴模板 (template_range) 定义为单个单元格类型，并将该模板利用于单元格以将一组数据 (data_expr) 加载到模板中。该模板能够包含多行和 / 或多列。

最终成果如图所示：

点击此处下载残缺示例。

要创立咱们的现金流日历，咱们须要创立如下所述的三张表：

  1. 数据源表
  2. 模板表
  3. 现金流日历：渲染表

数据源表

咱们示例的数据源是交易列表。

咱们创立了一个更动静的表格，当咱们须要数据而不是单元格范畴时，咱们能够援用 Table1。

此表蕴含无关 TransactionID、交易类型、交易日期、公司名称、帐户名称、贷款金额和取款的信息。

模板表

此页面蕴含咱们将用来出现现金流日历中产生的交易的模板范畴。

此处的此单元格范畴将用作蕴含现金流日历中所需信息的单元格的模板。

咱们要做的第一件事是排列单元格，而后设置单元格的绑定门路。

它能够通过 Javascript 应用 SpreadJS setBindingPath 办法来实现。

templateSheet.setBindingPath(0, 1, "month");
templateSheet.setBindingPath(1, 2, "date");
templateSheet.setBindingPath(2, 2, "start");
templateSheet.setBindingPath(3, 2, "withdrawals");
templateSheet.setBindingPath(4, 2, "deposits");
templateSheet.setBindingPath(5, 2, "end");

当然，上边这步操作也有不必写代码的办法——用 SpreadJS 设计器，下载 SpreadJS 安装包，在下载的安装包中，从“\SpreadJS.Release.x.x.x\Designer\Designer Runtime”门路下找到设计器的安装包，实现装置后，依照下列步骤操作：

  1. 单击数据选项卡上的模板菜单 – 字段列表面板将呈现在右侧
  2. 将鼠标悬停在 Start 分支上并通过单击绿色 + 按钮增加字段 * 请留神，你能够应用“x”按钮删除字段并应用位于分支右侧的设置批改这些字段
  3. 拖动模板范畴所需单元格中的字段

为了使现金短缺（期末余额为负）的日子能够用红色着色，期末余额为正的日子用绿色着色，中性的用彩色着色，咱们能够应用条件格局。在设计器上能够这样操作：

  1. 在合并时抉择日期单元格“A2:D2”
  2. 条件格局 → 新规定
  3. 通常，键入并抉择应用公式来确定要格式化的单元格
  4. 输出你的公式，在咱们的例子中 =’Cell Template’!$C$6>0
  5. 单击格局→填充→抉择绿色作为字体色彩
  6. 反复雷同的步骤，但应用公式：=’Cell Template’!$C$6<0 * 请留神，对于余额为负的状况，色彩应设置为红色

现金流日历：渲染表

第 1 步：增加 MonthPicker 元素
咱们日历的第一个元素是可变月份元素。要增加它，请应用 MonthPicker，这是 SpreadJS 中的一种下拉单元格款式。

JavaScript:
var monthPickerStyle = new GC.Spread.Sheets.Style();
monthPickerStyle.dropDowns = [
  {
      type: GC.Spread.Sheets.DropDownType.monthPicker,
      option: {
          startYear: 2019,
          stopYear: 2021,
          height: 300,
      }
  }
];
sheet.setStyle(2, 5, monthPickerStyle);

设计器：
抉择单元格（在咱们的例子中为 B2）

  1. 主页选项卡 → 单元格下拉菜单 → 月份选择器
  2. 在命令右侧，单击 …
  3. 设置选取器的开始、完结年份和高度

而后，咱们在进行计算时为蕴含月份的单元格指定一个名称。

  1. 在公式选项卡上，抉择名称管理器
  2. 在弹出窗口中，单击新建按钮
  3. 设置单元格的名称。在咱们的示例中：name: currentMonth
    参考：$D$2。你还能够增加评论并更改援用对象

第 2 步：创立现金流日历

应用 SEQUENCE(rows,columns,start,step) 函数来调配咱们日历中的日期。这容许咱们稍后在 CellClick 上检索单元格值。B4 单元格的公式为：

=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)

JavaScript:
cashflowSheet.setFormula(3, 1, '=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)');

咱们还没有为这些单元格应用格式化程序。

下一步是应用条件格局来使属于其余月份的日期成为可能，但所选日期为空白：

  1. 抉择 B4:H9 而后抉择日历的日期 → 条件格局
  2. 从下拉列表中抉择新规定，而后抉择“应用公式确定要格式化为规定类型的单元格”
  3. 输出你的公式，在咱们的例子中为“=MONTH(B4)<>MONTH(currentMonth)”– 此格局仅实用于月份与下拉列表中抉择的月份不同的单元格
  4. 单击格局
  5. 编号 → 自定义
  6. 输出”;;;”作为格式化程序将所有正确的单元格设为空白

上面的步骤包含应用 RANGEBLOCKSPARKLINE，它将 TemplateSheet 中的单元格范畴用作单个单元格类型，并应用 OBJECT 函数将模板利用于代表咱们现金流日历中日期的所有单元格中。

因为咱们应用 SEQUENCE 为这些单元格设置值，因而咱们将应用 RANGEBLOCKSPARKLINE 作为格局。

  1. 抉择单元格区域 B4:H9
  2. 格局→更多数字格局→自定义

  3. 将格式化程序设置为：

    =RANGEBLOCKSPARKLINE('Cell Template'!$A$2:$D$7,OBJECT("date",@,"start",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]<@))-SUM(FILTER(Table1[Withdrawal],Table1[Date]<@)),0),"withdrawals",IFERROR(SUM(FILTER(Table1[Withdrawal],Table1[Date][email protected])),0),"deposits",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date][email protected])),0),"month",MONTH($A$2)))

    作为第一个参数，它将单元格范畴作为 TemplateSheet 中的模板。

作为第二个参数，它须要一个 OBJECT，该 OBJECT 从位于数据源表的 Table1 中获取数据。

  1. [日期]：单元格的以后值
  2. [开始]：之前所有贷款的总和 – 之前所有提款的总和
  3. [提款]：以后提款的总和
  4. [贷款]：以后贷款的总和

应用公式是绑定并返回一个范畴模板，以便更轻松地应用范畴模板。

这是最终输入：

如上图所示，蕴含日历天数的单元格提供无关开始 / 完结余额、贷款总额和提款总额的信息。

第 3 步：获取每日交易
如果咱们想从 DataSource 页面中提取所有交易的列表，咱们能够借助 SelectionChanged 事件。当这些事件产生时，SpreadJS 中的工作表将其事件绑定到特定操作。

在咱们的示例中，当用户从日历中抉择日期时，咱们应用了这个不便的 SpreadJS 性能来提取所有交易的列表。

咱们为蕴含所选日期、贷款和取款的单元格指定一个名称，以便进行计算，此表格将蕴含所有交易数据。
为 currentMonth 创立名称范畴的步骤是：

  1. 在公式选项卡上，抉择名称管理器
  2. 在弹出窗口中，单击新建按钮
  3. 设置单元格的名称

在咱们的示例中：

name: 以后抉择；refer to: ='Cash-Flow'!$B$11

name: 以后贷款；refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Deposit]>0))

name: 以后取款；refer to: =FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Withdrawal]>0))

设置不同的公式来获取所有贷款列表、所有提款列表、完结和开始余额。

  1. 起始余额（之前所有贷款的总和 – 之前所有取款的总和）：=IFERROR((SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]<$B$11))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions [日期]<$B$11))),0)
  2. 完结余额（起始余额 + 以后贷款的总和 – 以后提款的总和）：=IFERROR(D13+(SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]=$B$11))-SUM(FILTER(tblTransactions[Withdrawal] ,tblTransactions[日期]=$B$11))),0)

其中 D13 是起始余额：

1. 贷款：=IFERROR(FILTER(currentDeposits,{1,0,1,1,0}),"")
2. 取款：=IFERROR(FILTER(currentWithdrawals,{1,0,1,0,1}),"")

目前手动插入 currentSelection。要依据用户日期抉择进行更改，请执行下一步。

在 JavaScript 中创立事件处理函数（见下文）：

// on day selection, update a cell used in filtering the data to show detailed transaction list
 cashflowSheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (sender, args) {
    const sheet = args.sheet;
    const row = args.newSelections[0].row;
    const col = args.newSelections[0].col;

    if ((row < 3 || row >= 3 + 6)
        || (col < 1 || col >= 1 + 7))
        return;
    // set the current date cell so that FILTER would update.
    sheet.setValue(10, 1, sheet.getValue(row, col));
});

一旦用户单击单元格，下面的代码就会查看单元格是否在日历边界内 (B4:H9)。否则，它会更新 currentSelection，因而，所有用于获取余额和无关交易信息的公式都会在它们指向更改的选定日期时给出正确的后果。

理解更多 demo 示例 :https://demo.grapecity.com.cn…
挪动端示例：http://demo.grapecity.com.cn/…

退出移动版