经过几天的折腾，终于搞定了这个功能，同时也搞定了通过计划任务程序发送乱码的问题，大家可以按照以下脚本进行相关设置，这里需要补充一下，由于查询结果中某些字段会有回车符，需要做一下替换，我替换了2次，保证了文件在一行中。
REPLACE(replace(字段名称 ,CHAR(13),','),CHAR(10),',') as '字段显示名称'
PowerShell代码：
$Subject ="SubjectName-$((Get-Date).ToString("yyyyMMdd_HHmmss"))"
$Directory="设定文件生成的目录需带盘符"
$csvfilename="csv文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).csv"
$xlsxfilename="xlsx文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).xlsx"
$Sendfile="$Directory$xlsxfilename"
$Database = '数据库名称'
$Server = '数据库服务器地址'
$UserName = '数据库用户名'
$Password = '数据库密码'
#数据库查询脚本
$SqlQuery = "SQL查询脚本"
function Export_Excel {
# Accessing Data Base
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$set = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($set)
# Consuming Data
$Table = $Set.Tables[0]
$Table | Export-CSV -encoding utf8 -NoTypeInformation $Directory$csvfileName
#$Table
}
function To_Excel {
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data ? From Text" in Excel
$TxtConnector = ("TEXT;" + "$Directory$csvfileName")
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
#$delimiter = "\r\n" #Specify the delimiter used in the file
### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)
### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFilePlatform = 65001
#$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1
### Execute & delete the import query
$query.Refresh()
$query.Delete()
### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs("$Directory$xlsxfilename",51)
$excel.Quit()
}
function SendMail {
#发邮件
$smtpServer = "smtp.126.com"
$smtpUser = "SMTP用户名"
$smtpPassword = "SMTP密码"
#$smtp.Send($mail)
$ss=ConvertTo-SecureString -String "$smtpPassword" -AsPlainText -force
$ss|Write-Host
$cre= New-Object System.Management.Automation.PSCredential("$smtpUser",$ss)
Send-MailMessage -to 收件人用逗号间隔 -from 自动邮件<发送邮箱地址> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $Sendfile -Credential $cre
}
Export_Excel
To_Excel
Start-Sleep -Seconds 10
SendMail
echo "remove-item -Force $csvfileName"
remove-item -Force $Directory$csvfileName,$Directory$xlsxfilename #删除两个临时文件