一键将excel中活动工作表内容导入到Oracle数据库

likeyouli

用的vba和Python，python要提前pip install oracledb库，才能与数据库建立连接。
绝对的一键导入，不需要再动一下鼠标操作


Sub 生成Python数据建表及导入()
   Dim oShell As Object, psCommand As String, i As Long, filepath3 As String, zhaodao As Range

filepath4 = "c:\create.py"
filepath5 = "c:\shuju.py"

TableName = InputBox("请输入Oracle表名:", "表名输入", "你的表名")
If Not TableName Like "[a-zA-Z一-龢]*" Then MsgBox "表名不是以字母或汉字开头,请从新开始": Exit Sub

riqilie = InputBox("请输入日期的列名（格式为m;n等字母，用符号分开，必须从a→z顺序，可以是aa、ab）: (如无日期列请直接点确定，点取消会终止运行代码)", "列名输入", "0")
If riqilie = "" Then MsgBox "您已取消,代码将终止。": Exit Sub
  Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "[a-zA-Z]+"  ' 匹配一个或多个数字
    End With
'因为往Oracle数据库里导入时用到',故先进行替换,以下为替换
Set zhaodao = Range("a1").CurrentRegion.Find("'")
If Not zhaodao Is Nothing Then
  Dim answer As VbMsgBoxResult
  answer = MsgBox("发现表格中内容存在单引号( ' )，如不替换无法导入数据。是否将其替换为“|||!|||”？", vbYesNo + vbQuestion, "确认替换")
   If answer = vbYes Then
         ' 用户选“是”，执行替换
        Range("a1").CurrentRegion.Replace What:="'", Replacement:="|||!|||", LookAt:=xlPart
     Else
            ' 用户选“否”，终止代码运行
            MsgBox "操作已取消，代码运行结束。", vbExclamation, "取消"
            Exit Sub
        End If
End If

answer = MsgBox("请确认是否建表 ？？", vbYesNo + vbExclamation, "是否需要建表")
   If Not answer = vbYes Then
   GoTo bujianbiao
   Else
For i = 1 To 100
      If Cells(1, i + 1) = "" Then colTypes = colTypes & """" & Cells(1, i) & """" & " varchar2(800)": Exit For
    colTypes = colTypes & """" & Cells(1, i) & """" & " varchar2(800)," & vbCrLf
Next i
strText = "aa = " & "'''CREATE TABLE " & TableName & vbCrLf & "(" & colTypes & ")'''" & vbCrLf
strText = strText & "import oracledb" & vbCrLf & "username = ""likeyou32""" & vbCrLf & "dsn = ""192.168.1.139:1521/orclcdb""" _
         & vbCrLf & "with oracledb.connect(user=username, password=""wuyou"", dsn=dsn) as connection:" & vbCrLf & _
         "    with connection.cursor() as cursor:" & vbCrLf & "        cursor.execute(aa)" & vbCrLf & _
         "        connection.commit() " & vbCrLf & "        print(""表创建成功！"")"

    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText11 & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath4 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run psCommand, 1, True
    Set oShell = Nothing

fileNum = FreeFile
    Open filepath4 For Append As #fileNum
    Print #fileNum, "# -*- coding: gbk -*-"
    Print #fileNum, strText
    Close #fileNum
CreateObject("WScript.Shell").Run "cmd /c python  " & filepath4, 1, True
    MsgBox "已在Oracle数据库中Create表"
End If
'以上为建表
'以下为生成shuju.py
bujianbiao:
riqijia = 0
For i = 1 To 100
     If Not regex.Test(riqilie) Then GoTo tiaoguo
     If riqijia = regex.Execute(riqilie).Count Then riqijia = regex.Execute(riqilie).Count - 1
      riqilie1 = regex.Execute(riqilie)(riqijia)
tiaoguo:
      zimu = GetExcelColumnName(i)
      '以下重新屡屡逻辑
'注意,一定要把判断日期"UCase(zimu) = UCase(riqilie1)"放到每次条件判断的首次判断
'仅有1列时,生成的元组要带逗号,所以单独拿出来写,仅有1列分为两种情况,1是有日期,2是无日期,因为加上了iserror,所以无日期也不要紧,不对,要紧,因为数字也会变为日期,所以只有要求的列才能设置text日期
If Cells(1, i + 1) = "" And i = 1 And UCase(zimu) = UCase(riqilie1) Then xx = "IF(ISERROR(" & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """('""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',),""" & "," & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),"")": GoTo gongshi
If Cells(1, i + 1) = "" And i = 1 Then xx = """('""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',),""": GoTo gongshi
'以下为有2列及以上时,分日期情况讨论:①当i=1,又分两种情况
If i = 1 Then
    If UCase(zimu) = UCase(riqilie1) Then
        xx = "IF(ISERROR(" & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """('""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',""" & "," & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',"")"
      riqijia = riqijia + 1
    Else
        xx = """('""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',"""
    End If
