大数据Excel通过POI导入数据库通用设计方案

1、目录一、总体设计思路- 1 -二、导入规则表设计- 1 -1、数据导入表规则- 1 -2、数据导入列规则- 1 -三、导入规则类设计- 3 -四、界面设计- 4 -1、规则配置界面- 4 -2、导入数据界面- 6 -五、导入逻辑- 7 -六、内部评审补充- 7 -七、设备依赖关系- 8 -设备信息excel导入设计方案一、 总体设计思路考虑到excel导入在项目中应用较多，随后其他项目中都会用到，为了减少重复工作，不必因为excel的样式、数据等变化而重新编码，重复劳动，因此必须抽取通用的东西出来，封装共同点，暴露个性点，使用时只需要关注相关业务，而不必关注excel的读取和存储。考虑到这个情

2、况，可以使用规则配置来解决这个问题。配置规则的时候，需要配置excel要导入的表，字段等信息，导入的时候依据这个规则将数据导入到数据库中。这样一来，在进行excel导入时候只需要为某种数据配置一个规则，然后调用封装的导入类就可以完成整个excel导入的工作了。二、 导入规则表设计1、 数据导入表规则data_import_table_rule字段名字段类型允许null值默认值备注主键idvarchar2(32)否唯一标识，uuid是create_timedate否sysdate创建时间rule_namevarchar2(32)否规则名称end_rowvarchar2(16)否blank_row

3、结束行，空行或第n行header_indexint否1标题行索引data_indexint否2数据行索引table_namevarchar2(32)否表名称delete_repeatint否0是否删除重复行0不删除1删除enableint否1有效标识，删除标识blank1varchar2(256)是扩展字段1blank2varchar2(256)是扩展字段2blank3varchar2(256)是扩展字段3blank4varchar2(256)是扩展字段4blank5varchar2(256)是扩展字段5（1） id：数据表唯一标识，没有业务含义；（2） create_time：创建时间；（3

4、） rule_name：规则名称；（4） end_row：数据读取结束标识，blank_row标识遇到空行结束，数字n表示读取到第n行结束；（5） header_index：excel标题行索引，默认值为1，表示第一行是标题行；（6） data_index：数据开始行索引，默认值为2，表示第2行开始是正式数据；（7） table_name：表名称，表示这个规则是针对这个表的数据导入定义的；（8） delete_repeat：是否删除重复行，即是否导入重复数据，0则不删除，直接导入，1则先删除重复数据，然后再导入；是否重复根据规则表data_import_column_rule的is_uniqu

5、e字段来判定，重复行是指数据库中存在与excel中相同的数据；（9） enable：有效标识；2、 数据导入列规则 data_import _column_rule字段名字段类型允许null值默认值备注主键idvarchar2(32)否唯一标识，uuid是table_rule_idvarchar2(32)否data_import_table_rule表的主键is_pkint否0是否是主键produce_methodvarchar2(32)否生成方法，导入数据/自定义策略/默认值is_uniqueint否0是否唯一，用来判定重复数据column_namevarchar2(32)否列名称heade

6、r_namevarchar2(32)否对应excel列头requiredint否1是否必须填入0不必须1必须data_typevarchar2(16)否string数据类型，string /number /datemax_lengthint否0允许最大存储长度，字节produce_method_valuevarchar2(32)是生成方法值，策略方法名/默认值commentvarchar2(128)是备注is_dictionaryint否0是否属于字典0不属于1属于dic_table_namevarchar2(32)是字典表名称dic_type_columnvarchar2(32)是字典类型字

7、段名dic_type_valuevarchar2(32)是字典类型dic_required_columnvarchar2(32)是字典表最终获取值列的名称dic_reference_columnvarchar2(32)是字典表参考列的名称enableint否1是否有效blank1varchar2(256)是扩展字段1blank2varchar2(256)是扩展字段2blank3varchar2(256)是扩展字段3blank4varchar2(256)是扩展字段4blank5varchar2(256)是扩展字段5（1） id：数据表唯一标识，没有业务含义；（2） table_rule_id：d

