excel导入：省市区联动+key-value对应

需求：excel实现下拉框之间级联关系 包括客户类型对应客户证件类型（例如：选择客户为个人则证件类型只能为身份证、通行证护照等，而不能为统一信用代码）、省市区联动；同时由于该excel模板是为了向系统导入数据，因此各枚举值需要将对应的码值传入后台，以免在后台再进行大规模kv对应的操作。

解决思路：

1.下拉框采用excel自带数据验证解决；

2.级联关系采用excel自带数据验证解决；

3.kv对应采用excel中VBA代码+隐藏列的方式解决。

具体解决方案：

1.其中下拉框枚举值设置比较简单，建立一个后期隐藏掉的sheet页，将枚举值放在其中，对需要下拉框的列点击【数据】-【数据验证】-【数据验证】，设置验证条件为“序列”，数据来源用鼠标选择全部枚举值即可。

2.级联关系需要先在【公式】-【名称管理器】中添加全部级联关系，具体添加方法可参考Excel级联操作_迷茫的老年人的博客-CSDN博客_excel 级联  

然后使用数据验证设置与相关单元格的相关关系，设置验证条件为序列，数据来源使用INDIRECT。

3.这个是我做下来花费时间最多的地方，但其实也相当的简单，只是因为不熟悉VBA代码才花了很多时间。我直接把代码贴出来逐行说一下。首先是环境设置，点击【开发工具】-【宏安全性】，信任所有宏，然后点击旁边的Visual Basic，在弹出来的页面中左边，点击需要进行kv对应的sheet页，在上面写相关的代码。

其次需要在之前的下拉框枚举值后附加码值，例子见下图：

Rem 省市区对应--输入后对应到code
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rng As Range
  For Each Rng In Target
   Rem 省
    If Rng.Column = 7 Then
        Rng.Offset(0, 2).ClearContents
        Rng.Offset(0, 1).ClearContents
       Cells(Rng.Row, 108) = "=IF(ISNA(VLOOKUP(G" & Rng.Row & ",Sheet2!$A$2:$B$35,2,FALSE)),"""",VLOOKUP(G" & Rng.Row & ",Sheet2!$A$2:$B$352,2,FALSE))"
    End If
    Rem 市
    If Rng.Column = 8 Then
        Rng.Offset(0, 1).ClearContents
       Cells(Rng.Row, 109) = "=IF(ISNA(VLOOKUP(H" & Rng.Row & ",Sheet2!$D$2:$E$132,2,FALSE)),"""",VLOOKUP(H" & Rng.Row & ",Sheet2!$D$2:$E$132,2,FALSE))"
    End If
    Rem 区
    If Rng.Column = 9 Then
       Cells(Rng.Row, 110) = "=IF(ISNA(VLOOKUP(I" & Rng.Row & ",Sheet2!$G$2:$H$707,2,FALSE)),"""",VLOOKUP(I" & Rng.Row & ",Sheet2!$G$2:$H$707,2,FALSE))"
    End If
  Next
End Sub

整个方法来说大概就是，光标在该sheet进行操作，如操作到方法中约束的单元格，则触发对应方法。个人需要改的代码是每个Rem下面的那几行（Rem为备注）。对应省的代码我用伪代码解释一下。

if 点击到第七列

那么将点击的单元格所在行的 第七列后的第二列 相交叉的单元格 内容清空

将点击的单元格所在行的 第七列后的第一列 相交叉的单元格 内容清空

令 点击的单元格所在行的 第118列的值设为 {

根据第G列和所在行 相交叉的单元格 的内容，在sheet2的A2到B35的区域中寻找，如找到的话则返回找到结果向右一格的数据 如果找不到则返回空空

}

写完后保存。然后就可以生效啦，大概的效果如图：

在导入后台之后，取对应value列的值即可。这些列可以右键点隐藏哈 好看很多~

到此，这个需求就被覆盖掉了。现在准备去优化一下导入性能的问题，如果有收获再来补充~~

——7.7补充

千条数据导入时间六七分钟，十分不友好；通过打印日志计算时间发现开销最大的还是调用接口读取数据的地方。这里考虑用线程池来实现并发处理。明天继续。

——7.20

多线程已经实现，但是由于最近太忙，又开始新项目了一直没上来整理。今天到的早顺便整理一下。

我在这里是使用了线程池，设置同时线程数为5，当数据量为10条以上才走多线程方法。1k条数据导入开销从之前的7min缩减到一分钟以内，具体多少不确定因为晃了一下神就导入完成了…

最近一直在看mySQL加锁和连接池配合多线程的问题。开一篇新文章分享一下吧。