基於純前端類Excel表格控制項實現在線損益表應用

2022 年 10 月 14 日
筆記
前端知識, 工具使用, 葡萄城裡

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

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

下面將會給大家展示如何在純前端環境中，利用純前端表格控制項創建損益表，並將其添加到你的Web項目中。本文將使用 PivotTables（數據透視表）和 PivotTables Slicer（數據透視表切片器）來創建動態的損益表報告並與之交互。最終效果如圖：

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

設置數據
插入數據透視表
構建數據透視表
添加計算項
添加計算欄位
添加切片器
進行一些外觀調整
生成報告

點擊此處下載完整Demo。

設置數據

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

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

在此示例中，我們添加了 Account Group 列來表示報告的不同部分，並將包含數據的表命名為 tblIncome。

插入數據透視表

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

選擇 tblIncome 表
在插入選項卡上單擊數據透視表
選擇「新工作表」
最後確定

或者，以上操作也可以通過編寫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。

注意：數據透視表可以在沒有數據透視面板的情況下工作，我們只是添加了它以方便使用。

添加計算項

除了數據透視表欄位中的現有項目外，這裡還支援使用自定義公式創建一個或多個計算項目。

單擊數據透視表分析
欄位、項目和集合 → 計算項目
設置名稱 3. Gross Profit 並作為公式：=’Account Group'[‘1.收入’]-‘賬戶組'[‘2.銷貨成本’]

或者使用一行程式碼就可以添加計算項目：

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

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

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

添加計算欄位

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

我們將使用計算欄位功能在數據透視表中添加差異和差異百分比。

單擊數據透視表分析。
欄位、項目和集合 → 計算欄位。
設置計算欄位的名稱差異。
要在公式中添加欄位，請選擇該欄位，然後單擊「插入欄位」。
單擊添加按鈕。

或用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);

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

添加切片器

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

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

單擊數據透視表分析
插入切片器
選擇地區和財政年度

或使用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);

進行一些外觀調整

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

摺疊計算項

這將隱藏匯總的帳戶以顯示計算項目的值。

在組頂部顯示小計

轉到設計選項卡
單擊小計
選擇「在組頂部顯示所有小計」

在每個項目後插入空行

轉到設計選項卡
單擊空白行
選擇「在每個項目後插入空白行」

隱藏按鈕和欄位標題

轉到數據透視表分析選項卡
單擊按鈕和欄位標題

更改樞軸布局

轉到設計選項卡
單擊報告布局
選擇「以大綱形式顯示」

上面提到的修改可以通過程式碼輕鬆更改。 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 *

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

轉到值 – > 值欄位設置
單擊數字格式
設置格式。在我們的例子中：$#,##0
確認

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

//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);

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

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

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

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

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

生成報告

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

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

更多純前端表格在線demo示例 ://demo.grapecity.com.cn/spreadjs/gc-sjs-samples/index.html
純前端表格應用場景：//www.grapecity.com.cn/developer/spreadjs#scenarios
移動端示例（可掃碼體驗）：//demo.grapecity.com.cn/spreadjs/mobilesample/

Tags: 前端知識工具使用葡萄城裡