Archive

Posts Tagged ‘Office’

Using Microsoft Office as a Front-end

March 4, 2014 1 comment

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:

Office lists

Before creating the solution, let’s see what the user interface will look like:

Excel

Let’s create the solution

We’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

Projects

We can add some entities to the model:

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 Access

Open Access and create a new blank database. Select SharePoint List from the External Data tab:

Access

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.

Access SharePoint

Now you can open and edit your data directly from Access:

Access Sync

Or maybe you would prefer to fill data in an automatically generated form?

Access Forms

Now let’s to do the same thing with Microsoft Excel! :)

Working with Microsoft Excel

To 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:
Lists Web

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:

Excel Sync

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.

Parameter Value

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.

Happy synchronizing!

The R&D team.

The sample source code is available for download.

November 23rd 2012 Links: Visual Studio 2012, .NET 4.5, Office 2013, IE10, Windows 8, SQL Server 2012, TFS, TypeScript, Build

November 23, 2012 Leave a comment

Here is our new entry in our link-listing series:

 

Windows 8

Windows 8 launched
Windows 8 has been released.

Windows Phone 8 launched
Windows Phone 8 has been released.

Microsoft Design Language: The newest official way to refer to ‘Metro’
Microsoft Design Language is apparently the new term for the design first known as Metro…

 

Visual Studio 2012 / .NET 4.5

Visual Studio 2012 and .NET 4.5 Launched
Visual Studio 2012 and .NET 4.5 have been released.

Visual Studio 2012 Update 1 CTP 4
Microsoft released the final CTP of Visual Studio 2012 Update 1.

Productivity Power Tools 2012
Power Tools for Visual Studio 2012.

 

Office 2013

Office 2013 reaches RTM!
Office 2013 Family product reaches RTM! (Office 2013, Lync 2013, Exchange 2013, SharePoint 2013)

 

IE10

IE10: Fast, Fluid, Perfect for Touch, and Available Now for Windows 7
IE10 available for Windows 7.

 

SQL Server 2012

Announcing Microsoft SQL Server 2012 Service Pack 1 (SP1)
Updates and new capabilities for SQL Server 2012.

 

Software Development

TypeScript
Microsoft unveiled Anders Hejlsberg’s newest creation: TypeScript.

Team Foundation Service is Released
A version of Team Foundation Server hosted on Windows Azure.

Announcing the release of the .NET Framework for Windows Phone 8
.NET on Windows Phone 8

 

Miscellaneous

Windows head Steven Sinofsky leaves Microsoft
Microsoft executive Steven Sinofsky, in charge of Windows has left the company.

Microsoft To End Windows Live Messenger Service
Windows Live Messenger Service to be retired soon.

Build
The Build conference 2012.

 

 

Cheers,

Thibault Nestor

July 24th 2012 Links: Software development, Windows Azure, Office

July 24, 2012 Leave a comment

Here’s our new entry in our link-listing series:

 

Software Development

Is the laptop doomed?

Ahaha, we’re not quite there yet, but we sure are getting there Winking smile

 

Entity Framework now Open Source

Big news: The Entity Framework source code is today being released under an open source license (Apache 2.0), and the code repository is now hosted on CodePlex (using Git).

 

Monogame

MonoGame is an Open Source implementation of the Microsoft XNA 4 Framework. Its goal is to allow XNA developers on Xbox 360, Windows & Windows Phone to port their games to the iOS, Android, Mac OS X, Linux and Windows 8 Metro.  (PlayStation Mobile development is currently in progress.)

 

Windows Azure

SOSP Paper – Windows Azure Storage: A Highly Available Cloud Storage Service with Strong Consistency

Wow, that’s interesting: the paper describes how Microsoft provisions and scales out capacity within and across data centers via storage stamps, and how the storage location service is used to manage their stamps and storage accounts. Then it focuses on the details for the three different layers of the architecture within a stamp, why these layers, what their functionality is, how they work, and the two replication engines.

A must read!

 

Office

Microsoft’s new Office: The cloud finally takes center stage

A sneak peek of Office 2013 (a.k.a. Office 15)

 

Microsoft released new Office (Customer Preview)

