技術文章 » 基於純前端類Excel表格控制元件實現線上損益表應用

基於純前端類Excel表格控制元件實現線上損益表應用

2022-10-14 18:02:59

財務報表也稱對外會計報表，是會計主體對外提供的反映企業或預算單位一定時期資金、利潤狀況的會計報表，由資產負債表、損益表、現金流量表或財務狀況變動表、附表和附註構成。財務報表是財務報告的主要部分，不包括董事報告、管理分析及財務情況說明書等列入財務報告或年度報告的資料。

為了全面系統地揭示企業一定時期的財務狀況、經營成果和現金流量，財務報表需按財政部會計準則的標準格式設計，因此，財務報表的典型特徵是資料更新頻繁、分析維度多、資料來源複雜，常規的報表工具很難同時滿足上述所有需求。
而藉助控制元件設計財務報表模板，可以在滿足財務資料展示、計算、決策分析的同時，提供如 Excel 一般的使用體驗，並可直接複用財務系統原始的 Excel 報表模板，減少從本地到線上的資料遷移工作量。

下面將會給大家展示如何在純前端環境中，利用純前端表格控制元件建立損益表，並將其新增到你的Web專案中。本文將使用 PivotTables（資料透視表）和 PivotTables Slicer（資料透視表切片器）來建立動態的損益表報告並與之互動。最終效果如圖：

建立損益表大致步驟如下，文中針對每一步操作，都提供了視覺化互動和編碼兩種實現方式：

  1. 設定資料
  2. 插入資料透視表
  3. 構建資料透視表
  4. 新增計算項
  5. 新增計算欄位
  6. 新增切片器
  7. 進行一些外觀調整
  8. 生成報告

點選此處下載完整Demo。

設定資料

我們需要做的第一件事就是確保原始資料採用表格格式。這表示資料符合以下條件：

  1. 把原始資料整理成標準的表格；
  2. 每個列代表一個欄位；
  3. 沒有空白行或列；
  4. 資料中沒有小計、總計這類二次計算的內容。

在此範例中，我們新增了 Account Group 列來表示報告的不同部分，並將包含資料的表命名為 tblIncome。

插入資料透視表

使用設計器元件可以實現視覺化操作，下載完成後，從「\SpreadJS.Release.x.x.x\Designer\Designer Runtime」資料夾安裝設計器，然後按照以下步驟插入資料透視表：

  1. 選擇 tblIncome 表
  2. 在插入索引標籤上單擊資料透視表
  3. 選擇「新工作表」
  4. 最後確定

或者，以上操作也可以通過編寫javascript程式碼實現，參考以下程式碼：

let pivotTable = sheet.pivotTables.add("myPivotTable", "tblIncome", 1, 1);

構建資料透視表

為了準確構建資料透視表，我們將使用資料透視表面板。如果這裡使用的是SpreadJS設計器，則每次單擊資料透視表時，面板都會顯示在工作表的右側。

使用以下程式碼將其新增到 javascript 範例中：

var pt = spread.getActiveSheet().pivotTables.all()[0]; 
var panel = new GC.Spread.Pivot.PivotPanel('myPivotPanel', pt, document.getElementById("panel")); 
panel.sectionVisibility(GC.Spread.Pivot.PivotPanelSection.fields |GC.Spread.Pivot.PivotPanelSection.area);

現在拖放右側區域的欄位以構建資料透視表。在我們的範例中：將 Account Group 和 Account 欄位新增到 Rows，並將 Actual 和 Budget 新增到 Values。

注意：資料透視表可以在沒有資料透視面板的情況下工作，我們只是新增了它以方便使用。

新增計算項

除了資料透視表欄位中的現有專案外，這裡還支援使用自定義公式建立一個或多個計算專案。

  1. 單擊資料透視表分析
  2. 欄位、專案和集合 → 計算專案
  3. 設定名稱 3. Gross Profit 並作為公式：='Account Group'['1.收入']-'賬戶組'['2.銷貨成本']

或者使用一行程式碼就可以新增計算專案：

pivotTable.addCalcItem("Account Group", "3. Gross Profit","='Account Group'['1. Revenue']-'Account Group'['2. COGS']");

按照上述步驟新增其他計算項。這些公式如下表所示：

這將使我們的損益表可讀性更好。

新增計算欄位

損益表經常使用方差分析進行業績比較。當實際收入回報高於預算預測或費用低於預算時，預算變化是積極的或有利的。

我們將使用計算欄位功能在資料透視表中新增差異和差異百分比。

  1. 單擊資料透視表分析。
  2. 欄位、專案和集合 → 計算欄位。
  3. 設定計算欄位的名稱差異。
  4. 要在公式中新增欄位，請選擇該欄位，然後單擊「插入欄位」。
  5. 單擊新增按鈕。

或用JavaScript實現：

