【日常随笔】游戏策划表格数据检查和Excel-Diff的终极技术方案

utmhikari
644 阅读17分钟

本文参考自本人的以下几篇CSDN文章，欢迎大家关注！

在游戏行业的研发过程中，策划通常采用最原始的excel方式来配置游戏数据，因此策划表格数据检查是游戏测试工作的刚性需求。在游戏开发期中，有大量bug的起因是策划同学在配置上不够规范到位，因此通常需要一套工具链，通过更加便捷、更加精准的方法去定位到策划表格配置的问题。本文就介绍这套表格数据检查和Excel-Diff工具链的终极技术方案。

表格数据检查工具

技术实现思路

表格数据检查的目的有以下一些：给定某个数值策划案，检查实际配置与策划案是否有出入；给定某个配置规则，检查实际配置是否有不符合规则，造成风险的地方（除去程序导表检查的那一部分）。针对这些需求，简单粗暴的方法就是强行coding的方式，将读取表格（Excel）数据出来（或者将表格数据转化为程序文件），然后用编码的方式联表，继而通过coding逻辑，去导出不符合规则的数据。这一种方式虽然灵活，但对于业务侧同学，还存在壁垒以及需要突破的地方：

  • 壁垒：业务侧同学的技术能力，通常难以hold住coding的技巧。从技术人员角度而言，涵盖大量代码的臃肿的表格检查规则，可读性很差且难以维护。
  • 突破：相较于人肉检查表格数据而言，时间的消耗主要在“联表”这一操作上面。游戏内部逻辑相对复杂，策划表之间的数据也是紧密耦合，比如一个宝箱抽取，就有可能涉及4~5个表的相互关系。如果不能够解决联表耗时的问题，表格数据检查的效率会大打折扣。

因此总体来看，除了采用coding这一备选方案之外，另外一个较好的方法是采用声明式、配置化的方式描述表格检查规则，通过一系列数据处理规则的串联，导出来一份最终数据。这样一来相对减少了学习成本，从业务侧角度而言，从“学习coding”变成了“理解配置”；二来解决了联表效率的问题，业务侧只需要描述一个联表的数据处理规则，后台就可以自动按照规则描述的方式对数据处理，最终呈现到业务的，直截了当，就是最终的结果；三来解决了根本目的，不论是策划案比对（通常是另外的文档，不能按版本diff）还是检索不合规的数据，本质上都是数据导出，因此用数据处理规则的串联，就能解决大部分的需求。

了解mongodb的同学，多多少少都会听说到mongodb聚合，亦即数据处理流水线。在mongodb中，如果需要实现复杂的数据查询需求，就需要用到聚合的方式，定义多个流水线规则，比如过滤查询、排序、group、lookup、字段值转化、字段值计算等，去呈现最终的数据形式。这种数据查询的实现方式，与表格检查的需求不谋而合，因此笔者在实践中，也果断借鉴mongodb的思路，实现了导表、联表、转化字段、列表展开等多种数据处理规则，并且研发了Web前端界面方便实现简单的配置。这样一来，业务测试同学也只需要按照给定的规则配置方法，配置流水线规则，就能获得最终想要的数据。

现在剩下的问题是，原生的表格数据从何而来？如何管理？针对这点还是需要从用户的角度出发。用户侧所关心的内容无非只有这么几个：我用什么样的导表规则，需要在什么版本的策划表，导出什么样的数据。因此，当用户提交到表规则与策划表版本的之后，程序后台就需要自动执行相应的行为：更新表格到对应的版本->通过脚本去Archive表格数据为特定的数据结构->通过一系列数据处理规则加工数据->将数据转化为用户友好的形式呈现。在笔者的方案中，做了这么几件事情去解决这些问题：

  • 实现一套策划数据仓库管理系统，管理同时存在的多个策划表仓库，具体形式和笔者实现的repomaster类似，仓库的元数据存在内存/缓存中，并且支持加载自定义导出数据脚本的方式导出策划配置数据。数据仓库管理模块需要与数据处理模块分离，尤其是仓库过大，每次更新仓库会占用较多资源，不分离的话会影响数据处理的进程。数据仓库管理与数据处理模块，通过共享代码/协议的方式，减少代码管理的成本。
  • 在策划数据处理和数据仓库管理系统中，分别实现一套简易的事务管理系统。针对每一个用户的导表请求，数据处理模块生成一个后台事务单独处理。如果需要更新数据，就发送请求数据仓库管理模块更新数据，数据仓库管理模块也相应地生成一个后台事务，随机选择一个仓库更新/导出数据，并通知数据处理侧拿取导出的数据来处理。这一过程中，就算出现单点故障/数据未成功获取等意外情况，也是可以接受的。从经验上看，大量的时间瓶颈主要集中在更新仓库这一过程上。

