SAP资产负债表实现方案探索 - 基于 Excel-DNA 自定

2021-11-22 本文已影响0人 Stone0823

早前曾基于 VBA 的 RFC 的方式从 SAP 获取数据，在 Excel 中自定义函数实现资产负债表，也把实现的过程在博文中进行了介绍：SAP 接口编程之综合实例（一）：资产负债表方案 - 简书 (jianshu.com) 。

完成之后，对这个方法进行回顾，觉得还是不尽人意，当时为了实现用户可以自行定义报表格式之目的，比如根据报表项找到包含的会计科目，并且将这些科目的余额进行合计，在 VBA 中实现，也挺费事的。所以后面就想要简化和重构，觉得可以把 SAP 的技术点和其他开发工具综合运用，既达到配置化的目的，还要能保证方案的优美。我将自己的思考进行了编码测试，完成了几种不同的实现方法，有 Excel-DNA 的，也有 VSTO 实现方案的，还有用 Excel 加载宏实现的等等。后续有时间我会陆续介绍，本篇介绍基于 Excel-DNA 的实现方案。

和上一篇文章介绍的方法相比，优化的地方包括：

利用 SAP 的 set，实现报表项的定义。SAP 的 set 基于 table 定义组合，我们把报表项与会计科目的对应关系放在 set 中进行定义
SAP 的 RFC 函数，作为数据提供者，不仅要提供基于会计科目的发生额和余额等，还提供基于报表项的发生额和余额等。比如「应收账款」是一个报表项，编写的函数能获取到「应收账款」的发生额、余额等，这样就大大减少了在客户端获取报表项数据的难度
将 Excel 自定义函数放在插件（加载项）中，只要 Excel 加载到这个加载项，函数就是可用状态，这样就实现了函数功能和报表载体的解耦

下面就是具体的实现过程了。

通过 set 定义报表项

我之前也写过如何使用 set 的文章，不熟悉的小伙伴可以自行参考：如何在ABAP中使用集(Sets)，本篇就不再重述了。为了实现资产负债表，我们首先定义一个名为 zbs 的集（bs 表示 balance sheet ），zbs 的结构如下：

编写 RFC 函数提供报表所需数据

在 SAP 中编写 RFC 函数 Z_BS_BALANCES 来提供资产负债表所需要的数据，该函数的入参主要是 company code, fiscal year 和 fiscal period:

函数的出参为 ACC_BALANCES 和 FS_BALANCES ，分别表示基于会计科目的数据值和基于报表项的数据值。

两个相关联类型的结构如下：

Z_BS_BALANCES 函数的源码如下：

function z_bs_balances.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     VALUE(COMPANYCODE) TYPE  ZGLACCBALANCE-COMPCODE
*"     VALUE(FISCALYEAR) TYPE  ZGLACCBALANCE-FISYEAR
*"     VALUE(FISCALPERIOD) TYPE  ZGLACCBALANCE-PERIOD
*"  TABLES
*"      ACC_BALANCES STRUCTURE  ZFSBALANCE
*"      FS_BALANCES STRUCTURE  ZFSBALANCESUM
*"----------------------------------------------------------------------
  data: lt_setvalues type table of rgsb4,
          ls_setvalues like line of lt_setvalues.

  data: lt_all like standard table of zglaccbalance with header line.   " balances for all acocunts

  data: lt_ret     like standard table of zfsbalance with header line,
        lt_sum     like standard table of zfsbalancesum with header line,
        lt_sumtemp like standard table of lt_sum with header line.

  call function 'Z_BAPI_GLACCPERIODBALANCES'
    exporting
      companycode  = companycode
      fiscalyear   = fiscalyear
      fiscalperiod = fiscalperiod
    tables
      acc_balances = lt_all.

  loop at lt_all.
    move-corresponding lt_all to lt_ret.
    append lt_ret.
    clear: lt_all, lt_ret.
  endloop.

  " 获取报表项的科目
  call function 'G_SET_GET_ALL_VALUES'
    exporting
      client        = sy-mandt
      setnr         = 'ZBS'
      table         = 'SKB1'
      class         = '0000'
      fieldname     = 'SAKNR'
    tables
      set_values    = lt_setvalues
    exceptions
      set_not_found = 1
      others        = 2.

  " 去掉0000
  loop at lt_setvalues into ls_setvalues.
    replace '0000' in ls_setvalues-setnr with ''.
    modify lt_setvalues from ls_setvalues.
    clear ls_setvalues.
  endloop.

  loop at lt_ret.
    loop at lt_setvalues into ls_setvalues.
      if lt_ret-glaccount between ls_setvalues-from and ls_setvalues-to.
        lt_ret-fsitem = ls_setvalues-setnr.
        modify lt_ret.
      endif.
      clear ls_setvalues.
    endloop.
  endloop.

  " 汇总
  loop at lt_ret.
    move-corresponding lt_ret to lt_sumtemp.
    append lt_sumtemp.
    clear: lt_ret, lt_sumtemp.
  endloop.
  sort lt_sumtemp by fsitem.
  loop at lt_sumtemp.
    collect lt_sumtemp into lt_sum.
    clear: lt_sumtemp, lt_sum.
  endloop.

  acc_balances[] = lt_ret[].
  fs_balances[] = lt_sum[].
  
