In Chapter 13, "Word Processing on the Web," you learned how to set up your word processor as a Web browser helper application. In this chapter, you add to that knowledge and learn how to set up your Intranet so that customers can retrieve spreadsheet data files using their Web browser and automatically open the files in their favorite spreadsheet software for revision, what-if analysis, recalculation, graphing, and more.
I focus on examples that use Excel and Explorer, Mosaic, and Netscape. If your organization uses other spreadsheet packages, you'll still be able to easily apply the ideas, if not the step-by-step instructions, in this chapter. Indeed, you can apply the ideas from this chapter and Chapter 12 to almost any computer program in the office.
Later in the chapter, I touch on the issue of portability of spreadsheet data files from one package to another. The chapter ends with a discussion of how spreadsheets can be put to great advantage on the Intranet and demonstrates some sample applications.
One of the reasons for the great success of Microsoft Windows is how well it integrates application software like spreadsheets into the desktop environment, enabling the user to start up any spreadsheet with a mouse click.
Integrating your spreadsheet in your Intranet is, in a sense, creating a new graphical interface for users based on your Web browser. Doing so allows you to tie in links to your everyday tools, like your spreadsheet, and provide a new interface with a common look and feel that's both easy and fun to use.
More importantly, you can use your Web server, HTML, your spreadsheet package, and other tools to put together shareable data warehouses of spreadsheet data files. Such libraries can be complete with searchable indices and point-and-click access through your Web browser. Locating and displaying a spreadsheet data file is no longer a process of finding a file, and then starting up your spreadsheet to read or use it. Using a Web browser, your customer can instead click a Web hyperlink and have the spreadsheet fire up with a copy of the data file loaded, all in one simple process. After the spreadsheet program has loaded the file, your customers have all the capabilities of the spreadsheet program at their disposal; they can change the data, recalculate it, graph it, print it, save it, and so on.
As with commercial groupware packages like Lotus Notes (see Chapter 28, "Collaboration on Your Intranet"), expensive data warehouse software packages are available, but you can easily replicate many of their features in your Intranet. Moreover, you can do so at a substantially lower cost and without requiring users to learn to use yet another new software package. Because they'll be using their familiar Web browser as the interface to your homegrown data warehouse and their everyday spreadsheet package to examine and manipulate the stored data, you need not purchase another package. In fact, and I realize I'm repeating myself here, one of the greatest things about the Intranet is that the Web browser is so inexpensive to deploy (Mosaic and Explorer are free).
You can store company sales or production data, for example, on your Web server in spreadsheet format, complete with formulas and macro commands. Properly set up, your Web server can provide those data files when users select hyperlinks pointing to them. Web browsers can then take the data and hand it off to your customers' spreadsheet package, which they can use to play with the numbers for forecasting or preparing presentation graphics.
Even if you use your Intranet exclusively to serve spreadsheet data files, you may well spend less money on hardware and software to do so than you would spend in buying a commercial data warehouse package. If your Web is already serving other purposes, a few minutes' work can set it up to function as an essentially free data warehouse. Although these commercial packages have features that you won't be able to replicate in your Intranet, the price of implementing those features you can replicate is certainly right, and you may find that you're able to replicate enough of these features to make it unnecessary to buy a data warehouse package.
You'll recall from earlier chapters the terms helper application and external viewers to refer to computer programs that your Web browser uses when it can't directly display a type of data that you have downloaded. In this chapter, your spreadsheet package is treated as a helper application. However, to avoid pointless repetition here in the fundamental setup and configuration of helper applications, I'll often refer you to Chapter 12, "MIME and Helper Applications," and Chapter 13, "Word Processing on the Web." The information in those chapters is all that you need to configure your Intranet to serve spreadsheets.
Regardless of which spreadsheet package you use, your first step in setting up your spreadsheet as a helper application is to configure your Web server to know about your spreadsheet's data files. With small differences, as are noted in context, the instructions in this section apply to any spreadsheet, and even to any word processor (as discussed in Chapter 13).
As you learned in Chapter 12, Web servers use the MIME mechanism to identify documents according to their MIME data type/subtype. Remember that the MIME mechanism divides data into a relatively small handful of data types, with each type further subdivided into subtypes. Spreadsheet data files fall into the application data type.
You also learned in Chapter 12 how Web
servers use filename extensions to map data files on the server
to a MIME data type/subtype. You associate the filename extensions
with MIME types/subtypes in the Windows NT Registry on the Web
server, if you are using IIS, or in a configuration text file,
if you are using some other Windows NT Web servers.
| Note |
Please refer to the section titled "Editing the Registry MIME Map" in Chapter 13 for detailed information about configuring IIS. The concepts described in that section for word processors can be applied to spreadsheets as well. |
Assuming that you've completed the setup of your Web server to properly identify and serve your spreadsheet data files, your next step is to populate your server with some data files for your spreadsheet program. The Explorer in Windows NT 4 makes this task easy. All you have to do is share the drive or directory on the Web server, and map the drive or directory on the computer that you want to copy the files from (again using Explorer or Network Neighborhood).
To share a folder in NT, just right-click it in Explorer. That opens a context-sensitive menu. Slide the mouse down to the item called Properties at the bottom. Click the Sharing tab, and you will see a dialog very similar to one in Figure 14.1. Just click the Share As radio button to share the drive. The default share name is probably fine (in the context of this discussion), and you don't have to get into the security options (by clicking the Permissions button in this example) unless you feel the need to do so.
Figure 14.1: Sharing a folder in the Windows NT Explorer is as easy as 1-2-3.
Windows often enables you to do things more than one way. The context-sensitive menu from the preceding paragraph also includes an item that takes you directly to the Sharing tab. Furthermore, you can go to the Sharing tab from the Explorer menubar by choosing File | Properties | Sharing (assuming you have already selected the given file or folder in the lower left pane window).
You also can copy files through shared network drives if your Web server is in a separate domain from the data files, unless your system administrators have chosen not to configure a trust relationship between the domains for security reasons. If you have no trust relationships between domains, regardless of whether the Web server is located an inconvenient physical distance away, you can fall back to the traditional UNIX method of copying files on the network: FTP. Consult Chapter 9, "Adding FTP and Gopher Services," for information about running the IIS FTP service and then you can happily copy files to your server using an FTP client program, such as CuteFTP included on the CD-ROM with this book. Whatever method you use, you should put the files in a dedicated directory on your Web server for your particular spreadsheet package.
Next, you must create some sort of HTML listing of the data files. This listing can be as simple or as complex as you like; setting up hyperlinks to spreadsheet data files is exactly the same as setting up links to other kinds of documents. A simple HTML listing, using Microsoft Excel as an example, might be something like the following (adapted in seconds from the code in Chapter 13):
Listing 14.1. This HTML code provides links to spreadsheet
files.
<HTML> <HEAD> <TITLE>Excel Spreadsheets</TITLE> </HEAD> <BODY> <H1>Microsoft Excel Spreadsheets</H1> This directory contains a set of Excel spreadsheets. Just click on one to open it up in Excel. <UL> <LI><A HREF="qrtrly_sales.xls">Spreadsheet 1</A>, Last Quarter's Sales</LI> <LI><A HREF="qrtrly_prod.xls">Spreadsheet 2</A>, Last Quarter's Production</LI> <LI><A HREF="cpi_forecast.xls">Spreadsheet 3</A>, Consumer Price Index Forecasts</LI> </UL> If you haven't already done so, you must set up your Web browser to understand Excel spreadsheet data files. <A HREF="excel_setup.html">Here are instructions.</A> </BODY> </HTML>
Now that you have set up your Web server to know about spreadsheet data files and populated it with some actual data files from the spreadsheet program, your next step is to set up Excel as a helper application. The concept of setting up helper applications for Web browsers is a general one, but the steps for going about it differ depending on the Web browser you're using. Please see the section titled "Setting Up Microsoft Word as a Helper Application on the Client" in Chapter 13 for detailed information about how to accomplish this step in both Mosaic and Netscape. If you're using another browser, you may need to take a look at its documentation to find out how to accomplish this step.
Just keep in mind as you go that Excel uses .xls as the default extension for spreadsheet documents. You should also add .xlm to the MIME map to cover Excel macro spreadsheets that your customers have written or want to retrieve. Further, .xcl and .xlw are also widely used.
There is no requirement that every client machine on a Windows NT network be a pc. Independent of the Intranet, Windows NT 4 Server provides excellent connectivity to Macintosh computers using the AppleTalk protocol. (Obviously, to take advantage of the Intranet, a Macintosh will need to have its own TCP/IP software loaded in addition to, or instead of, AppleTalk.) If you have Macintosh users in your organization, you may need to provide them with Intranet access to spreadsheet documents. After all, once you've gone to the effort of setting up the server, you may as well get the most benefit out of it by letting everyone participate.
Macintosh users will find the process of setting up Excel or Lotus 1-2-3 as a viewer/helper application virtually identical to the process for Windows pcs. The only difference is in specifying the folder and executable names for the spreadsheet program. You can use the Browse process, just as in Windows, with the browser displaying Mac folder names in place of pc directory names.
You should be able to use the general principles described in this chapter to set up other spreadsheet packages as helper applications. As each of the preceding sections has shown, the major steps in setting up a program as a helper application are as follows:
Because your organization may use multiple spreadsheet packages, you should anticipate your customers' needs for data file portability. This section discusses two options:
Your organization may use more than just a single spreadsheet
program, and, if so, you're probably wondering how you make the
various data files portable. Like many word processors, many spreadsheet
packages can read data files in other spreadsheet programs' native
formats. Excel, for example, can directly read 1-2-3 files, and
1-2-3 can read Excel files. As a result, if your company uses
multiple spreadsheet packages, you can share your data among them
using their capabilities to read each others' data file formats.
| Warning |
Be careful not to use duplicate entries in your Web server's MIME map. For example, don't do the following: application/msexcel xls xcl wks Web servers read the MIME map linearly, from top to bottom, until they find a match of filename extensions. With entries like those in the preceding example, your Web server's MIME type/subtype header information for .wks files will always be read as application/msexcel. This may not be what you want if you have Lotus or Xess customers. Each spreadsheet customer in your Web will need to configure his browser based on the spreadsheet he uses. Web browsers will override the MIME type/subtype header information they receive if you have configured them to do so. |
Any new MIME type/subtype information you've added in your helper application setup in your Web browser is added to the browser's internal list. If you set up your browser to use 1-2-3 as a helper application when it encounters the filename extension .wks, it will use 1-2-3 on any file with that extension, even if one of the following things happens:
In other words, your local helper application setup can both fill in missing MIME type/subtype information and override what it gets from the Web server. Suppose you've set up your Web server's MIME map to have entries like the following:
application/msexcel,xls,,5 application/msexcel,xlc,,5 application/lotus,wks,,5
Further suppose that a particular customer has only the 1-2-3 spreadsheet package on her pc. Because you know Excel can directly read 1-2-3 data files, you can extend the preceding Excel helper application setup by adding a 1-2-3 filename extension. For example, Mosaic should be set up to use Excel not only for files with the .xls, .xlm, .xlw, and .xcl extensions, but also for those with .wks. Note that you can modify the Mosaic Description of MIME Type to include a reminder of this change.
In addition to some spreadsheets being able to directly read the data files created by others, most have a Save As feature that allows you to save a native data file in some other spreadsheet package's format. Excel, for example, supports saving data files in several variations of Lotus 1-2-3's .wks format, as well as several others. Lotus has a similar feature, enabling you to save data files in Excel's .xls format.
There's also a semi-universal spreadsheet data file format many packages support, the Symbolic Link Format, that often uses a filename extension of .slk. (This format is much like the Rich Text Format used in making word processing documents portable.) You can use this format to make multiple copies of spreadsheet data files on your Web server with versions for each spreadsheet package your customers use. Alternatively, because most packages support the Symbolic Link Format, you may just want to use that format for all your data files. If you do so, be sure your Web server's MIME map and your customers' browser setup correspond.
Both Excel and 1-2-3 have Save As functions that enable you to not only save spreadsheet data files in other spreadsheet formats, but also to save a spreadsheet in plain text format. Xess (for UNIX) has the same capability, along with the capability to save in PostScript format. Although recalculation and other capabilities are lost, of course, when a spreadsheet is saved in plain text or PostScript, the tabular layout and data are preserved. You can view these plain text files in your Web browser just like any other text file. You can view PostScript files with an appropriate PostScript viewer helper application.
Jordan Evans of the U.S. National Aeronautics and Space Administration has written XL2HTML, a Visual Basic for Applications (VBA) macro for Excel 5.0 that converts an Excel spreadsheet into an HTML table. Written for Excel 5.0 for Windows, the macro also works on both Macintosh Excel 5.0 and the Windows 95 version, Excel 7.0 (according to its author). A copy is on the CD-ROM that accompanies this book. You can specify a range of cells to be converted, and XL2HTML outputs HTML Table markup and retains character formatting, such as boldface, and underlining, from the original spreadsheet.
You can learn more about XL2HTML at http://www710.gsfc.nasa.gov/704/dgd/xl2html.html; instructions are included. As with spreadsheet files saved in plain text, customer interactivity (the ability to change and recalculate the spreadsheet) is lost in XL2HTML, and it would not be when using the spreadsheet program itself as a helper application. Nonetheless, you may find situations in which XL2HTML is useful for your Web.
Microsoft now has available for free download from its Web site
(http://www.microsoft.com)
a version of its Internet Assistant (IA) for Excel (also available
on the CD-ROM that accompanies this book). Like the companion
IA products for Microsoft Word and PowerPoint, this package enables
you to save Office data files (in this case, Excel spreadsheets)
directly to HTML format for use on your Intranet. IA supports
version 5.0 of Excel in both Windows 3.1 and NT and Macintosh
and version 7.0 on Windows 95.
| Tip |
While you're visiting the Microsoft Web site, you may also want to download a copy of a read-only Excel Viewer for Windows 95. Just as you set up Excel itself as a Web browser helper application, you can set up the Excel Viewer as one for customers who don't have their own copy of Excel. Although a version of the Excel Viewer for Windows is included on the CD-ROM with this book, you may need to obtain a more current version directly from the Microsoft Web site in the future. |
The rest of this chapter focuses on what you might do with spreadsheets on your Intranet. The ideas presented are meant to serve as examples; I encourage you to creatively adapt them to the specific needs of your own organization.
If you're into spreadsheets, perhaps you like to compare costs. Expensive data warehouse software packages are available from a number of vendors. These packages accumulate information from a variety of sources around an organization and provide database-like front ends, allowing searches for mostly numerical data. Data sources can include a number of different corporate databases running on different platforms. Once located, the data can be downloaded for local number crunching to users' pcs or workstations, usually in a spreadsheet package.
In this section, you'll learn about using your Intranet as a data warehouse, making full-blown, ready-to-run spreadsheet data files available to your customers. Although less capable than true data warehouse packages, your Intranet data warehouse shares many features with its larger cousins and at a much smaller price. You may find the trade-off worthwhile.
Cliché has it the spreadsheet was the first killer app for the pc, so it's no surprise that spreadsheet applications are used in many organizations. Individual users create and maintain their own spreadsheets, sometimes for personal use, sometimes shared. Often, the numbers used in the spreadsheets are company-wide data on sales, production, and other statistically measurable information. Collectively, your customers probably already have a great deal of useful data in spreadsheet format that could be quite valuable on your Intranet.
Of course, you probably don't want to go snooping on your Intranet customers' pcs or in their private fileserver directories for candidate spreadsheet files. But you should encourage your customers to contribute information for your Intranet. As you'll see, the collaborative aspects of making spreadsheets available on your Intranet should encourage customers to contribute. Although locating documents in this way may be a tedious, logistical problem, it's still possible, and what you find will provide the basis of your Intranet spreadsheet data.
The main principles of this activity are those already outlined, in this chapter and in others, with respect to the conversion of your legacy data for your Intranet. Where you have spreadsheet data available, you'll want to use what you learned previously to move this data quickly onto your Intranet, making it accessible via your Web server. Your customers will use their Web browsers to retrieve the data files into the spreadsheet helper applications (such as Excel, or the Excel Viewer) running on their own machines.
If you have multiple spreadsheet applications in use in your company, you may need to select a common spreadsheet data file format, such as standard Lotus format, which most other spreadsheets can read, or the exportable SYLK format. (See your spreadsheet documentation for its capabilities in this area.) Even though many, or even most, of your customers have the necessary spreadsheet software available to them, you might also want to make plain-text versions of the data files available for the benefit of customers who don't have the software. For example, a company schedule built in Excel could be saved in text format without losing the tabular formatting. Either Internet Assistant for Excel or the XL2HTML VBA script mentioned earlier in the chapter can help out with this task.
As you've learned, the basic setup of Web pages containing simple, clickable lists of available documents is quite easy. Adding a little subject matter organization is simple, too; use hyperlinks to create nested menu listings and add explanatory text to the pages. In just a few minutes, you can present a useful list of available spreadsheet data files to your customers.
For example, monthly reports can contain clickable cross-references to other documents, statistical tables, live spreadsheet data files, images, or even earlier months' reports (all with the same kinds of embedded links). Your customers can then use their Web browsers to jump from one document to another, looking for answers to questions by following promising threads. Where they come across spreadsheet data, they'll be able to look at it and even manipulate it using their own spreadsheet software as Web browser helper applications.
The more cross-references and hyperlinks you're able to add, of
course, the more capabilities you'll give to your customers. To
give your customers even more options for searching for specific
information, you can index the contents of your spreadsheet data
files. (This topic is covered later in the chapter.)
| Note |
Remember that helper applications, like your customers' spreadsheet application, always operate on a copy of the original spreadsheet data file. Your original remains unchanged on your Web server until you change it. Customers can freely change the data in the spreadsheet they've opened with a Web browser helper application (assuming the helper application is not a viewer only) for their own needs, all without touching the original. This situation is directly analogous to the data warehouse, where copies of corporate data are downloaded for local processing without changing the original source data. The purpose of these Intranet spreadsheets is simply to provide raw corporate data, such as sales numbers, or production numbers. |
How will your customers locate spreadsheet data they want without having to go through long on-screen lists of filenames? Subject-oriented menus can help, but only up to a point. Beyond a certain number of files, such a system would become time-consuming. Chapter 21, "Indexing Your Intranet with WAIS," discusses a powerful tool for indexing data on your Intranet. This tool enables your customers to search and retrieve Intranet data from indexes of the files on the server.
If you're familiar with indexing tools on the Internet, you know that they are usually text-based. You probably wonder how binary files such as spreadsheet data files can be indexed with a text-based tool. Experimentation when reading Excel data files reveals that useful indexes can be created. Character strings in spreadsheet files (and other binary files, such as word processor documents) can be rooted out. Virtually all spreadsheet files contain some character strings, primarily as column and row labels; they would not be worth much without these labels to identify the data that the columns contain.
Spreadsheets may also contain significant amounts of text in individual cells as well as in column or row labels. Your Intranet spreadsheet files, when carefully indexed with the right indexing tools, are nearly as searchable as your plain-text data. Searching these indexes is quite fast because, unlike all-text files, the word count of text in most spreadsheet files is quite small and has a limited vocabulary.
Results of your searches show as lists of clickable Web browser
hyperlinks, showing the hits on your search keywords. Just as
selecting such a hyperlink pointing to a text or HTML document
displays the document for viewing in your browser, so does selecting
a spreadsheet data file hyperlink. The difference is the spreadsheet
data file is handed off to your customer's spreadsheet helper
application for viewing. And, of course, once loaded, the spreadsheet
is an interactive entity your customer can use, not a static Web
page that just sits there.
| Tip |
Mosaic gives downloaded spreadsheet data files a temporary filename (such as mos2.wk4). Netscape generates a completely random temporary filename for spreadsheet files you open. To save the file, give it a meaningful name and save it in a permanent place in your system. |
The EMWAC WAIS package included on the CD-ROM with this book is an indexing tool that is distributed as freeware. Depending on the extent and nature of the overall library of documents on your Intranet and your customers' indexing needs, you may want also to look into commercial full-text indexing tools.
Assuming you've made spreadsheet data files available on your Intranet, this section examines how your customers might use these files.
Because of the generic nature of data accessible from standard data warehouse applications, the numerical data customers retrieve is not in spreadsheet format, ready for them to use. Rather, it's in raw, plain text, as columns of numbers or text with the entries on each line separated by a field separator. A spreadsheet package can import such raw data, including both numerical data and text row and column headings, provided the customer tells it about the format of the incoming data.
Microsoft Excel, for example, uses its TextWizard feature to prompt the customer through the importing of the incoming data. Lotus 1-2-3 accepts only a few standard field separators and also requires row and column labels to meet a specific format. Thus, even though customers can bring rows and columns of text and data into their spreadsheet package, the resulting spreadsheet isn't of much more value than a plain tabular listing of numbers and text. Customers have to take the time to add formulas and other spreadsheet-specific features. For the imported data to be immediately useful, each customer needs to have spent time building spreadsheet templates containing the necessary housekeeping that allows imported raw data to be dealt with.
Negative-sounding comparisons between commercial data warehouse applications and spreadsheet applications on your Intranet shouldn't be taken too far. Data warehouse packages have many strong features not present in the more limited situation described in this chapter. For instance, data warehouse packages can browse a variety of sources, including multiple corporate databases, and then integrate the chosen data into a single spreadsheet for ad hoc manipulation. Canned Intranet spreadsheets can't match this capability, though it can be roughly replicated through saving and combining individual downloaded spreadsheets.
Your trade-off is between cost and features. (Cost includes more than purchase price; it also includes staff time.) If you need the advanced features an industrial-strength data warehouse package provides, you'll want to get one. Nevertheless, you don't want to ignore the ability to replicate and improve on some of these packages' features on your Intranet at very low cost. If you can get 75 percent of the features of a data warehouse package for five percent of its cost by integrating replication into your Intranet, you may still be ahead of the game, even taking into account staff costs. Only you can decide on the value of the remaining 25 percent.
Keep in mind that Web interfaces to the major data warehouse packages haven't arrived on the scene yet. Because your overall objective in using Web technology for your Intranet is to enable use of your customers' Web browsers as front ends to as much organizational data as possible, this is a critical point. Each vendor's data warehouse has its own user interface, and although these can be perfectly good, user-friendly graphical interfaces, each one is different. Customers have to learn to use the interface before it's much good to them. On the other hand, your customers already know how to use their Web browser.
The critical difference between the generic data warehouse approach and the Intranet spreadsheet approach becomes clear in this context. The spreadsheet your Intranet customers access by clicking a Web page hyperlink is already a live, ready-to-run spreadsheet in their own spreadsheet packages' formats. Formulas, spreadsheet layout, and all the other housekeeping details are already in place.
If you provide, for example, spreadsheet data files containing corporate revenue, expense, and inventory information together with appropriate formulas linking the information, customers can do quick what-if analyses by changing some of the numbers in their temporary copy of the spreadsheet or by resorting the data to get different views of it. Similarly, interactive macro commands can be built into commonly accessed spreadsheets. Thus, customers can run their spreadsheet's statistical-analysis tools (frequency distribution, regression analysis, data matrices, and so on) on a range of spreadsheet cells they interactively select.
What can you do with an Intranet spreadsheet? The quick and easy answer to this question is, of course, anything you can do with any other spreadsheet. Although this answer is both obvious and true, it's not a sufficient answer to the question. To answer this question in the context of your Intranet, think about how your customers might use the spreadsheets you make available to them.
The World Wide Web is revolutionary, enabling technology. It provides new, easy-to-use ways for people to view and use information using their computers. Your customers use their Web browsers, not their standard operating system or windowing interface or data warehouse graphics front end, to access your Intranet spreadsheet data. Your spreadsheet data files are among all the other files available on your Intranet, all of which are accessible by a simple point and click.
By making corporate spreadsheet files available through an internal Web, you will be giving powerful new tools to your customers who use that data. Moreover, the audience of potential users of the data is also vastly increased. Customers who might never use the corporate data filed away in filing cabinets or in annual stockholders' reports, now might begin to use it, potentially benefiting themselves and your company. Putting that data in the form of Web page hyperlinks makes it accessible to everyone in your Intranet. Although doing so might lead on occasion to unqualified people acting as junior actuaries, there's much to be said for making unclassified statistical information widely available to the members of the organization.
As discussed in Chapter 28, spreadsheet data is yet another way in which you can enable your customers to collaborate using your Intranet. Unlike static Web pages, Intranet spreadsheets can be used by customers to do things. Even more important, a spreadsheet is an original creation, aimed at making it possible to view statistical data in some unique way, with the additional ability of refining or changing that view through what-if and other analysis. A spreadsheet is much like the results of an experiment published on your Intranet with the program the scientist wrote to perform the experiment. The data, methodology, and means of rerunning the experiment (that is, the spreadsheet itself) is made available to others who can attempt to replicate the results, possibly refine the view of them, and, of course, use them.
Customer-created spreadsheets, with results based on their unique views of common data, can be made readily accessible by their creators to the rest of your Intranet. Other customers who use these grass-roots spreadsheets are likely to offer comments and improvements to them, stimulating and feeding a collaborative process, generating new ideas, solving research or production problems, and increasing your organization's overall body of knowledge.
Here's a story that may help you see the potential of spreadsheet/Intranet integration. Frank, a salesman for Amalgamated Enterprises can't understand why his clients have started complaining about slow delivery of the widgets he's sold them. Using his laptop, Frank plays with corporate figures in a Lotus 1-2-3 spreadsheet, discovering there's plenty of widget inventory. He probes further and hits on what he thinks might be the problem, although he isn't sure. Deciding to use the Amalgamated Enterprises Intranet to share his analysis, Frank creates a simple Web page (he's not an HTML expert, so it's pretty plain) with a link to his spreadsheet. He uploads both files to the Web server in the home office using his laptop from his motel room in Boise. The Web page points out a potentially dangerous trend in the delivery of widgets, which portends production and inventory problems. Frank has put his analysis into spreadsheet form, made the spreadsheet accessible on the Amalgamated Enterprises Intranet, and asked for comments.
If necessary, Frank can then send e-mail to key people asking them to check out his Web page at their convenience. With this simple stroke, Frank has brought everyone's attention to a problem that may threaten the business. All who want to look at Frank's data need only click his link to do so. If they want to contact Frank, who's on the road, all they need do is click a mailto hyperlink to send him e-mail. (Now, I'd like to keep this story as realistic as possible, so given that Frank is a salesman, let's assume that an engineer taught him how to include the mailto link in his HTML code.)
It's true that this same sort of collaboration could have been accomplished through any of several other means. Frank could have sent a group e-mail message with the spreadsheet(s) attached or entered it in a Lotus Notes forum. He might well have gotten a good deal of response to either, and possibly even solved his problem. Nevertheless, Frank's simple, direct Intranet presentation is quite effective. Anybody on the Amalgamated Enterprises Intranet can independently view the spreadsheet data using their own spreadsheet software and refine or change the view of the data, recalculate/reanalyze it, and otherwise seek to verify or disprove Frank's conclusions.
Other customers may generate new spreadsheet views of the original data, supporting or disproving Frank's conclusions, and make their own revised versions available on the Intranet as well, for still further collaboration and discussion by the group. Frank will be able to see the new spreadsheet versions and read his e-mail by the time he gets to Seattle the next afternoon. By the following day, the shipping bottleneck having been traced to Denver, the Amalgamated personnel locate 50 flatcars of widgets sitting forgotten on a railroad siding, and Frank's been given a fat bonus. Hooray!
Continuing the discussion of the widget crisis, you can see some of the possibilities for spreadsheet data on your Intranet. As already discussed, such a situation has a collaborative nature because many Intranet customers can view and verify data in a spreadsheet available through a hyperlinked Web page. Some detailed possibilities for this spreadsheet data include the following:
Again, each of these separate activities, even for a single overall situation such as Amalgamated's widget crisis, could have been done completely outside the context of your Intranet. Certainly, the importation of spreadsheet data and graphics into word processing documents isn't anything new, nor is the creation of slides from the data using something like PowerPoint, nor is the querying of databases using the spreadsheet software. This integration is one of the primary strengths of integrated office packages such as Microsoft's or Corel's. What is new about this scenario is that it arose and was played out completely on Frank's Intranet, with one customer initiating the collaboration and others participating in the process. And the entire process used Web browsers and browser helper applications. If Frank was right in identifying shipping bottlenecks as the culprit, he helped his own sales by keeping his own clients happy, and he helped Amalgamated Enterprises by moving inventory to make room for urgent plant expansion-all using an Intranet.
Admittedly, this example is pretty contrived; not everyone can save their company from disaster wearing only a Web browser as a loin cloth. Nonetheless, it shows important possibilities for collaboration on your Intranet using lowly spreadsheet data. In a more practical sense, certainly data from multiple hyperlinked spreadsheets on your Intranet can be combined to form altogether new spreadsheets. This process is analogous to, though probably a clumsier process than, the data-warehouse browsing process described earlier, where data from various sources is combined into new spreadsheets for analysis. Spreadsheet numbers also can be turned into charts, graphs, or presentation slides or incorporated into documents, each of which can be plugged right back into your Intranet for more customer viewing and collaboration using Web browsers and helper applications.
Stockbrokers and plant engineers need access to continuously changing information. Spreadsheet packages can be connected to live stock market tickers and other live data feeds. Perhaps more widely useful than a stock market ticker, real-time spreadsheets can monitor data generated by computer controlled instruments in a manufacturing facility, refinery, or laboratory. In such a process control setting, the real-time spreadsheet monitors the instrument's data file as it grows, continuously loading incoming new data into the spreadsheet as it arrives. Whenever your Intranet customers click the link to that process-control spreadsheet, the spreadsheet package fires up as a helper application to display the latest set of data. Through the use of automatically executing macro commands, if available, your spreadsheet could automatically graph the new data as soon as it is loaded.
For example, a clickable HTML image map could be used to represent an overall process, with process control sensor icons in the image map being the hot spots. Clicking one of the sensors reads the latest spreadsheet data file containing data points that are dynamically passed to the spreadsheet graphing tool, called as a helper application.
You've learned some practical applications of spreadsheet data for your Intranet in this chapter. Use of spreadsheets as Web browser helper applications adds to your Intranet an important feature of interactivity that was heretofore lacking. This interactivity enables your customers to do their work in a completely new way. In addition, you've learned the potential value of collaboration on your Intranet using spreadsheet data as an example. The following is a review of what this chapter discussed:
The next chapter goes beyond the word processor and the spreadsheet to consider more Intranet office applications. Some of what's in that chapter builds on the material in this chapter and the previous two (Chapters 12 and 13).