我有下面的代码来获取电子邮件正文中表格格式的数据。
现在，我正在使所有列正确对齐。
但我需要前三列左对齐。
请告诉我如何才能做到这一点。我正在PowerShell.exe 中运行此代码
任何建议都会帮助我
使用的代码如下
代码.ps1
<# Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)
#>
$Query = "declare @todate datetime=(select dateadd(day, datediff(day,0, '2020-03-02'),'7:00'));
select ytd.site_id SiteId,ytd.site_name SiteName,ytd.Region,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(mtd.budgettarget),2)),1), '.00','') 'MTD_Budget Target',
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(MTD.IncentiveTarget),2)),1), '.00','')'MTD_Incentive Target' ,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(mtd.NetAmount),2)),1), '.00','') 'MTD_Net Amount',
round(AVG(mtd.ActualVsBudget),4) 'MTD_Actual Vs Budget',
round(AVG(mtd.ActualVsIncentive),4) 'MTD_Actual Vs Incentive' ,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(ytd.budgettarget),2)),1), '.00','') 'YTD_Budget Target',
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(ytd.IncentiveTarget),2)),1), '.00','') 'YTD_Incentive Target'
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,round(sum(ytd.NetAmount),2)),1), '.00','') 'YTD_Net Amount',
round(AVG(ytd.ActualVsBudget),4) 'YTD_Actual Vs Budget',
round(AVG(ytd.ActualVsIncentive),4) 'YTD_Actual Vs Incentive'
from
(
select b1.site_id,b1.Site_Name,b1.region,Convert(date, b1.trxdate, 102) ReportDate,--b1.Region,
b1.amount1 BudgetTarget,b1.amount1*1.02 IncentiveTarget,
sum(net_Amount) NetAmount,
((sum(net_Amount)- b1.amount1)/(b1.amount1)) ActualVsBudget ,
((sum(net_Amount)- (b1.amount1*1.02))/(b1.amount1*1.02)) ActualVsIncentive
from
(
select fbd1.date date2,fbd1.amount amount1,fcr.region, tl1.trxid,v1.trxdate,v1.amount,s1.site_id,s1.site_name,
case when v1.product_name='Refund Card' and v1.net_amount =0 and v1.amount <> 0 and isnull(v1.tax,0)=0
then v1.amount
else (v1.net_amount*(1-isnull((td1.discountpercentage/100),0)))
end net_amount
from TransactionView v1
left join trx_lines tl1 on v1.trxid = tl1.trxid and v1.lineid=tl1.LineId
left outer join TrxDiscounts td1 on td1.trxid = tl1.trxid and td1.lineid = tl1.lineid
left join site s1 on tl1.site_id = s1.site_id
left outer join fc_budget_data fbd1 on Convert(date, v1.trxdate, 102) =Convert(date, fbd1.date, 102) and fbd1.sitecode=s1.site_id
left join fc_region fcr on fcr.sitecode = s1.site_id
where trxdate >= dateadd(month,datediff(month,'1900-04-01', @ToDate)/12*12,'1900-04-01')
and v1.trxdate <= @ToDate
and s1.active_flag ='Y'
--and v1.trxid = tl1.trxid
--and s1.site_id = 1019
) b1
group by b1.site_id,b1.site_name,b1.region,b1.amount1,Convert(date, b1.trxdate, 102)
)ytd
left outer join (
select b.site_id,b.Site_Name,b.region,Convert(date, trxdate, 102) ReportDate,
b.amount1 BudgetTarget,b.amount1*1.02 IncentiveTarget,
sum(net_Amount) NetAmount,
((sum(net_Amount)-b.amount1)/b.amount1) ActualVsBudget,
((sum(net_Amount)-(b.amount1*1.02))/(b.amount1*1.02)) ActualVsIncentive
from
(
select fbd.date date2,fbd.amount amount1,fcr.region, v.trxid,trxdate,v.amount,s.site_id,s.site_name,
case when product_name='Refund Card' and net_amount =0 and v.amount <> 0 and isnull(tax,0)=0
then v.amount
else (v.net_amount*(1-isnull((td.discountpercentage/100),0)))
end net_amount
from TransactionView v
left join trx_lines tl on v.trxid = tl.trxid and v.lineid=tl.LineId
left outer join TrxDiscounts td on td.trxid = tl.trxid and td.lineid = tl.lineid
left join site s on tl.site_id = s.site_id
left outer join fc_budget_data fbd on Convert(date, trxdate, 102) =Convert(date, fbd.date, 102) and fbd.sitecode=s.site_id
left join fc_region fcr on fcr.sitecode = s.site_id
where trxdate >= dateadd(day,1,EOMONTH(@ToDate,-1)) and trxdate <= @ToDate
and s.active_flag ='Y'
--and v.trxid = tl.trxid
--and s.site_id = 1015
) b
group by b.site_id,b.site_name,b.region,b.amount1 ,Convert(date, trxdate, 102)
) MTD on mtd.site_id=ytd.site_id and mtd.reportdate=ytd.reportdate
-- left join (select distinct sitecode,site, region from fc_budget_data)fcr on mtd.site_id= fcr.SiteCode
-- and ytd.site_id = 1011
group by ytd.site_id,ytd.site_name,ytd.Region"
$MySQLAdminUserName = 'parafaitreadonly'
[Int32]$QueryTimeout=90
$MySQLAdminPassword = 'Semnox@123'
$MySQLDatabase = 'Parafait'
$MySQLHost = 'SEMNOXHOSERVER'
$ConnectionString = "server=" + $MySQLHost + ";uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
$Time = '2020-03-02 07:00'
Try {
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object System.Data.SqlClient.SqlCommand($Query, $Connection)
$Command.CommandTimeout=$QueryTimeout
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
echo "spooling to csv"
# pause
$Dataset.Tables[0] | Export-Csv -path "C:TempReportsceduleSales Vs Budget ReportTest.csv" -NoTypeInformation
}
Catch {
Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
}
Finally {
$Connection.Close()
}
echo "csv created"
# pause
$user = "itsupport.citymax@landmarkgroup.in"
$pass = ConvertTo-SecureString -String "welcome@20" -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential $user, $pass
##$body = Get-Content -Path "C:TempTestJava.csv" | Out-String
$Header = @"
<style> TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}</style>
"@
##$body = Import-Csv -Path "C:TempTestJava.csv" | ConvertTo-Html -Head $Header -Fragment | Out-String
#$summ = Import-Csv -Path 'C:TempReportsceduleSales Vs Budget ReportTest.csv'
#$body = "Please find attached following reports in the mail"+"<br>`r`n"+"<br>`r`n"+
"Sales Vs Budget Report: "+"$Time"+"<br>`r`n"+"<br>`r`n"
$body = "Please find attached following reports in the mail"+"<br>`r`n"+"<br>`r`n"+
"Sales Vs Budget Report: "+"$Time"+"<br>`r`n"+"<br>`r`n
"
$body+= Import-Csv -Path "C:TempReportsceduleSales Vs Budget ReportTest.csv"| ConvertTo-Html -Head $format | Out-String
$body= $body -replace "<table", "<table border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse;'"
$body = $body -replace "<th", "<th border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; background: #dddddd; border-collapse: collapse; padding:5px;'"
$body = $body -replace "<td", "<td border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse; padding:5px;text-align: right'"
$body=$body -replace"<td align=Left><b>SiteName</b></td>'"
$mailParam = @{
To = @('prajna.b@semnox.com')
From = "Funcity HQ<itsupport.citymax@landmarkgroup.in>"
Subject = "Parafait Schedule Report -Sales Vs Budget ReportTest"
Body = $body
SmtpServer = "smtp.office365.com"
Port = 587
Credential = $cred
Attachment = "C:TempReportsceduleSales Vs Budget ReportTest.csv"
}
[System.Net.ServicePointManager]::SecurityProtocol = 'TLS,TLS11,TLS12'
IF ((Get-Item 'C:TempReportsceduleSales Vs Budget ReportTest.csv').length -gt 0kb)
{
echo "Sending email"
Send-MailMessage @mailParam -BodyAsHtml -Usessl
# pause
}
echo "Check your mail box - I am done "
# Pause
我以前添加过它，但它在中不起作用
以下是使用的代码
<# Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)
#>
$Query = "declare @todate datetime=(select dateadd(day, datediff(day,0, '2020-03-02'),'7:00'));
select ytd.site_id SiteId,ytd.site_name SiteName,ytd.Region,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(mtd.budgettarget),2)),1), '.00','') 'MTD_Budget Target',
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(MTD.IncentiveTarget),2)),1), '.00','')'MTD_Incentive Target' ,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(mtd.NetAmount),2)),1), '.00','') 'MTD_Net Amount',
round(AVG(mtd.ActualVsBudget),4) 'MTD_Actual Vs Budget',
round(AVG(mtd.ActualVsIncentive),4) 'MTD_Actual Vs Incentive' ,
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(ytd.budgettarget),2)),1), '.00','') 'YTD_Budget Target',
REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, round(sum(ytd.IncentiveTarget),2)),1), '.00','') 'YTD_Incentive Target'
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,round(sum(ytd.NetAmount),2)),1), '.00','') 'YTD_Net Amount',
round(AVG(ytd.ActualVsBudget),4) 'YTD_Actual Vs Budget',
round(AVG(ytd.ActualVsIncentive),4) 'YTD_Actual Vs Incentive'
from
(
select b1.site_id,b1.Site_Name,b1.region,Convert(date, b1.trxdate, 102) ReportDate,--b1.Region,
b1.amount1 BudgetTarget,b1.amount1*1.02 IncentiveTarget,
sum(net_Amount) NetAmount,
((sum(net_Amount)- b1.amount1)/(b1.amount1)) ActualVsBudget ,
((sum(net_Amount)- (b1.amount1*1.02))/(b1.amount1*1.02)) ActualVsIncentive
from
(
select fbd1.date date2,fbd1.amount amount1,fcr.region, tl1.trxid,v1.trxdate,v1.amount,s1.site_id,s1.site_name,
case when v1.product_name='Refund Card' and v1.net_amount =0 and v1.amount <> 0 and isnull(v1.tax,0)=0
then v1.amount
else (v1.net_amount*(1-isnull((td1.discountpercentage/100),0)))
end net_amount
from TransactionView v1
left join trx_lines tl1 on v1.trxid = tl1.trxid and v1.lineid=tl1.LineId
left outer join TrxDiscounts td1 on td1.trxid = tl1.trxid and td1.lineid = tl1.lineid
left join site s1 on tl1.site_id = s1.site_id
left outer join fc_budget_data fbd1 on Convert(date, v1.trxdate, 102) =Convert(date, fbd1.date, 102) and fbd1.sitecode=s1.site_id
left join fc_region fcr on fcr.sitecode = s1.site_id
where trxdate >= dateadd(month,datediff(month,'1900-04-01', @ToDate)/12*12,'1900-04-01')
and v1.trxdate <= @ToDate
and s1.active_flag ='Y'
--and v1.trxid = tl1.trxid
--and s1.site_id = 1019
) b1
group by b1.site_id,b1.site_name,b1.region,b1.amount1,Convert(date, b1.trxdate, 102)
)ytd
left outer join (
select b.site_id,b.Site_Name,b.region,Convert(date, trxdate, 102) ReportDate,
b.amount1 BudgetTarget,b.amount1*1.02 IncentiveTarget,
sum(net_Amount) NetAmount,
((sum(net_Amount)-b.amount1)/b.amount1) ActualVsBudget,
((sum(net_Amount)-(b.amount1*1.02))/(b.amount1*1.02)) ActualVsIncentive
from
(
select fbd.date date2,fbd.amount amount1,fcr.region, v.trxid,trxdate,v.amount,s.site_id,s.site_name,
case when product_name='Refund Card' and net_amount =0 and v.amount <> 0 and isnull(tax,0)=0
then v.amount
else (v.net_amount*(1-isnull((td.discountpercentage/100),0)))
end net_amount
from TransactionView v
left join trx_lines tl on v.trxid = tl.trxid and v.lineid=tl.LineId
left outer join TrxDiscounts td on td.trxid = tl.trxid and td.lineid = tl.lineid
left join site s on tl.site_id = s.site_id
left outer join fc_budget_data fbd on Convert(date, trxdate, 102) =Convert(date, fbd.date, 102) and fbd.sitecode=s.site_id
left join fc_region fcr on fcr.sitecode = s.site_id
where trxdate >= dateadd(day,1,EOMONTH(@ToDate,-1)) and trxdate <= @ToDate
and s.active_flag ='Y'
--and v.trxid = tl.trxid
--and s.site_id = 1015
) b
group by b.site_id,b.site_name,b.region,b.amount1 ,Convert(date, trxdate, 102)
) MTD on mtd.site_id=ytd.site_id and mtd.reportdate=ytd.reportdate
-- left join (select distinct sitecode,site, region from fc_budget_data)fcr on mtd.site_id= fcr.SiteCode
-- and ytd.site_id = 1011
group by ytd.site_id,ytd.site_name,ytd.Region"
$MySQLAdminUserName = 'parafaitreadonly'
[Int32]$QueryTimeout=90
$MySQLAdminPassword = 'Semnox@123'
$MySQLDatabase = 'Parafait'
$MySQLHost = 'SEMNOXHOSERVER'
$ConnectionString = "server=" + $MySQLHost + ";uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase
$Time = '2020-03-02 07:00'
Try {
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object System.Data.SqlClient.SqlCommand($Query, $Connection)
$Command.CommandTimeout=$QueryTimeout
$DataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet, "data")
echo "spooling to csv"
# pause
$Dataset.Tables[0] | Export-Csv -path "C:TempReportsceduleSales Vs Budget ReportTest.csv" -NoTypeInformation
}
Catch {
Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
}
Finally {
$Connection.Close()
}
echo "csv created"
# pause
$user = "itsupport.citymax@landmarkgroup.in"
$pass = ConvertTo-SecureString -String "welcome@20" -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential $user, $pass
##$body = Get-Content -Path "C:TempTestJava.csv" | Out-String
$Header = @"
<style> TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}</style>
"@
$format = @"
<style>
body {
background-color: White;
}
table {
border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse;
}
th {
border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; background: #dddddd; border-collapse: collapse; padding:5px;
}
td {
border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse; padding:5px;
}
td:nth-child(1) {
text-align: left;
}
td:nth-child(2) {
text-align: Left;
}
td:nth-child(3) {
text-align: Left;
}
td:nth-child(4) {
text-align: Right;
}
</style>
"@
##$body = Import-Csv -Path "C:TempTestJava.csv" | ConvertTo-Html -Head $Header -Fragment | Out-String
#$summ = Import-Csv -Path 'C:TempReportsceduleSales Vs Budget ReportTest.csv'
#$body = "Please find attached following reports in the mail"+"<br>`r`n"+"<br>`r`n"+
"Sales Vs Budget Report: "+"$Time"+"<br>`r`n"+"<br>`r`n"
$body = "Please find attached following reports in the mail"+"<br>`r`n"+"<br>`r`n"+
"Sales Vs Budget Report: "+"$Time"+"<br>`r`n"+"<br>`r`n
"
$body+= Import-Csv -Path "C:TempReportsceduleSales Vs Budget ReportTest.csv"| ConvertTo-Html -Head $format | Out-String
$body= $body -replace "<table", "<table border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse;'"
$body = $body -replace "<th", "<th border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; background: #dddddd; border-collapse: collapse; padding:5px;'"
$body = $body -replace "<td", "<td border='0' cellspacing='0' cellpadding='0' style='border: 1px solid black; border-collapse: collapse; padding:5px;text-align: right'"
$body=$body -replace"<td align=Left><b>SiteName</b></td>'"
$mailParam = @{
To = @('prajna.b@semnox.com')
From = "Funcity HQ<itsupport.citymax@landmarkgroup.in>"
Subject = "Parafait Schedule Report -Sales Vs Budget ReportTest"
Body = $body
SmtpServer = "smtp.office365.com"
Port = 587
Credential = $cred
Attachment = "C:TempReportsceduleSales Vs Budget ReportTest.csv"
}
[System.Net.ServicePointManager]::SecurityProtocol = 'TLS,TLS11,TLS12'
IF ((Get-Item 'C:TempReportsceduleSales Vs Budget ReportTest.csv').length -gt 0kb)
{
echo "Sending email"
Send-MailMessage @mailParam -BodyAsHtml -Usessl
# pause
}
echo "Check your mail box - I am done "
# Pause