如何能修改数据库的数据

浏览次数:4472 最新更新:老慢 于 2023-11-24

为什么需要修改数据

数据质量(错误)是难以避免的管理性问题,如果报表发现了错误,一般情况需要通过管理员对系统中的数据进行修正(可能还要追溯错误产生的源头),这样是需要很长时间的,有没有发现错误马上修正的方法呢?这就是“回写”功能的定位了!

简单来说,就是打开报表看到数据问题,可以通过直接修改并保存,完成对数据库中问题的修正,同时可以马上看到修正结果后的报表内容,这个过程见如下示意图:

如果你看懂了这个原理,我们可以简单延展一些应用场景:

1、从技术角度,不仅可以update(修改),还能insert(插入),实现数据的填报;

2、实现数据的一致性,可以对多张报表完成数据修改;

3、数据输入可以通过简单规则或者正则表达式,对数据质量进行控制;

4、如果配合审核流程使用,可以完成简单的预算审批、数据上报等管理流程。

举个简单的例子,产品体验中有一个销售人员基本信息的报表,如果发现信息错误,可以在线修改并保存回数据库中(尝试一下)。

回写数据的示例

因此,修正错误的数据、统一修改数据、填报新的数据,都可以使用这个功能——回写

什么是回写功能

回写是指通过系统提供的回写规则,将当前电子表格报表的数据写入到指定的数据库中。文中提到的回写库、回写表,对应存储数据的目标数据库、数据表。

注意:

1、常用的RDBMS数据库都可以回写数据,例外的只有hbase、impala、sparksql、hive这种大数据生态的数据库。

2、电子表格报表有三种类型,只有分组报表可以设置回写,清单表和融合分析报表是不能设置回写的。

回写实现原理也是通过拼装SQL完成的,其中回写规则设置的主键实质上就是标志位或称为条件(即where部分),同时回写规则设置的字段映射关系就是需要修改的字段(即update/insert部分)。回写里的“主键”,并非数据库的“主键”,不需要唯一性约束,除非你就是按照数据库主键进行回写的!

最核心的回写规则

我们通过回写规则设置电子表格和回写表的关联关系,实现电子表格的数据写入到回写表中。

其中为回写各设置项说明如下:

设置项

功能说明(回写)

名称

设置此回写规则的名称。

禁用

勾选禁用表示禁用此回写规则。

回写库

回写表

数据库

选择用于存储当前报表数据的目标数据库。

选择用于存储当前报表数据的目标数据表。

值的管理和设置




主键

主键实质上就是标志位或称为条件,依据这个条件更新或插入新数据。

设置回写规则时必须添加主键。

1)设置取消主键方式一:

双击主键列对应的单元格,进行设置/取消主键。

2)选中列,在主键列对应的单元格右键菜单中,选择 设置主键/取消主键 

设置为“主键(自增)”的字段,表示该字段的数据在回写时不进行操作,当保存数据时,主键字段会自行+1保存。

主键自增需要在数据库中对应的字段也设置主键自增,当主键为自增时,只能通过右键菜单设置主键。

指回写表中需关联的字段。

点击 倒三角()按钮,下拉列表列出回写表里的所有字段

类型

类型指获取电子表格数据的类型,包括“单元格、参数、值”三种。

值指依据类型的变化而变化,将单元格数据或参数数据或值写入到对应列字段。

· 类型为“单元格”:“值”设置的方式(方式一:双击“值”对应格子,输入其对应的单元格行列号;方式二:选择列,点击 定位 按钮,定位单元格)

· 类型为“参数”:在“值”中选择当前电子表格包含的参数。

· 类型为“值”:在“值”中输入需要写入到对应字段中的内容。

所有列

点击 所有列 按钮,将回写表中所有字段添加进来,然后根据需要修改类型和值。

增加

点击 增加 按钮,依次添加回写表中的字段。

删除

删除选中的字段。

· 方式一:点击 删除 按钮,删除选中的字段。

· 方式二:选中字段,这右键菜单选择 删除

清空

清除所有字段。

· 方式一:点击 清空 按钮,清除所有字段。

· 方式二:选中字段,在右键菜单选择 清空

定位

当类型为“单元格”时,“值”可通过点击 定位 按钮,定位单元格。

单元格统一调整

对类型为“单元格”时,点击 单元格统一调整 按钮,弹出“单元格偏移”窗口,对“值”的单元格行列号进行统一调整。

回写方式设置

回写内容

回写内容分为“回写修改内容”和“回写所有内容”。

· 回写修改内容:回写规则所对应的回写表中,只有当回写表中的数据发生了修改,修改的数据才回写到数据表中。一般适用于回写表和电子表格数据表是不同的。

