程序员必须知道的几个Excel技巧——批量生成sql脚本
说明
有时候我们在维护数据库时，需要编写SQL脚本批量导入数据库（尤其是在项目上线初期），比如业务给了一个Excel文档（成千上万条数据的那种），要你导入数据库，当然你可以通过写代码来读取Excel文档进行导入，不过这种需求可能是一次性的，用一次后面就不用了，写代码成本太高。因此直接通过Excel函数来生成脚本更省事。
几个常用的Excel函数
-
IF()函数
该函数使用方式和MySQL中的if()函数一样，可以嵌套使用。
IF(<条件表达式>, <满足条件后的返回值>, <不满足条件的返回值>) -
SUBSTITUTE()函数
字符串替换函数，返回替换后的值。
SUBSTITUTE(<原始字符串>,<被替换的字符>,<替换后的字符>) -
CLEAN()函数
清空不可见字符。
如回车、换行、TAB键、不可见的双引号等。
由于Excel复制单元格时，如果单元格内容是字符类型，则会复制后再粘贴到文本文档中会自动添加双引号，可以通过这个函数去掉双引号。 -
TRIM()函数
去空格函数 -
几个特殊符号
&字符串拼接符号，如：A1&“hello你好”, A1是一个单元格引用。$绝对定位符号，$A1,$A$1。 使用绝对定位符后，在做Excel拖拉复制操作时单元格的引用不会随位置变化而变化。
批量生成SQL脚本
- 先写一个sql示例模板，这里的 @field_name 、@field_value 、@pkg_id 、@seq 是占位符，需要替换的。
update t_test
set @field_name='@field_value'
WHERE package_id=@pkg_id and sequence = @seq
;
- 准备如下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 ;
-
要批量生成后面的sql，只需选中需要复制的区域，然后将表格往下拉去自动填充即可。
如果有成千上万行数据你可不能这么往下拉，那要拉到何年何月去，你可以用快捷键：
（1）首先选中要填充的区域：如用选中E3单元格，然后按住shift键不松开，再点击J100，最后松开shift键，这样从E3到J100对角线的整个方形区域就被选中了（或者直接按ctrl + shift + 下箭头从当前选择的区域到Excel最后一行全部选中）。
（2）然后自动填充所选区域：ctrl + d。
如果是Mac，将ctrl换成command即可 -
最后将【最终结果】这一列全部复制，粘贴到文本格式中即可
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&";"
真实的业务数据格式远比这个示例要复杂，但是你可以通过这种方法进行扩展基本都能实现，实在不行就只能使出程序员的杀手锏了——show me the code。