我们在实际的开发过程中,我们需要在excel中操作一些数据,并使其生成insert的sql语句，有很多小伙伴不知道如何进行操作，下面我们就来给大家详细介绍一下如何使用Excel宏将表格的数据转换成insert语句。
编写宏实现数据转换成insert语句
excel 数据样例
打开excel，在视图找到宏，然后进行查看，命名一个名称，进行宏的开发。
完整的开发样例
Sub sheet1_oracle_trans_index()
Dim Fso As Object, sFile As Object
Dim iRow, iPos As Integer, FileName As String, sqlstr As String
Dim sPath As String
Dim three_index_set_codeAs String'3.0指标集
Dim three_indexs_code As String '3.0指标编码
Dim five_indexs_code As String'5.0指标编码
Dim five_indexs_name As String'5.0指标名称
'弹出导出文件名提示框
Set Fso = CreateObject("Scripting.FileSystemObject")
FileName = Application.InputBox("请输入导出文件名：", "输入", "Oracle_trans_index")
'如果选择"否",直接退出
If FileName = "False" Then
Exit Sub
End If
If FileName = "" Then
FileName = "Oracle_trans_index"
End If
sPath = Application.ActiveWorkbook.Path
iPos = InStr(sPath, "\HSPRS3\")
If iPos > 1 Then
sPath = Left(sPath, iPos - 1) + "\HSPRS3\Procedure\Common\Risk"
FileName = sPath + "\" + FileName + ".sql"
Else
FileName = sPath + "\" + FileName + ".sql"
End If
Set sFile = Fso.CreateTextFile(FileName)
sqlstr = "delete from trans_index where three_index_set_codein (select sys_three_index_set_codefrom indx_sysinfo where sys_three_index_set_code='120850');"
sFile.WriteLine (sqlstr)
'扫描行数
iRow = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
For i = 2 To iRow
out1:
three_index_set_code= Trim(Range("A" & i))
three_indexs_code = Trim(Range("B" & i))
five_indexs_code = Trim(Range("C" & i))
five_indexs_name = Trim(Range("D" & i))
'判断空
If three_index_set_code= "" Then
If i < iRow Then
i = i + 1
GoTo out1
Else
GoTo out2
End If
End If
sqlstr = "insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) "
sqlstr = sqlstr + "values("
If three_index_set_code= "" Then
sqlstr = sqlstr + "null,"
Else
sqlstr = sqlstr + "'" + three_index_set_code+ "',"
End If
If three_indexs_code = "" Then
sqlstr = sqlstr + "null,"
Else
sqlstr = sqlstr + "'" + three_indexs_code + "',"
End If
If five_indexs_code = "" Then
sqlstr = sqlstr + "null,"
Else
sqlstr = sqlstr + "'" + five_indexs_code + "',"
End If
If five_indexs_name = "" Then
sqlstr = sqlstr + "null,"
Else
sqlstr = sqlstr + "'" + five_indexs_name + "'"
End If
sqlstr = sqlstr + ");"
sFile.WriteLine (sqlstr)
Next i
out2:
sFile.WriteLine ("commit;")
If MsgBox("文件已导出到目录" + sPath + ",是否打开该文件？", vbYesNo + vbInformation) = vbYes Then
Shell ("NotePad.exe " & FileName)
End If
End Sub
执行结果如下
delete from trans_index where three_index_set_codein (select sys_three_index_set_codefrom indx_sysinfo where sys_three_index_set_code='120850');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0100','D0101','D0101','组合收益率1');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0101','D0102','D0102','组合收益率2');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0102','D0103','D0103','组合收益率3');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0103','D0104','D0104','组合收益率4');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0104','D0105','D0105','组合收益率5');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0105','D0106','D0106','组合收益率6');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0106','D0107','D0107','组合收益率7');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0107','D0108','D0108','组合收益率8');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0108','D0109','D0109','组合收益率9');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0109','D0110','D0110','组合收益率10');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0110','D0111','D0111','组合收益率11');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0111','D0112','D0112','组合收益率12');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0112','D0113','D0113','组合收益率13');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0113','D0114','D0114','组合收益率14');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0114','D0115','D0115','组合收益率15');
insert into trans_index(three_index_set_code , three_indexs_code, five_indexs_code, five_indexs_name) values('D0115','D0116','D0116','组合收益率16');
commit;
已经编写好的excel模板文件 链接：https://pan.baidu.com/s/1AOjeA0_osKAiiIB9xVml_w 提取码：8gtn
