Process HTML table by HTMLAgility Pack

Ocassionaly I need to deal with data inside a html table. For example, a report generated from Salesforce: though it is in ".xls" format, it is actually a html file. From what I tried, HTMLAgility pack can do quite a lot jobs for you, as demonstrated below.

Firstly I need a report from Salesforce. To do so, I log into my developer account, and navigate to Reports -> Account and Contact Report. From here I select New Accounts report, and press Export Details button to save the report into my local drive.

After save the report, when we open the report, an error message pops up and tells us the file doesn't look like an Excel file.

Ignore the error by pressing Yes button, the file is opened, and everything seems to be fine.

Now close the file and open it by your favorate text editor. Now you see it is actually a html file.

To import such a file by SSIS is not a simple task, as the file cannot be used as an Excel Data Source. What I usually did, is using HTMLAgility  pack in C# to generate the data source.

As usual, the coding task itself is simple, understanding how the HTMLAgility pack deals with the document object is important. Basically it will treat every html tag as a node, then drill down to the lowest child node. After understand this point, it is just a matter of dealing exceptions, for instance, we do not want to process a report with column header only.  Here is the example of using HTMLAgility pack when I need to import Salesforce report

       string filepath = @"D:\sforcerpt\report1458540550328.xls";  
       if (File.Exists(filepath))  
       {  
         HtmlDocument doc = new HtmlDocument();  
         doc.Load(filepath);  
         DataTable dt = new DataTable();  
         foreach (HtmlNode n in doc.DocumentNode.ChildNodes)  
         {  
           if (n.Name == "table" && n.HasChildNodes && n.ChildNodes.Where(x => x.Name == "tr").Count() >= 2)  
           {  
             int NbrOfRows = n.ChildNodes.Where(x => x.Name == "tr").Count();  
             //column headers  
             HtmlNode headerRow = n.SelectSingleNode("/table[1]/tr[1]");  
             foreach (HtmlNode header in headerRow.ChildNodes)  
             {  
               dt.Columns.Add(header.InnerText);  
             }  
             //content rows  
             for (int i = 2; i <= NbrOfRows; i++)  
             {  
               string path = "/table[1]/tr[" + i.ToString() + "]";  
               HtmlNode contentRow = n.SelectSingleNode(path);  
               DataRow dr = dt.NewRow();  
               for (int j = 1; j <= contentRow.ChildNodes.Count; j++)  
               {  
                 dr[j - 1] = contentRow.ChildNodes[j - 1].InnerText;  
               }  
               dt.Rows.Add(dr);  
             }  
           }  
         }  
         foreach (DataRow r in dt.Rows)  
         {  
           Console.WriteLine("Account ID: {0}; Account Name: {1}", r["Account ID"].ToString(), r["Account Name"].ToString());  
         }  
         Console.ReadLine();  
       }  

Control Number of Rows per Page in SSRS

This is just a quick tour to show you how to control page breaks in SSRS report.

Sometimes you may want to control number of rows on each page of the report, for example, you want to put 10 rows on each page if the rendering extension is pdf.  To do that, you can work it out by using TSql, or simply you can just use SSRS to do it.

Now assume we have a query like below, and we want to control number of rows per page on the report.

 use staging  
 go  
 ;with cte as  
 (  
      select 1 as RowNbr  
      union all  
      select RowNbr + 1  
      from cte  
      where RowNbr < 50  
 )  
 select RowNbr from cte  

Firstly let’s get a blank report ready by adding data source and dataset.

 

Now add a parameter “RowNbr” to let user input number of rows needs to be displayed on each reporting page.

 

Next drop a table onto the report, and drag the dataset column into the table and remove empty columns. Your report now should be like demonstration below

 

Now in SSDT, go to the lower panel, under the “Row Groups” section, right click “Details” entry and click “Group Properties”. In the pop up window, navigate to the “Page Breaks” section, check the checkbox “Between each instance of a group”, and then close the pop up window.

After close the pop up window, leave the “Details” entry in selected status, go to the “Properties” window (which usually docked at the bottom-right corner, if you cannot see it on the IDE, go to the menu bar View – Properties Window). What we need to do now, is expand the “Page Break” under the “Group” section, and change the value of the “Disabled” property.

Expand the dropdown box of the “Disabled” property and select entry “<Expression…>”. On the expression editor window, input expression

 =IIF(Fields!RowNbr.Value mod Parameters!RowNbr.Value = 1, false, true)  

This basically tells the report after how many rows (controlled by report parameter) we would like page break to occur.

So that is all. Time to run the report and input number of rows you want to put on each page. Quite simple, isn’t it? :P

Access Office 365 Mail Box by Exchange Service Managed API

So here is the requirement from clinet: We are required to monitor several Office 365 mail boxes, and if the specific email is found, we need to do some follow up activities, e.g. re-distribute the email to a nominated user.

To access the Office 365 mail box, we can use Exchange Service Managed API. In the old way we can use method listed at here. However now we can use a much easier way to handle the request, as now Office 365 provides a direct endpoint for EWS Managed API. Below is a quick demonstration of how to access Office 365 mail box.

The first step, and the only step to prepare the project, is to reference the EWS API in your project. The API can be referenced into your project from nuget directly. Or alternatively you can obtain it from Microsoft download centre. If you download the API from Microsoft download centre, you need to manually reference the API in your project. The dll can be found at “C:\Program Files (x86)\Microsoft\Exchange\Web Services\{version no.}\Microsoft.Exchange.WebServices.dll”.

Now time to start coding. Firstly we need to declare the endpoint for the EWS API.

       string emailaddr = "{o365 account}";  
       string password = "{o365 password}";  
       ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2013_SP1);  
       service.Credentials = new WebCredentials(emailaddr, password);  
       Uri u = new Uri(@"https://outlook.office365.com/EWS/Exchange.asmx");  
       service.Url = u;        

To make things simple, assume we are going to search unread emails in the inbox folder. So we create a search view for the folder

       Folder inbox = Folder.Bind(service, WellKnownFolderName.Inbox);  
       SearchFilter filter = new SearchFilter.SearchFilterCollection(LogicalOperator.And, new SearchFilter.IsEqualTo(EmailMessageSchema.IsRead, false));  
       ItemView view = new ItemView(10);  

Now there is a small trouble: we need to find the item ID from FindItems call, then we use GetItem call to get item contents.

       FindItemsResults<Item> results = service.FindItems(WellKnownFolderName.Inbox, filter, view);  
       ServiceResponseCollection<GetItemResponse> responses = service.BindToItems(results.Select(i => i.Id)  
         , new PropertySet(BasePropertySet.FirstClassProperties, EmailMessageSchema.From, EmailMessageSchema.ToRecipients));  
       foreach (var item in responses)  
       {  
         EmailAddress fromaddress = (EmailAddress)item.Item[EmailMessageSchema.From];  
         EmailAddressCollection to = (EmailAddressCollection)item.Item[EmailMessageSchema.ToRecipients];  
         string toaddress = string.Empty;  
         foreach (EmailAddress a in to)  
         {  
           toaddress += a.Address + "; ";  
         }  
         Console.WriteLine("From: {0}", fromaddress.Address);  
         Console.WriteLine("To: {0}", toaddress);  
         Console.WriteLine("Subject: {0}", item.Item.Subject);  
         Console.WriteLine("Body:{0}", item.Item.Body == null ? string.Empty : item.Item.Body.Text);  
         Console.WriteLine();  
       }  
       Console.ReadLine();  

So that is all, quick and simple. Below is the result from above code