Else
    If Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
        xx = xx & "&" & "IF(ISERROR(" & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """'""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',),""" & "," & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """'),"")"
       Exit For
    ElseIf Not Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
         xx = xx & "&" & "IF(ISERROR(" & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """'""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',""" & "," & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',"")"
      riqijia = riqijia + 1

    Else
        If Cells(1, i + 1) = "" Then
            xx = xx & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """'),""": Exit For
        Else
            xx = xx & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',"""
        End If
    End If
End If
Next i
gongshi:
Cells(2, i + 1).NumberFormatLocal = "G/通用格式"
Cells(2, i + 1) = "=" & xx
h = Range("a1").CurrentRegion.Rows.Count
If h < 500000 Then
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Else
h = 50
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
End If
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy

    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText11 & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath5 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run psCommand, 1, True
    Set oShell = Nothing

For x1 = 1 To i
If x1 = i Then cc = cc & ":" & x1 & ")'": Exit For
cc = cc & ":" & x1 & ","
Next x1
cc = "'INSERT INTO " & TableName & " VALUES (" & cc
Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' 创建MSForms.DataObject对象
    clipboard.GetFromClipboard     ' 从剪贴板获取文本
    textData = clipboard.GetText
textData = Left(textData, Len(textData) - 3)
textData = "data = [" & textData & "]" & vbCrLf & "cc =" & cc & vbCrLf & "import oracledb" & vbCrLf & "import time" & vbCrLf & _
"xinbiao =" & "'" & TableName & "'" & vbCrLf & _
"with oracledb.connect(user=""likeyou32"", password=""wuyou"", dsn=""192.168.1.139:1521/orclcdb"") as connection:" & vbCrLf & _
"    with connection.cursor() as cursor:" & vbCrLf & "        cursor.executemany(" & vbCrLf & _
"            cc," & vbCrLf & "            data" & vbCrLf & "        )" & vbCrLf & "        connection.commit()" & vbCrLf & _
"        print(f""成功向 '{xinbiao}' 表中插入 {cursor.rowcount} 条记录，倒计时结束,代码自动停止。您老可以去忙别的了....."")" & vbCrLf & vbCrLf & _
"for i in range(30, 0, -1):" & vbCrLf & "    print(f""倒计时: {i}秒..."",end=""\r"")" & vbCrLf & "    time.sleep(1)"

fileNum = FreeFile
    Open filepath5 For Append As #fileNum
    Print #fileNum, "# -*- coding: gbk -*-"
    Print #fileNum, textData
    Close #fileNum
CreateObject("WScript.Shell").Run "cmd /c python  " & filepath5, 1, True

'MsgBox "已完成,全部代码运行结束!!"
End Sub

Function GetExcelColumnName(colNumber As Long) As String
    Dim dividend As Long
    Dim remainder As Long
    Dim columnName As String
    dividend = colNumber
    columnName = ""
    Do While dividend > 0
        remainder = (dividend - 1) Mod 26
        columnName = Chr(65 + remainder) & columnName ' 65是"A"的ASCII码
        dividend = (dividend - remainder - 1) \ 26
    Loop
    GetExcelColumnName = columnName
End Function


