Figure 1: Spreadsheet with Complex Data Types

Skeptical? I didn't believe it either until I ran Office 2000 through several tests. The Microsoft Excel spreadsheet shown in Figure 1 contains several cells of data, clip art, and sounds. I've intentionally used clip art and wave files represented as OLE objects because they don't have HTML equivalents. Saving this Microsoft Excel spreadsheet as a Web page (see Figure 2) invokes the Office 2000 Save As dialog box (see Figure 3).

Figure 2: Saving a Spreadsheet as a Web Page

Figure 3: Office 2000 Save As Dialog

This Save As dialog box, common to all Office 2000 products, is the key to making Office Web publishing simple. It displays Web server locations as if they're just more folders. So saving a document as a page on a Web server is functionally equivalent to saving it as a binary file to your local drive. This makes it exceedingly simple for any Office user to publish Web content, thereby taking some of the load off Web managers.       I chose to save the spreadsheet to a Web server on our network. When I click OK, my spreadsheet is magically transformed into a browser-friendly Web page on the server. That's all there is to it: plain, simple, and to the point. No wizards need apply.       But does the HTML rendition of the document appear as it should? Surfing to the Microsoft Excel Web page I just saved reveals the answer (see Figure 4). Notice that the appearance of the Web page in Microsoft Internet Explorer is identical to the original spreadsheet. You'll be happy to know that the results are the same with all of the Office 2000 products. But what makes it even more exciting is the return trip.

Figure 4: The Data is Preserved

For the return trip you simply open or reopen a URL that points to an Office 2000 document that you (or another Office user) previously saved as a Web page. You can also open Web pages created outside Office. Starting the return trip is easy with the Office 2000 applications or with Internet Explorer. Just like FrontPage® does, Office 2000 enables the browser's Edit button. The Edit button puts this return trip one click away. If you've surfed to a Web page that you want to modify, simply click the Edit button (see Figure 5). Clicking the Edit button starts up the appropriate Office 2000 application and loads the document for editing.

Figure 5: Editing a Web Page

I am using the first beta release of Office 2000 for this review, but the results for round-tripping several documents through the Web server are surprisingly good using Internet Explorer 4.0 and even a prerelease of Internet Explorer 5.0. Having the Edit button in the browser toolbar is a simple yet key feature for Office users. It enables users to find documents with the browser, not the File dialog. How Did They Do That?       How does Microsoft round-trip Office documents? After all, the HTML standard was never intended to describe WYSISYG documents like the ones in Office. The burden of rendering the final appearance of a document is placed on the HTML consumer: a graphical Web browser in most, but not all, cases (a few people still use Lynx).       Content creators and software developers have demanded finer control of Web page rendering and greater functionality in their Web applications. This demand has driven Internet technology in innovative directions, but has resulted in a collection of complex standards, languages, browsers, protocols, and publishing tools. The creation of great Web pages today is not a far cry from application program coding. Take a look at the source of a few popular Web sites and you'll see what I mean. The complexities of Web publishing today make document round-tripping difficult indeed.       Office 2000 overcomes the difficulties of round-tripping files between Web and application by taking advantage of three open Internet technologies: HTML, Cascading Style Sheets (CSS), and Extensible Markup Language (XML). Office 2000 uses HTML for the base content of documents, CSS to specify formatting of that content, and XML to store the information that has no visual representation but is nonetheless needed by the original application to ensure a successful return trip (binary objects, properties, menu items, application-specific settings, and so on).       Generally, when an Office 2000 document is saved as a Web page, the binary objects (such as images and sounds) are replaced by a browser-friendly representation stored in a subdirectory. For instance, the ExcelExample1.htm file has image tags that point to GIF images in the subdirectory named ExcelExample1 files. In this case, the GIF images are snapshots of the Office clip art. These snapshot images were generated as part of the Save As Web Page process and are automatically optimized to load quickly while giving a true representation of the original.       If the original images (like the clip art) get replaced with Web-friendly versions, how is a document reinstantiated in the return trip to Office? Simply put, copies of the originals are also saved to the Web. The copies are saved in the same subdirectory as the Web-friendly snapshots, where they wait until needed for a return trip. Information about these originals is tracked with XML. If you look at the HTML source of an Office 2000 document that has been saved as a Web page, you'll find the XML tags that contain the application-specific properties needed to bring a document back to life in Office.       In my spreadsheet sample, when Microsoft Excel reopens the Web page version of the spreadsheet, it uses the XML to locate the original clip art and sound files. It uses the information stored in the XML tags to position and size the objects appropriately, so you end up with exactly what you started with (yes, the wave files still play).       That pretty much covers the simplest case. In other scenarios, Office 2000 takes advantage of client-side scripting to make browser-rendered documents not only look but also interact much like they would in the Office application that created them. For example, saving a PowerPoint presentation as a Web page (see Figure 6) results in an online presentation that the Web user can navigate and interact with (see Figure 7). PowerPoint generates a hefty amount of script that enables the browser to deliver the slide show. Granted, PowerPoint 97 had this feature too, but in Office 2000 the Web version can be opened in PowerPoint with a single click. Client-side Coolness with Office Web Components       Sometimes Office documents are so rich with functionality and interactivity that HTML, CSS, XML, and scripting don't quite cut it. Spreadsheets are a prime example, especially if they have pivot tables and charts. When you need a high level of data interaction on the Web, you'll want to use the Office Web components.       Office 2000 includes three special COM objects: PivotTable®, SpreadSheet, and PivotChart™. (By the time you read this, there may be more.) These Office Web components are in-process COM servers that are installed on your computer as part of Office 2000. They're implemented in a DLL that ships with the product, and you must have a valid Office 2000 license to use them. These components are most effective when you want to provide other Office 2000 users a very high level of data interactivity for their Web documents. A good example is data analysis and reporting.