8、ata_import_table_rule表的主键，表示这条列规则是属于哪个表的规则；（3） is_pk：是否是主键；（4） produce_method：数据生成策略，导入数据/自定义策略/默认值，三种策略供选择；（5） is_unique：是否唯一，用来判定重复数据；（6） column_name：列名称，对应表中的列名；（7） header_name：excel列标题，与数据表列是对应的；（8） required：是否必须导入，0不必须1必须；（9） data_type：数据类型，数字还是字符，number/string/date；（10） max_length：最大允许长度，不能大于，

9、数据表中该列的长度；（11） produce_method_value：数据生成策略值，导入数据是为空，自定义策略是为策略方法名，或者是默认值；（12） comment：备注；（13） is_dictionary：最终导入数据是否来源于字典，0不1是；（14） dic_table_name：字典数据来源表的名称；（15） dic_type_column：字典类型列的字段名；（16） dic_type_value：该列所属的字典类型值；（17） dic_required_column：字典表最终获取值列的名称，例如：现在需要导入“编码器类型”，excel内容是“飞利浦”，“编码器类型”是一个字典

10、，对应的字典表是“video_coding_type”，“飞利浦”是“video_coding_type_name”列的值，而数据表需要存入的是“video_coding_type_id”列的值，这时“video_coding_type_id”就是dic_required_column，而“video_coding_type_name”就是dic_reference_column，“video_coding_type”是dic_table_name；（18） dic_reference_column：参见第17条说明。（19） enable：该规则是否有效；三、 导入规则类设计class im

11、portresult private int success;/成功数目private int failed;/失败数目 class excel private dataimporttablerule tablerule;public excel(string path)public importresult import()class dataimporttablerule private string id;private date createtime;private string rulename;private string endrow;private int headerinde

12、x;private int dataindex;private string tablename;private boolean deleterepeat;private string exceptcolumns;private set columnrules;public void initcolumnrules()public void addcolumnrule(dataimportcolumnrule columnrule)class dataimportcolumnrule private string id;private string tableruleid;private bo

13、olean isunique;private string columnname;private string headername;private boolean required;private string datatype;private string maxlength;private string defaultvalue;private string comment;private boolean isdictionary;private string dictablename;private string dictypecolumn;private string dictype

14、value; private string dicrequiredcolumn;private string dicreferencecolumn;四、 界面设计1、 规则配置界面取 消保 存是/否21blank_row新建表规则规则名称 例如：视频编码器规则结束行 blank_row表示遇空行结束，也可输入行索引标题行索引 标题通常在第1行，也可输入行索引数据开始行 数据通常从第2行开始，也可输入行索引表名 要导入的数据表是否删除重复行新建删除修改当前位置：数据导入规则表规则规则名称表名创建时间列规则摄像机导入规则添加 / 查看 共23条 共3页 当前第3页 转到第页添加列规则摄像机导入规则

15、是/否是/否string/number是/否保 存取 消所属表规则 readonly列名 对应的columnname是否唯一 用于判定是否重复数据excel标题 对应excel列的标题名称不能为空数据类型默认值 excel对应列为空是填充此数据来源于字典字典表名称 最终数据来源表名数据列名称 最终来源列名字典参照列 参照列名，匹配到数据列2、 导入数据界面是/否取 消下一步下拉列表（规则列表）浏览d:城市监控与报警联网视频编码器.xls导入数据，第1步选择excel文件应用导入规则是否显示导入过程 影响导入速度下载文件模板 视频编码器.xls注：excel文件不能大于20m 进度条忽略第1行，

16、标题行。导入第2行，成功。导入第3行，失败，原因：字典表dic_table符合条件typename=三星的数据不存在。导入第4行，成功。取 消暂 停导入数据，第2步正在导入数据，请稍后继续导入完 成导入数据，结束执行完毕，导入成功32，失败3。详细日志输出在以下路径：http:/localhost:8080/bank/importlog/import_201109231001.log 五、 导入逻辑关于excel操作，设计将采用apache的poi组件，版本采用项目lib中已经包含的版本poi-2.5.1。具体导入逻辑如下：1、 将excel文件上传到web服务器，利用poi加载excel文件