Function GetFileLineCountSwitch(filePath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")
   
    ' 使用 switch 语句，逐行处理，内存效率高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "switch -File '" & filePath & "' { default { $count++ } }; " & _
                "Write-Output $count" & """"
    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)
   
    If IsNumeric(result) Then
        GetFileLineCountSwitch = CLng(result)
    Else
        GetFileLineCountSwitch = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountSwitch = -1
End Function

Function GetFileLineCountDotNet(filePath As String) As Long
    On Error GoTo ErrorHandler
    Dim shell As Object, exec As Object, psCommand As String, result As String
    Set shell = CreateObject("WScript.Shell")
   
    ' 使用 .NET StreamReader，逐行读取，内存效率最高
    psCommand = "powershell -command """ & "$count = 0; " & _
                "$reader = New-Object System.IO.StreamReader('" & filePath & "'); " & _
                "while ($reader.ReadLine() -ne $null) { $count++ }; " & _
                "$reader.Close(); " & "$reader.Dispose(); " & "Write-Output $count" & """"
   
    Set exec = shell.exec(psCommand)
    result = Trim(exec.StdOut.ReadAll)
    If IsNumeric(result) Then
        GetFileLineCountDotNet = CLng(result)
    Else
        GetFileLineCountDotNet = -1
    End If
    Exit Function
ErrorHandler:
    GetFileLineCountDotNet = -1
End Function

Sub 测试耗时时间()
Dim filepath3 As String
filepath3 = "c:\shengcheng12.txt"

' (Get-Content 'c:\shengcheng.txt' | Measure-Object).Count
'$count = 0;switch -File 'c:\output.sql' { default { $count++ } };echo $count

startTime = Timer
result1 = GetFileLineCountSwitch(filepath3)
MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:调用函数的switch"


startTime = Timer
psCommand = "powershell -command ""$count = 0;switch -File '" & filepath3 & "' { default { $count++ } };echo $count"""
result1 = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:直接switch"


  startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "' | Measure-Object).Count"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1 & "方法:Measure-Object"


startTime = Timer
result1 = GetFileLineCountDotNet(filepath3)
MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1

  startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Length"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1
   
startTime = Timer
psCommand = "powershell -command ""(Get-Content '" & filepath3 & "').Count"""
result = CreateObject("WScript.Shell").exec(psCommand).StdOut.ReadAll
    MsgBox "处理完成！耗时: " & Format(Timer - startTime, "0.00") & " 秒,共有行数:" & result1

End Sub

likeyouli

在python中获取剪贴板的内容，才是最高效的。但获取后要转为列表，通过json转列表，才最高效。



Sub 生成Python数据建表及导入_通过json转列表_仅有1列仅能通过eval转列表()
   Dim oShell As Object, psCommand As String, i As Long, filepath3 As String, zhaodao As Range

filepath4 = "c:\create.py"
filepath5 = "c:\shuju.py"
MsgBox "请先确认列名是否重复,如果重复,建表将出现错误!!"
DoEvents
TableName = InputBox("请输入Oracle表名:", "表名输入", "你的表名")
If Not TableName Like "[a-zA-Z一-龢]*" Then MsgBox "表名不是以字母或汉字开头,请从新开始": Exit Sub

riqilie = InputBox("请输入日期的列名（格式为m;n等字母，用符号分开，必须从a→z顺序，可以是aa、ab）: (如无日期列请直接点确定，点取消会终止运行代码)", "列名输入", "0")
If riqilie = "" Then MsgBox "您已取消,代码将终止。": Exit Sub
  Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .Pattern = "[a-zA-Z]+"  ' 匹配一个或多个数字
    End With
'因为往Oracle数据库里导入时用到',故先进行替换,以下为替换
DoEvents
Set zhaodao = Range("a1").CurrentRegion.Find("'"): DoEvents
If Not zhaodao Is Nothing Then
  Dim answer As VbMsgBoxResult
  answer = MsgBox("发现表格中内容存在单引号( ' )，如不替换无法导入数据。是否将其替换为“|||!|||”？", vbYesNo + vbQuestion, "确认替换")
   If answer = vbYes Then
         ' 用户选“是”，执行替换
        Range("a1").CurrentRegion.Replace What:="'", Replacement:="|||!|||", LookAt:=xlPart
     Else
            ' 用户选“否”，终止代码运行
            MsgBox "操作已取消，代码运行结束。", vbExclamation, "取消"
            Exit Sub
        End If
End If

answer = MsgBox("请确认是否建表 ？？", vbYesNo + vbExclamation, "是否需要建表")
   If Not answer = vbYes Then
   GoTo bujianbiao
   Else
For i = 1 To 100: DoEvents
      If Cells(1, i + 1) = "" Then colTypes = colTypes & """" & Cells(1, i) & """" & " varchar2(800)": Exit For
    colTypes = colTypes & """" & Cells(1, i) & """" & " varchar2(800)," & vbCrLf
Next i
strText = "aa = " & "'''CREATE TABLE " & TableName & vbCrLf & "(" & colTypes & ")'''" & vbCrLf
strText = strText & "import oracledb" & vbCrLf & "username = ""likeyou32""" & vbCrLf & "dsn = ""192.168.1.139:1521/orclcdb""" _
         & vbCrLf & "with oracledb.connect(user=username, password=""wuyou"", dsn=dsn) as connection:" & vbCrLf & _
         "    with connection.cursor() as cursor:" & vbCrLf & "        cursor.execute(aa)" & vbCrLf & _
         "        connection.commit() " & vbCrLf & "        print(""表创建成功！"")"

    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText11 & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath4 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run psCommand, 1, True
    Set oShell = Nothing

fileNum = FreeFile
    Open filepath4 For Append As #fileNum
    Print #fileNum, "# -*- coding: gbk -*-"
    Print #fileNum, strText
    Close #fileNum
CreateObject("WScript.Shell").Run "cmd /c python  " & filepath4, 1, True
    MsgBox "已在Oracle数据库中Create表，表名为：" & TableName
End If
'以上为建表
'以下为生成shuju.py
bujianbiao:
riqijia = 0
For i = 1 To 100: DoEvents
     If Not regex.Test(riqilie) Then GoTo tiaoguo
     If riqijia = regex.Execute(riqilie).Count Then riqijia = regex.Execute(riqilie).Count - 1
      riqilie1 = regex.Execute(riqilie)(riqijia)
tiaoguo:
      zimu = GetExcelColumnName(i)
      '以下重新屡屡逻辑
'注意,一定要把判断日期"UCase(zimu) = UCase(riqilie1)"放到每次条件判断的首次判断

'仅有1列时,生成的元组要带逗号,所以单独拿出来写,仅有1列分为两种情况,1是有日期,2是无日期,因为加上了iserror,所以无日期也不要紧,不对,要紧,因为数字也会变为日期,所以只有要求的列才能设置text日期
If Cells(1, i + 1) = "" And i = 1 And UCase(zimu) = UCase(riqilie1) Then xx = "IF(ISERROR(" & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """('""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',),""" & "," & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),"")": GoTo gongshi
If Cells(1, i + 1) = "" And i = 1 Then xx = """('""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',),""": GoTo gongshi
'以下为有2列及以上时,分日期情况讨论:①当i=1,又分两种情况
If i = 1 Then
    If UCase(zimu) = UCase(riqilie1) Then
        xx = "IF(ISERROR(" & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """('""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',""" & "," & """('""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',"")"
      riqijia = riqijia + 1
    Else
        xx = """('""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',"""
    End If
Else
    If Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
        xx = xx & "&" & "IF(ISERROR(" & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """'""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',),""" & "," & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """'),"")"
       Exit For
    ElseIf Not Cells(1, i + 1) = "" And UCase(zimu) = UCase(riqilie1) Then
         xx = xx & "&" & "IF(ISERROR(" & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',),""" & ")," & _
      """'""" & " & " & "CLEAN(" & zimu & 2 & ")" & " & " & """',""" & "," & """'""" & "&" & "TEXT(" & "CLEAN(" & zimu & 2 & "),""yyyy-mm-dd"")" & "&" & """',"")"
      riqijia = riqijia + 1

    Else
        If Cells(1, i + 1) = "" Then
            xx = xx & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """'),""": Exit For
        Else
            xx = xx & "&" & """'""" & "&" & "CLEAN(" & zimu & 2 & ")" & "&" & """',"""
        End If
    End If
