背景:
最近给客户做了一个小工具,主要是为了减轻客户那边的工作压力,一般公司都有很多自己的业务数据需要定期发给自己的员工,比如各种指标数据,绩效评比等, 大多都是按公司的组织结构来,一级一级往下发,领导接收的数据一般会包含自己下一级人员的数据,所以客户这边也不例外,每次到了一定的周期(每周,每月或者每季度),就会由专业人员将一份完整的数据分别按组织结构一级一级往下发,全体领导基本都需要做这件事。
于时,我们需要给领导做一个自动分发数据的工具,将这些数据按一定要求自动的发送到各自人的邮箱中,需求给简单,就是发邮件,从程序功能上讲也就分三部分: 1:解析数据源并加载到数据库中 2:提取数据并发送邮件 3:将发送结果反馈给指定的人 之前我们一致认为数据源是稳定的,数据源格式是excel,里面的sheet名称及数量是固定的,字段也是固定的(字段数量以及名称),所以我们有一种非常简单的做法,就是将excel的每个sheet映射成数据库中的一张表,表字段和excel字段一一对应即可,最后根据不同的业务数据需求,构建不同的邮件模块,最后实现邮件发送。 问题: 但有一天,我们的客户负责人发生了变更,他提出了一个问题:如果以后发送的数据发生变化,你们的软件能支持吗,比如今年我们的excel发送的是10个指标数据,明年如果想再增加5个或者重新替换原来的指标。 由于我们对此工具的定位非常低,及不用花太大代价在小工具上,尽早用上尽早减轻客户工作量,同时也会降低开发成本,当时我们理解为数据源不会轻易发生变化,所以设计了上面的解决方案,及不支持数据的动态变更。但新的负责人提出了我们的方案不够灵活,可扩展性,可配置性稍弱,为此我们不得不重新设计方案以支持指标数据的动态变化需求,哎,怎么说呢,如果我们给客户想的多想的全,他们也有可能说,就一小工具不用这么复杂吧,总之既然问题提出我们就需要想办法及解决呀。 习惯性思维产生的解决方案: 动态构建数据表,及首先解析每个sheet有多少列,然后将这些列信息映射为数据库表,这个方案有如下缺点: 缺点一:sheet的列名需要特殊处理才行,我们需要在前面增加 []标识,以防止字符不符合表字段要求; 缺点二:理论上数据库表的列最大数没有excel的大,即在某种极端的情况下,无法容纳所有excel列数据; 缺点三:在数据库查询上不方便,因为字段都是未知的; 缺点四:在绑定email模板(email模板是用xlst来做的)上也是个问题,同样也是字段都是未知的; 缺点五:每次导入数据都需要全部删除数据库中的表,然后重建,这会带来一些不可控的因素。 我同事最终设计了一套表结构,能够非常完善的解决我们所遇到的问题,且结构非常简单。 完美解决方案: 将每个sheet的数据分解为三部分: 1:列信息表Column,主要记录列名称 2:行信息表Row,主要记录行号 3:数值信息Data,以列坐标以及行坐标能够在sheet上唯一确定一个数据,这里记录数据信息。数据库表结构图如下:
方案分析: 1:将sheet中的所以列信息分别插入到Column表中,多少列就插入多少行记录,这样就能够无限支持列数据的增加,而不会受到表字段数量的限制。 2:将行信息,对excel左侧对应的行号数据插入到Row中,有多少行就插入多少。 3:最后是数据的存储,主要是利用二维坐标定位逻辑来确定数据。
4:尽管看起来一个sheet的数据分成三个表来存储,但每个表存储的内容都相当简单,逻辑清晰,所以不会带来更大的复杂性问题。
如何将数据库中的数据加载到内存中,我这里构建一个对象,此对象代表excel中的一行数据,如果是sheet所有数据可以用List<SheetData>来表示。ColumnInfo中的Key就是列名称,Value就是某一列的某一行的数据。按照我们的表结构,构建下面的对象模型也是比较容易的。
如何绑定Email模板,既然我们有了明确的对象结构,那么应用xsl语法就更加容易了: