ECO III and Skype: Building a Graphical User-Interface and Echanging Data with Microsoft Excel (Part 2)
In the last issue we built an ECO model that stored data about Skype contacts. Contact information was imported using the Skype COM API. I showed you how to insert operations and associations into the model to make the data transfer easier. In this article we will add a graphical user interface for this model, which will not only allow you to browse the contacts, but will also export the data to Microsoft Excel. The focus of this article for the user interface lies on the ability to create master-detail relationships using .NET data grids and ECO. The data exchange with Excel will be described in detail as well, as automating Microsoft Office is one possible means to make your data interchangeable and is used quite often these days. After reading this article you will also be able to communicate with any Microsoft Office application using Delphi for .NET.
Prerequisites
Of course, you need to read part 1 of this article (see [1]), as it describes the model for the user interface we will create. Furthermore, part 1 contains the setup you need in order to reproduce the content I describe here. ECO Basics that I covered in [2] are also required. In addition, for this article you need Microsoft Excel 2003, which is part of Microsoft Office 2003. In order to access any Office application using the .NET framework, Microsoft deploys developer assemblies with Office that you need to install manually. For that, you need to run the Microsoft Office setup (e.g. use Start / Control Panel / Software / Microsoft Office … / Change) and use “Add or Remove Features”. Make sure Excel is checked and also check “Choose advanced customization of applications”. On the next form you will see a tree view in which you can expand the “Microsoft Office Excel” node. The very first node in the list is the one that needs to be installed and takes care of all the necessary assemblies being not only copied onto your hard drive, but also registers them in the Global Assembly Cache.
Fig. 1: Selecting “.NET Programmability Support” in Microsoft Office 2003 Setup for Excel.
“Birthday”
I still owe you some details about the class “Birthday”. It was never explained in part 1 how the call “DateOfBirth := myNewDate” linked an instance of “Contact” with an instance of “Birthday”. Listing 1 shows a so-called set-method with user-code. You can tell ECO to provide a method for you that lets you write custom code that is being executed whenever you assign a value to an attribute. Figure 2 shows that the attribute “DateOfBirth” has its property “Has user code” set to “True”. This will generate “Contact.set_DateOfBirth”. We can put any code in there that is supposed to be executed when we assign a value to “DateOfBirth”. In this case, we want to link the instance of “Contact” with the correct instance of “Birthday”. I.e. we need to look for an instance of “Birthday” with the correct day and month that is specified in “DateOfBirth”. .NET makes this very easy, as System.DateTime offers properties for both values. Thus, we can pass the day and the month along to the class method “getBirthday” of “Birthday” that retrieves an instance. If there is no instance being found, it is being created. This way you make sure that every contact refers to an instance of “Birthday” if the user has specified a data in his or her profile. If no date is being specified, Skype uses the year “1900”, which we check for as well. The method “Birthday.getBirthday” is self-explanatory. The only difficult thing is that a reference of the EcoSpace needs to be passed as IEcoServiceProvider. Every ECO object has a reference to the EcoSpace it belongs to, which can be accessed using “myObject.AsIObject.ServiceProvider”. This makes it very easy for “Contact.set_DateOfBirth” to dispatch a reference to the EcoSpace to “getBirthday”. As you can see, linking ECO objects does become very easy using set-methods with user-code. However, let us get started with something the user can actually see!
Fig 2: The property “Has user code” of the attribute “DateOfBirth” has to be set to “True”.
The property “Has user code” of the attribute “DateOfBirth” has to be set to “True” so that the ECO code generator creates the empty method body for us to write the code in (see listing 1). Interestingly, we set an attribute of type “DateTime” and ECO will link the corresponding instance of “Birthday” to “Contact” this way.
procedure Contact.set_DateOfBirth(
Value: System.DateTime);
var
d, m : Integer;
begin
if Value.Year <> 1900 then
begin
// User code here
Self._DateOfBirth := Value;
// -- now link the Birthday object
d := Value.Day;
m := Value.Month;
self.Birthday :=
ContactPkgUnit.Birthday.getBirthday(
self.AsIObject.ServiceProvider, d, m );
self.Birthday.Controller := self.Controller;
end;end;
Listing 1: Setting the value of an attribute using code written by the user
class function Birthday.getBirthday(
AService: IEcoServiceProvider;
ADay: Integer; AMonth: Integer): Birthday;
var
ocl: IOclService;
b: Birthday;
begin
ocl := EcoServiceHelper.GetOclService(AService);
b := ( ocl.Evaluate( 'Birthday.allInstances->' +
select((day=' + ADay.ToString() + ') and (' +
'month=' + AMonth.ToString() + '))
->first') as IObject ).
AsObject as Birthday;
// create if there is no birthay on that day yet
if not Assigned(b) then
begin
b := Birthday.Create(AService);
b.day := ADay;
b.month := AMonth;
end;
Result := b;
end;
Listing 2: Static class method that gets an instance of “Birthday” for the requested day and month; if no instance can be found, it is being created.
Designing the Winform
ECO comes with excellent design-time support for Winform applications. As a Delphi developer you might be more used to VCL forms and I agree that it takes some “getting used” to Winforms. However, this approach offers a quick way to build ECO applications. If you get more skilled using ECO and its services, you will be able to build VCL.NET applications as well. There is no design-time support for that in Borland Developer Studio 2006 and that makes it difficult for beginners.
Before you start building the user interface, make sure that your EcoSpace references all the packages of the model (see [2] how to do that) and build your application. If all is set up correctly, select “rhRoot” at the bottom of the form designer of WinForm.pas (denoted by “WinForm” in the code editor tabs) and it should be possible to select “EcoSpaceType” from a drop-down list. Make sure it is set, otherwise you will not have design-time support. “rhRoot” is a reference handle that can be considered your link to the EcoSpace. A reference handle delivers the context for your OCL expressions. If you create a new ECO Winforms application the reference handle “rhRoot” will point to the model. This is also true for “rhRoot” right now. However, we want “rhRoot” to connect to our single “Controller”-instance. This we can do in the constructor of the Winform as shown in listing 3. The class that encapsulates our Winform has a field named “FController”, which is supposed to hold a reference to our instance of “Controller”. We assign it using the helper function “getController”. Then we assign “rhRoot” to that instance using the “SetElement” method. This code is executed at run-time only. During design-time ECO will not be able to “parse” that code and thus we need to tell ECO manually that “rhRoot” is hooked up to an instance of “Controller”. Thus, set “StaticValueType” of “rhRoot” to “Controller”.
If you have multiple forms in your application, this is the way to “connect” forms with each other. It might be good practise to add a second Winform that has the selected Contact as its “rhRoot” after you read this article completely.
Fig. 3: The Winform at design-time consisting of two data grids that have a master-detail relationship
constructor TWinForm.Create;
begin
inherited Create;
//
// Required for Windows Form Designer support
//
InitializeComponent;
FEcoSpace := TSkypeUserBrowserEcoSpace.Create;
rhRoot.EcoSpace := FEcoSpace;
FEcoSpace.Active := True;
// -- get instance of Controller
FController :=
Controller.getController(FEcoSpace);
// -- assign instance as element
rhRoot.SetElement(FController);
end;
Listing 3: Using the constructor of the Winform to hook up the reference handle “rhRoot” to the controller
Handle the handles!
We have set up “rhRoot”. Now we need to be able to retrieve data from the EcoSpace. For that you need expression handles. Figure 4 shows 4 expression handles named “ehContacts”, “ehGroups”, “ehBirthdays” and “ehGroupsForContact”. The key to understanding expression handles is associating them with the correct context. E.g. drop an expression handle on the form (you will find them in the Tool Palette in Enterprise Core Objects), name it “ehContacts” and assign its “RootHandle” to “rhRoot”. This way you set the context of the handle to the instance of “Controller”. Click “…” of the property “Expression” and the expression editor will open, giving you the context “Controller”. Now you can specify an OCL query that originates from “Controller”. The expression “self.Contacts” delivers a collection of all contacts that are linked to the controller (see [2] for more information on OCL expressions).
In order to show the data in a grid, you drop a data grid on the form and set its data source property to “ehContacts”. Of course, you can customize the layout so it looks better, but the difficult job is being taken care of. The user can browse the Skype contacts that are stored in the model.
In order to get data from Skype, we need to call the import method of the controller. Drop a button on the form and double-click it to implement its Click-event. Listing 4 shows all the code you need. I think it becomes crystal clear now why we spend so much time on the model. Consider that you have a team of many developers and only few developers know the specifics how to connect to Skype, for example. This way you make sure that any developer can connect to Skype. What is even nicer is the fact that you can switch to a new version of the model whenever the Skype API might change; the client application using the model does not need a single change.
procedure TWinForm.Button1_Click(
sender: System.Object;
e: System.EventArgs);
begin
FController.collectData;
end;
Listing 4: Calling the Controller-method to get the contact data from Skype
Fig. 4: Reference handles, expression handles and currency manager handles link the user interface to your objects in the EcoSpace
Building the master-detail relationship
I admit that this will be difficult to understand at first. However, it is not more complex as doing the same thing with Delphi’s visual database components. Just as it is new terminology, it feels “strange” at first.
We want to show the groups a user belongs to. Thus, the user interface is supposed to show the groups in a second grid for a user that is selected in the first one. In my demo application I named the first grid “dtgContacts” and the second grid “dtgGroups”. You should name the components too, so it becomes easier to connect the components with each other.
First, we need the selected instance of contact in the first grid. .NET uses the so-called currency manager to point to an item that is selected inside a grid instead of using a number to refer to it. For that reason ECO offers a “CurrencyManagerHandle”-component that does exactly that: it points to the instance of a class in the EcoSpace that is selected in a visual component, a grid in this case.
Drop a currency manager handle on the form and name it “cmhContacts”. Now we have to link the user interface with the EcoSpace. This we do telling the currency manager that we bind to a data grid named “dtgContacts” by setting “BindingContext” to “dtgContacts” and by setting “RootHandle” to “ehContacts” as that expression handle contains all the data being displayed. As a result the currency manager can point to any element in “ehContacts” that is being selected in “dtgContacts”.
The next step is the one that many people do not understand at first. In order to display all the groups a contact belongs to, we have to show (look at the model!) any instance of “Group” that is in the association between “Contact” and “Group”. So we need an expression handle with “Contact” as context and the expression “self.Groups”. We get the context by setting “RootHandle” to – and this might be difficult to grasp – “cmhContacts”. But if you think about the fact that “cmhContacts” always refers to an instance of “Contact”, what else should the context be? Name the expression handle that shows the groups “ehGroups” and connect it to the second data grid. That is all it takes to set up a master-detail relationship with ECO. Run the application and see for yourself that the list of groups changes, depending on the contact being selected.
We need one more expression handle named “ehBirthdays”. It lists all the contacts that have a birthday date given and can thus be used to export a birthday calendar. Set its expression to “self.Contacts->select(b|not b.Birthday.isNull)” and its root handle to “rhRoot”.
Finally, I want to show you how to use Office Automation with Delphi for .NET and ECO.
Connecting to Microsoft Office Excel
Being able to write so-called “co-operative” applications becomes more and more important these days. Microsoft Office is one of many means to export data so that a huge number of users can read or simply import it into their own applications. Personally, I use Excel to share tabular material with other users all the time. Being able to export data in this format is a huge advantage. However, this way of “exporting data” will only work if Microsoft Office is installed on the workstation you want to export the data from. We will “automate” Excel, i.e. we actually start another process from our application which we then control completely.
Microsoft created a complete object-oriented approach to “control” any Office application. Thus, the process object that we invoke has properties and methods to create, open and modify workbooks, worksheets or any other element that Excel offers. This holds true for any other Microsoft Office Application as well.
In order to being able to use Office Automation in Delphi for .NET you need to add references to the Office assemblies to your application. With the demo application already open, you simply click on “Project / Add Reference…” in the main menu. The “COM Imports” tab contains a list with all registered type libraries. Microsoft Office Setup registers the necessary libraries for you. In order to use Excel select (see figure 5):
- OLE Automation
- Microsoft Office 11.0 Object Library
- Microsoft Excel 11 Object Library.
Then press “OK” and Delphi will create the so-called “interop assemblies” which build the bridge between COM and .NET.
Figure 5: Necessary COM imports to automate Microsoft Office Excel 2003
Basic Automation Example
Before I will show you the export method that creates a birthday list of all contacts in Skype, you need to know about some common pitfalls with Office Automation. First, you need to extend your uses-clause in the WinForm unit that gives your unit access to the automation objects.
Add “Microsoft.Office.Interop.Excel” to the uses clause as shown in Figure 6. Delphi helps you with code completion entering long namespaces like that. You will also need to add
- System.Threading,
- System.Globalization, and
- System.Reflection.
If you read official Microsoft documents you might wonder why you need to add threading- and globalization-related units. This is because of a bug that you might encounter when the language of your Office version and the language of Delphi do not match. I ran into this issue as I use a German Microsoft Office and own an English Delphi (see [3]).
Listing 5 shows a minimal code snippet that initiates automation of Microsoft Office Excel. First, we create a new instance of an ApplicationClass. As we use the Excel-namespace, Excel is being launched in the background. Setting the “Visible”-property of that instance to true will show Excel on screen. The very first line makes certain that the Office version knows which localization we want to use and that the correct assemblies can be loaded. Please see [3] for a detailed explanation as that problem still prevails.
Fig. 6: Borland Developer Studio 2006 helps with code completion to browse the automation classes
procedure TWinForm.btExcel_Click(sender: System.Object;
e: System.EventArgs);
var
excelApp : Microsoft.Office.Interop.
Excel.ApplicationClass;
Begin
// modify as advised in http://tinyurl.com/yf7xp4
System.Threading.Thread.CurrentThread.CurrentCulture :=
System.Globalization.CultureInfo.Create('en-US');
excelApp := Microsoft.Office.Interop.
Excel.ApplicationClass.Create;
excelApp.Visible := True;
end;
Listing 5: Basic code sample that starts Microsoft Office Excel
Missing.Value
There is one huge difference to standard Object Pascal usage and automating Office Applications. Let us look at listing 6 which contains the complete method to export the data to Excel. The method “Workbooks.Add” has one parameter that lets you specify a template for the workbook you want to add. However, in our demo we do not want to use a template and thus we want to leave that parameter empty. It might sound strange, but we are not allowed to simply leave out that parameter and we must pass in a reference to a System.Object (= TObject) that has the “value” of Missing.Value, which basically says that the value is not to be used and empty. “Missing” is a class in the .NET framework that “represents a missing object”. Personally, I find this very tedious to use considering the sheer number of parameters most methods have. If you want to open an existing Excel document the call of the “Open”-method looks like this:
excelApp.Workbooks.Open(‘myfile.xls’, empty, empty, … )
Figure 7 shows the total number of parameters for which you have to pass in “Missing.Value”.
Fig. 7: If default or no values are supposed to be used, a reference to Missing.Value has to be passed in.
Getting started
It sometimes is quite difficult to get started or finding out which property to use in order to get a certain thing done. However, there is a knack for almost everything in life: record a macro and look at the code in the Visual Basic editor. Whenever you record a macro using Office it generates code that you can look at in the VB editor that comes with Office. Thus you can see which classes, properties and methods are being used. Furthermore, you can record macros for certain jobs, store it in a template and run that macro from your application that uses the template.
There are also quite a lot of 3rd party components out there that help you to write less code in order to automate Office.
Exporting the Skype contacts
Just as in the simple example, we start with instantiating an “ApplicationClass” from the Excel namespace and set the languages correctly. We create a new workbook by using “Workbooks.Add” and select the first sheet of that workbook to insert the data. We then simply iterate all of the objects that the expression handle “ehBirthdays” yields. Expression handles return IObjectList instances which can be iterated, but return IObject instances that have to be cast to objects in the .NET world (see [4]). The information is then simply being put into the sheet line by line without any formatting. You might want to try to add that yourself. Try, for example, modifying the column widths, so that all the information is visible when Excel is being shown.
procedure TWinForm.btExcel_Click(
sender: System.Object; e: System.EventArgs);
var
excelApp : Microsoft.Office.Interop.
Excel.ApplicationClass;
list : IObjectList;
o: IObject;
c: Contact;
empty : TObject;
newBook: WorkBook;
newSheet : WorkSheet;
line : Integer;
begin
// modify as advised in http://tinyurl.com/yf7xp4
System.Threading.Thread.CurrentThread.CurrentCulture:=
System.Globalization.CultureInfo.Create('en-US');
empty := Missing.Value;
excelApp := Microsoft.Office.Interop.Excel.
ApplicationClass.Create;
newBook := excelApp.Workbooks.Add(empty);
newSheet:=excelApp.Worksheets['Sheet1'] as WorkSheet;
line := 1;
list := ehBirthdays.Element as IObjectList;
for o in list do
begin
c := o.AsObject as Contact;
setCell(excelApp, 'A'+line.ToString, c.handle);
setCell(excelApp, 'B'+line.ToString, c.displayName);
setCell(excelApp, 'C'+line.ToString,
c.Birthday.month.ToString);
setCell(excelApp, 'D'+line.ToString,
c.Birthday.day.ToString);
setCell(excelApp, 'E'+line.ToString,
c.DateOfBirth.Year.ToString);
Inc( line );
end;
excelApp.Visible := True;
excelApp := nil;
end;
procedure TWinForm.setCell(AExcelInstance: TObject;
ACell, AValue: String);
var
excel : Microsoft.Office.Interop.
Excel.ApplicationClass;
begin
excel := AExcelInstance as
Microsoft.Office.Interop.Excel.ApplicationClass;
excel.Range[ ACell, Missing.Value ].Value2 := AValue;
end;
Listing 6: Method that uses Office Automation to create a list of birthday dates in Microsoft Office Excel 2003
Making visual components model-aware
Our application can connect to Skype and can also export contact information from Skype. However, the user can do a lot of things wrong. E.g. it is possible to click on “Export birthdays…” when there is nothing to export. We could go for the regular approach and set the “Enabled”-property of the button to “False” initially and set it to “True” after import. However, we also needed to check if there is really data that affects the export. In addition, if we make changes to the export, we also need to make changes to the logic that enables and disables the button. That is not something we want to do. ECO has a solution built into the framework. Every Winform in an ECO Winforms Application extends the standard .NET button control so that it gets special properties that allow you to link the visual component with the model. Basically, we are able to say “If there is no content in ehBirthdays, disable the button for export.”. In order to do that – you might have guessed – we can use OCL. So, select the export button and set its “RootHandle” to “ehBirthdays”. Now we have the context for the OCL expression set to “Collection of Contacts” as “ehBirthdays” delivers a collection of “Contact”. The final step is to set “EnabledOcl” to “self->size>0” which returns “true” whenever there are more than 0 objects in the collection. ECO will take care of the fact that the button will be enabled as soon as there are objects to be exported. Again, we did not have to write a single line of code for this. We also do not have to take the complexity of the business logic into consideration – we specify a condition about a list of contacts and we do not have to take into consideration when exactly to enable or disable the button. ECO, i.e. the model, determines that for us automatically every time the content of the EcoSpace changes.
Conclusion
This article proved the statement I made in the first part of this article series: if you design the model well, you can concentrate on the user interface much easier later. We did not have to deal with any business logic in this article. All we had to do was setting up the visual components, so that they display the correct information from the model. At the same time, the model already offered the information in a manner that we could use immediately. Even the data import procedure could be abstracted of as we simply needed to make a method-call and the business logic dealt with importing and putting the information into the model. I also showed that the user interface can be controlled by the model without you having to write any code. OCL can be used not only in the model for many tasks to collect additional information or to retrieve objects from the EcoSpace, but also to make the user interface more flexible, user-friendly and less error-prone. We also had a detailed look at using Excel to export information so that we can share it with other users easily or use it for reporting.
References
- ECO III and Skype: Building a Model to Consume, Organize and Browse Skype Contacts (Part 1), Holger Flick, Software Developer Network, Magazine, Issue 91
- ASP.NET: Using ECO III to Build Maintainable Applications With Authentication and Authorization in Delphi for .NET, Holger Flick, Software Developer Network, Magazine, Issue 90
- Frequently Asked Questions regarding Office Automation: http://tinyurl.com/yf7xp4
- ECO III: Using IOclService, Holger Flick, CodeGear Developer Network, http://dn.codegear.com/article/33462