Figure 8: Sample Spreadsheet

Figure 9: Interactive Web Spreadsheet

I've designed a sample spreadsheet that contains financial data (see Figure 8). The default sort is by Gross Sales. Previous versions of Microsoft Excel would indeed allow me to save this as a Web page, but I'd end up with a static HTML table. With Microsoft Excel 2000, I can click a checkbox during the Web save that enables client interactivity (see Figure 9). This tells Microsoft Excel 2000 I want users to have interactive access to the spreadsheet when they view it on the Web.       When the resulting page is loaded into Internet Explorer, the SpreadSheet component renders the data kind of like Microsoft Excel lite (see Figure 10). It provides a clean subset of the Microsoft Excel program's capabilities to interact with the data. You can even experiment on the data by editing values. It is easy to change the sort order and perform several other useful spreadsheet tasks (see Figures 11 and 12).

Figure 10: Less Filling, Tastes Great

figure 11: Sorting by Net Sales

figure 12: Results of Net Sales Sort

Similar interactivity can be achieved with the PivotTable and PivotChart Web components. These components can be used to create highly interactive graphical Web pages. My next sample uses the PivotChart and PivotTable components on one page. When the page is initially loaded, the chart and table display total sales figures (see Figure 13). If you want to view only figures for May, you simply click the dropdown list in the pivot table (see Figure 14), check May, then click OK. The PivotTable and PivotChart dynamically update and show only the data you want (see Figure 15). For true enterprise usability, the data rendered in these components can come from dynamic queries to other data sources (like SQL Server™). The information about how to reestablish the connections to the data sources is stored in the XML that is part of the Web page.

figure 13: PivotChart and PivotTable

figure 14: Using PivotTable

figure 15: Dynamically Updating Data

This functionality makes the Office 2000 Web components perfect for enabling easy interactive access to enterprise data on the Web. It couldn't be much easier. Although Office Web components allow you to deliver a high level of interactivity, they are client-centric, meaning they place certain requirements on users: they must use Internet Explorer 4.0 or 5.0, and they must have a valid installation of Office 2000. There may be times when you want to give similar functionality to users who don't have Internet Explorer or Office 2000. To do that, you can still use Office 2000; you just have to do it from the other end. Server-side Solutions with Office 2000       I have always wanted to use Office components to dynamically generate data for ASP, specifically charts. I'm a firm believer in not reinventing the wheel, and Microsoft Excel 97 has a terrific charting system. When I tried to script to the Microsoft Excel 97 chart components, I found they lacked methods to convert charts to GIF or JPG format—a must-have for Web use. In that sense, it's not easy to use the Office 97 object model to output Web-centric files. But Office 2000 is a different story. Since it treats HTML as a native format, the object model is somewhat geared toward generating browser-digestible files. In some cases, you can take advantage of this on the server side to generate content for thin clients.       Before I go on, you should be aware of a couple of potential issues when scripting to Office 2000 in ASP. For instance, if you want Microsoft Excel to generate a chart, you'll need to instantiate an Excel.Application object, as shown in the following ASP script:

Set xl = server.createobject("Excel.Application")

The Excel.Application object is implemented as an out-of-process COM server. An instance of this object can only be created if the service processing the script (in this case, Internet Information Server) has permissions to run Excel.exe. For a variety of reasons, including security and performance, the default settings of Internet Information Server (IIS) prohibit ASP scripts from creating out-of-process components.       You can change this setting by modifying a flag in the Web service metabase. This can be accomplished by executing the following ASP script in a Web that has Windows NT Challenge/Response user authentication turned on. You must be logged on as an administrator.

<html> <% 'This script will allow ASP to create 'out-of-proc COM servers. ' Get the IIsWebService Admin object Set oWebService = GetObject("IIS://LocalHost/W3svc") ' Enable AspAllowOutOfProcComponents oWebService.Put "AspAllowOutOfProcComponents", True ' Save the changed value to the metabase oWebService.SetInfo %> response.write "Done" </html>

For a complete discussion of these and other metabase settings, consult the documentation that accompanies IIS (or Personal Web Server). You should also read Don Box's House of COM column in the September 1998 issue of Microsoft Systems Journal that addresses this and other issues related to ASP and COM.       Once you've enabled out-of-process components for your Web service (and you understand the security and server-resource implications), you're ready to take advantage of some Office 2000 capabilities to generate Web content. One way to do that is by using Microsoft Excel to create a chart on the Web.       For this ASP script to function properly, you must add references to the Microsoft Excel object model and the Scripting object model to the global.asa file. Note that the full path and file name is required so ASP can locate the type libraries for these components. You may need to update these paths in global.asa depending on the location of the files on your system. global.asa

<!-- METADATA TYPE="TypeLib" FILE="F:\Program Files\Microsoft Office\Office\excel9.olb" FILE="F:\WINNT\System32\scrrun.dll" -->

By referencing these type libraries, you can use various Microsoft Excel constants by name, resulting in cleaner code. Here's a rundown (in English) of what the chart-generating ASP script does (the code is shown in Figure 16): Create an instance of the Excel.Application object Create an instance of Scripting.FileSystemObject Use the Scripting.FileSystemObject to delete the previous chart (if it exists) Add a new workbook to the Microsoft Excel object Add some test data to the workbook Add a new chart to the workbook Set the data source and style of the chart Save the chart as HTML (which generates a GIF image of the chart) Close the Microsoft Excel workbook Close Microsoft Excel Redirect the browser to the generated HTML file       You'll need to modify the file name variable to point to the physical location of the Web you're running this on.       Once you have everything set up properly, point your browser at GenerateChartUsingExcel.asp to have Office 2000 generate a nifty chart (see Figure 17). If you're using Internet Explorer 4.0 or later, you'll notice a row of tabs across the bottom that give this Web page a spreadsheet feel. You can switch to Sheet1 to see the data that's rendered in the chart.

figure 17: ASP-generated Microsoft Excel Chart in Office 2000