pivotTable.addCalcField('diff', '=Actual-Budget'); 
pivotTable.add("diff", "Difference", GC.Spread.Pivot.PivotTableFieldType.valueField); 

pivotTable.addCalcField('diff%', '=Actual/Budget-1'); 
pivotTable.add("diff%", "Difference %", GC.Spread.Pivot.PivotTableFieldType.valueField);

我們新增的兩個欄位是差異和差異 %。使用的公式如下：

新增切片器

切片器作為用於過濾資料透視表的新功能。使用此功能按地區和財政年度過濾資料。

如果使用的是設計器，執行以下操作：

  1. 單擊資料透視表分析
  2. 插入切片器
  3. 選擇地區和財政年度

或使用JavaScript實現：

var regionSlicer = sheet.slicers.add("Region", pivotTable.name(), "Region", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable); 
var yearSlicer = sheet.slicers.add("Financial Year", pivotTable.name(), "Financial Year", GC.Spread.Sheets.Slicers.SlicerStyles.dark4(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);

進行一些外觀調整

為了使資料透視表更易於閱讀，這裡進行了一些調整，例如：

摺疊計算項

這將隱藏彙總的帳戶以顯示計算專案的值。

在組頂部顯示小計

  1. 轉到設計索引標籤
  2. 單擊小計
  3. 選擇「在組頂部顯示所有小計」

在每個專案後插入空行

  1. 轉到設計索引標籤
  2. 單擊空白行
  3. 選擇「在每個專案後插入空白行」

隱藏按鈕和欄位標題

  1. 轉到資料透視表分析索引標籤
  2. 單擊按鈕和欄位標題

更改樞軸佈局

  1. 轉到設計索引標籤
  2. 單擊報告佈局
  3. 選擇「以大綱形式顯示」

上面提到的修改可以通過程式碼輕鬆更改。 SpreadJS 提供了許多不同的選項來根據應用程式的需要自定義資料透視表的外觀和功能。我們可以更改資料透視表選項和佈局，如下所示：

let option = pivotTable.options;
option = {
        allowMultipleFiltersPerField: true,
        insertBlankLineAfterEachItem: true, // Insert Blank Line after Each Item *
        grandTotalPosition: GC.Spread.Pivot.GrandTotalPosition.row,
        subtotalsPosition: GC.Spread.Pivot.SubTotalsPosition.top, // Show SubTotals at the Top of the Group *
        displayFieldsInPageFilterArea: GC.Spread.Pivot.DisplayFields.downThenOver,
        reportFilterFieldsPerColumn: 1,
        bandRows:true,
        bandColumns: true,
        showRowHeader: true,
        showColumnHeader: true,
        showDrill: true, // Collapse Buttons *
        showMissing: true,
        showToolTip: true,
        missingCaption: 'something',
        fillDownLabels: false,
        repeatAllItemLabels: false,
        rowLabelIndent: 4,
        mergeItem: false,
        showHeaders: true // Collapse Field Headers *
    };
pivotTable.layoutType(1); // Change the Pivot Layout to Outline Form *

條件和自定義格式
接下來，將格式化資料透視表欄位。這裡可以使用如下所示的資料透視面板設定格式：

  1. 轉到值 - > 值欄位設定
  2. 單擊數位格式
  3. 設定格式。在我們的例子中：$#,##0
  4. 確認

如果想通過程式碼執行此操作，請參見下文：

//identify the area
var areaActual= {
            dataOnly: true,
            references: [
                {
                    fieldName: "Actual",
                    items: [fieldName]
                }
            ]
        };
 
var style = new GC.Spread.Sheets.Style();
style.formatter = "$#,##0";
//set style to the area
pivotTable.setStyle(areaActual, style);

我們可以對其他欄位使用相同的邏輯。使用下表對應的格式：

這裡推薦使用條件格式，以使檢視者更快地檢視最大的帳戶。資料透視表提供了為指定維度設定條件規則的能力。無論資料透視表佈局如何變化，條件規則都只遵循指定的維度。

如果使用設計器，按照以下步驟新增條件格式規則：

  1. 選擇單元格：G7:H11
  2. 主頁 → 條件格式 → 新規則
  3. 選擇「根據單元格的值格式化所有單元格」
  4. 格式樣式：2 色標度（藍色表示最高值，白色表示最低值）

對其他帳戶組重複相同的操作，記住使用黃色作為費用和藍色作為收入。

生成報告

下面是我們製作好的損益表報告截圖：

以上就是如何使用 SpreadJS 純前端表格控制元件，來生成所需的財務報告來支撐企業的財務應用。

更多純前端表格線上demo範例 :https://demo.grapecity.com.cn/spreadjs/gc-sjs-samples/index.html
純前端表格應用場景：https://www.grapecity.com.cn/developer/spreadjs
行動端範例（可掃碼體驗）：http://demo.grapecity.com.cn/spreadjs/mobilesample/