只有小功能没有小需求，6000字详解导入Excel

在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种情况：

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

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

下边会细聊。

（3）字段格式

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

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

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

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

（4）字段长度

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

（5）固定值校验

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

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

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

（6）关联校验

关联校验有2种情况：

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

举个例子：当填写年龄最小值或者最大值时，则必须填写年龄单位；当填写年龄单位时，则必须填写最大值或者最小值。

2、字段跨sheet页校验

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

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

5、其它校验

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

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

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

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

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

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

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

二、导入报错提示

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

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

一般分为2种：

1. 发现错误，立即报错
2. 发现错误，记录错误，最后统一报错

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

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

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

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

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

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

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

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

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

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

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

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

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

方法1：直接文字提示

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

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

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

方法2：导出错误数据

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

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

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

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

三、数据处理

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

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

有2种：

1. 哪条数据校验通过，哪条数据进数据库某条数据通过校验则直接导入进数据库，对于错误的数据，则不进入数据库。
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中的校验。

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

​Read More