End If
Next i
gongshi:
Cells(2, i + 1).NumberFormatLocal = "G/通用格式"
Cells(2, i + 1) = "=" & xx
h = Range("a1").CurrentRegion.Rows.Count
If h < 5000000 Then
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
Else
h = 50
Cells(2, i + 1).AutoFill Range(Cells(2, i + 1), Cells(h, i + 1))
End If
Range(Cells(2, i + 1), Cells(h, i + 1)).Copy

    psCommand = "powershell -Command " & """$bytes = [System.Text.Encoding]::UTF8.GetBytes('" & strText11 & "'); " & _
                "$stream = [System.IO.File]::Create('" & filepath5 & "'); " & "$stream.Write($bytes, 0, $bytes.Length); " & "$stream.Close()"""
    Set oShell = CreateObject("WScript.Shell")
    oShell.Run psCommand, 1, True
    Set oShell = Nothing

For x1 = 1 To i
If x1 = i Then cc = cc & ":" & x1 & ")'": Exit For
cc = cc & ":" & x1 & ","
Next x1
cc = "'INSERT INTO " & TableName & " VALUES (" & cc


'Set clipboard = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") ' 创建MSForms.DataObject对象
'    clipboard.GetFromClipboard     ' 从剪贴板获取文本
'    textData = clipboard.GetText
'textData = Left(textData, Len(textData) - 3)
DoEvents

