posts - 930,  comments - 588,  views - 402万
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

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

excel1

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

excel2

  接着我们创建一个宏,在编辑器中输入(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
出处:http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog

posted on   PetterLiu  阅读(3741)  评论(0编辑  收藏  举报
编辑推荐:
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
阅读排行:
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· 【.NET】调用本地 Deepseek 模型
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
历史上的今天:
2009-05-16 C#3.0实现延迟赋值
点击右上角即可分享
微信分享提示