SqlCelServer

SqlCelServer是一个前后端分离的数据分析系统。后端是一个war包部署在tomcat服务器上,前端是Excel/WPS插件。前后端通过查询接口衔接。

SqlCel的其它子产品都是通过数据库连接信息直连数据库,唯有SqlCelServer通过接口查询数据库。通过接口查询的目的是为了数据安全。因为在多数情况下服务器的数据库连接信息不会对外开放。

SqlCelServer的核心功能包含两个方面:数据查询和报表开发。它的优势在于可以快速搭建起这样的一个数据分析系统,并让远程服务器和本地Excel/WPS紧密衔接起来。

接下来详细介绍SqlCelServer的使用方法。

搭建SqlCelServer服务器

首先需明确一点,SqlCelServer服务器后端仅支持MySQL(MariaDB)数据库。

搭建SqlCelServer服务器的所有操作都在服务器后端由管理员完成。

首先新建一个数据库,名称可自定义。用于给管理员控制用户权限及配置系统信息。此处假定该数据库名称为ADDB。

  1. 在ADDB中新建用户权限控制表

代码如下:

CREATE TABLE `userpriv` (
  `clientUserName` varchar(32) NOT NULL COMMENT '客户端用户名',
  `clientPassWord` varbinary(255) DEFAULT NULL COMMENT '客户端密码',
  `TB1` varchar(64) DEFAULT NULL COMMENT '模板1',
  `TB2` varchar(64) DEFAULT NULL COMMENT '模板2',
  `TB3` varchar(64) DEFAULT NULL COMMENT '模板3',
  `TB4` varchar(64) DEFAULT NULL COMMENT '模板4',
  `TB5` varchar(64) DEFAULT NULL COMMENT '模板5',
  `TB6` varchar(64) DEFAULT NULL COMMENT '模板6',
  `TB7` varchar(64) DEFAULT NULL COMMENT '模板7',
  `TB8` varchar(64) DEFAULT NULL COMMENT '模板8',
  `TB9` varchar(64) DEFAULT NULL COMMENT '模板9',
  `TB10` varchar(64) DEFAULT NULL COMMENT '模板10'
) 

clientUserName:客户端用户名;

clientPassWord:客户端密码;

TB1-TB10:给用户授权的二维报表名称,在后面的二维报表制作中再详细讲解。

  1. 在ADDB中新建系统解锁密码表
CREATE TABLE `passkey` (
  `passkey` varchar(64) DEFAULT NULL COMMENT '仅管理员权限,用于加密或者解密userpriv中的字段clientPassWord的密码,一经设定不便修改'
) 

执行以上语句在ADDB中创建表passkey。

  1. 向表passkey中插入一行记录作为密钥用来解锁整个系统
insert into passkey(passkey) value('password');

password可以自己设置

  1. 向用户权限控制表(userpriv)中插入记录为用户授权

举例如下:

insert into userpriv (clientUserName, clientPassWord) 
value ('userName',AES_ENCRYPT('userNamepass','password'));

以上语句表示向userpriv表中插入一个用户,用户名为userName,密码为userNamepass,同时让密码在数据库中以加密的形式储存,使用的加密密码"password"同表passkey中设置的密码。

  1. 连接数据库

进入sqlcelserver安装根目录下的WEB-INF/classes文件夹,打开struts.properties并配置数据库连接信息

