当前位置: 首页 > 办公技巧 > 正文

excel办公技巧快速插入(只有小功能没有小需求,6000字详解导入Excel)

  • 叁碗诸角 叁碗诸角
  • 办公技巧
  • 2023-08-25 09:47:27
  • 0

在B端产品中,导入是最常见的功能之一。当我们做项目时,需要涉及到大量的数据,应该如何处理数据?本文介绍了将excel数据导入到数据库中需要注意的点和方法,希望对你有所帮助。


最近在从0到1做一个新的项目,涉及到大量数据的维护,有简单的基础字典数据的维护,也涉及到“严肃数据”的维护,这些数据有个要求:一个都不能错。

我们是第一期版本,本来想直接写SQL更新,但是运维不让搞,也没有足够的时间去开发页面功能,为了快速生产数据,这个时候最好的方式就是“批量导入excel”。把导入的功能搞上线后,坑我踩了不少,同时导入功能也是B端产品中最常见的功能。

咱们今天就说说导入excel~接下来我先不按照导入的流程说,因为导入整体的流程比较简单。大流程就是将Excel数据导入到数据库中。

在细化一点,就是模板制作,用户下载模板,填写完数据后再上传文件。程序开始数据校验,有错误数据用户再修改,直到导入成功进入到数据库。

我就不按照导入的流程说了,直接说我个人感觉比较重要的地方,这些弄清楚那导入就没问题了。

我整理了以下4点,咱们一个个说。


一、导入校验

导入时为了保证数据的准确性,最重要的就是“校验”了。

因为是导入的数据最终是到数据库,如果你对数据库有了解,校验逻辑你肯定会清楚很多。

不懂也没事,咱们先说校验。

校验一般分为以下内容:

我们一个一个的说下:


1、文件校验

(1)导入文件格式

对于excel,常见的格式有xlsx、xls、csv

推荐xlsx格式,它不仅是目前主流的excel格式,而且相同行 列数据时,xlsx格式文件体积较小。

(2)导入文件大小

对于导入文件体积大小的限制,对文件大小添加限制的主要原因是文件过大时,程序处理起来会很费劲,所以可以添加大小限制。

可以根据数据量大小设置,建议最大值在5M。

当然也可以对文件内的“行数”添加限制,比如说每次导入最多2000条等。

目的都是为了加个限制。

(3)导入文件名称

校验上传文件的文件名,当上传与要求的文件名称不一致时则报错。

当文件名称没有特殊作用时,不建议对名称进行校验。


2、模板校验

模板里有表头名称、sheet页名称,用于让程序知道需要更新数据库里的哪个表,哪个字段。

表头名称是指导入模板内的列名,当导入到数据库的时候,程序知道哪个列对应数据库里的表字段,当与导入模板要求不同时则报错。

由于一个excel里可以添加多个sheet页,如果你的模板里涉及到多个sheet页,这个时候就需要对sheet页名称进行校验。

另外还有对表头字段名称顺序的校验,我们可以加上字段顺序的校验,当字段列顺序和模板不一样的时候,则提示模板不对。

当然也可以直接按照字段名称去匹配,不管字段顺序。

当模板校验通过后,在进入到下一步的数据校验。


3、文件内数据校验

当模板校验通过后,然后在对Excel内的数据进行校验,这个时候可以对文件内的必填的单元格数据、不可重复的单元格数据进行校验。

这个时候还是对Excel内的数据进行校验,还没有到数据库。

当然有个极端情况,就是模板内数据为空,只有个表头,这个时候不需要处理,直接提示:文件内数据为空,请补充数据后再上传。

接下来就是对字段进行其他校验,具体的校验咱们在下边一起说。


4、字段校验

(1)字段是否必填

如果字段是必填值,导入的excel里单元格为空,所以数据就是错误的,则需要进行报错提示。

(2)字段是否唯一

对于一些字段,我们要求是唯一的,但是会存在2种情况:


文件内的数据有重复值文件内的数据和数据库里已有的值存在重复

对于这2种情况,都是由于数据有误引起的,我们可以制定规则:按照最新的唯一值进行更新数据,或者是直接提示报错,

下边会细聊。

(3)字段格式

如数字格式、日期格式、时间格式、字符串格式。如果这列字段和数据库表字段的格式对不上,也导不进去。

还有就是数字的大小,比如只能输入个位数,导入时填写了100,则这个数据就有问题,就需要报错。

还有小数点位数、手机号、身份证号、税号等基础格式的校验。

这个校验需要针对每个字段进行校验逻辑说明。

(4)字段长度

比如说最大长度是250个字符,但是excel里的字段长度写了500字符,那就导不进去了,得提示报错了。

(5)固定值校验

如果某个字段需要填写的固定值。

比如说单元格内只能填写是或者否,填写其他内容时,则无法导入数据库。

对于要填写固定值的单元格,我们可以在excel模板里添加下拉框,让用户直接选择,而不用再去输入。

(6)关联校验

