Calendar Item Aggregates with Exchange Web Services (EWS) Managed API 2.2

[アーティクル]
09/11/2014

I love group calendars, who does not? Smile Yet when you think about it, you would need to have dedicated calendars for specific purpose and I would imagine a nightmare afterwards to track. So, what about getting around this and instead asking people to create blockers on their calendars with specific subject format, type, or even details? This way you can use EWS to aggregate results from the various calendars and display them in one place.

What’s cooking for today?

We will use EWS managed API to get a list of calendar items for a specific group of people and put it in a table. To start, you will have to download and install EWS Managed API. At the time of writing this article, the latest version is 2.2 and can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=42951. Once it’s installed, we are ready to get some code on. So go ahead and create your project. I will use Web Forms, nothing personal but I could bind quickly to a ASP.NET GridView. You will need to add the API assembly to the project from here: C:\Program Files\Microsoft\Exchange\Web Services\2.2. You will need to add a using statement for this namespace: Microsoft.Exchange.WebServices.Data.

using Microsoft.Exchange.WebServices.Data;
using System;
using System.Collections.Generic;
using System.Data;

Connect me to EWS

Since this is a Web Form, I will have my fun on Page_Load. I created a function called Populate Appointments to run the query for me. Notice that I defined the time period of the next 60 days. So logically I will get all appointments that will take place in the next 60 days for a specific group of people.

protected void Page_Load(object sender, EventArgs e)
{
//Let's create a reference to an Exchange service, I am running 2010 with SP2 in my Azure Lab
var service = new ExchangeService(ExchangeVersion.Exchange2010_SP2);
//and use Auto Discover to figure out where Exchange sits
service.AutodiscoverUrl("your@li.as", RedirectionUrlValidationCallback);
service.UseDefaultCredentials = true;
//Define the query period time span
var startDate = DateTime.Now;
var endDate = startDate.AddDays(60);
//Call the Popuolate Appoinments fuctions, which I will explain in a bit :-)
PopulateAppointments(service, GetAttendees(), GetAvailabilityOptions(),
startDate, endDate);
}

The PopulateAppointments function requires the following parameters:

Instance of the Exchange Service which we defined at the beginning of the code block
The group of people I want to query there calendars, To loosely-couple it, I created another function for you that returns the list of people called GetAttendees. It will return an IReadOnlyList of AttendeeInfo (Microsoft.Exchange.WebServices.Data.AttendeeInfo).
A list of the types of meetings/empty slots to look for. The logic is kind of twisted here, yeah I get it. I could not find a way to send a query to get current meetings of a specific person, but if you do a lookup to schedule a meeting request you will get both empty and filled slots which is what we are looking for
The time span, I used here 60 days. Note that by max you can do 62 days for a single request to EWS. I did not find this documented, I learned it when I got the following exception: (I used 90 days)

Finally, handle the redirection to validate if the callback is happening over a secure connection. I copied it from here: https://msdn.microsoft.com/en-us/library/office/dn567668(v=exchg.150).aspx

private static bool RedirectionUrlValidationCallback(string redirectionUrl)
{
// The default for the validation callback is to reject the URL.
var result = false;
// Validate the contents of the redirection URL. In this simple validation
// callback, the redirection URL is considered valid if it is using HTTPS
// to encrypt the authentication credentials.
var redirectionUri = new Uri(redirectionUrl);
if (redirectionUri.Scheme == "https")
{
result = true;
}
return result;
}

Let’s get some items

Before we can get the query to happen, let’s first look at the functions we used as parameter inputs for the PopulateAppointments function.

private AvailabilityOptions GetAvailabilityOptions()
{
//As I explained, logic is a bit twisted,
//I pretend to schedule a meeting and thereofore I ask for a time slot
//from the specifc person, and by design I would get back both free
//and busy slots with there details
var myOptions = new AvailabilityOptions
{
MeetingDuration = 30,
RequestedFreeBusyView = FreeBusyViewType.DetailedMerged
};
return myOptions;
}

private IReadOnlyList<AttendeeInfo> GetAttendees()
{
//Populate a static list of meeting attendees. This list will be the group
//of people we are querying their calendar
var attendees = new List<AttendeeInfo>
{
new AttendeeInfo("your@ali.as",
MeetingAttendeeType.Required, false),
new AttendeeInfo("another@ali.as",
MeetingAttendeeType.Required, false)
};
return attendees;
}

So far we have an Exchange Server to query; a the time period less than 62 days, and a list of people. Let’s start digging in the PopulateAppointments function.

