在实施中合理使用Excel导入的技术实现

在实施中合理使用Excel导入的技术实现

                                                                       扶程星云

       笔者多年实施经验中，发现数据初期开账是一件非常头疼的时间段，后期数据维护也是企业信息化人员的头号难题，有时候甚至采取数据库导出后，批量修改后再导入的危险操作方法，笔者最近基于ODOO研究出一套行之有效的Excel导入方法，这里抛砖引玉，期望对一些ODOO爱好者能起到一些作用。

       这里以设备为例，整个设备体系见下图。

 

       系统中有系统分类、设备分类、设备列表、设备明细四个主模型（支持多公司，所以隐含了公司主模型，），还有设备地址和设备参数两个子模型，常规导入方式需要每个模型一个导入模版，当然也可以通过ID的方式来导入，这种方式从程序员角度没有任何问题，但是从企业信息化人员和现场实施人员角度来看，就非常不人性化，他们希望通过设备地址Excel或者设备参数Excel直接打通所有主模型。这样不管是新增还是修改都能一键处理。

笔者分析实际数据结果如下：

1. 公司名称不可能重复。
2. 系统分类名称字不可能重复，比如照明系统、通风系统。当然我们也可以强制设置名字不能重复。我们可以通过公司名称+系统分类名称来确定唯一性。
3. 设备分类名称也不可能重复，比如通风系统里面有排风扇类、新风设备类、空调类灯。我们可以通过公司名称+系统分类名称+设备分类名称来确定唯一性。
4. 设备列表名称就可能重复了，比如不同的品牌的走廊照明灯，名称都叫走廊照明灯，我们在常规导入Excel的时候，系统会随机生成ID，该ID不可预见，导致再下一级的设备明细如果要导入的话，需要先知道设备ID，否则无法一次导入。所以我们需要引进ERP中常见的唯一编码，这个编码如果设置为系统唯一编码的话，编写相对简单，如果多公司的话，需要采取公司名+设备编码来确定唯一性。
5. 设备明细的关键字是公司名和设备明细编码，辅助字段是设备编码，即该设备明细属于哪个设备，通过这个设备就可以知道系统分类、设备分类的信息了。在更新设备明细的参数时，通过公司名+设备明细唯一编码来确定是新增一个设备明细还是更新设备明细的参数。
6. 以设备明细地址为例：设备明细地址通过公司名+设备明细编码+地址编码来确定是新增一个地址还是更新原来地址上的各种参数。

笔者分析难点如下：

      1、模型的字段和Excel中Title如何绑定。

      2、如何判断是否新增。

解决方法见下方：

1、模型的字段和Excel中Title绑定方法。

          1.1 用xlrd打开Excel文件，并传入sheet内容。

          1.2用rows = sheet.row_values(0)的方式获得第一行的内容，即Excel的Title，注意不要让模版有列有相同的文字，免得出错。

          1.3 通过res = self.env[models_name].search([], limit=1)得到一行要导入模型的数据，比如设备明细地址模型，这里需要注意的是，我们要保证数据库中有一条可用的数据。

          1.4 通过 in_fields = res._fields得到所有模型字段相关信息。大家有兴趣的话可以跟踪看下in_fields里面的内容。

          1.5 通过keys = res._fields.keys()得到所有key，如([‘active’,’name’,’code’])

          1.6 我们通过 for key in keys方式得到 in_fields[key].string，然后我们可以利用以上信息在rows里面找到索引。

         1.7 返回 dict_key_name({‘active’:’可用’,’name’:’名字’,’code’:’编码’})和dict_key_index({‘可用’:’-1’,’名称’:6,’编码’:’2’})，一个是字段和string的对照表字典，一个是string和excel中列的位置。

这样，剩下的就很好做了。

 