关联校验有2种情况:

1、当填写某个字段后,另外一个字段则必须填写。

举个例子:当填写年龄最小值或者最大值时,则必须填写年龄单位;当填写年龄单位时,则必须填写最大值或者最小值。

2、字段跨sheet页校验

对于模板里有多个sheet页时,且sheet页之间存在数据关系时,这个时候需要说清楚sheet页之间的校验。

举个例子:“说明书药品基本信息”中的商品编码与“说明书用法用量”中的“商品编码”需要对应上,当这两个sheet页里的商品编码对不上的时候,就是错误数据,这个时候就要报错。


5、其它校验

整行数据重复校验:如果一行数据相同,我们就可以认为是数据存在重复值,这个时候可以进行报错提示。

多个字段联合重复校验:多个字段合并在一起联合作为唯一值,则存在重复时则可以进行提示。

根据填写的字段做更细的校验:

比如填写是数字,则需要考虑数字的大小,数字的小数点位数。

如果填写的是手机号,则需要考虑手机号格式的校验。

当以上校验通过后,接下来程序就需要将数据导入到数据库了。

我们回顾以上的校验逻辑,可以发现,这些校验和“表单录入”功能需要考虑到的校验基本相似。


二、导入报错提示

在导入时,一定会存在填写内容不对的时候,这个时候就需要进行提示出问题数据了。

首先我们先确定错误提示的时机,就是“什么时候提示”。

一般分为2种:

因为报错的时机是跟着校验走的,我们需要根据错误类型进行判断:


1、当对文件校验不通过时,需要立即提示

因为文件校验不通过,压根无法处理数据,这个时候直接报错提示。如文件类型不对、文件大小过大、文件名称不对等等。

对于文件类型的限制,我们可以在选择文件的时候,限制选择的类型,在选择文件的弹窗内,仅展示支持的文件类型,过滤掉其它类型的文件。

对于文件体积大小的校验,可以在选择文件后,由前端进行校验,直接进行提示。

对于文件名称的校验,我建议不校验,如果非要校验,可以在上传后由前端或者后端进行校验,出现错误后直接报错提示。


2、当导入模板不对时,则立即提示

模板不对没有办法进行下一步字段校验,这个时候需要报错。

如果模板错误,我们需要将全部错误的表头一起报错出来,而不是发现一个错误表头后就立刻报错。

报错的方式,可以直接展示出错误的表头名称,并提示出正确的表头名称。


3、字段校验不通过,则统一报错

当导入模板正确,这时系统会对填写的每个字段数据进行校验,这种时候出现的问题,我认为可以在最后进行统一提示。

对于字段校验时的错误数据,有几种方式我们可以参考:

方法1:直接文字提示

说清楚sheet页名称 第几行 第几列 错误信息 正确信息。让用户线下修改,重新导入。

对于错误的信息,需要制定个排序方式,有条理的展示出错误数据,可以按照sheet页顺序 行数顺序 列顺序依次展示。

不过在提醒第几行时,有个小细节,这个第几行要和excel文件内的行数对应,要提醒研发注意。

方法2:导出错误数据

功能上支持导出错误数据,在excel文件内加一列【错误信息】列,展示出每行的问题数据,让用户导出错误数据进行线下修改。

这种比较适合导入字段内容较多,数据量较大的情况。

方法3:在线修改错误数据

展示出报错数据,并提供功能让用户自己在线更改。这种方式比较适合excel内字段内容较少,校验逻辑较少的情况。


三、数据处理

当导入数据后,程序会一边跑数据,一边校验。这个时候还有几个点需要注意。

什么样的数据才能进数据库?

有2种:


哪条数据校验通过,哪条数据进数据库某条数据通过校验则直接导入进数据库,对于错误的数据,则不进入数据库。全部数据校验通过后,全部导入进数据库意思就是excel里的全部数据都通过校验后,将excel内的数据全部导入进数据库;当存在任何一条数据是错误的,则整个excel内的数据都无法导入进数据库。

重复数据的处理方式?

在导入时,当唯一值存在重复时,这个重复有2种情况:一个是在excel文件内有重复,一个是excel文件内唯一值与数据库已有的数据存在重复值。对于重复值有以下处理方式:


报错,把重复值作为错误数据提示报错,让用户线下处理。这种方式比较严谨,我倒是比较推荐这种方式。更新数据不进行报错,直接按照唯一值更新数据。对于经常进行变更的数据我们可以采用这种方式。跳过数据,不进行处理当唯一值已存在时,则跳过数据,不更新数据。当已存在的唯一值无法进行更新,或者更新后影响其他数据时,则建议使用这种方式。当然,如果区分不了什么时候更新、什么时候跳过时,可以提供功能让用户选择处理。

过滤不必要的数据:

(1)过滤空格

填写在单元格里的数据当有空格时,如果没有单独要求,则可以让程序过滤掉空格。

如果空格没过滤,导入到数据库后则会形成脏数据。