配置上面一个数据库连接(Database connection for controlling users' privileges),连接到用户权限控制数据库ADDB。

配置下面一个数据库连接(Database connection for users),连接到用户查询的数据库。

  1. 测试服务器是否搭建成功

打开Excel(WPS)加载SqlCelServer,点击“登录服务”按钮进入登录面板。勾选右下角的“远程服务”复选框,录入sqlcelserver服务器的根目录地址,比如:

https://sqlcel.com/sqlcelserver/

在浏览器中浏览以上服务器网址,如果页面可以正常返回"Hello SqlCel!"说明地址有效。

录入sqlcelserver的服务器地址后关闭登录面板并重新打开,录入权限控制表中的用户名和密码如果登录成功说明控制用户权限的数据库连接信息可正常可用。

点击“SQL查询”按钮进入SQL编辑器界面,录入一个SQL语句如果可以正常执行,说明供用户查询的数据库连接信息正常可用。

  1. 删除系统解锁密码

这一步可以做也可以不做,当登录服务器成功后,passkey表中的密码就不需要了。为了防止别人看到这个密码,可以把它记住并删掉。

需注意的是,解锁密码删掉后如果重启sqlcelserver服务还需要把这个密码重新录入passkey表中让服务器读取。

至此“SQL查询”的配置已经完成。如果还有二维报表开发需求可继续后续配置。

  1. 在权限控制数据库(ADDB)中创建模板配置表——tablesetups,并配置报表信息
CREATE TABLE `tablesetups` (
  `ExcuteOrder` int(11) NOT NULL COMMENT '制表的执行顺序',
  `TableName` varchar(64) NOT NULL COMMENT '制作的表名',
  `SQLQuery` varchar(2048) NOT NULL COMMENT '执行的SQL语句',
  `Comment` varchar(512) DEFAULT NULL COMMENT '注释'
)

字段解释:

ExcuteOrder:对于一个报表来说可能需要执行多个SQL语句,该字段用于控制多个SQL语句的执行顺序。

TableName:报表的名称。

前端会将当前活动工作表的名称(TableName)传送给服务器后端,后端接收到表名后会判断这个用户是否具有这张报表的制作权限。如果有权限则会根据表名到模板配置表(tablesetups)中寻找该表的配置信息。

一张报表中可能会有多条配置信息。根据表名查出所有的配置信息按照先后顺序(ExcuteOrder)逐一执行即可生成所需报表。举例如下:

tablesetups

增加多个不同的表名可配置多张报表。

SQLQuery:SQL查询语句。具体可参考SQL语句

以上链接中只需参考其中的 “指定数据去向(无法指定数据来源)”,“引用单元格”和“单元格操作”三个部分。

Comment:对该配置的注释。

  1. 在用户权限控制表(userpriv)中将报表权限授权给用户

在用户权限控制表(userpriv)中找到相应的用户名并将需授权给该用户的报表名称更新到TB1-TB10的任意一个字段中。

如果报表数量超出了10个,可在在用户权限控制表中添加新的字段TB11,TB12...

至此服务器端的配置信息已经完成。

SqlCelServer客户端

SqlCelServer客户端面向终端用户,需先登录后使用。

  1. 登录SqlCelServer

必须登录系统后才能使用工具。点击“登录服务”进入登录面板,如下:

tablesetups

勾选远程服务复选框,在远程url中配置sqlcelserver的远程地址。然后关闭该面板并重新打开,如果可以正常出现验证码说明远程服务有效。

输入账号、密码、验证码登录后即可与服务器建立会话。如果该工具在较长的一段时间内没有访问服务器则会话将终断需重新登录。

  1. SQL查询

该功能用来向远程数据库提交SQL查询语句。SQL编辑器仅可向服务器提交查询语句。不可以提交增删改语句。

具体的SQL语法请参见

SQL编辑器

  1. 自动报表

该功能用于根据服务器配置自动生成报表。

点击“自动报表”按钮后会将当前活动工作表的名称提交给服务器后端,查询并解析该报表相应的配置信息,并根据配置信息从服务器读取数据到当前Excel中。

如果服务器后端需要引用Excel单元格地址作为变量,那么会将相应的引用地址显示在Excel的状态栏,供用户修改配置信息。

对接已有的查询接口

对于已有的查询接口,往往返回的查询结果是一个JSON字符串。此时如果需要用SqlCelServer编辑器对接该接口则需要做一些针对性的处理。

对于互联网企业来说,由于数据量较大,底层的数据库往往是Hive, Hadoop。至于底层是怎样计算的我们不必关心,我们只需关心提供的查询接口。用SqlCelServer对接上这个接口,就可以用SqlCel编辑器在Excel里面查询这个平台里面的数据了。

如果需要对接已有的查询接口,请联系作者协助实现。

下载SqlCelServer