endfunction.

可以看到， z_bs_balances 调用了 z_bapi_glaccperiodbalances 函数， z_bapi_glaccperiodbalances 函数的代码在 SAP接口编程之综合实例（一）：资产负债表方案中有提供。

函数测试：

资产负债表所需要的数据都在这里。

ABAP restful service

如何在外部调用 RFC 函数获取到数据呢? 由于最终的客户端是 Excel 工作簿，我觉得与其用传统的 RFC 或者 Nco3.0，不如直接通过 restful service，这样做的好处是客户端不用关心与 SAP 的连接。并且为了减少中间环节，我们直接从 SAP 来提供 restful service。SAP 新的版本提供了 Restful Service 开发框架，我使用的方案是网上一个开源的实现，该实现能够将任意的 RFC 函数暴露为 restful service，通过 query string 来传输函数的参数。该开源代码和相关介绍文章如下:

代码的部署推荐使用 abapGit，老一点的版本可以用 sapLink 部署。该代码考虑到了调用的权限，所以在部署之后，要么增加一个 Z_JSON 的权限对象，包含该函数名，并且将权限对象赋给角色，要么从 hanle_request 代码中删除权限检查的代码。作者的原话为：

通过事务码 SICF 在 default_host 下配置 Restful 服务，下面的配置对应的的路径为 <sap host>/sap/zrfc/

image

zrfc 调用 zcl_json_handler:

对 SAP 提供 Restful service，之前也曾经写过三篇文章，小伙伴们请自行参考：

SAP 如何提供 RESTful Web 服务？
SAP 如何提供 RESTful Web 服务(2) - ABAP 与 JSON
SAP 如何提供 RESTful Web 服务(3) - Rest 路径处理

对 Restful Serivce, 利用熟悉的 Postman 测试一下，在 postman 中新建一个 GET 请求，路径为：

http://sapecc6:8000/sap/zrfc/z_bs_balances?COMPANYCODE=Z900&FISCALYEAR=2020&FISCALPERIOD=10

测试结果：

可以看到，外部通过调用 SAP 的 restful service，已经成功获取到数据。为了便于大家理解，我把完整的 json 数据贴出来：

