Share via

Offline and server-based Office document scenarios

  • Article

What happens if you bind host controls in a document that's part of a Microsoft Visual Studio Tools for the Microsoft Office System, Version 2.0 solution to a remote data source (such as a Microsoft SQL Server database) and then you want to take the document offline? I mean, I do this with Microsoft Office Word and Microsoft Office Excel documents all the time: I routinely insert numbers and text into Word bookmarks and Excel cells and then take these documents with me in e-mail, store them in various file shares, and so on. Surely, you should be able to persist these numbers and text strings from the remote data source into the documents when you're not connected, right?

We have a unique solution to this scenario. Every Excel and Word document that is part of a Visual Studio Tools for Office, Version 2.0 project contains a data cache that we call a data island. The purpose of the data island is to store a copy of some or all of the source data inside of the document, primarily for offline use. (But, as I'll share later in this post, there is another use for the data island— enabling data mining or data filling of documents on a server without instantiating Word or Excel on that server.)

Here's how it works: when your Visual Studio Tools for Office, Version 2.0 document is online, your view controls (such as NamedRange view controls, ListObject view controls, Bookmark view controls, and so on) can be programmatically connected to the data source through ADO.NET objects. The first time the document starts up, if you have written code to cache the data, when you save the document, the data is cached from the data source into the document. If you later open the document and the connection to the data doesn't exist, the data is reconstituted from the cache. When you later reconnect to the source data, you can programmatically send the cached data back to the data source.

The other benefit of this data caching behavior is that you have the ability to read from and write to the data island inside of the document without starting up Word or Excel. This is very advantageous in server scenarios. Excel and Word are client applications, not designed (nor supported by Microsoft) to run on servers. Instead of instantiating Word or Excel on the server and using the object model to get at the data in cell E12 or the fourth bookmark in the document, you can now access the data island using an API that we've designed just for this purpose. This separation of data management code from visual representation code makes coding easier by not forcing developers to learn the entire Office object models to manipulate the data. We go even farther by enabling document data access that doesn't require Word or Excel to be running or even to be installed on the server.

This leads to very cool scenarios. For example, you can have the users enter the data into documents as they normally do, using the Word and Excel applications and document templates with which they're most productive. Your code can store their data inputs into the data islands and then post the documents to the server. The server can then grab the data out of the data islands and either throw the documents away or modify the data in the data islands and return the documents to the users for further work on their local computers.

I'll provide the code that lets you examine and modify the data islands' contents in a later post.

Many thanks to Eric Lippert and Eric Carter for their contributions to this information.

-- Paul Cornell

-----
This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

  • Anonymous
    April 23, 2004

    I don't get what it's called new stuff. Use DDE or embedding and you can update your data without opening Word or Excel either. Today, not tomorrow. What am I missing here?

  • Anonymous
    April 25, 2004
    DDE and OLE both require the application to be running; you just might not notice it if the application's main window is not visible.

    Try using OLE or DDE on a machine that doesn't have Office installed! :-)

  • Anonymous
    April 26, 2004

    "Try using OLE or DDE on a machine that doesn't have Office installed"

    VSTO doesn't require Office either?


    Regarding the data islands, I have learnt somewhere else that we are talking new storage/streams within the current xls/doc/... document. Why not create new full fledged file formats then? What's the point of those data islands being part of Excel files? I guess what I want as a user is to make my Excel content become a data island and vice versa, not really something that leaves in separate storages from the same stream. That being said, if data islands bring an application logic, next-gen VBA macros, then I withdraw what I said above. An application logic running on top of Excel workbooks (charts please!!!) makes sense.

  • Anonymous
    April 26, 2004
    Correct; access to the data cache in VSTO-enabled Office documents does not require Office to be installed.

    I presume you are referring to Eric Carter's blog - http://weblogs.asp.net/eric_carter/

    No new file format because this has to work with Office 2003, which shipped last year. Sorry!

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2403309-environment-is-windows-application-creating

  • Anonymous
    June 18, 2009
    PingBack from http://thestoragebench.info/story.php?id=10400