如果用户填写的数据就有空格时,这个时候可以考虑清空单元格数据内的前后空格。

(2)过滤全行空白数据

当excel中的数据整行数据全部为空时,可以将空白行以及之后的行数据都不做处理。

如果我们在excel里使用了下拉框等方式,虽然没有填写值,但是程序在处理的时候会认为里边有值。

我们可以设置个规则:当一行数据全部为空时,则认为此行以及之后行都无数据。


四、导入模板制作

对于导入模板,我们需要说清楚模板怎么填写,需要把上边提到的字段校验说清楚,同时结合excel的功能制作模板。


1、制定好模板

定义好需要填写字段的表头名称,名称要和功能上的名称一致。

提供下载模板功能,让用户下载模板使用。

对于1对1的关系,这个就很简单,每列对应数据库表中的一个字段,定义好模板中的每个表头列名。

当有1对多的关系时,我们可以设计2种方式。

①一个单元格填写多个值

在单元格内填写多个值,通过一个固定的分隔符号,比如用中文逗号,中文顿号、空格等固定值,让程序知道该怎么分隔。

②分成多行填写

将1对多的关系,按照多行填写。

我们可以根据实际填写的场景设置,把规则说清楚即可。


2、制定好模板字段的填写逻辑

说明每个字段的填写要求,哪些字段是必填、哪些字段不能重复等等,就是要说清楚字段该怎么填才能导进去。

如果字段过多时,可以拉个excel表,单独说明校验逻辑提供给研发。

示例如下:


3、说清楚“如何填写模板”

对于导入模板的填写我们可以使用以下方式:

①新增一个sheet页写模板说明

②在模板前几行填写模板说明

③在单元格添加批注的方式填写说明

④填写示例数据

新增一行示例数据,让用户知道该怎么填写。

以上的方式我们也可以结合在一起,同时提示用户该怎么填。


4、结合excel里的功能设计模板

① 如果填写的数据是固定值,我们可以使用下拉框,直接让用户下拉选择。

如果字典值过多的时候,可以新增一个sheet页,通过设置下拉框取值范围即可。

②在模板内的添加基础校验

使用excel里的“有效性”,添加基础的校验。


五、其它注意点

1、每次导入文件的数量

在开始导入前,首先要看每次支持导入的文件数量。一般每次导入1个文件。当然也能一次导入多个文件,直接每次选择多个文件即可。当同时导入多个文件时,需要注意每个文件直接是否有关系,每个文件的处理是否有处理顺序。


2、同步还是异步处理

同步处理就是导入后,直接处理,页面处于加载状态,后台进行导入处理,此时用户不能进行其它操作,只能当处理完成后,才能进行其他操作。对于数据量较小,处理时间在用户可接受时间内,比如说10秒内,可以采用同步处理的方式。我和研发沟通后,同步处理起来比较方便,我们采用的都是同步处理方式。

异步处理,就是上传文件后,在后台进行处理,用户可以进行其他操作。当后台处理完成后,在提示用户处理结果。对于数据量较大,处理时间较长时,可以采用异步处理的方式。


3、导入文件交互方式的选择

导入文件的交互方式要看采用哪种数据处理方式,才能设计对应的页面交互。

我就不一个个说了,给大家举个例子:

最简单的,每次导入1个文件 出现一个错误数据则无法导入 同步处理。

下载模板:列表里有个下载模板的入口

选择文件:点击导入,直接弹出文件选择框,每次只能选择1个文件,上传文件就开始进行导入。

导入后,程序同步处理,添加个加载状态,当有错误数据时,则报错提示。

导入成功后,加个“导入成功”的提示。

其它的交互方式大家可以搜搜看,有很多。


4、添加日志记录

由于我们对数据要求很严格,所以我们添加了日志记录,用于数据追查。


5、保证功能的连续性

在我们系统中,数据导入后,需要将导入的数据提交给审核人员进行审核,所以我们在导入成功后,添加了“提交审核”的功能,用户可以直接提交数据去审核。

为了保证流程的流畅,我们可以在导入完成后,提供接下来的功能操作入口。


总结

说了很多,导入excel是常见的基础功能,但是涉及到的内容很多,是个细活,从模板制作到校验逻辑,到报错提示,直到数据导入成功。

能把导入excel搞清楚,那我们弄其它数据相关的需求也就so easy 了。

咱们再回头看,导入excel其实就是导入到数据库。

当你对数据库有了解,你就会清晰很多,数据库有字段格式、长度、是否为空,外键等要求,这些不就是excel中的校验。

所以还是推荐大家了解数据库的知识来丰富自己~

专栏作家

王大鹿,公众号:产品大鹿,人人都是产品经理专栏作家。关注医疗领域,擅长原型设计、需求分析和方案设计,分享能落地的工作技能~

本文原创发布于人人都是产品经理,未经许可,禁止转载

题图来自 Unsplash,基于 CC0 协议

该文观点仅代表作者本人,人人都是产品经理平台仅提供信息存储空间服务


最新文章