· 回写所有内容:不管数据是否修改,所有数据都会回写到数据表中。一般适用于回写表和电子表格数据表是相同的。

更新和插入

更新和插入分为“执行更新和插入”、“只执行更新”、“只执行插入”和“强制先执行更新再执行插入”。

· 执行更新和插入:更新和插入数据。

· 只执行更新:只更新数据。

· 只执行插入:只插入新数据。

· 强制先执行更新再执行插入:先更新数据再插入数据。

“强制先执行更新再执行插入”的使用方法:

当电子表格的数据来源表和回写表不是同一个表,但是主键相同,回写设置必须 勾选”强制先执行更新再执行插入”设置项,才能保证回写成功。其处理逻辑为: 回写时 会根据设置的主键,作为Where条件去执行一次更新,将主键相同的数据更新完毕后,如果有数据要进行插入的再执行插入 。

示例1:在回写报表使用Excel批量导入数据时,勾选“执行更新和插入”,如果报表中使用了数据集并在Web端回写修改了报表内容时,执行更新数据操作,其他情况执行插入数据操作。

示例2:在回写报表使用Excel批量导入数据时,遇到主键相同的情况,需要先勾选“强制执行更新再执行插入”,然后更新数据。

回写的后台逻辑

更新和插入

执行更新和插入

① 通过点击“添加行”新增的数据,前端记录为“插入”操作,执行“insert”语句,如下:

注意当回写表为空时(即表中无数据),在报表中仍然会保留一行空白行。这一行视为插入的新行,在其中填入内容不会执行更新操作。

image2021-12-8_15-18-6.png

② 当有数据进行变更,前端记录为“更新”操作,根据其主键的值作为查询条件去执行“update”语句,如下:

只执行更新

前端不管是“插入”还是“更新”操作,都根据其主键的值作为查询条件去执行“update”语句,如下:

只执行插入

前端不管是“插入”还是“更新”操作,都是执行“insert”语句,如下:

强制先执行更新再执行插入

对每一个操作记录,都是默认先设置的主键作为where条件去执行一次更新操作,满足更新的where语句则进行更新,不满足则执行插入操作

简单示例演示,现有回写表 test ,主键为“id”,表结构如下所示:

id
name
sex
1 李华
2 小明
3 小红

在报表前端做以下操作:

  • 点击“添加行”新增数据:id=“4”,name="阿强",sex=“男”;
  • 修改 id = 2 所在行数据的“name“,原始值为“小明”,现修改为“郝明”;
  • 修改 id = 3 所在行数据的“name“,原始值为“小红”,现修改为“涂红”;

点击“保存”后,数据库层面的操作如下:

删除数据

通过点击“删除行”删除的数据,前端记录为“删除”操作,根据其主键的值作为查询条件去执行“delete”语句;

必须设置填报属性

提醒:设置填报属性的单元格,可能会有默认值,要首先确认一下默认值是否符合预期,特别小心看不见的空格(表单类型使用父格会自动加空格)!!

填报属性是必须设置的,其中将控制数据输入的数据质量,同时用于设置单元格是否允许在报表前端修改回写数据或插入单元格。

image2019-5-29 11:14:11.png

其中各设置项说明如下:

设置项

说明

允许修改

表示是否允许用户在前端修改回写报表内容。请根据单元格回写内容的类型选择,有“数字、日期、文本、文件、参数”。

不允许为空

勾选不允许为空表示用户在前端修改报表内容时,不允许输入空值。

编辑器



数字

用户在前端修改报表内容时,在单元格内只能输入“数字”类型,并且我们可以对数字范围进行限制。点击 定义 按钮,弹出“数字”界面

· 允许小数:表示输入值可以是浮点型,默认勾选。

· 最大值最小值:表示对输入数据的范围进行限制。设置后,在前端修改报表数据时,只能输入最大值最小值范围内的值,否则会弹出提示框。

日期

用户在前端修改报表内容时,弹出日期控件 

单行文本

用户在前端修改报表内容时,在单元格只能输入“字符串”类型,并且不能换行,只能用一行文本显示。

多行文本

用户在前端修改报表内容时,在单元格只能输入“字符串”类型,并且支持输入多行文本(按Enter键进行换行)。

参数

用户在前端修改报表内容时,弹出“参数控件”。一般适用于参数备选值不多的情况下,通过参数绑定的修改方式,给用户在前端修改报表内容提供便利。

1)点击 绑定参数 按钮,弹出“绑定参数”界面,选择参数 

2)在前端修改报表内容时,弹出参数控件 

文件

用户在前端修改报表内容时,在单元格上传文件。可上传至“数据库、FTP、SFTP、服务器”,点击 设置 按钮,配置文件的上传信息 

