需求

Web在线Excel编辑

实习公司要求实现Excel内容同步到数据库功能，后期需要数据库数据来做BI系统，一开始构思Web的Excel组件来完成

推荐几个挺不错的实现方案：

SpreadJS localhost下完全自由免费，其余域名黑屏
毕升Office 毕升Demo（私人部署) Docker安装，可移植性很强，但是没有很好的数据持久化方案
Microsoft Add-in 微软官方的Web-Office插件，有学习成本
SheetJS 界面美观，全英
腾讯云文档API 审核时间不定，可以参考Tower、Worktile

Excel直接与MSSQL交互

经市调发觉，无论用什么方案都绕不开数据存储方案，进而继续寻到VBA编程，所以便有了思路直接将Excel作为数据源录入的前端、客户端，来解决BI系统的前期数据准备问题，这里的Excel需要的是Office完整版或者WPS Office Pro Plus,WPS没有激活码的同学可以私信我，当然记得点赞~因为接触VBA才一天，可能很多东西理解浅显，轻喷Σ(っ °Д °;)っ
首先已知呢，我们要存数据库需要一张表，如果有四五十个表头的话建表就太难了，所以我们先写一个自动建表语句，进入Excel按ALT+F11
（1）初始化一个InputBox自定义表名，建表方法JugeData()

Dim table_Name$
Sub JugeData()
Dim i%, Cnum%, dt$, arry1() As String, arry2() As String, arry3() As String, cnn As Object, rst As Object

cdt = Format(Date, "MMDD")

table_Name = InputBox("请输入数据库表名", "数据库表名", "excel" & cdt)

Cnum = ActiveSheet.Range("a1").CurrentRegion.Columns.Count
On Error GoTo errmsg

Inputbox.png

（2）定义数据库列字段类型，自定义需要重新写方法~

For i = 1 To Cnum
  ReDim Preserve arry2(i - 1)
  dt = ActiveSheet.Cells(1, i).Value
  dt2 = FindDataType(dt)
  arry2(i - 1) = dt2 & " varchar(800)"
Next

（3）生成建表语句并结束方法，需要在cnn.Open中修改数据库参数，MySQL、Oracle都是可以连接的，修改之后记得把工具里的引用包勾上，我用的是Microsoft ActiveX Data Objects 2.6这个比较稳

str1 = Join(arry2, " , ")
str2 = "create table " & table_Name & " (" & str1 & " )"
'MsgBox str2


Set cnn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
cnn.Open "Provider=sqloledb;Server=ASVMPAJ4G5GAJKL;Database=excel;User Id=sa;Password=19981019;"

sql = str2
cnn.Execute (sql)
MsgBox "create table OK"


cnn.Close
Set rst = Nothing
Set cnn = Nothing

Call 插入数据


    Exit Sub
errmsg:
    MsgBox Err.Description, , "错误报告"
End Sub

（4）插入数据方法,需要在cnn.Open中修改参数

Sub 插入数据()
    Dim tt, arr1(), i%, j%, str$, arr2(), str2$
    tt = Timer
    Dim cnn As Object, sql$, rst As Object
    arr1 = ActiveSheet.Range("a1").CurrentRegion.Value
    Set cnn = CreateObject("ADODB.Connection")
    Set rst = CreateObject("ADODB.Recordset")
    On Error GoTo errmsg
    cnn.Open "Provider=sqloledb;Server=ASVMPAJ4G5GAJKL;Database=excel;User Id=sa;Password=19981019;"
   
    '构造插入语句
    sql = "insert into " & table_Name & " values ("
    '从第2行开始插入数据
    For i = 2 To UBound(arr1, 1)
        For j = 1 To UBound(arr1, 2)
            ReDim Preserve arr2(j - 1)
            '在单元格内容插入单引号
            arr2(j - 1) = "'" & arr1(i, j) & "'"

        Next
        str = Join(arr2, ",")
        '执行插入语句
        str2 = sql & str & ")"
        cnn.Execute (str2)
    Next

    MsgBox "ok,用了" & Timer - tt & "秒"

    cnn.Close
    Set cnn = Nothing
    Exit Sub
errmsg:
   'MsgBox str2
    MsgBox Err.Description, , "错误报告"
End Sub

另外，表头有空或者特殊字符很容易导致建表失败，如果有特殊字符，我们写一个方法来替换掉它

Function FindDataType(V_cell As String)

If V_cell = "" Then V_cell = "空的"

If IsNumeric(Left(V_cell, 1)) = True Then V_cell = "a" & V_cell

If Len(V_cell) >= 15 Then V_cell = Left(V_cell, 14)

If InStr(V_cell, "/") Then V_cell = Replace(V_cell, "/", "_")

If InStr(V_cell, "'") Then V_cell = Replace(V_cell, "'", "")



FindDataType = V_cell

End Function

运行结果

点三角符号运行，运行成功后去数据库刷新

结果.png