前言

为啥要写这篇文章呢，原因是2022年元旦前一天项目加班，加班到元旦凌晨6点多，才上线完成，打车回家。这期间看着朋友圈到处发的去年总结、今年祝福、周围环境红灯酒绿，再看看当时的我，正在公司加班，埋头、眼花、大脑闷胀、盯着电脑，写着复杂的sql语句，哎，成年人的生活，真是哎。你们会问，为啥会你那么晚呢，我只能说，项目上线，要初始化数据。当时用到了Excel的CONCATENATE函数，其实在前前公司，做用户系统的时候，经常会给产品同学批量插入一些sql语句，这个时候会将产品提供的excel批量数据编写成insert sql语句，转成一个个的sql来执行，如果是几条数据，我们可以直接将数据写成sql语句，但是情况往往是几千或者几万条数据，所以通过手工拼接的方式比较费劲，这个时候就想到了用Excel公式CONCATENATE，不限数据条数，分分钟搞定。对于程序员CONCATENATE函数还是比较实用的，废话到此为止了，下面看看如何将很多条数据拼接成sql语句。

正文

程序猿都知道，我们在拼接insert语句的时候，有几种方式，例如：

一条sql语句

insert into (字段A,字段B) values(插入的值),(插入的值)...

多条sql语句

insert into (字段A,字段B...)values(值,'值'...);
insert into (字段A,字段B...)values(值1,'值1'...);
insert into (字段A,字段B...)values(值2,'值2'...);

本文我用采用一条sql语句将Excel数据转成sql语句，原因是公司有规定，insert语句最多支持1万行，而我要初始化的数据超出了该限制，下面我们一起看看如何操作。

1、我们打开Excel，如果下图所示

 2、在数据列的最后侧，也就是k列，点击K2咱们要把函数写到这个单元格，将该单元格拉长，第一列是标题，咱们不用

3、点击fx，例如下图标示，会弹出第二张图，如果你以前没有使用过CONCATENATE函数，可能不会在选择函数框显示，你在查找函数下面输入CONCATENATE，就能显示

 4、 选择CONCATENATE函数，点击确定

5、字符串1、字符串2等等就是我们拼接的字符串，如果我们要拼接字符串则使用""包围，如果只是单纯的选择某一列则无需""

6、我们拼接一条sql语句，在这里只要拼接values后面字符串即可 ，首先在第一个字符串1中填写

7、第二个字符串就是选择第一列，点击字符串2后面的选择按钮

8、会出现下面的框

 9、选择A2列后，会将A2回写到如下框中，回车后，又回到字符串页面

10、从上面截图中能看到，字符串2对应的是A2

11、字符串3中填写如下图所示，我们第二列是一个字符串，所以要加 ' 号，而值之间要用,号连接，所以出现 ,' 2个符号，如果我们能确定是那一列，其实直接填写对应列即可，无需点击选择列按钮，拼接其他列也是类似。我们实时能够看到，该行拼接的结果，在最下面的计算结果。

12、通过上面的拼接，整体拼接如下

13、点击确定按钮，返回Excel，如下图

 14、那么其余行如何生成呢，我们点击上图的行，将鼠标移动到该单元格的最后，当鼠标由粗+变更成细的+时，双击，那么会自动按照该单元格规则生成其他行。

15、将K列生成的语句复制出来再最前面添加insert into (字段1，字段2..)values，完活。

引子

Excel过滤与查找数据的功能很强，但如果是非常复杂的查询还是用SQL语句来得直接。但这需要有一些数据库的专业知识。

实事上excel支持对Sheet上的数据进行sql查询，这时Sheet名就是要查询的数据表的名字，只不过要加上中括号。

为了方便用sql语句直接操作sheet上的数据，特地用VBA写了一个小程序。

实例操作

程序的文件结构如下：

execlSQL.xls是主程序所在的excel文件，testData.xls是存放测试数据的excel文件。
打开execlSQL.xls，效果如下图所示：

点击左上方的“打开数据源”按钮,选择要做为数据源的excel文件，这里选择包含测试数据的testData.xls.
然后点击右下方的“执行查询“按钮，就可以看到查询的结果，如下图所示：

如果想修改查询所用的sql语句，直接在文本框中修改sql语句即可。

工作原理

在“打开数据源时” 通过如下语句建立了数据源

Set cnn = CreateObject("adodb.connection")          '创建数据库连接cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & srcFileName

在“执行查询”时，通过adodb的connection对象执行了sql查询

 Set rs = CreateObject("adodb.recordset")            '创建一个数据集
 SQL = Trim(txtSql.Text)
 Set rs = cnn.Execute(SQL)                           '执行查询

VBA代码

Option Explicit

Private cnn, rs, SQL$
Private srcFileName As String


'打开数据源xls文档
Private Sub cmdOpen_Click()

    Dim strFileName As Variant
    If getOpenFiles(strFileName) = True Then
        srcFileName = strFileName
    Else
        srcFileName = ""
    End If
    
End Sub


'执行查询
Private Sub cmdExec_Click()

    If srcFileName = "" Then
        MsgBox "没有打开数据源"
        Exit Sub
    End If

    On Error GoTo errHandle
    
    Set cnn = CreateObject("adodb.connection")          '创建数据库连接
    cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & srcFileName

    Set rs = CreateObject("adodb.recordset")            '创建一个数据集
    SQL = Trim(txtSql.Text)
    Set rs = cnn.Execute(SQL)                           '执行查询
    
    Sheet2.Cells.ClearContents
    
    Dim i As Integer
    For i = 1 To rs.Fields.Count
        Sheet2.Cells(1, i) = rs.Fields(i - 1).Name
    Next
    Sheet2.Range("a2").CopyFromRecordset rs
    
    cnn.Close
    Set cnn = Nothing
    
    Sheet2.Activate
    
    Exit Sub
errHandle:
    MsgBox Err.Description
    
End Sub


'取得打开的文件名,如果没有打开文件则返回false
Private Function getOpenFiles(strFileName As Variant) As Boolean

    Dim workPath
    workPath = ThisWorkbook.Path
    ChDrive Split(workPath, ":")(0)
    ChDir workPath
    
    Dim FileNames As Variant
    
    FileNames = Application.GetOpenFilename("Excel文件 (*.xls),*.xls", , "选择要执行查询的源文件", , False)
  
    'VarType(varname)　返回一个 Integer，指出变量的子类型 ,TypeName 返回类型名称
   
    If TypeName(FileNames) = "Boolean" Then
       getOpenFiles = False
    Else
        strFileName = FileNames
        getOpenFiles = True
    End If
    
End Function

完整程序下载

可以直接执行的完整程序请到CSDN资源中下载