SQL查询命令互转vba格式

Kls673M

最近搞个Excel的vba查询数据库，发现vba有代码行长度限制需要转换下就弄了这个，布局和功能暂且这样了，哪位大佬如果有兴趣的可以再美化下！

这次更新了SQL命令互转VBA格式，
SQL原始格式要分行的不能一坨贴进去，不然转换后会出现多个连接符，能力有限不懂改了……
vba格式原始格式尽量从vba复制出来吧
我使用的2008 R2所以vba那个段仅当测试用的，如果你用其他数据库自行修改把

成品：https://wwvz.lanzoue.com/b04koop8b密码:e5d5

[Python] 纯文本查看 复制代码

import tkinter as tk
from tkinter import ttk
import re
import tkinter.messagebox as messagebox
import tkinter.scrolledtext as scrolledtext
from tkinter import Tk

# 定义一个函数，用于移除文本中的换行符和回车符
def remove_newlines():
    # 数据库连接信息
    server_name = server_entry.get() 
    database_name = database_entry.get() 
    username = username_entry.get() 
    password = password_entry.get()

    sql_command = text.get("1.0", "end-1c")
    sql_command = re.sub(r"(.{130}(?:\s|$))", r'\1 "& _\n" ', sql_command)
    sql_command = re.sub(r"\s+", " ", sql_command)
    sql_command = re.sub(r"(.{130}(?:\s|$))", r'\1 "& _\n" ', sql_command)

    vba_code = generate_vba_code(
        server_name, database_name, username, password, sql_command
    )

    sql_command_text.delete(1.0, tk.END)
    sql_command_text.insert(tk.END, sql_command)

    vba_code_text.delete(1.0, tk.END)
    vba_code_text.insert(tk.END, vba_code)

def convert_to_sql():
    vba_command = text.get(1.0, tk.END)
    vba_command = re.sub(r'"\s*&\s*_\s*"', '', vba_command)
    sql_command_text.delete(1.0, tk.END)
    sql_command_text.insert(tk.END, vba_command)

# 定义一个函数，用于生成VBA代码
def generate_vba_code(server_name, database_name, username, password, sql_command):
    vba_code = f'''
    Sub RunSQLQuery()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object

    ' 数据库连接信息
    serverName = "{server_name}"
    databaseName = "{database_name}"
    userName = "{username}"
    password = "{password}"

    ' 获取输入框变量的值，这个可以删除
    bFullName = ""
    pFullName = ""
    pUserCode = ""
    pStandard = ""
    Number = ""
    CFDA = ""
    pArea = ""

    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    ' 建立数据库连接
    conn.Open "Provider=SQLOLEDB;Data Source=" & serverName & ";Initial Catalog=" & databaseName & ";User ID=" & userName & ";Password=" & password & ";"

    ' SQL查询命令
    strSQL = "{sql_command}"

    ' 执行查询
    rs.Open strSQL, conn

    ' 将查询结果输出到Excel工作表
    Dim i As Integer
    For i = 1 To rs.Fields.Count
    ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
    Next i

    If Not rs.EOF Then
    ActiveSheet.Range("A2").CopyFromRecordset rs
    End If

    ' 清理资源
    rs.Close
    conn.Close

    Set rs = Nothing
    Set conn = Nothing
    End Sub'''
    return vba_code

# 定义一个函数，用于粘贴文本
def paste_text():
    try:
        text.delete('1.0', tk.END)
        text.insert(tk.END, root.clipboard_get())
    except tk.TclError:
        pass

# 定义一个函数，用于复制文本到剪贴板
def copy_text():
    try:
        root.clipboard_clear()
        root.clipboard_append(sql_command_text.get("1.0", tk.END))
        messagebox.showinfo('复制成功', '已将转换后的内容复制到剪贴板')
    except tk.TclError:
        pass

# 主程序入口
root = tk.Tk()

notebook = ttk.Notebook(root)
notebook.pack(fill="both", expand=True)

input_frame = tk.Frame(notebook)
input_frame.pack(fill="both", expand=True)

text_frame = tk.Frame(input_frame)
text_frame.pack(side=tk.TOP, fill="both", expand=True)

text = tk.Text(text_frame, height=10, width=100)
text.pack(side=tk.LEFT, fill="both", expand=True)

text_scrollbar = ttk.Scrollbar(
    text_frame, orient=tk.VERTICAL, command=text.yview
)
text_scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
text.configure(yscrollcommand=text_scrollbar.set)

notebook.add(input_frame, text="输入框")

input_frame = tk.Frame(root) 
input_frame.pack(side=tk.TOP)

server_label = tk.Label(input_frame, text="服务器：") 
server_label.grid(row=0, column=0, sticky=tk.E, padx=5, pady=5)
server_entry = tk.Entry(input_frame) 
server_entry.grid(row=0, column=1, padx=5, pady=5)

database_label = tk.Label(input_frame, text="数据库：") 
database_label.grid(row=1, column=0, sticky=tk.E, padx=5, pady=5)
database_entry = tk.Entry(input_frame) 
database_entry.grid(row=1, column=1, padx=5, pady=5)

username_label = tk.Label(input_frame, text="用户名：") 
username_label.grid(row=2, column=0, sticky=tk.E, padx=5, pady=5)
username_entry = tk.Entry(input_frame) 
username_entry.grid(row=2, column=1, padx=5, pady=5)

password_label = tk.Label(input_frame, text="密码：") 
password_label.grid(row=3, column=0, sticky=tk.E, padx=5, pady=5)
password_entry = tk.Entry(input_frame) 
password_entry.grid(row=3, column=1, padx=5, pady=5)

output_frame = tk.Frame(notebook)
output_frame.pack(fill="both", expand=True)

sql_command_text = scrolledtext.ScrolledText(output_frame, height=10, width=100)
sql_command_text.pack(fill="both", expand=True)

vba_code_text = scrolledtext.ScrolledText(output_frame, height=10, width=100)
vba_code_text.pack(fill="both", expand=True)

notebook.add(output_frame, text="输出框")

button_frame = tk.Frame(root)
button_frame.pack(side=tk.TOP)

paste_button = tk.Button(button_frame, text="清空粘贴", command=paste_text)
paste_button.pack(side=tk.LEFT, padx=10)

button = tk.Button(button_frame, text="转换VBA格式", command=remove_newlines)
button.pack(side=tk.LEFT, padx=10)

button = tk.Button(button_frame, text="转换SQL格式", command=convert_to_sql)
button.pack(side=tk.LEFT, padx=10)

copy_button = tk.Button(button_frame, text="复制结果", command=copy_text)
copy_button.pack(side=tk.LEFT, padx=10)

root.mainloop()

四哥!

感谢分享。下载备用一下。

liunx0001

有没有成品？

shitdevops

感谢分享

Kristine_He

可以反过来VBA格式转SQL吗?

Kls673M

liunx0001 发表于 2023-10-6 13:18
有没有成品？

暂时没有，因为不是很完美，而且内置有数据库的连接信息就不方便放了

Kls673M

Kristine_He 发表于 2023-10-6 14:03
可以反过来VBA格式转SQL吗?

这是个好功能，下次可以搞下这个！

dunet

谢谢分享，收藏备用。

kllt

还不错，多谢分享！

一蟲先生一

Kls673M 发表于 2023-10-6 14:08
这是个好功能，下次可以搞下这个！

你要是搞了请踢我一脚，马上来围观