17、。2、 根据用户选择的导入规则，从数据库加载对应的dataimporttablerule对象；3、 根据标题行索引header_index，数据开始行索引data_index，结束行标识end_row，判断该excel是否满足对应规则的导入条件，满足则继续，不满足则给出提示信息。4、 从数据开始行data_index开始，逐行读取excel文件。5、 处理excel第一列，根据excel第一列的标题以及header_name属性，匹配到对应的dataimportcolumnrule对象，如果没有匹配的对象，则抛出异常（未找到与之匹配的列规则），如果该列已经处理过，也抛出异常（excel标题存在

18、重复列），否则继续；6、 读取excel当前列的内容text，如果text是空值，判断required属性是否为true，如果是false，则将defaultvalue属性作为excel内容text直接执行第11条，如果是true则继续；7、 判断is_dictionary属性是否为true，如果为false，则跳过8、9逻辑，直接执行第10条，如果为true，则继续；8、 判断dic_table_name属性、dic_type属性、dic_required_column属性以及dic_reference_column属性，若其中有任意一个为null或空值，则抛出异常（属于字典，却未指定字典表

19、名称dic_table_name以及相关属性dic_type、dic_required_column、dic_reference_column），否则继续；9、 查找字典表，查找语句为”select dic_required_column from dic_table_name where dic_type_column=dic_type_value and dic_ reference_column=text”，若sql语句不能查询到数据，则抛出异常（找不到对应的字典项），若查询到数据，则取第一条数据的dic_required_column值，继续；10、 判断is_unique属性是否为t

20、rue，若为false，执行第11条，若为true，则查找表table_name中column_name值为text的数据是否存在，若不存在则执行第11条，否则继续判断delete_repeat属性是否为true，若为true则删除数据库中该条数据；11、 判断data_type属性，并判断text与该类型是否匹配，若不匹配，则抛出异常（数据类型不符合条件），如果匹配则继续；12、 判断text长度是否超过max_length属性值，若超过，则抛出异常（单元格内容长度太大），否则继续；13、 将text作为column_name列的值，加到准备好的sql语句对应参数位置，等待执行；14、 处理

21、excel第二列，从第5条开始执行循环处理，直到excel列结束；15、 执行或批量执行准备好的sql语句，返回执行结果；16、 处理excel第二行，并判断是否符合end_row的条件，如果是结束行，则整个导入结束，否则从第4条开始执行循环处理，直到end_row结束。六、 内部评审补充1、 主键生成策略。考虑到系统中大多设备的主键都与业务有关联，某个设备的主键可能是由网点、设备类别、设备型号等组合出来的，所以各种设备的主键生成规则可能不同，因此设备导入的时候可以根据设备种类，调用不同的主键生成策略。因此制定导入规则的同时，还要关联相应的主键生成策略。2、 excel文件的空白行、空白列问题

22、。经过测试，poi组件不存在这个问题，例如30行数据，第15行是空白行，poi依然识别为30行，列同样如此。3、 excel标题行有名字重复的问题。名字重复视为同一个属性，直接忽略同时给出警告信息。4、 中止，继续如何实现。采用java的线程实现，wait()实现暂停，notify()实现继续。5、 enable字段引发问题。逻辑删除的数据enable字段是0，当excel中有主键与之重复的数据时，有2中方法：（1）删除掉原来的数据，重新插入；（2）直接在原来的数据上更新。6、 读写文件内存。网上已经查找到poi行级操作的源码，封装一下，可以满足要求。7、 假删除数据的处理，同5；8、 及时删除垃圾文件，释放对象；9、 注意代码结构和实现；10、 重复逻辑的判断，出现重复数据的处理（根据不同数据，使用相应的判断逻辑，主键判断还是联合主键判断。逻