{
    "ACC_BALANCES": [
        {
            "COMPCODE": "Z900",
            "FSITEM": "ASST020",
            "GLACCOUNT": "0010010100",
            "ACCTEXT": "现金-人民币(CNY)",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": -2262.0000,
            "OPEN_BALANCE": -2262.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": -3500.0000,
            "PER_AMT": -3500.0000,
            "BALANCE": -5762.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "ASST020",
            "GLACCOUNT": "0010010101",
            "ACCTEXT": "现金-人民币",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": -2240.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -2240.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "ASST020",
            "GLACCOUNT": "0010020100",
            "ACCTEXT": "工行基本户",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": -120000.0000,
            "OPEN_BALANCE": -119000.0000,
            "DEBIT_PER": 3500.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 3500.0000,
            "BALANCE": -115500.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "ASST070",
            "GLACCOUNT": "0011310100",
            "ACCTEXT": "应收账款",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": 1200.0500,
            "DEBIT_PER": 3588.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 3588.0000,
            "BALANCE": 4788.0500,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "LIAT050",
            "GLACCOUNT": "0021210100",
            "ACCTEXT": "应付账款",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": -3513.0000,
            "OPEN_BALANCE": -3513.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -3513.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "LIAT110",
            "GLACCOUNT": "0021810900",
            "ACCTEXT": "其他应付款—其他",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": -123.0000,
            "OPEN_BALANCE": -123.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -123.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "LIAT080",
            "GLACCOUNT": "0022210100",
            "ACCTEXT": "应交税费-应交增值税-进项税额",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 26.0000,
            "OPEN_BALANCE": 26.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": 26.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "EQT060",
            "GLACCOUNT": "0031411500",
            "ACCTEXT": "利润分配—未分配利润",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 125872.0000,
            "OPEN_BALANCE": 125872.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": 125872.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "EQT060",
            "GLACCOUNT": "0051020200",
            "ACCTEXT": "其他业务收入",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": -1200.0500,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -1200.0500,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "EQT060",
            "GLACCOUNT": "0053010100",
            "ACCTEXT": "营业外收入",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": -1500.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": -3588.0000,
            "PER_AMT": -3588.0000,
            "BALANCE": -5088.0000,
            "CURR": "CNY"
        },
        {
            "COMPCODE": "Z900",
            "FSITEM": "EQT060",
            "GLACCOUNT": "0055010601",
            "ACCTEXT": "营业费用-招待费",
            "FISYEAR": "2020",
            "PERIOD": "10",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": 2740.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": 2740.0000,
            "CURR": "CNY"
        }
    ],
    "FS_BALANCES": [
        {
            "FSITEM": "ASST020",
            "YR_OPENBAL": -122262.0000,
            "OPEN_BALANCE": -123502.0000,
            "DEBIT_PER": 3500.0000,
            "CREDIT_PER": -3500.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -123502.0000
        },
        {
            "FSITEM": "ASST070",
            "YR_OPENBAL": 0.0000,
            "OPEN_BALANCE": 1200.0500,
            "DEBIT_PER": 3588.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 3588.0000,
            "BALANCE": 4788.0500
        },
        {
            "FSITEM": "EQT060",
            "YR_OPENBAL": 125872.0000,
            "OPEN_BALANCE": 125911.9500,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": -3588.0000,
            "PER_AMT": -3588.0000,
            "BALANCE": 122323.9500
        },
        {
            "FSITEM": "LIAT050",
            "YR_OPENBAL": -3513.0000,
            "OPEN_BALANCE": -3513.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -3513.0000
        },
        {
            "FSITEM": "LIAT080",
            "YR_OPENBAL": 26.0000,
            "OPEN_BALANCE": 26.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": 26.0000
        },
        {
            "FSITEM": "LIAT110",
            "YR_OPENBAL": -123.0000,
            "OPEN_BALANCE": -123.0000,
            "DEBIT_PER": 0.0000,
            "CREDIT_PER": 0.0000,
            "PER_AMT": 0.0000,
            "BALANCE": -123.0000
        }
    ]
}

Excel-DNA 实现 Excel 自定义函数

什么是 Excel-DNA 呢? 简单解释一下，微软给 Excel 的开发提供了两个接口：一个是 COM 接口，一个是 C API 接口。COM 接口的执行效率低，C API 接口的效率高。微软提供了 xll 格式的 addins，使用 C API 来操作excel。但是因为 xll 只能用 C++ 来开发，门槛还是比较高的，所以，很多 shim 就应运而生，shim 意思是给 xll 加上一个中间垫片，使其能够加载 .net 写的 dll。Excel-Dna 就是诸多 shim 中最好的一个。也就是是说 Excel-Dna 是实现 xll 加载 .net 库的一个 loader，从而开发人员可以在 c# 中编写代码，xll 能加载后调用。和 VSTO 比较起来，另一个优点是 Excel-DNA 尤其适合编写函数。