Several files result from the execution of this ASP script. The file to which I redirected the browser, charttest.htm, and all the other files reside in the subdirectory charttest files. In this directory you'll find, among several other files, image001.gif. This is the snapshot in GIF format that was created to represent the chart in the browser (see Figure 18). figure 18: charttest Files       I've intentionally kept this example extremely simple (you'll have enough challenges the first time around). If you want to make it truly vanilla, then write a simple HTML page that contains an image link directly to the chart's GIF file. Once you have that working, you'll be able to do more complex ASP pages involving Office 2000. For example, have Microsoft Excel load a database query and generate a chart from that data. Just remember that running out-of-process COM servers like Microsoft Excel can be a bit expensive when it comes to server resources. But it's a useful way to build upon some Office 2000 features in thin-client scenarios.       If you plan to get intimate with Office 2000 (through scripting like I've shown here or with Visual Basic®), you'll quickly discover that its object model is much like a first date. You know what you want, but at first you won't know how to get it. It's only fair to share a productivity trick with you. A Productivity Trick       Today's object models are a lot like home-improvement warehouses. You're going to spend time looking around to find what you need. When writing code that uses complex object-oriented systems like Office 2000, you'll often research and experiment to get the results you want. I call this a research curve, not a learning curve, because you won't retain much of the information you find—just like you don't remember which aisle has extension cords at your local megastore. This doesn't mean you're a weak developer; it means you're human. It's not only difficult, but generally a waste of cycles to try to remember all the details.       You'll be happy to know there is a clever way to bypass the research curve when using the Office 2000 object model in your applications. This trick can be applied when you're writing Visual Basic applications or ASP scripts that use Office 2000. You can even use this technique with Office 97.

figure 19: Macro-recorded Chart

Review the ASP script I used to generate my chart. You'll notice the code calls several methods of the Excel.Application object. If you think I spent a lot of time researching the Microsoft Excel object model to generate that code, you're wrong. I simply turned on the Microsoft Excel macro recorder and created a chart by hand (see Figure 19). Microsoft Excel tracked what I did and generated Visual Basic for Applications code that it stored as a macro. I referred to this code when I created the ASP script for chart generation (see Figure 20).

Figure 20: Macro Code

You can't simply copy and paste the Visual Basic for Applications macro into an ASP script, but the syntax is very similar. Using the macro recorder to map interactive work to the object model can save a lot of time. You can apply this technique when coding COM-aware languages to learn about the object model. It's not applicable in all cases, but it can be a great help in getting over the research curve. I wish more products implemented scripting object models and macro recorders. If you have an idea for a macro recorder I can use for shopping at my local hardware megastore, drop me a line! Lighten Your Load with Office 2000 Developer       Office 2000 Developer was not available at the time of this writing, but it's too cool not to mention. A key design goal of the developer edition is to help alleviate requirements on MIS and Web staffers. In other words, allow Patti to stay focused on Web design rather than constantly helping Biff format his division's home page. Microsoft is reaching that goal by giving Office developers the power to fully customize Office 2000 through an open object model and the adoption of a common implementation of Visual Basic for Applications across all the Office products. Office 2000 Developer will be the most programmable Office version to date. You can read more about some of the new features at http://msdn.microsoft.com/officedev/.       At CodeMarine (http://www.codemarine.com), we make our living by using COM. For us, the most exciting feature of Office 2000 Developer is the COM Add-in designer. This tool will allow you to create and debug components that extend all of the Office 2000 applications. You won't have to learn a new programming language because you can write them in the native tongue of Office 2000, Visual Basic for Applications 6.0. You can now encapsulate your contributions to Office productivity in simple, interfaced chunks of functionality. There will even be templates that Visual Studio® developers can use to create Office 2000 add-ins. Of course, as I've said before, if something is easy to do, then by default it's easy to do poorly. So if you plan to develop COM add-ins for Office 2000, you owe it to yourself to learn about COM.       I didn't have a chance to tinker with COM add-ins for this article, but I can think of several potential applications: a wizard that helps users connect to the company database, a toolbar that allows users to retrieve contact information, a menu item that publishes a document to the local knowledge base, a dialog that queries the accounting system. Web developers can also use Office 2000 components by creating plug-in logic for their content publishers. Imagine an add-in that automatically formats Web documents to your company's standards. Tools like this further enable users to get involved in the Web publishing process and ease the strain on MIS. Ship It       I wasn't asked to write the definitive guide on Office 2000. I haven't covered several of its Internet-related features: Web discussions, Web subscriptions, Web notifications, online meetings, presentation broadcasts, and the Office 2000 Web Server Extensions. Some of the other new features that will maximize the productivity of Office users include: collect and paste, personalized menus, personalized toolbars, intelligent install, install on demand, self- repairing applications, euro currency support, and multilingual autocorrect.       I hope I've effectively demonstrated some ways you can use Office 2000 in your Web applications. Whether you want dynamic content for client interaction on the intranet or server-side functionality for thin clients on the Internet, Office 2000 round-trip support for HTML will help make Web-enabled collaboration and information sharing a reality in your organization. Plus, you'll be able to easily extend the platform to make life easier for you and your users with the COM technology in Office 2000 Developer.

From the December 1998 issue of Microsoft Interactive Developer.