If Not Cells(1, 2) = "" Then
textData = "import win32clipboard" & vbCrLf & "import ast" & vbCrLf & "# 获取剪贴板文本" & vbCrLf & "win32clipboard.OpenClipboard()" & vbCrLf & _
"data = win32clipboard.GetClipboardData(win32clipboard.CF_UNICODETEXT)" & vbCrLf & "win32clipboard.CloseClipboard()" & vbCrLf & _
"data = '[' + data.rstrip(', \n\r\t') + ']'" & vbCrLf & "cc =" & cc & vbCrLf & "import oracledb" & vbCrLf & "import time" & vbCrLf & _
"import math" & vbCrLf & "def chunked_iterable(data, chunk_size):" & vbCrLf & "    for i in range(0, len(data), chunk_size):" & vbCrLf & _
"        yield data[i:i + chunk_size]" & vbCrLf & "BATCH_SIZE = 50000" & vbCrLf & "zongshuliang = 0" & vbCrLf & "jici = 0" & vbCrLf & _
"start_time = time.time()" & vbCrLf & "print('正往数据库里导入数据, 请稍等...转列表前')" & vbCrLf & "import json" & vbCrLf & _
"data = data.replace('(', '[').replace(')', ']')" & vbCrLf & "data = data.replace(""'"", '""')" & vbCrLf & "data = data.replace('None', 'null')" & _
vbCrLf & "data = json.loads(data)" & vbCrLf & "# data = [tuple(row) for row in data]" & vbCrLf & "print('正往数据库里导入数据, 请稍等...转后')" & _
vbCrLf & "end_time = time.time()" & vbCrLf & "elapsed = end_time - start_time" & vbCrLf & "print(f'运行时间: {elapsed} 秒')" & vbCrLf & _
"xinbiao =" & "'" & TableName & "'" & vbCrLf & "# data = ast.literal_eval(data)" & vbCrLf & _
"with oracledb.connect(user=""likeyou32"", password=""wuyou"", dsn=""192.168.1.139:1521/orclcdb"") as connection:" & vbCrLf & _
"    with connection.cursor() as cursor:" & vbCrLf & "        for batch in chunked_iterable(data, BATCH_SIZE):" & vbCrLf & "            cursor.executemany(cc,batch)" & vbCrLf & _
"            connection.commit()" & vbCrLf & "            jici += 1" & vbCrLf & "            print(f'已提交 {cursor.rowcount} 行, 用的rowcount,第{jici}次提交 ')" & vbCrLf & _
"            zongshuliang += cursor.rowcount" & vbCrLf & _
"        print(f""成功向 '{xinbiao}' 表中插入 {zongshuliang} 条记录，倒计时结束,代码自动停止。您老可以去忙别的了....."")" & vbCrLf & _
"for i in range(30, 0, -1):" & vbCrLf & "    print(f""倒计时: {i}秒..."",end=""\r"")" & vbCrLf & "    time.sleep(1)"
Else

