Laptop Issue Letter (读取Excel中指定内容,然后生成新的Excel文件)

$xl = New-Object -ComObject "Excel.Application"
$cmdbwb = $xl.Workbooks.Open("F:\Ivan\HZCMDB.xlsx")
$cmdbws = $cmdbwb.Worksheets.Item("02--Laptop")
$row = Read-Host -Prompt "请输入Excel最左侧的行标"
$details = [pscustomobject][ordered]@{
EmployeeNo = $cmdbws.range("A$row").text
EmployeeName = $cmdbws.range("B$row").text
SeatNo = $cmdbws.range("C$row").text
Project = $cmdbws.range("D$row").text
Model = $cmdbws.range("E$row").text
ServiceTag = $cmdbws.range("F$row").text
FinanceNo = $cmdbws.range("G$row").text
RAM = $cmdbws.range("H$row").text
Hostname = $cmdbws.range("I$row").text
}
$letterwb = $xl.Workbooks.Open("F:\Laptop Issue letter\template.xlsx")
$letterws = $letterwb.Worksheets.Item("sheet1")
$letterws.Range("B1").Value2 = (Get-Date -Format yyyy-M-dd) #日期
$letterws.Range("B2").value2 = $details.EmployeeName #姓名
$letterws.Range("D9").value2 = $details.Model.Split(" ")[0] #型号
$letterws.Range("F9").value2 = $details.ServiceTag #序列号
$letterws.Range("H9").value2 = $details.FinanceNo #资产编号
$letterwb.SaveAs("F:\Laptop Issue letter\Laptop Issue_" + $details.EmployeeName + " (" + (Get-Date -Format yyyy-M-dd) + ").xlsx")
$xl.Quit()
$letterws = $null
$letterwb = $null
$cmdbws = $null
$cmdbwb = $null
$xl = $null
[GC]::Collect()
#[System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
#Remove-Variable xl
$mail = New-Item -Path 'F:\Laptop Issue letter\mail.txt' -ItemType file -Force
$firstline = 'Dear ' + $details.EmployeeName + ','
$secondline = 'You are hereby being issued a Laptop having the Service tag ' + $details.ServiceTag + ' and Finance asset no ' + $details.FinanceNo + ' for official purpose.'
$thirdline = 'Pls print following attachment 3 copies and sign then submit to IS and Admin for tracking, one copy is kept by yourself. Admin will inform security guard to allow you take the laptop in and out office.'
Add-Content -Path $mail -Value $firstline
[Environment]::NewLine | Out-File $mail -Encoding ascii -Append
Add-Content -Path $mail -Value $secondline
[Environment]::NewLine | Out-File $mail -Encoding ascii -Append
Add-Content -Path $mail -Value $thirdline
[Environment]::NewLine | Out-File $mail -Encoding ascii -Append
Add-Content -Path $mail -Value 'yours,'
上一篇:Silverlight 利用DataGrid行加载事件动态控制行列显示


下一篇:Django——邮件发送