纯前端Excel数据透视表实现损益表应用

财务报表也称对外会计报表，是会计主体对外提供的反映企业或预算单位一定时期资金、利润状况的会计报表，由资产负债表、损益表、现金流量表或财务状况变动表、附表和附注构成。财务报表是财务报告的主要部分，不包括董事报告、管理分析及财务情况说明书等列入财务报告或年度报告的资料。

为了全面系统地揭示企业一定时期的财务状况、经营成果和现金流量，财务报表需按财政部会计准则的标准格式设计，因此，财务报表的典型特征是数据更新频繁、分析维度多、数据来源复杂，常规的报表工具很难同时满足上述所有需求。而借助葡萄城 纯前端表格控件 SpreadJS 和 服务端表格组件 GcExcel 来设计财务报表模板，可以在满足财务数据展示、计算、决策分析的同时，提供如 Excel 一般的使用体验，并可直接复用财务系统原始的 Excel 报表模板，减少从本地到线上的数据迁移工作量。

下面将会给大家展示如何在纯前端环境中，利用SpreadJS创建损益表，并将其添加到你的Web项目中。本文将使用 PivotTables（数据透视表）和 PivotTables Slicer（数据透视表切片器）来创建动态的损益表报告并与之交互。最终效果如图：

网页端利用数据透视表实现损益表

创建损益表大致步骤如下，文中针对每一步操作，都提供了可视化交互和编码两种实现方式：

1. 设置数据

2. 插入数据透视表

3. 构建数据透视表

4. 添加计算项

5. 添加计算字段

6. 添加切片器

7. 进行一些外观调整

8. 生成报告

点击此处下载完整Demo。

设置数据

我们需要做的第一件事就是确保原始数据采用表格格式。这表示数据符合以下条件：

1. 把原始数据整理成标准的表格；

2. 每个列代表一个字段；

3. 没有空白行或列；

4. 数据中没有小计、总计这类二次计算的内容。

在此示例中，我们添加了 Account Group 列来表示报告的不同部分，并将包含数据的表命名为 tblIncome。

插入数据透视表

使用SpreadJS设计器组件可以实现可视化操作，下载SpreadJS安装包，从“\SpreadJS.Release.x.x.x\Designer\Designer Runtime”文件夹安装SpreadJS设计器，然后按照以下步骤插入数据透视表：

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

我们添加的两个字段是差异和差异 %。使用的公式如下：

添加切片器

在 SpreadJS v15 版本中，添加了切片器作为用于过滤数据透视表的新功能。使用此功能按地区和财政年度过滤数据。

如果使用的是SpreadJS设计器，执行以下操作：

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

我们可以对其他字段使用相同的逻辑。使用下表对应的格式：

这里推荐使用条件格式，以使查看者更快地查看最大的帐户。数据透视表提供了为指定维度设置条件规则的能力。无论数据透视表布局如何变化，条件规则都只遵循指定的维度。

如果使用SpreadJS设计器，按照以下步骤添加条件格式规则：

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#scenarios
• 移动端示例（可扫码体验）：http://demo.grapecity.com.cn/spreadjs/mobilesample/