VBS 代码主要完成从 Excel 文件读取数据并插入到 SQL Server 数据库的操作，同时会先删除数据库中指定表的数据。
Option Explicit
' 主程序入口
Call Main()
Sub Main()
' 飞机数据处理
Call deleteAA_Huilianyi_Flight_Bill2024()
Call insertAA_Huilianyi_Flight_Bill()
End Sub
Sub insertAA_Huilianyi_Flight_Bill()
Dim connectionString, filePath
connectionString = "Provider=SQLOLEDB;Data Source=192.168.xx.xxx;Initial Catalog=UFDATA_00xxxxx;User ID=sa;Password=xxxxxe;"
filePath = CreateObject("Scripting.FileSystemObject").GetParentFolderName(WScript.ScriptFullName) & "\SETTLEMENT_EXPORT_duizhangzhongxin.xlsx"
' 创建 Excel 对象
Dim excelApp, excelWorkbook, excelWorksheet, excelRange
Set excelApp = CreateObject("Excel.Application")
Set excelWorkbook = excelApp.Workbooks.Open(filePath)
Set excelWorksheet = excelWorkbook.Worksheets(1)
Set excelRange = excelWorksheet.UsedRange
' 构建插入 SQL 语句
Dim insertSql, col, columnName
insertSql = "INSERT INTO AA_Huilianyi_Flight_Bill0717 ("
For col = 1 To excelRange.Columns.Count
columnName = excelRange.Cells(1, col).Value
columnName = ReplaceColumnNames(columnName)
insertSql = insertSql & "[" & columnName & "], "
Next
insertSql = Left(insertSql, Len(insertSql) - 2) & ") VALUES ("
For col = 1 To excelRange.Columns.Count
columnName = excelRange.Cells(1, col).Value
columnName = ReplaceColumnNames(columnName)
insertSql = insertSql & "@" & columnName & ", "
Next
insertSql = Left(insertSql, Len(insertSql) - 2) & ")"
' 执行插入操作
Dim conn, cmd, row
Set conn = CreateObject("ADODB.Connection")
conn.Open connectionString
For row = 2 To excelRange.Rows.Count
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = insertSql
For col = 1 To excelRange.Columns.Count
columnName = excelRange.Cells(1, col).Value
columnName = ReplaceColumnNames(columnName)
Dim value
value = excelRange.Cells(row, col).Value
If value Is Nothing Then value = ""
cmd.Parameters.Append cmd.CreateParameter("@" & columnName, 200, 1, 255, value)
Next
WScript.Echo "Generated SQL: " & cmd.CommandText
On Error Resume Next
cmd.Execute
If Err.Number <> 0 Then
WScript.Echo "Error executing SQL: " & cmd.CommandText & vbCrLf & "Error: " & Err.Description
Err.Clear
End If
On Error Goto 0
Next
' 关闭 Excel
excelWorkbook.Close
excelApp.Quit
Set excelRange = Nothing
Set excelWorksheet = Nothing
Set excelWorkbook = Nothing
Set excelApp = Nothing
' 关闭数据库连接
conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub
Sub deleteAA_Huilianyi_Flight_Bill2024()
Dim server, database, username, password
server = "192.168.xxx.xxxx"
database = "UFDATA_00xxxxx"
username = "sa"
password = "1xxxxx"
Dim conn, cmd, deleteSQL
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";User ID=" & username & ";Password=" & password & ";"
conn.Open
deleteSQL = "DELETE FROM AA_Huilianyi_Flight_Bill0717 "
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = deleteSQL
On Error Resume Next
cmd.Execute
If Err.Number <> 0 Then
WScript.Echo "Error executing SQL: " & deleteSQL & vbCrLf & "Error: " & Err.Description
Err.Clear
End If
On Error Goto 0
conn.Close
Set cmd = Nothing
Set conn = Nothing
End Sub
Function ReplaceColumnNames(columnName)
If InStr(columnName, "国内/国际") > 0 Then
ReplaceColumnNames = "国内_国际"
ElseIf InStr(columnName, "公司名称 ") > 0 Then
ReplaceColumnNames = "公司名称"
ElseIf InStr(columnName, "实际出发地（司机点击开始的位置）") > 0 Then
ReplaceColumnNames = "实际出发地"
ElseIf InStr(columnName, "实际目的地（司机点击结束的位置）") > 0 Then
ReplaceColumnNames = "实际目的地"
Else
ReplaceColumnNames = columnName
End If
End Function
代码说明：
1. 主程序入口：`Main` 子程序是程序的入口点，调用 `
deleteAA_Huilianyi_Flight_Bill2024` 函数删除数据库中指定表的数据，然后调用 `
insertAA_Huilianyi_Flight_Bill` 函数从 Excel 文件读取数据并插入到数据库。
2. 删除数据：`
deleteAA_Huilianyi_Flight_Bill2024` 子程序创建一个 ADODB 连接，执行删除指定表中数据的 SQL 语句。
3. 插入数据：`
insertAA_Huilianyi_Flight_Bill` 子程序完成以下操作：
- 连接到数据库。
- 打开 Excel 文件并读取数据。
- 构建插入 SQL 语句，处理列名替换。
- 逐行读取 Excel 数据并插入到数据库。
- 关闭 Excel 应用程序和数据库连接。
4. 列名替换函数：`ReplaceColumnNames` 函数用于处理列名中的特殊字符，将特定列名替换为统一格式。
注意事项：
- 确保系统中已安装 Excel 应用程序，因为代码依赖 Excel COM 对象。
- 确保 SQL Server 数据库服务正常运行，并且提供的数据库连接信息（服务器地址、数据库名、用户名、密码）正确。
- 运行代码时，确保 Excel 文件 `
SETTLEMENT_EXPORT_duizhangzhongxin.xlsx` 存在于脚本所在目录。