东大桥网站建设产品营销推广

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

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

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 ObjectDim rs As ObjectDim strSQL As StringDim xlApp As ObjectDim xlWorkbook As ObjectDim 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 IntegerFor i = 1 To rs.Fields.CountActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).NameNext iIf Not rs.EOF ThenActiveSheet.Range("A2").CopyFromRecordset rsEnd If' 清理资源rs.Closeconn.CloseSet rs = NothingSet conn = NothingEnd 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()