工具架构设计

整个测试工具分为两个部分：

  • 仓库管理服务：负责管理多个策划表格的svn/git仓库，并涵盖导入表格数据的功能
  • 表格测试服务：负责接收用户的测试请求，向仓库管理服务请求特定版本的数据，执行表格测试任务

如果组织里技术基建比较捉鸡的话，仓库管理服务需要挂载一个大空间的硬盘，硬盘里存储多个策划表格仓库，而管理服务则缓存仓库信息在redis中。同时用户可以在仓库管理服务的目录下自定义导入数据到redis的脚本，仓库管理服务运行时动态执行脚本代码，将表格数据以及其元数据导入到redis

策划表格测试服务连接redis，但约定上只有读的权限，没有增删的权限，且只能读取redis中缓存的表格数据及元数据。表格测试服务将测试相关的配置以及结果则都存储在mongodb中，提供服务接口给到web前端

设计仓库管理服务，如果用python的话，可以用ProcessPoolExecutor先开一组worker，每个worker在接收到任务时，再在其中开另外的ProcessPoolExecutor执行任务（像表格对比就需要同时更新并上传两个表的数据，因此得另外开pool去submit任务），在任务执行完后销毁每个worker里的pool。这样能够有效解决内存泄露的问题

在先前的文章当中，提到表格检查的本质其实是数据导出，可以用mongodb聚合的方式去实现数据导出。

mongodb的聚合是由一系列的stages组合而成的，每个stage有固定的语法描述数据处理的方式。而放到表格数据导出这个需求里，我们可以拟定一套数据处理流水线：

  • 数据容器：从redis中读取某个版本的表格数据，转化成一个数据容器DataContainer的结构
  • 初始数据：从数据容器中，筛选需要的数据作为初始数据，合并为一个List[Dict[str, Any]]形式的数据
  • 数据处理步骤集：通过一系列数据处理步骤，将初始数据转化成包含最终需要的数据的集合
    • 每个数据处理步骤的接口定义是process(data: List[Dict[str, Any]], container: DataContainer, tracer: PipelineStageTracer) -> List[Dict[str, Any]]，其中data是上一个处理步骤下来的数据，container是数据容器源头，tracer则用于跟踪当前处理步骤的信息
  • 数据提取规则：根据最终处理的数据，再次提取需要的数据出来，标注每个字段的含义。这一步主要用于人性化展示数据导出结果

数据处理步骤需要实现json配置->处理步骤数据结构的转化。根据mongodb的定义，首先需要拆解这些步骤包含的元素：

一些基础的表达式包括：

  • 定位符Locator：xx.yy.zz，用来标识数据在某个object的位置
  • 计算符Calculator：用于执行数学计算，参考aggregation expressions中的arithemtic expr operators部分
  • 累积符Accumulator：用于提取某个列表数据的属性，参考mongodb group的内容

一些基础的处理步骤包括：

  • 过滤FilterStage： 可以参照mongodb的查询实现
    • 需要的基本数据包括：定位符Locator、运算符Operator、计算值Value。根据不同的Operator去解析不同的Value。比如如果需要进行逻辑运算，可以让Value变成内嵌FilterStage的列表
  • 联表LookupStage：可以参照mongodb的lookup实现
    • 需要的基本数据包括：另一条流水线的定义Pipeline、自己流水线和另外流水线的Locator以及运算符Operator、流水线联入到的字段Alias
    • 可以根据lookup的实现自由添加stage的属性，比如保留不匹配的结果：PreserveNullResults
  • 列表展开FlattenStage：展开特定定位符下的列表值，每一行包含列表的一个元素（1->n）的转换
    • 主要用于最终人性化地在表格中展示数据
  • 字段遴选PickStage：只保留特定的字段值到下一轮，通常用于内存优化

每个stage都需要实现前面说的数据接口process。如果有特殊的数据检查需求，可以根据这些基础stage的定义方式去自创一些，实测用python创建一个新的stage，可能不需要100行。

