如何通过sqlcmd从批处理文件生成.xlsx或转换.csv为.xlsx

直接修改文件后缀名只是改变了文件名，文件本质还是纯文本格式的CSV，Excel不会自动按分隔符拆分列。你可以通过两种方式解决：

一、将现有CSV转换为真正的XLSX文件 #

方法1：用VBScript配合批处理 #

写一个VBS脚本（比如convert_csv_to_xlsx.vbs）：

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False ' 后台运行，不显示Excel窗口
Set objWorkbook = objExcel.Workbooks.Open("C:\your\input\path\data.csv")
' 51是Excel XLSX格式的对应编码
objWorkbook.SaveAs "C:\your\output\path\data.xlsx", 51
objWorkbook.Close
objExcel.Quit
Set objWorkbook = Nothing
Set objExcel = Nothing

然后在批处理文件里添加调用命令：

cscript.exe //nologo convert_csv_to_xlsx.vbs

方法2：用PowerShell脚本（更灵活） #

写一个PowerShell脚本（比如Convert-CsvToXlsx.ps1）：

param(
    [string]$InputPath = "C:\your\input\path\data.csv",
    [string]$OutputPath = "C:\your\output\path\data.xlsx"
)

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open($InputPath)
$workbook.SaveAs($OutputPath, 51)
$workbook.Close()
$excel.Quit()
# 释放COM对象，避免Excel进程残留
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

然后在批处理中调用：

powershell -ExecutionPolicy Bypass -File Convert-CsvToXlsx.ps1 -InputPath "C:\your\input\path\data.csv" -OutputPath "C:\your\output\path\data.xlsx"

二、直接从SQL脚本生成XLSX文件 #

如果要跳过CSV步骤，直接导出XLSX，可以用PowerShell结合数据库连接实现，以SQL Server为例：
写一个PowerShell脚本（比如Export-SqlToXlsx.ps1）：

param(
    [string]$Server = "YOUR_SQL_SERVER",
    [string]$Database = "YOUR_DATABASE",
    [string]$Query = "SELECT * FROM YOUR_TABLE",
    [string]$OutputPath = "C:\your\output\path\data.xlsx"
)

# 初始化Excel对象
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.Worksheets.Item(1)

# 连接SQL Server并执行查询
$connectionString = "Server=$Server;Database=$Database;Integrated Security=True"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.SqlClient.SqlCommand($Query, $connection)
$connection.Open()
$reader = $command.ExecuteReader()

# 写入表头
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
    $worksheet.Cells(1, $i+1) = $reader.GetName($i)
}

# 写入数据行
$rowIndex = 2
while ($reader.Read()) {
    for ($colIndex = 0; $colIndex -lt $reader.FieldCount; $colIndex++) {
        $worksheet.Cells($rowIndex, $colIndex+1) = $reader.GetValue($colIndex)
    }
    $rowIndex++
}

# 保存并清理
$workbook.SaveAs($OutputPath, 51)
$workbook.Close()
$excel.Quit()
$connection.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

然后在批处理中调用：

powershell -ExecutionPolicy Bypass -File Export-SqlToXlsx.ps1 -Server "YOUR_SQL_SERVER" -Database "YOUR_DATABASE" -Query "SELECT * FROM YOUR_TABLE" -OutputPath "C:\your\output\path\data.xlsx"

注意事项 #

• 确保Excel已安装在运行批处理的机器上，上述方法依赖Excel的COM组件。
• 若使用非Windows认证的数据库连接，可修改connectionString添加用户名和密码："Server=$Server;Database=$Database;User ID=YOUR_USER;Password=YOUR_PWD;"

内容的提问来源于stack exchange，提问作者MartaR