在SQL Server 整合服務中自動擷取SharePoint的PowerPivot報表的圖表並產生PDF及發送郵件的操作！

      最近做了一個小整合應用測試，適合於對於那些不想在網站上查看相關報表而希望定時通過郵件的方式查看的使用者，特別適合相對靜態報表（不需要使用者進行互動操作），可以使用本文介紹的一種整合操作方法，主要實現思路為：

     1.首先通過SharePoint的ExcelService的Web服務自動擷取並下載SharePoint的PowerPivot報表的圖表到本機臨時檔案夾中；

     2.其次通過開源PDF組件的iTextSharp產生本地的圖表PDF；

     3.最後通過SSIS中內建的發送郵件任務發送圖表PDF到指定信箱使用者中。

      本文以PowerPivotHealthcareAudit.xlsx報表為例，主要擷取Dashboard的Sheet中的圖表，具體操作步驟如下：

      

     

 

      （一）.下載SharePoint的PowerPivotHealthcareAudit.xlsx報表的圖表的腳步任務代碼，如下：  

/*   Microsoft SQL Server Integration Services Script Task   Write scripts using Microsoft Visual C# 2008.   The ScriptMain is the entry point class of the script.*/using System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;using System.IO;using System.Net;namespace ST_1c6a297d211c4ba690ec8a0a9bd66306.csproj{    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase    {        #region VSTA generated code        enum ScriptResults        {            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure        };        #endregion        /*        The execution engine calls this method when the task executes.        To access the object model, use the Dts property. Connections, variables, events,        and logging features are available as members of the Dts property as shown in the following examples.        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;        To post a log entry, call Dts.Log("This is my log text", 999, null);        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);        To use the connections collection use something like the following:        ConnectionManager cm = Dts.Connections.Add("OLEDB");        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.                To open Help, press F1.    */        public void Main()        {            // TODO: Add your code here            ExcelService.ExcelService xlS = new ST_1c6a297d211c4ba690ec8a0a9bd66306.csproj.ExcelService.ExcelService();            xlS.Url = "http://portal.contoso.uat/sites/cockpit/_vti_bin/ExcelService.asmx";            //xlS.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;            System.Net.NetworkCredential nc = new System.Net.NetworkCredential("UserName", "Password", "contoso.uat");            xlS.Credentials = nc;            ExcelService.Status[] status;            string sessionId = xlS.OpenWorkbook(@"http://portal.contoso.uat/sites/cockpit/PowerPivot/PowerPivotHealthcareAudit.xlsx", string.Empty, string.Empty, out status);            string url = xlS.GetChartImageUrl(sessionId, null, "Chart 1", out status);            CreateXlsPng(xlS, url, "Chart 1");            url = xlS.GetChartImageUrl(sessionId, null, "Chart 2", out status);            CreateXlsPng(xlS, url, "Chart 2");            url = xlS.GetChartImageUrl(sessionId, null, "Chart 3", out status);            CreateXlsPng(xlS, url, "Chart 3");            url = xlS.GetChartImageUrl(sessionId, null, "Chart 4", out status);            CreateXlsPng(xlS, url, "Chart 4");            url = xlS.GetChartImageUrl(sessionId, null, "Chart 13", out status);            CreateXlsPng(xlS, url, "Chart 13");            status = xlS.CloseWorkbook(sessionId);            Dts.TaskResult = (int)ScriptResults.Success;        }        private void CreateXlsPng(ExcelService.ExcelService es,string url,string pngname)        {            WebRequest req = WebRequest.Create(url);            req.Credentials = System.Net.CredentialCache.DefaultCredentials;            using (FileStream output = File.Create("c:\\temp\\" +pngname +".png"))            using (WebResponse response = req.GetResponse())            using (Stream input = response.GetResponseStream())            {                byte[] buffer = new byte[1024];                int read;                while ((read = input.Read(buffer, 0, buffer.Length)) > 0)                {                    output.Write(buffer, 0, read);                }            }        }    }}

    執行完此指令碼後，將在本機c:\temp目前下產生5個圖表，具體如：

        （二）.利用開源PDF組件iTextSharp產生本地的圖表PDF的腳步任務代碼，如下：  

/*   Microsoft SQL Server Integration Services Script Task   Write scripts using Microsoft Visual C# 2008.   The ScriptMain is the entry point class of the script.*/using System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;using iTextSharp.text;using iTextSharp.text.pdf;using System.IO;namespace ST_8a0e74f918f64cac9fe6e94300fa4ccb.csproj{    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase    {        #region VSTA generated code        enum ScriptResults        {            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure        };        #endregion        /*        The execution engine calls this method when the task executes.        To access the object model, use the Dts property. Connections, variables, events,        and logging features are available as members of the Dts property as shown in the following examples.        To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;        To post a log entry, call Dts.Log("This is my log text", 999, null);        To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);        To use the connections collection use something like the following:        ConnectionManager cm = Dts.Connections.Add("OLEDB");        cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";        Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.                To open Help, press F1.    */        public void Main()        {            // TODO: Add your code here            ImageDirect();                       Dts.TaskResult = (int)ScriptResults.Success;        }        public void ImageDirect()        {            string imagePath = @"c:\temp\Chart 1.png";            string fileName = string.Empty;                       FileStream fi = System.IO.File.Create(@"c:\temp\PowerPivotHealthcareAudit.pdf");            fi.Close();             fileName = @"c:\temp\PowerPivotHealthcareAudit.pdf";                Document document = new Document();                PdfWriter writer = PdfWriter.GetInstance(document, new FileStream(fileName, FileMode.Create));                document.Open();                iTextSharp.text.Paragraph p = new iTextSharp.text.Paragraph("PowerPivotHealthcareAudit", new iTextSharp.text.Font(Font.FontFamily.HELVETICA, 22f));                p.Alignment = Element.ALIGN_CENTER;                document.Add(p);                iTextSharp.text.Image img = iTextSharp.text.Image.GetInstance(imagePath);                document.Add(img);                imagePath = @"c:\temp\Chart 2.png";                iTextSharp.text.Image img2 = iTextSharp.text.Image.GetInstance(imagePath);                document.Add(img2);                imagePath = @"c:\temp\Chart 3.png";                iTextSharp.text.Image img3 = iTextSharp.text.Image.GetInstance(imagePath);                document.Add(img3);                imagePath = @"c:\temp\Chart 4.png";                iTextSharp.text.Image img4 = iTextSharp.text.Image.GetInstance(imagePath);                document.Add(img4);                imagePath = @"c:\temp\Chart 13.png";                iTextSharp.text.Image img5 = iTextSharp.text.Image.GetInstance(imagePath);                document.Add(img5);                              document.Close();                  }    }}

     執行完此指令碼後，將在C:\temp下產生PowerPivotHealthcareAudit.PDF檔案，具體如：

         （二）.在SSIS中添加發送郵件任務，具體如： 

   執行完此郵件任務後，使用者將收到圖表PDF檔案，如：

 

    通過以上步驟，就實現了一個自動組建圖表PDF的整合服務，可以根據需要進一步擴充，以滿足實際需求。

 

本部落格為軟體人生原創，歡迎轉載，轉載請標明出處：http://www.cnblogs.com/nbpowerboy/archive/2013/05/16/3081599.html 。演繹或用於商業目的，但是必須保留本文的署名軟體人生（包含連結）。如您有任何疑問或者授權方面的協商，請給我留言。