有了这些基础，就能够以配置化的方式去实现数据检查规则，最终达到表格数据检查的目的

Excel-Diff

diff算法

excel-diff的算法有非常多，但是如何体现策划表与策划工作的特性，这才是最需要注意的。许多项目的策划表都通过SVN进行存储，因此从SVN的commit信息中就可以知道哪些策划表发生了变更/增加，因此，我们只需关心每个excel文件如何进行diff运算就可以了。

策划的excel配表有如下的特点：

  • 每个sheet有表头header，一般为行表头header
  • header一般定下后不会再变化
  • 不同的sheet，表结构基本不同
  • row不一定有主键，甚至同sheet有重复的id
  • 每次变更时，变更的行数相对不变的行数较少
  • 可能存在将某些行移动到其它位置的情况
  • 策划同学在excel某些空白区域可能会加上注释

因此，我们从sheet的粒度来看，diff模块可以这样设计：

  • 定义表头行index，数据起始行index，数据起始列index
  • 统计增加与去除的表头。如果单纯表头名称改了，下面的数据改动基本没有，也算整个列都改了。这样，列的长度就变得一样了。
  • 对于共有的表头，统计下面的行数据，去除不合法的行。如果行中起始列上没有数据，就算不合法。
  • 通过求行的hash，来获得变更前excel与变更后excel中行的映射，从而知道哪些行没有变动，哪些行变动了。
  • 针对没有变动的行，求变更后excel中这些行的索引的LIS。具体的求法可以参考stackoverflow上的一个帖子，主要思想是用两个数组分别维护长为x的lis的最后一个数的最小索引以及某索引的数作为lis最后一个数时前一个数的索引，然后通过回溯后一个数组，从而获得lis串。通过lis串，我们可以知道哪些行只是单纯地被移动位置，而其中内容并没有被改动过。
  • 针对“变动”的行，也分三种情况：增加行，删减行与修改行。由于每次变更所涉及的行一般不多，因此可以采用o(n方)复杂度的方法每行逐格比较。通过逐格比较，可以求出行间相似度，因此我们可以定义一个相似度阈值来判断两行是否相似。如果某个行跟原来的行的相似度大于阈值，就说明这两行相对应，是一个“修改行”的行为，故我们只需记录其中单元格的变化；如果从变更前的某行找不到相似的变更后的某行，就是一个“删减行”的行为；如果有些变更后的行没有变更前的行对应，就是一个“增加行”的行为。

这个模块详细的代码已经写在了excel_differ.py中，虽然没有过于细致的整理，可能有许多优化空间，但模块已经拆分的足够明确，并且性能表现也足够OK了。如果拿两个文件夹下的excel文件作为对比的话，可以输出一个类似于这样的json报告。因此若要投产，不论是单纯copypaste脚本，还是接到web server，都绰绰有余了。

通过openpyxl展示diff数据

diff数据的展示方面，最好的一个办法就是，通过生成excel文件，在不同的sheet里面来展示。每个包含diff的sheet需要生成1个excel文件，每个文件分成3个sheet，2个sheet用于展示sheet原先的数据与修改后的数据，1个sheet用于描述性统计和超链接每个diff的情况。要生成这样的excel文件，不用专门的excel库是不行的。基于python的技术栈，经过一番调研，笔者采用openpyxl作为生成excel的库，并且顺利完成了需求。

学习openpyxl可以直接从官方文档入手。一些基本操作比如，启动一个excel实例，直接构造Workbook实例即可：wb = Workbook

Workbook实例默认会带一个sheet，名称即为Sheet，可以直接通过ws = wb.worksheets[0]获取到这个默认的sheet。如果要改标题，可以直接用ws.title = 'xxx'来执行。

为sheet添加数据有多种方法：

  • ws.append(list_data)，添加一行数据
  • ws.cell(row, col, value)，为某行某列（都从1开始算）的单元格赋一个值

创建sheet，通过wb.create_sheet(title='xxx')接口即可实现；通过ws = wb.active，可以设置某个sheet为默认打开展示的。