textData = "import win32clipboard" & vbCrLf & "import ast" & vbCrLf & "# 获取剪贴板文本" & vbCrLf & "win32clipboard.OpenClipboard()" & vbCrLf & _
"data = win32clipboard.GetClipboardData(win32clipboard.CF_UNICODETEXT)" & vbCrLf & "win32clipboard.CloseClipboard()" & vbCrLf & _
"data = '[' + data.rstrip(', \n\r\t') + ']'" & vbCrLf & "cc =" & cc & vbCrLf & "import oracledb" & vbCrLf & "import time" & vbCrLf & _
"import math" & vbCrLf & "def chunked_iterable(data, chunk_size):" & vbCrLf & "    for i in range(0, len(data), chunk_size):" & vbCrLf & _
"        yield data[i:i + chunk_size]" & vbCrLf & "BATCH_SIZE = 50000" & vbCrLf & "zongshuliang = 0" & vbCrLf & "jici = 0" & vbCrLf & _
"start_time = time.time()" & vbCrLf & "print('正往数据库里导入数据, 请稍等...转列表前')" & vbCrLf & "import json" & vbCrLf & _
"data = eval(data)" & vbCrLf & "print('正往数据库里导入数据, 请稍等...转后')" & _
vbCrLf & "end_time = time.time()" & vbCrLf & "elapsed = end_time - start_time" & vbCrLf & "print(f'运行时间: {elapsed} 秒')" & vbCrLf & _
"xinbiao =" & "'" & TableName & "'" & vbCrLf & "# data = ast.literal_eval(data)" & vbCrLf & _
"with oracledb.connect(user=""likeyou32"", password=""wuyou"", dsn=""192.168.1.139:1521/orclcdb"") as connection:" & vbCrLf & _
"    with connection.cursor() as cursor:" & vbCrLf & "        for batch in chunked_iterable(data, BATCH_SIZE):" & vbCrLf & "            cursor.executemany(cc,batch)" & vbCrLf & _
"            connection.commit()" & vbCrLf & "            jici += 1" & vbCrLf & "            print(f'已提交 {cursor.rowcount} 行, 用的rowcount,第{jici}次提交 ')" & vbCrLf & _
"            zongshuliang += cursor.rowcount" & vbCrLf & _
"        print(f""成功向 '{xinbiao}' 表中插入 {zongshuliang} 条记录，倒计时结束,代码自动停止。您老可以去忙别的了....."")" & vbCrLf & _
"for i in range(30, 0, -1):" & vbCrLf & "    print(f""倒计时: {i}秒..."",end=""\r"")" & vbCrLf & "    time.sleep(1)"

End If

fileNum = FreeFile
    Open filepath5 For Append As #fileNum
    Print #fileNum, "# -*- coding: gbk -*-"
    Print #fileNum, textData
    Close #fileNum
CreateObject("WScript.Shell").Run "cmd /c python  " & filepath5, 1, False

End Sub

Function GetExcelColumnName(colNumber As Long) As String
    Dim dividend As Long
    Dim remainder As Long
    Dim columnName As String
    dividend = colNumber
    columnName = ""
    Do While dividend > 0
        remainder = (dividend - 1) Mod 26
        columnName = Chr(65 + remainder) & columnName ' 65是"A"的ASCII码
        dividend = (dividend - remainder - 1) \ 26
    Loop
    GetExcelColumnName = columnName
End Function

likeyouli

以上都是在vba中操作，第一个是在vba中调用剪贴板赋值给vba中的变量，一切数据及代码整理好后生成python文件，然后运行，但数据量大时容易报内存溢出；

                                  第二个是用vba生成了python代码，在python中调用剪贴板赋值给变量生成数据。

下边用纯python，不需要任何vba，也不需要打开提前打开Excel，默认使用表中第一行建表：

import pandas as pd,oracledb,tempfile,os,time,fastexcel
starttime=time.time()
print("运行到这里1")
reader = fastexcel.read_excel("z:\\12.xlsx")

sheet = reader.load_sheet(0)
print(sheet.name)
df = sheet.to_pandas()
print(f"打开文件并转为pandas耗时 {time.time() - starttime:.2f} 秒")
print("运行到这里2")

tt=""
for i in df.columns:
  tt = tt + i + " varchar2(800),"
tt = tt.rstrip(', \n\r\t')

tablename = "你的表名1"
aa= "create table " + tablename  + "(" + tt + ")"


df = df.fillna('')  # 将空值替换为空字符串
print("运行到这里3")
temp_file="z:\\nihaode.csv"

try:
    data_list = [tuple(row) for row in df.to_numpy()]
    columns = df.columns.tolist()
    print("运行到这里4")
    placeholders = ','.join([f':{i+1}' for i in range(len(columns))])
    # insert_sql = f"INSERT INTO 你的表名1 ({','.join(columns)}) VALUES ({placeholders})"
    insert_sql = f"INSERT INTO {tablename}  VALUES ({placeholders})"
    print("运行到这里5")
    batch_size = 100000
    with oracledb.connect(user="likeyou32", password="wuyou", dsn="192.168.1.139:1521/orclcdb") as connection:
        with connection.cursor() as cursor:
            cursor.execute(aa)
            connection.commit()
            print("表创建成功！")
            for i in range(0, len(data_list), batch_size):
                batch = data_list[i:i+batch_size]
                cursor.executemany(insert_sql, batch)
                connection.commit()
                print(f"已插入 {min(i+batch_size, len(data_list))}/{len(data_list)} 条")
finally:
    # 删除临时文件
    if os.path.exists(temp_file):
        os.remove(temp_file)
print(f"总用时间为: {time.time()-starttime} 秒")