The new Office is available online (preview).

 

Introducing “Napa” – Office 365 Development Tools

The Visual Studio team released a preview of a new toolset called “Napa”, which is, as they say, “the easiest way to start building apps for the new Cloud App Model”.

 

Hope this helps,

Carl Anderson

March 19th 2012 Links: Windows 8, Visual Studio 11 Beta, Office 15, SQL Server, Tips

March 19, 2012 Leave a comment

Wow, this was a busy month!!

Here’s our latest batch in our link-listing series:

Windows 8

Windows 8 Consumer Preview

The Windows 8 Consumer Preview is now available!

 

Optimizing the .NET Framework Deployment Experience for Users and Developers

A great article which looks at the integrated experience that Windows 8 provides to run apps built for the .NET Framework.

 

Visual Studio 11 Beta

Welcome to the Beta of Visual Studio 11 and .NET Framework 4.5

Jason Zanders announces that Visual Studio 11 Beta, Team Foundation Server 11 Beta and .NET Framework 4.5 Beta are now available for download!

 

Introducing the New Developer Experience

In this blog post the Visual Studio Team introduces a few of the broad reaching experience improvements that they’ve delivered in Visual Studio 11.

 

Visual Studio 11 Beta Frequently Asked Questions (FAQ)

Jason Zanders answers some frequently asked questions on Visual Studio 11 Beta.

Note: Read the comments! Especially Patrcik Smacchia’s question:

-> Is there a chance that the VS team takes account of the thousands of negative feedbacks concerning greyish icons, and switch back to VSpre11 colorful icons?

To which Jason Zanders answered:

@Patrick – we have definitely heard the feedback.  as mentioned above, the beta is about getting feedback and will not reflect the final look and feel

 

Coming Soon: TFS Express

In TFS 11, Microsoft is introducing a new download of TFS, called Team Foundation Server Express which is free for individuals and teams of up to 5 users.

 

The C# 5.0 Beta Release is now available

Included in the Visual Studio 11 Beta, the C# version 5 and Visual Basic version 11 are shipped.

 

Office 15

Microsoft SharePoint 15: A 2012 release target, an app marketplace and more

Microsoft is adding new features to the next versions (Wave 15) of its SharePoint Server and SharePoint Online offerings. Mary Jo Foley takes an early look at some of them.

 

Microsoft chops Office 365 enterprise prices by 20%

Yay!! Price reductions!!

 

SQL Server

SQL Server 2012 RTM Available for Download

The new SQL Server version is available for download

 

Software Development

Envisioning the Future of UX: WebVisions NYC 2012

Some takeaways from the WebVisions NYC 2012 conference by Miao Wang

 

Cheers,

Carl Anderson

Using Office Applications As Clients

August 3, 2011 Leave a comment

Using CodeFluent Entities you can generate synchronizable lists which lets you use Office Excel (2003 and upper) and Office Access (2007 and upper) as front-end clients of your application. This feature is actually discussed in this post: Introduction to SharePoint Lists.

However, in Excel 2007 and 2010, the ability to update SharePoint lists changed in the following ways:

  • Opening a list from SharePoint with Excel 2007 or 2010 disables the "Synchronize with SharePoint" feature.
  • Existing .XLS files from Excel 2003 still load and continue to support update functionality when opened in Excel 2007 or 2010.
  • Lists that link to a SharePoint site are converted to read-only tables when you save existing files to the new Open XML Excel 2007 or 2010 file formats.

This means that the synchronization functionality still works and is still assured by Excel, however Excel 2007 and 2010 can not access it when exporting from SharePoint.

To address this issue we released a tool named the SharePoint List Synchronizer.

It opens Excel in compatibility mode since the synch feature is still available in this mode, and loads the list. Once this is done, our tool is out of the equation, it’s fully transparent and restores the same experience as in Excel 2003.

If interested, more information on the tool is also available on CodeProject as well: Restoring Two-Way Synchronization on SharePoint Lists Using Excel.

Introduction to SharePoint Lists

May 6, 2011 3 comments

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:

OfficeLists

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.

Follow

Get every new post delivered to your Inbox.

Join 52 other followers