创建一个 Class Libray 项目，在项目中通过 NuGet 安装下面的四个 Package:

install-package RestSharp -Version 106.13.0
install-package Newtonsoft.Json -Version 13.0.1
install-package ExcelDna.AddIn -Version 1.5.0
install-package ExcelDna.IntelliSense -Version 1.5.0

定义 model

namespace SAPGlFunctions.Models
{
    public class FSBalance
    {
        public string FSITEM { get; set; }
        public double YR_OPENBAL { get; set; }
        public double OPEN_BALANCE { get; set; }
        public double DEBIT_PER { get; set; }
        public double CREDIT_PER { get; set; }
        public double PER_AMT { get; set; }
        public double BALANCE { get; set; }
    }
}

基于 restsharp 消费 SAP restful service

namespace SAPGlFunctions.Services
{
    public static class GlBalnceService
    {
        public static string BaseUrl = "http://sapecc6:8000/sap/zrfc/";

        public static IList<FSBalance> GetBalances(string companyCode, string fiscalYear, string fiscalPeriod)
        {
            var client = new RestClient(BaseUrl);
            var req = new RestRequest("z_bs_balances", Method.GET);
            req.AddParameter("COMPANYCODE", companyCode);
            req.AddParameter("FISCALYEAR", fiscalYear);
            req.AddParameter("FISCALPERIOD", fiscalPeriod);

            IRestResponse resp = client.Execute(req);

            if (!resp.IsSuccessful) {
                throw new Exception(resp.ErrorMessage);
            }

            return Parse(resp.Content);
        }

        private static IList<FSBalance> Parse(string responseContent)
        {
            JObject content = JObject.Parse(responseContent);
            IList<JToken> results = content["FS_BALANCES"].Children().ToList();
            IList<FSBalance> balances = new List<FSBalance>();
            foreach (JToken token in results) {
                balances.Add(JsonConvert.DeserializeObject<FSBalance>(token.ToString()));
            }

            return balances;
        }
    }
}

ResSharp 消费 restful service, newtonsoft.json 实现 json 格式的解析。

实现 Excel 函数

namespace SAPGlFunctions.ExcelDnaFunctions
{
    public class GlFunctions
    {
        [ExcelFunction(Name = "FSBalance", Description = "根据报表项(FS Item)和(Amount type)获取金额")]
        public static double FsItemBalance(
            [ExcelArgument(Description = "公司代码")]
            string companyCode,
            [ExcelArgument(Description = "年度")]
            string year,
            [ExcelArgument(Description = "期间")]
            string period,
            [ExcelArgument(Description = "报表项, 在SAP中用GS03查看")]
            string fsItem,
            [ExcelArgument(Description = "金额类型(1:年初余额,2:期初余额,3:期间借方,4:期间贷方,5:期间净额,6:期末余额)")]
            int amountType)
        {
            double rv = 0.0;

            var glAccountBalances = GlBalnceService.GetBalances(companyCode, year, period);
            var fsItemBalances = glAccountBalances.FirstOrDefault(i => i.FSITEM.Equals(fsItem));

            switch (amountType) {
                case 1:
                    rv = fsItemBalances.YR_OPENBAL;  
                    break;
                case 2:
                    rv = fsItemBalances.OPEN_BALANCE;
                    break;
                case 3:
                    rv = fsItemBalances.DEBIT_PER;
                    break;
                case 4:
                    rv = fsItemBalances.CREDIT_PER; 
                    break;
                case 5:
                    rv = fsItemBalances.PER_AMT; 
                    break;
                case 6:
                    rv = fsItemBalances.BALANCE;
                    break;
            }

            return rv;
        }
    }
}

编译后，在项目的 bin 目录中有如下文件：