2、如何判断是否新增。

       这个说起来还是比较简单的，就是通过关键字来判断，比如数据库中公司名称+系统分类和Excel的公司名称+系统分类比较来确定有没有该数据，如果有的话通过字典记录对应的ID，等遍历完毕后，就可以看哪些是有ID的，哪些是没有ID的，没有ID的就新建，有ID的就更新。当然说起来简单，做起来还是比较耗费时间和精力的。

       因为有了对照表，我们可以通过代码灵活处理要导入的数据，做得好的话，只要关键字不变，新增多少字段都不需要改写代码。

       这样做完以后，运维人员只要保证编码的正确性就可以很轻松的导入数据了。

 

关键代码：

# 函数用途：传入模型名，Excel的第一行Title，得到一行记录，然后返回Title对应在Excel中的列数，所以必须要有一行记录，否则会出错。
# 传入值：
#       models_name: 模型名称
#       in_rows: Excel第一行的关键字
# 返回值：
#       dict_key_name:字段名和字段string的字典，例：{'active': '可用', 'name': '空间名称', 'space_code': '空间编码'｝
#       dict_key_index： 字段string和在Excel中的列索引 {'可用': -1, '空间名称': 6, '空间编码': 2, '标识id': 3, '排序': 0, '公司': 1,}
def make_dict_values_index(self, models_name, in_rows):
    res = self.env[models_name].search([], limit=1)
    in_fields = res._fields
    keys = res._fields.keys()

    dict_key_index = dict()
    dict_key_name = dict()

    for key in keys:  # 生成字段对应的索引字典
        key_str = in_fields[key].string
        re = dict_key_name.setdefault(key, key_str)
        default_idx = -1
        if key_str in in_rows:
            default_idx = in_rows.index(key_str)
        if key_str in dict_key_index:
            dict_key_index[key_str] = default_idx
        else:
            re = dict_key_index.setdefault(key_str, default_idx)

    print(f'dict_key_name:{dict_key_name},dict_key_num:{dict_key_index}')
    return dict_key_name, dict_key_index


# 函数用途：传入模型的字段名，返回符合ODOO导入的列，主要是规范导入的数据。
# 传入值：
#       in_sheet: Excel要导入的Sheet
#       in_field_name: 模型编码(例:空间)字段(例:space_code)对应的string，也就是在导出模版中的title
#       in_dict_key_name：字段名和字段string的字典，例：{'active': '可用', 'name': '空间名称', 'space_code': '空间编码'｝
#       in_dict_key_index：字段string和在Excel中的列索引 例：{'可用': -1, '空间名称': 6, '空间编码': 2, '标识id': 3, '排序': 0, '公司': 1,}
# 返回值：
#       idx: in_field_name在Excel中的列索引
#       cols：处理过符合导入用的数据列，比如日期转换、数值转换、布尔型转换等
def get_field_idx(in_sheet, in_field_name, in_dict_key_name, in_dict_key_index):
    if (in_field_name in in_dict_key_name):
        field_string = in_dict_key_name[in_field_name]  # 模型编码(例:空间)字段(例:space_code)对应的string，也就是在导出模版中的title
        idx = in_dict_key_index[field_string]  # 返回在excel中列的序号
        # excel ctype类型: 0:empty, 1:string, 2:number, 3:date, 4:boolean, 5:error

        cols = in_sheet.col_values(idx)  # 获取指定列的所有记录

        icount = 0
        for value in cols:
            ctype = in_sheet.cell(icount, idx).ctype  # 第一行，idx列
            icount += 1
            if value == 0:
                continue
            if (ctype == 2) and (value % 1 == 0.0):  # ctype为2且为浮点
                cols[icount - 1] = int(value)  # 浮点转成整型

            elif ctype == 3:
                # 转成datetime对象
                date = datetime(value)
                cols[icount - 1] = date.strftime('%Y/%d/%m %H:%M:%S')
            elif ctype == 4:
                cols[icount - 1] = True if value == 1 else False

        if len(cols) > 1:  # 删除头
            del cols[0]
        return idx, cols
    else:
        return -1, False