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

likeyouli · 发表于 2026-5-28 15:34:11

本帖最后由 likeyouli 于 2026-5-30 10:32 编辑

用的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 · 发表于 2026-5-31 06:41:06

本帖最后由 likeyouli 于 2026-5-31 11:17 编辑

在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 · 发表于 2026-6-1 08:03:20

本帖最后由 likeyouli 于 2026-6-1 08:18 编辑

以上都是在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} 秒")

		自动登录	找回密码
密码			注册