程序员必须知道的几个Excel技巧——批量生成sql脚本

说明

有时候我们在维护数据库时，需要编写SQL脚本批量导入数据库（尤其是在项目上线初期），比如业务给了一个Excel文档（成千上万条数据的那种），要你导入数据库，当然你可以通过写代码来读取Excel文档进行导入，不过这种需求可能是一次性的，用一次后面就不用了，写代码成本太高。因此直接通过Excel函数来生成脚本更省事。

几个常用的Excel函数

1. IF()函数
   该函数使用方式和MySQL中的if()函数一样，可以嵌套使用。
   IF(<条件表达式>, <满足条件后的返回值>, <不满足条件的返回值>)

2. SUBSTITUTE()函数
   字符串替换函数，返回替换后的值。
   SUBSTITUTE(<原始字符串>,<被替换的字符>,<替换后的字符>)
   比如将换行、回车替换为“\n”字符：
   SUBSTITUTE(D6,CHAR(10),"\n")
   SUBSTITUTE(D6,CHAR(13),"\n")
   SUBSTITUTE(SUBSTITUTE(D6,CHAR(10),"\n"),CHAR(13),"\n")

3. CLEAN()函数
   清空不可见字符。
   如回车、换行、TAB键、不可见的双引号等。
   由于Excel复制单元格时，如果单元格内容是字符类型，则会复制后再粘贴到文本文档中会自动添加双引号，可以通过这个函数去掉双引号。

4. TRIM()函数
   去空格函数

5. LOOKUP()函数
   两种用法：

(1) LOOKUP(lookup_value,array)
lookup_value： 要查找的值
array: 查找范围的数组（集合），可以是Excel中的一行或者一列的区域，如：A1:A5 或者 A1:F1，也可以是一组计算公式。
该函数从array中查询lookup_value，如果找到了lookup_value则返回该值，则返回array中小于lookup_value的最大数值，错误值会忽略（比如array的元素可以有计算公式，如果计算公式有错误则忽略该元素）。
例如：LOOKUP(5.2,{4.2,5,7,9,10})=5 由于array中没有5.2，则返回小于5.2的最大值5 。

(2) LOOKUP(lookup_value,array,result_array)
lookup_value： 要查找的值。
array： 查找范围的数组（集合），可以是Excel中的一行或者一列的区域，如：A1:A5 或者 A1:F1，也可以是一组计算公式。
result_array：返回值从这个数组取，返回result_array中索引值与array对应的值，比如根据lookup_value找到array[2]，则返回result_array[2]，数组下标索引值都是2。

注意：
array和lookup_vector的数据必须按升序排列，否则函数LOOKUP不能返回正确的结果。
文本不区分大小写。
如果函数LOOKUP找不到lookup_value，则查找array中小于lookup_value的最大数值。
如果lookup_value小于array中的最小值，函数LOOKUP返回错误值#N/A。

6. 几个特殊符号
   & 字符串拼接符号，如：A1&“hello你好”, A1是一个单元格引用。
   $ 绝对定位符号，$A1,$A$1。 使用绝对定位符后，在做Excel拖拉复制操作时单元格的引用不会随位置变化而变化。

批量生成SQL脚本

1. 先写一个sql示例模板，这里的 @field_name 、@field_value 、@pkg_id 、@seq 是占位符，需要替换的。

update t_test 
set @field_name='@field_value'
WHERE package_id=@pkg_id and sequence = @seq
;

2. 准备如下Excel
   

A1单元格：输入固定值5，用于替换占位符@pkg_id，当然这个值是需要根据具体需求修改的。
B1单元格：输入上面的SQL模板。

我们要处理的数据是从第3行开始：
E3单元格：=IF(CLEAN(TRIM(B3))="模式识别能力","recognition_ability",IF(CLEAN(TRIM(B3))="抽象思维能力","abstract_ability","")) 根据B3单元格的值获取更新的字段名。
F3单元格：=SUBSTITUTE($B$1,"@field_name",E3) B1为sql模板，该函数替换模板中的@field_name为E3中计算出来的字段名，由于B1是固定的因此用绝对引用$B$1。
G3单元格：=SUBSTITUTE(F3,"@field_value",C3) 替换sql模板中的@field_value为C3的值。
H3单元格：=SUBSTITUTE(G3,"@pkg_id",$A$1) 替换sql模板中的@pkg_id为A1的值，由于A1是固定的，因此用绝对引用$A$1。
I3单元格：=SUBSTITUTE(H3,"@seq",D3) 替换sql模板中的@seq为D3的值。
J3单元格：=CLEAN(I3) 清空I3单元格值中的不可见字符得到最终结果。

I3计算后的值如下：

"update course_lesson  
set recognition_ability='excel测试内容001' 
WHERE package_id=5 and `sequence` = 4 
;"

J3计算后的值如下：（已经去掉了回车、最外层的双引号等字符）

update course_lesson  set recognition_ability='excel测试内容001' WHERE package_id=5 and `sequence` = 4 ;

3. 要批量生成后面的sql，只需选中需要复制的区域，然后将表格往下拉去自动填充即可。
   
   如果有成千上万行数据你可不能这么往下拉，那要拉到何年何月去，你可以用快捷键：
   （1）首先选中要填充的区域：如用选中E3单元格，然后按住shift键不松开，再点击J100，最后松开shift键，这样从E3到J100对角线的整个方形区域就被选中了（或者直接按 ctrl + shift + 下箭头 从当前选择的区域到Excel最后一行全部选中）。
   （2）然后自动填充所选区域：ctrl + d 。
   如果是Mac，将ctrl换成command即可

4. 最后将【最终结果】这一列全部复制，粘贴到文本格式中即可

update course_lesson  set recognition_ability='excel测试内容001' WHERE  package_id=5 and `sequence` = 4 ;
update course_lesson  set abstract_ability='excel测试内容002' WHERE package_id=5 and `sequence` = 4 ;
update course_lesson  set abstract_ability='excel测试内容003' WHERE package_id=5 and `sequence` = 5 ;
update course_lesson  set recognition_ability='excel测试内容004' WHERE package_id=5 and `sequence` = 5 ;
update course_lesson  set recognition_ability='excel测试内容006' WHERE package_id=5 and `sequence` = 6 ;
update course_lesson  set recognition_ability='excel测试内容007' WHERE package_id=5 and `sequence` = 7 ;
update course_lesson  set abstract_ability='excel测试内容008' WHERE package_id=5 and `sequence` = 7 ;

当然你也可以不使用sql模板占位符的方式，直接用&符号将sql片段拼接起来，
但是如果sql太长你会头晕的。 拼接示例如下：

="update t_test set "&IF(CLEAN(TRIM(B3))="模式识别能力","recognition_ability",IF(CLEAN(TRIM(B3))="抽象思维能力","abstract_ability",""))&"='"&C3&"' WHERE package_id="&$A$1&" and sequence = "&D3&";"