const string ResourceName = "Resource Name";
private void PopulateAppointments(ExchangeService service,
IReadOnlyList<AttendeeInfo> attendees, AvailabilityOptions availabilityOptions,
DateTime startDate, DateTime endDate)
{
//Query Exchange for the Free and Busy timeslots from the specified list of people
var freeBusyResults =
service.GetUserAvailability(attendees, new TimeWindow(startDate, endDate),
AvailabilityData.FreeBusy, availabilityOptions);
//Being a web developer at heart, I think of a DataTable. Put some columns and
//add the needed rows, aaaand voilaa just bind
var calendarSet = new DataTable();
//The table will have one column for an Alias and then the rest of the columns will
//contain the meetings we are looking for to aggregate
calendarSet.Columns.Add(ResourceName);
//Create columns for the each day with the date as a title
for (var col = 0; col < (endDate - startDate).Days; col++)
{
calendarSet.Columns.Add(startDate.AddDays(col).ToShortDateString());
}
var i = 0;
//Let's loop the results set
foreach (var availability in freeBusyResults.AttendeesAvailability)
{
//Logically table rows will be person driven, right? ;-)
var userForecastRow = calendarSet.NewRow();
userForecastRow[ResourceName] = attendees[i].SmtpAddress;
//Let's loop the calendar items
foreach (var calendarItem in availability.CalendarEvents)
{
//Are you a null? Would you break my code?
if (calendarItem != null)
//Do you have the details I need?
if (calendarItem.Details != null)
//Do you have a subject? Remember, I am interested in meetings
//where the subject would contain specific prefix
if (!string.IsNullOrEmpty(calendarItem.Details.Subject))
{
//Do you have Prefix1 or Prefix2 in your subject?
if (calendarItem.Details.Subject.ToUpper().Replace(" ",
string.Empty).Trim().IndexOf("PREFIX1-", StringComparison.Ordinal) == 0 ||
calendarItem.Details.Subject.ToUpper().Replace(" ",
string.Empty).Trim().IndexOf("PREFIX2-", StringComparison.Ordinal) == 0)
{
//If so then let's figure where in which column it will sit aka calendar day
int colIndex = 0;
foreach (DataColumn col in calendarSet.Columns)
{
//Focus only on calendar days
if (col.ColumnName != ResourceName)
{
if (DateTime.Parse(col.ColumnName).Date >= calendarItem.StartTime &&
DateTime.Parse(col.ColumnName).Date <= calendarItem.EndTime.AddHours(-1))
{
//Are you a weekend end day?
if (DateTime.Parse(col.ColumnName).DayOfWeek == DayOfWeek.Sunday)
{
userForecastRow[colIndex] = string.Empty;
}
//Are you a weekend end day?
else if (DateTime.Parse(col.ColumnName).DayOfWeek == DayOfWeek.Saturday)
{
userForecastRow[colIndex] = string.Empty;
}
else
{
//You are the meeting I am looking for, let's add you to the calendar day column
string preSeparator = string.Empty;
if (userForecastRow[colIndex].ToString().IndexOf("-", StringComparison.Ordinal) > 0)
{
preSeparator = ", ";
}
userForecastRow[colIndex] += preSeparator + calendarItem.Details.Subject;
}
}
}
colIndex++;
}
}
}
}
i++;
//Done with this person, let's go and do it again untill we loop the list of people
calendarSet.Rows.Add(userForecastRow);
}
//Since we have the DataTable ready, let's sort it by name and bind it to the GridView :-)
var viewCalendarSet = calendarSet.DefaultView;
viewCalendarSet.Sort = ResourceName;
ForecastView.DataSource = viewCalendarSet;
ForecastView.DataBind();
}

aaand this is how it looks

Yepee Smile We can see now only the meetings that matched our prefixes for a specific group of people and a specific time period. You can take this an extra level and place some fields to allow users to select the time period, or an export button to push this GridView into an Excel sheet, etc.

Let me know if you have any questions in the comments section below. Enjoy.! Thumbs up

Comments

Anonymous
September 11, 2014
I like how you twisted the logic from not useful to very useful
Anonymous
September 11, 2014
Thanks =) It was the only way I found to get someone's calendar items without asking them specifically for permission.
Anonymous
September 12, 2014
you talk about putting this in an excel sheet. how do you put it in excel sheet from grid view?
Anonymous
September 13, 2014
There are a lot of ways to doing this. One would be using an HTMLWriter and then sending the response as an application/excel content type. Pick any of them: http://www.bing.com/search?q=export+gridview+to+excel+sheet&go=Submit&qs=n&form=QBLH&pq=export+gridview+to+excel+sheet&sc=0-10&sp=-1&sk=&cvid=6ed2b631ab52409eb2dfe52e625b8975
Anonymous
September 15, 2014
Double good double again :)
Anonymous
December 20, 2014
this was very useful and and I like bloggers who tell a real storyvery good

次の方法で共有