A: 報告長官, 統計報表已寫好上線, 請每天登錄至xxx看報表
長官: ...
===== 重來一遍, 你應該做的是... ===============================================
A: 報告長官, 統計報表已寫好上線, 每天早上9點會自動pdf到您的信箱
長官: Good
===== 我是分隔線 ==========================================================
Tableau除了提供 Server平台, 登入後即可調閱Desktop設計完成的統計報表
也提供了訂閱的功能(時間, 頻率可自定)
屆時, Server會自動寄發報表的圖片, 使用者最終仍需登人Server查看完整的報表
現有個需求, 使用者每天早上9點要收到pdf格式的報表, 作法如下:
技能: windows排程 + power shell + csv + xml
===== list.csv: 收件者清單, 如下: =======================================
Rep_Name,Rep_ID,Email
第一名,1,de1name@gmail.com.tw
王一二,2,wangonetwo@gmail.com.tw
===== config.xml: 此次需求的設定檔, 如下 =======================================
<root>
<!--This is the absolute path of the logfile-->
<logfile>c:\log.txt</logfile>
<!--This is the .csv file containing our email address and filter options-->
<csvfile>c:\list.csv</csvfile>
<tableau>
<!--This is the server that we are downloading the files from-->
<server>1.2.3.4</server>
<!--This is the tableau server user name and password so we can login to the server to get the view we want to distribute-->
<username>iamadmin</username>
<password>123456</password>
<!--This is the URL of the tableau view we want. Right now it's hard coded in the script to pull a pdf.-->
<!--It is also possible to pull the file as .csv or .png -->
<view_url>Report/Sales</view_url>
<!--The .ps1 script will append the file with the filter term and '.pdf' so we don't need to add it here-->
<saved_filename>c:\Sales</saved_filename>
</tableau>
<mail>
<!--This is your smtp mail server.-->
<smtp_server>smtp.ggmail.com.tw</smtp_server>
<!--smtp port number goes here. If SSL is enabled use port 587 instead of port 25-->
<smtp_server_port>25</smtp_server_port>
<!--This allows us to enable or disable SSL. (SSL must be enabled for gmail accounts)-->
<enable_ssl>0</enable_ssl>
<message>
<!--This populates our from field. NOTE: Depending on your permissions this may be overwritten to your user name.-->
<from>iamadmin@gmail.com.tw</from>
<!--Our subject and body-->
<subject>Tableau 測試 自動匯出PDF 發mail</subject>
<body>You Got It !</body>
</message>
</mail>
</root>
===== test.ps1: powershell 執行檔 =======================================
param ([string] $config_path = $('C:\..\..\Documents\Script\config.xml'))
#used for debugging. Should be passed in as a param above
#$config_path = 'C:\..\..\Documents\Script\config.xml'
#This could be set to Continue, but will likely just repeat the same error message for every line in your .csv file if there is a problem.
$ErrorActionPreference = "Stop"
[xml]$config_xml = Get-Content $config_path
#Setting all the variables
$logfilepath = $config_xml.selectsinglenode('/root/logfile')."#text"
$server = $config_xml.selectsinglenode('/root/tableau/server')."#text"
$username = $config_xml.selectsinglenode('/root/tableau/username')."#text"
$password = $config_xml.selectsinglenode('/root/tableau/password')."#text"
$orig_view_url = $config_xml.selectsinglenode('/root/tableau/view_url')."#text"
$save_file = $config_xml.selectsinglenode('/root/tableau/saved_filename')."#text"
$smtp_server = $config_xml.selectsinglenode('/root/mail/smtp_server')."#text"
$smtp_server_port = $config_xml.selectsinglenode('/root/mail/smtp_server_port')."#text"
$mail_username = $config_xml.selectsinglenode('/root/mail/mail_username')."#text"
$mail_password = $config_xml.selectsinglenode('/root/mail/mail_password')."#text"
$message_from = $config_xml.selectsinglenode('/root/mail/message/from')."#text"
$message_subject = $config_xml.selectsinglenode('/root/mail/message/subject')."#text"
$message_body = $config_xml.selectsinglenode('/root/mail/message/body')."#text"
$csvpath = $config_xml.selectsinglenode('/root/csvfile')."#text"
$csvfile = import-csv -path $csvpath
$view_url = $orig_view_url
foreach ($line in $csvfile)
{
#Here is where we add our filter criteria and format our url to request a pdf.
$view_url = $orig_view_url + ".pdf?Show=Show&FilterName=$($line.Rep_ID)"
#Here we define who we are sending the message to
$message_to = $line.Email
$saved_file = "$($save_file)_$($line.Rep_ID).pdf"
function write-log ([string]$logtext)
{
"$(get-date -f 'yyyy-MM-dd hh:mm:ss'): $logtext" >> $logfilepath
}
write-log("Execution Started")
#you will need to add the path variable to
tabcmd login -s $server -u $username -p $password
#Getting the view and saving as pdf.
tabcmd get "$view_url" -f "$saved_file"
write-log("Saved $saved_file")
#Creating a Mail object
$msg = new-object Net.Mail.MailMessage
$attach = new-object Net.Mail.Attachment("$saved_file")
#Creating SMTP server object
$smtp = new-object Net.Mail.SmtpClient($smtp_server,$smtp_server_port)
$smtp.Credentials = New-Object System.Net.NetworkCredential;
$smtp.Timeout = 1000000
if ($config_xml.selectsinglenode('/root/mail/enable_ssl')."#text" -eq 1)
{$smtp.enablessl = $true}
#Email structure
$msg.From = "$message_from"
$msg.To.Add("$message_to")
$msg.subject = "$message_subject"
$msg.body = "$message_body"
$msg.Attachments.add($attach)
#Sending email
$smtp.Send($msg)
write-log("Email Successfully sent.")
#cleans up file locks on downloaded file
$attach.dispose()
$msg.dispose()
write-log("Cleanup successfull")
write-log("Subscription Complete")
"Process Complete"
}
將windows排程設定於早上8點執行test.ps1
完成! 收工!
參考連結: Automated PDF Email Distribution Of Tableau Views Using PowerShell And Tabcmd