获取某行、某列以及单元格数据，可以通过ws.iter_rows(row_idx, row_idx)、ws.iter_cols(col_idx, col_idx)、ws.cell(row_idx, col_idx)获取，其中行、列索引均从1开始算。但如果只是要获取行、列实例，比如要调行列样式的话，需要通过ws.row_dimensions[row_idx]、ws.col_dimensions[get_column_letter(col_idx)]获取，其中get_column_letter是openpyxl.util中，通过列索引获得对应字母（AZ，AAAZ之类）的方法

通过load_workbook、save_workbook接口，可以读取或存储为excel文件。需要注意的是，这两个操作在大数据量表的情况下会有时间开销。

为了凸显diff，需要调整excel表格样式，建议用NamedStyle定义各个不同的样式。一个NamedStyle可以应用单个单元格可支持的所有样式，包括：

  • font：字体
  • fill：背景颜色
  • border：边框
  • alignment：排版（水平垂直居中之类）
  • etc

单元格样式需要遍历每个单元格设置，比如：

ns = NamedStyle(name='test_style')
ns.font = Font(bold=True)  # 粗体
ns.alignment = Alignment(wrapText=True)  # 自动换行
wb.add_named_style(ns)

for row_idx in range(2, 6):
    for col_idx in range(3, 7):
        ws.cell(row_idx, col_idx).style = 'test_style'

行、列样式，通过上述从row_dimensions、col_dimensions获取的实例，可以直接赋予width、height之类的行列样式属性

如果要冻结行列，需要通过设置ws.freeze_panes为特定值，这个值是这样规定的。比如ws.freeze_panes = 'D5'，则表示冻结D列之前的A到C列，同时冻结5行之前的1到4行。以此类推，如果ws.freeze_panes = 'A1'，那就是没有冻结的行列了。

openpyxl内部涵盖的功能非常丰富，笔者所讲述的只是冰山一角。有兴趣的同学可以尽情探索openpyxl的官方文档以及源代码，有问题也可直接google查阅~

通过vxe-table在web端展示diff数据

如果需要在web端展示diff数据，Vue技术栈下的vxe-table表格组件能够支持大量数据的展示，因此可以用vxe-table展示excel-diff的结果。

excel-diff的算法本身，先前的文章已有讲解，在结果展示上会按file->sheet来分。为了让结果展示更加人性化，需要对表格的样式进行区分。在vxe-table的api列表中，我们可以通过cell-class-name的回调函数指定每个单元格的样式。针对excel-diff的结果可以这样设计样式：

  • 新增行/列：背景浅绿色
  • 删除行/列：背景浅红色
  • 重复行/列：背景浅灰色
  • 移动行/列：背景浅橙色
  • 修改单元格：背景浅黄色，字体红色

用户在实际查看excel-diff结果时，通常需要自动滚动到对应的位置。vxe-table提供了如下api支持滚动：

  • scrollToRow(row, fieldOrColumn)：滚动到对应的行或列（注意field不是展示出来的表头）
  • scrollToColumn(fieldOrColumn)：滚动到对应的列

获取行、列的实例，可以用这些方法：

  • getRowById(rowid)：根据行的唯一主键（string）获取行
  • getColumns()：获取columns列表
  • getColumnById(colid)：根据列的唯一主键（string）获取列
  • getColumnByField(field)：根据列的字段名获取列

需要注意的是，滚动后，默认滚动到的行会置顶，列会放到最左边，观感不是很好。因此可以做以下的优化：

  • 当处在较为靠左的列，直接滚动到最左边
  • 当处在较为靠右的列，直接滚动到最右边
  • 其他情况下，滚动到前面隔2个的列
  • 滚动到的行也可做类似处理，这样大部分选中数据都会显示在上面靠左的位置，基本满足观感需求

针对大的表，可能会出现性能问题。性能的优化tips有以下几个：

  • 针对excel数据本身，过滤空表头、空行等无效数据，保证只有有效数据参与diff计算
  • 表格需要设定scroll-x与scroll-y虚拟滚动设置，尽量一次性不渲染太多内容
  • 用Object.freeze冻结excel数据、diff结果相关的object，因为这些object本身就应当是immutable的，用Object.freeze可避免vue做底层各属性的getter/setter绑定
  • 对于每个单元格取cell-class-name，也需要预先computed + Object.freeze一个缓存data，使得回调函数判断class-name直接可以在其中取字段来判断，这样逻辑复杂度会小
  • 数据预处理中，如果要用到循环逻辑，可考虑普通的for循环代替forEach
avatar
文章
阅读
粉丝