PowerShell从数据库导出CSV并转换为Excel文件后邮件发送 2022-01-04 Website News 暂无评论 1543 次阅读 之前的脚本在没有安装Office的环境中,会出现组件注册错误,具体错误如下: ```csharp New-Object : 检索 COM 类工厂中 CLSID 为 {00000000-0000-0000-0000-000000000000} 的组件失败,原因是出现以下错误: 80040154 没有注册类 (异常来自 HRESULT:0x80040154 (REGDB_E_CLASSNOTREG))。 ``` 查询了很多资料后,决定在PowerShell 5.0以上版本安装一个ImportExcel的模块来解决这个问题,正好也将之前Sendmail的问题一起解决。 ```csharp Install-Module ImportExcel ``` ```csharp $Directory="D:\TEMP\" $csvfilename="CSV文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).csv" $xlsxfilename="Xlsx文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).xlsx" $Sendfile="$Directory$xlsxfilename" $Database = '数据库名称' $Server = 'SQLSERVER服务器ip' $UserName = 'SQLSERVER用户名' $Password = 'SQLSERVER密码' #数据库查询脚本 $SqlQuery = "SQL脚本" function Export_Csv { # 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 Csv_To_Excel{ Import-Csv $Directory$csvfileName | Export-Excel $Directory$xlsxfilename -Autosize -NoNumberConversion * } function SendMail { #Compress-Archive -Path $Sendfile $Sendfile1 #发邮件 $userName = "发送邮箱用户名" $password = ConvertTo-SecureString –String "发送邮箱密码" –AsPlainText -Force $credentials = New-Object System.Management.Automation.PsCredential($userName, $password) # SMTP Variables $emailFrom = "发送人名称<发送邮箱地址>" $emailTo = "收件人地址,多人用英文,做间隔" $Subject ="邮件主题" $body = "邮件内容" $message = new-object System.Net.Mail.MailMessage $message.From = $emailFrom $message.To.Add($emailTo) $message.IsBodyHtml = $True $message.Subject = $Subject $attach = new-object Net.Mail.Attachment($Sendfile) $message.Attachments.Add($attach) $message.body = $body $SMTPServer = "smtp.126.com" $SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, 25) $SMTPClient.EnableSsl = $true $SMTPClient.Credentials = $credentials $SMTPClient.Send($message) } Export_Csv Csv_To_Excel Start-Sleep -Seconds 5 SendMail echo "remove-item -Force $csvfilename" echo "remove-item -Force $csvfilename" remove-item -Force "D:\TEMP\*.*" remove-item -Force "$Directory$xlsxfilename" #删除两个临时文件 ``` 标签: OFFICE, SQL, PowerShell, sqlserver, csv转excel, CSV to Excel, CSV, SENDMAIL, SMTP, SQLSERVER TO CSV 本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可。
评论已关闭