技術文章 » 一篇帶你瞭解如何使用純前端類Excel表格構建現金流量表

一篇帶你瞭解如何使用純前端類Excel表格構建現金流量表

2022-10-19 06:01:32

現金流量表（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]=@)),0),"deposits",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]=@)),0),"month",MONTH($A$2)))

作為第一個引數，它將單元格範圍作為 TemplateSheet 中的模板。

作為第二個引數，它需要一個 OBJECT，該 OBJECT 從位於資料來源表的 Table1 中獲取資料。

  1. [日期]：單元格的當前值
  2. [開始]：之前所有存款的總和 - 之前所有提款的總和
  3. [提款]：當前提款的總和
  4. [存款]：當前存款的總和
  5. [end]:[start] + 所有當前存款的總和 - 所有當前提款的總和

使用公式是繫結並返回一個範圍模板，以便更輕鬆地使用範圍模板。

這是最終輸出：

如上圖所示，包含日曆天數的單元格提供有關開始/結束餘額、存款總額和提款總額的資訊。

第 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}),"")

![](https://img2022.cnblogs.com/blog/139239/202210/139239-20221018232814572-876221459.png)
目前手動插入 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/spreadjs/gc-sjs-samples/index.html
行動端範例：http://demo.grapecity.com.cn/spreadjs/mobilesample/