· 上传至“数据库”,点击 设置 按钮,弹出“文件上传设置-数据库”窗口,设置文件大小和文件类型,如图:

· 上传至“FTP/SFTP/服务器”,点击 设置 按钮,弹出“文件上传设置-FTP/SFTP/服务器”窗口,设置服务器、相对路径、文件大小、文件类型和是否同名文件覆盖

在前端修改报表内容时,点击单元格,弹出 请先选择要上传的文件 按钮,点击上传文件 

Excel数据验证(序列)

回写时有些信息是固定的,如性别、学历、婚否等信息,因此不需要每次手动输入,创建下拉菜单,用鼠标点击选择会更方便准确。

该功能的实现需要设置单元格属性为“Excel数据验证(序列)”。

1)Excel用数据验证功能创建下拉菜单 

关于Excel用数据验证功能创建下拉菜单的操作,请参考 https://jingyan.baidu.com/article/ff42efa9f12100c19e220216.html

2)设置该单元格的填报属性为“Excel数据验证(序列)”  

格式(正则表达式)

格式不匹配时提示信息

在V6.2及以上版本中,增加了正则表达式功能,可以输入正则表达式,对输入的内容进行校验。

1)格式(正则表达式):在输入框中输入对应的正则表达式,可以对输入的内容按照正则表达式进行校验。

常用正则表达式如下:

· 邮箱地址正则表达式:^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$

· 身份证正则表达式:^(^[1-9]\d{7}((0\d)|(1[0-2]))(([0|1|2]\d)|3[0-1])\d{3}$)|(^[1-9]\d{5}[1-9]\d{3}((0\d)|(1[0-2]))(([0|1|2]\d)|3[0-1])((\d{4})|\d{3}[Xx])$)$

· 手机号正则表达式: ^1[3,5,8]\d{9}$

· 电话号正则表达式:^(^0\d{2}-?\d{8}$)|(^0\d{3}-?\d{7}$)|(^0\d2 -?\d{8}$)|(^0\d3 -?\d{7}$)$

· QQ号正则表达式:^\d{5,10}$

以上正则表达式,可参考对应文档: http://blog.csdn.net/lun379292733/article/details/8169807 、 http://www.cnblogs.com/ITEagle/archive/2009/12/27/1633086.html 

从网上搜索到的正则表达式,一般前后都有“/”斜杠符号,在写填报属性时,不需要带上斜杠符号。

2)区分大小写:设置用户在前端修改报表内容时,是否区分大小写。

3)格式不匹配时提示信息:在输入框中输入提示信息。当进行回写时,若输入的内容与正则表达式不符,则会以弹窗的形式弹出提示框中的内容,提示用户格式不正确,重新进行回写。

可以插入/删除行

· 勾选可以插入/删除行:表示允许用户在前端插入/删除一行单元格来输入内容。

· 勾除可以插入/删除行:在前端修改报表内容时,其工具栏上的插入行/删除行按钮都置灰。

“可以插入/删除行”设置项用于当前电子表格含有字段,而不是静态电子表格。

只允许修改插入行

表示在前端修改报表内容时,只能修改插入新的一行的数据,原来报表行是无法进行修改的。

清除前后空白字符

去掉字符序列左边和右边的空格或TAB字符。

如果设置填报属性的单元格太多,可以从“填报列表”查看所有填报单元格,并快速进入某个单元格的设置界面!

回写报表的使用

执行回写是指设置报表回写规则和填报属性后,在前端将当前电子表格数据写入到指定的数据库中。

打开电子表格报表,界面如图:

image2019-2-26 16:18:55.png

功能说明如下:

功能

说明

刷新

刷新当前报表数据。

保存

将数据保存到指定的回写库和表中。

添加行

选中主键单元格,点击 添加行 按钮,在下方添加一行,如图:

如果单元格同时存在上父格、左父格,则不可以添加行。

删除行

选中某一主键单元格,点击 删除行 按钮

清空数据修改记录

清空保存前对数据做的修改。

添加到收藏夹

将该电子表格报表添加到收藏夹。

导出

导出该电子表格报表。支持的格式有:HTML、PNG、PDF、Word、Excel。

打印

打印该电子表格报表。

批量导入

在清单式回写报表中,可通过Excel批量导入功能,导入数据量比较大的文件,提高录入数据的效率。

点击 选择文件 按钮,导入XLSX格式文件。

点击 下载当前模板文件 链接,可自动生成当前文件的模板并下载到本地。

批量导入功能的使用必须在“回写规则设置”界面勾选“允许批量导入”。

不能回写的报表

注意:回写功能只支持“分组表”,不支持“Excel融合分析”、“清单表”!

————————————————

立足经典、真Excel设计、社区版永久免费!立即下载使用,开启高效报表开发之旅!

Demo体验

返回顶层