$subject ="邮件主题-$((Get-Date).ToString("yyyyMMdd_hhmmss"))"   
$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
$DataSet = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($DataSet)
$DataSetTable = $DataSet.Tables["Table"];

## ———- Working with Excel ———- ##

## – Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## – Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## – Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};

## – Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## – Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## – Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};

## – Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit();

## ———- Saving file and Terminating Excel Application ———- ##

## – Saving Excel file – if the file exist do delete then save
$xlsFile = "带有路径的文件名_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## – End of Script – ##

function SendMail {
#发邮件
$smtpServer = "SMTP服务器地址"
$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 [email protected],[email protected] -from 发件人显示名称<[email protected]> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $xlsFile  -Credential $cre
}
#Export_Excel
Start-Sleep -Seconds 30
SendMail
echo "remove-item -Force $xlsFile"
remove-item -Force $xlsFile  #删除当前生成的文件