有时我们需要从EXCEL文档中把数据导入到数据库，这时我们可以使用Excel的宏功能。假设如下图这样的DEMO数据:


然后通过“视图”找到如下选项：


接着我们创建一个宏，在编辑器中输入(Vbscript):


Sub CreateInsertScript()
Dim Row As Integer
Dim Col As Integer
'To store all the columns available in the current active sheet
Dim ColNames(100) As String
Col = 1
Row = 1
Dim ColCount As Integer
ColCount = 0
'Get Columns from the sheet
Do Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.
ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"
ColCount = ColCount + 1
Col = Col + 1
Loop
ColCount = ColCount - 1
'Inputs for the starting and ending point for the rows
Row = InputBox("Give the starting Row No.")
Dim MaxRow As Integer
MaxRow = InputBox("Give the Maximum Row No.")
'File to save the generated insert statements
File = "c:\\InsertCode.txt"
fHandle = FreeFile()
Open File For Output As fHandle
Dim CellColCount As Integer
Dim StringStore As String 'Temporary variable to store partial statement
Do While Row <= MaxRow
StringStore = ""
CellColCount = 0
'ActiveSheet.Name will give the current active sheet name
'this can be treated as table name in the database
StringStore = StringStore + "insert into [" + ActiveSheet.Name + "] ( "
Do While CellColCount <= ColCount
StringStore = StringStore + ColNames(CellColCount)
'To avoid "," after last column
If CellColCount <> ColCount Then
StringStore = StringStore + " , "
End If
CellColCount = CellColCount + 1
Loop
'Here it will print "insert into [TableName] ( [Col1] , [Col2] , ..."
Print #fHandle, StringStore + " ) "
'For printing the values for the above columns
StringStore = " values( "
CellColCount = 0
Do While CellColCount <= ColCount
StringStore = StringStore + " '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"
If CellColCount <> ColCount Then
StringStore = StringStore + ", "
End If
CellColCount = CellColCount + 1
Loop
'Here it will print "values( 'value1', 'value2', ..."
Print #fHandle, StringStore + ");"
Print #fHandle, " "
Row = Row + 1
Loop
Close #fHandle
MsgBox ("Successfully Done")
End Sub
接着点击运行，好了弹出两个对话框，输入起始行2，结束行5,确定后在生成一个文本文件，这些参数你可以修改的 c:\\InsertCode.txt
内容是我们最终想要的T-SQL:


insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
values(  'Peter',  '23',  '2009-01-01',  '3003.5');
insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
values(  'Lucy',  '21',  '2003-10-01',  '2087.65');
insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
values(  'Max',  '29',  '2011-01-01',  '1989.11');
insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] )
values(  'Eric',  '35',  '1999-05-01',  '5043.2');


很简单，您可以自己动手试一下。

希望对您开发有帮助。



作者：Petter Liu

本文版权归作者，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

该文章也同时发布在我的独立博客中-Petter Liu Blog。