Using CodeFluent Entities you can generate synchronizable lists which lets you use Microsoft Office Excel (2003 and above) and Microsoft Office Access (2007 and above) as front-end clients of your application. This feature was actually already discussed in this post: Introduction to SharePoint Lists, but we’ll digg a bit further today🙂.As a reminder the global architecture is illustrated in the following figure:
Before creating the solution, let’s see what the user interface will look like:
Let’s create the solutionWe’ll create 4 projects:
- A CodeFluent Entities Model project
- A Class Library project to store the Business Object Model
- A Database project for the data
- An empty WebForm project to host the web site and some special web services
We can add some entities to the model:
Note : there should be only one key (not a composite one) of type Int (or aliases like integer, int32, etc.), with identity (automatic numbering) for the entity to support two-ways synchronization with Office apps, so don’t use Guid or anything else.Now, let’s also add two computed properties: FullName and Age, and let’s configure the following producers:
- The SQL Server Producer
- The Business Object Model (C#) Producer
- The Office Producer: “Physical Root Path” and “Target directory” must be set to the web project
Now we can build the model. After that, the last thing to do for the synchronization magic to happen is to add a special component that emulates a SharePoint website to our ASP.NET generated web site. We used to provide an ISAPI filter for this, but starting with II7, we now provide a .NET HttpModule class that does the job. So you just need to add the following piece of configuration in the web.config file for this to work:
<system.webServer> <modules runAllManagedModulesForAllRequests="true"> <add name="WssEmulator" type="CodeFluent.Runtime.Web.WssEmulator" /> </modules> <validation validateIntegratedModeConfiguration="false" /> </system.webServer>
Working with Microsoft AccessOpen Access and create a new blank database. Select SharePoint List from the External Data tab:
Enter the root URL of your CodeFluent Entities generated “Office” website. Access will then show a list of available lists. Now, choose the list(s) you want to link to.
Now you can open and edit your data directly from Access:
Or maybe you would prefer to fill data in an automatically generated form?
Now let’s to do the same thing with Microsoft Excel!🙂
Working with Microsoft ExcelTo open a list in Excel you need an .IQY file. This “internet query” file format us used by Microsoft Excel to run queries over HTTP(S). CodeFluent Entities generates a web page that contains all available lists and allows Excel to download the associated .IQY file. Launch the web project and navigate to http://localhost/en-us/lists.aspx:
Note that you can also generate .IQY files by using the template located in the “C:\Program Files (x86)\SoftFluent\CodeFluent\Modeler\Templates\OfficeServiceHost\ClientIqy” directory, created by the CodeFluent Entities installation.
Select one of them to open your list in Microsoft Excel:
If you are using Excel 2007 or above you want to read this article: Restoring Two-Way Synchronization on SharePoint Lists Using Excel.
Points of interest
- Enumerations and relations are selectable through a drop-down list (relations show the property defined as the display property of the related entity)
- By default, relations (drop-down lists in Excel) load all values from the related entity. You can change that by setting the loadMethodName attribute cfpo:loadMethodName=”LoadMethodCustom”
- Supports data validation. Excel or Access ensures that values are valid for a given type. For example you can’t write a string into an integer defined column.
- Supports CodeFluent Entities blob-type columns. They will be displayed as hyperlink in Excel. Click on one of them to download the related file/blob.
- Supports rich text as HTML. For example if you set a cell value to test (bold), the column cell will contain <strong>Test</strong>
- Supports Read-only columns (by setting the property Is Read Only to true in the model)
- Supports HTTP and HTTPS
- Supports authentication (NTLM, Kerberos, basic authentication with an extra CodeFluent Entities provided module)
- Supports offline work. Excel can save the data even if the network is unavailable.
- Create different views of the same Entity, for example one for editing data and another one for viewing data. In the first one you can set FirstName, LastName and Date of birth. In the second one, you may prefer showing the full name and the age (you could use computed properties in this case).
- Add parameters to your lists
LOAD(string name) WHERE FirstName STARTSWITH @name OR LastName STARTSWITH @name
An Excel standard dialog box will prompt you for defined parameters when you open the list from Excel. This is a nice way to filter the list. Note in this mode the list is not updatable, just read-only.
CodeFluent Entities provides out-of-the box a user friendly way to view and edit data.
Don’t forget that in Microsoft Office Excel 2007, 2010 & 2013, the ability to update the data in SharePoint lists directly from Excel is somehow deprecated. Nevertheless, you should look at SharePoint List Synchronizer to address this issue and restore this two-way sync functionality.
The R&D team.
The sample source code is available for download.
Starting with Office 2003, Microsoft introduced SharePoint Lists which allow Office client application such as Excel to manipulate lists of data provided by SharePoint. This way users can keep information in their Excel/Access tables synchronized with a list hosted on the SharePoint site. This feature is available for Excel 2003+ and Access 2007+.
CodeFluent Entities can generate Web Services – which can be hosted in IIS or WCF – which emulate a SharePoint server so that Excel and/or Access can be used to edit your data. Since it leverages a Microsoft Office built-in feature, it does not require any deployment on user’s machines. The global architecture is illustrated in the following figure:
As shown in the figure above, using the generated lists does not require SharePoint at all, it only emulates SharePoint as Office applications such as Excel and Access were built to consume Lists hosted in SharePoint. From a developer’s perspective, all server components from the data tier to the web services can be generated by CodeFluent Entities. By default each entity has a list per load method (e.g. LoadAll). Moreover, you can design extra lists by adding views on your entity. By the way, the generated classes are public partial classes, so if you need to create some very specific list, you can always write it yourself without having your code overwritten on a following generation.
Finally, since your generated lists use the business objet model to manipulate data, your business logic is still applied.
In a nutshell, generating Office synchronizable lists for Excel and Access enables end-users to edit and manipulate massive amounts of data in a familiar UI. End-users will benefit from advanced features such as Excel’s filters, sorting and pivot tables or Access’ Form View. Furthermore, it’s all based on a built-in feature which does not require to deploy any component on the client side, nor to actually have SharePoint on the server.