Excel
Page 225
Creating Static and Dynamic Web Pages Using Excel
Objectives:
· You will have mastered the material in this Web feature when you can:
· Publish a worksheet and chart as a static or a dynamic Web page
· Display Web pages published in Excel in a browser
· Manipulate the data in a published Web page using a browser
· Complete file management tasks within Excel
CASE PERSPECTIVE
Home Wireless Fidelity, a network company that specializes in the installation of wireless home networks, has experienced significant growth since it developed the first wireless home network system. In two years, the company has grown from a single owner, garage-based company to one with annual sales in the millions.
Sergio Autohbon is a spreadsheet specialist for Home Wireless Fidelity. One of Sergio’s responsibilities is a workbook that summarizes quarterly sales by store location (Figure 1a on the next page). In the past, Sergio printed the worksheet and chart, sent it out to make copies of it, and then mailed it to his distribution list.
Home Wireless Fidelity recently upgraded to Office 2003 because of its Web and collaboration capabilities. After attending an Office 2003 training session, Sergio had a great idea and called you for help. He would like to save the Excel worksheet and Pie chart (Figure 1a) on the company’s intranet as a static Web page (Figure 1b), so the lower-level management on the distribution list can display it using a browser. He also suggested publishing the same workbook on the company’s intranet as a dynamic (interactive) Web page (Figure 1c), so the higher-level management could use its browser to manipulate the data in the worksheet without requiring Excel.
Finally, Sergio wants both the static and dynamic Web pages saved as single files, also called Single File Web Page format, rather than in the traditional file and folder format, called Web Page format.
As you read through this Web feature, you will learn how to create static and dynamic Web pages from workbooks in Excel and then display the results using a browser.
Introduction
Excel provides fast, easy methods for saving workbooks as Web pages that can be stored on the World Wide Web, a company’s intranet, or a local hard disk. A user then can display the workbook using a browser, rather than Excel.
You can save a workbook, or a portion of a workbook, as a static Web page or a dynamic Web page. A static Web page, also called a noninteractive Web page or view-only Web page, is a snapshot of the workbook. It is similar to a printed report in that you can view it through your browser, but you cannot modify it. In the browser window, the workbook appears as it would in Microsoft Excel, including sheet tabs that you can click to switch between worksheets. A dynamic Web page, also called an interactive Web page, includes the interactivity and functionality of the workbook. For example, with a dynamic Web page, you can view a copy of the worksheet in your browser and then enter formulas, reformat cells, and change values in the worksheet to perform what-if analysis. A user does not need Excel on his or her computer to complete these tasks.
Page 226
As illustrated in Figure 1, this Web feature shows you how to save a workbook (Figure 1a) as a static Web page (Figure 1b) and view it using your browser. Then, using the same workbook, the steps show how to save it as a dynamic Web page (Figure 1c), view it using your browser, and then change values to test the Web page’s interactivity and functionality.
Page 227
Page 228
The Save as Web Page command on the File menu allows you to publish workbooks, which is the process of making a workbook available to others; for example, on the World Wide Web or on a company’s intranet. If you have access to a Web server, you can publish Web pages by saving them in a Web folder or on an FTP location. To learn more about publishing Web pages in a Web folder or on an FTP location using Microsoft Office applications, refer to Appendix C.
This Web feature illustrates how to create and save the Web pages on a floppy disk, rather than on a Web server. This feature also demonstrates how to preview a workbook as a Web page and create a new folder using the Save As dialog box.
More About: Web Folders and FTP Locations
Web folders and FTP locations are particularly useful because they appear as standard folders in Windows Explorer or in the Save in list. You can save any type of file in a Web folder or on an FTP location, just as you would save in a folder on your hard disk. For additional information, see Appendix C.
Using Web Page Preview and Saving an Excel Workbook as a Static Web Page
After you have created an Excel workbook, you can preview it as a Web page. If the preview is acceptable, then you can save the workbook as a Web page.
Web Page Preview
At anytime during the construction of a workbook, you can preview it as a Web page by using the Web Page Preview command on the File menu. When you invoke the Web Page Preview command, it starts your browser and displays the active sheet in the workbook as a Web page. The following steps show how to use the Web Page Preview command.
To Preview the Workbook as a Web Page
Step 1:
• Insert the Data Disk in drive A.
• Start Excel and then open the workbook, Home Wireless Fidelity Quarterly Sales, on drive A.
• Click File on the menu bar.
Excel starts and opens the workbook, Home Wireless Fidelity Quarterly Sales. The workbook is made up of two sheets: a worksheet and a chart. Excel displays the File menu (Figure 2).
Page 229
Step 2:
• Click Web Page Preview.
Excel starts your browser. The browser displays a preview of how the Quarterly Sales sheet will appear as a Web page (Figure 3). The Web page preview in the browser is nearly identical to the display of the worksheet in Excel. A highlighted browser button appears on the Windows taskbar indicating it is active. The Excel button on the Windows taskbar no longer is highlighted.
Step 3:
• Click the 3-D Pie Chart tab at the bottom of the Web page.
The browser displays the 3-D Pie chart (Figure 4).
Step 4:
• After viewing the Web page preview of the Home Wireless Fidelity Quarterly Sales workbook, click the Close button on the right side of the browser title bar.
The browser closes. Excel becomes active and again displays the Home Wireless Fidelity Quarterly Sales worksheet.
Page 230
The Web Page preview shows that Excel has the capability of producing professional looking Web pages from workbooks.
More About: Publishing Web Pages
For more information about publishing Web pages using Excel, visit the Excel 2003 More About Web page (scsite.com/ex2003/more) and then click Publishing Web Pages Using Excel.
Saving a Workbook as a Static Web Page in a New Folder
Once the preview of the workbook as a Web page is acceptable, you can save the workbook as a Web page so that others can view it using a Web browser, such as Internet Explorer or Netscape Navigator.
Whether you plan to save static or dynamic Web pages, two Web page formats exist in which you can save workbooks. Both formats convert the contents of the workbook into HTML (HyperText Markup Language), which is a language browsers can interpret. One format is called Single File Web Page format, which saves all of the components of the Web page in a single file with an .mht extension. This format is useful particularly for e-mailing workbooks in HTML format. The second format, called Web Page format, saves the Web page in a file and some of its components in a folder. This format is useful if you need access to the components, such as images, that make up the Web page.
Experienced users organize the files saved on a storage medium, such as a floppy disk or hard disk, by creating folders. They then save related files in a common folder. Excel allows you to create folders before saving a file using the Save As dialog box. The following steps create a new folder on the Data Disk in drive A and save the workbook as a static Web page in the new folder.
To Save an Excel Workbook as a Static Web Page in a Newly Created Folder
• With the Home Wireless Fidelity Quarterly Sales workbook open, click File on the menu bar.
Excel displays the File menu (Figure 5).
Page 231
• Click Save as Web Page.
• When Excel displays the Save As dialog box, type Home Wireless Fidelity Quarterly Sales Static Web Page in the File name text box.
• Click the Save as type box arrow and then click Single File Web Page.
• Click the Save in box arrow, select 31/2 Floppy (A:), and then click the Create New Folder button.
• When Excel displays the New Folder dialog box, type Web Feature in the Name text box.
Excel displays the Save As dialog box and New Folder dialog box as shown in Figure 6.
• Click the OK button in the New Folder dialog box.
Excel automatically selects the new folder Web Feature in the Save in box (Figure 7). The Entire Workbook option in the Save area instructs Excel to save all sheets in the workbook as static Web pages.
• Click the Save button in the Save As dialog box.
• Click the Close button on the right side of the Excel title bar to quit Excel.
Excel saves the workbook in a single file in HTML format in the Web Feature folder on the Data Disk in drive A.
Page 232
The Save As dialog box that Excel displays when you use the Save as Web Page command is slightly different from the Save As dialog box that Excel displays when you use the Save As command. When you use the Save as Web Page command, a
Save area appears in the dialog box. Within the Save area are two option buttons, a check box, and a Publish button (Figure 7 on the previous page). You can select only one of the option buttons. The Entire Workbook option button is selected by default. This indicates Excel will save all the active sheets (Quarterly Sales and 3-D Pie Chart) in the workbook as a static Web page. The alternative is the Selection Sheet option button. If you select this option, Excel will save only the active sheet (the one that currently is displaying in the Excel window) in the workbook. If you add a check mark to the Add interactivity check box, then Excel saves the active sheet as a dynamic Web page. If you leave the Add interactivity check box unchecked, Excel saves the active sheet as a static Web page.
In the previous set of steps, the Save button was used to save the Excel workbook as a static Web page. The Publish button in the Save As dialog box in Figure 7 is an alternative to the Save button. It allows you to customize the Web page further. Later in this feature, the Publish button will be used to explain how you can customize a Web page further.
If you have access to a Web server and it allows you to save files in a Web folder, then you can save the Web page directly on the Web server by clicking the My Network Places button in the lower-left corner of the Save As dialog box (Figure 7). If you have access to a Web server that allows you to save on an FTP site, then you can select the FTP site below FTP locations in the Save in box just as you select any folder on which to save a file. To learn more about publishing Web pages in a Web folder or on an FTP location using Office applications, refer to Appendix C.
After Excel saves the workbook in Step 4 on the previous page, it displays the HTML file in the Excel window. Excel can continue to display the workbook in HTML format, because, within the HTML file that it created, it also saved the Excel formats that allow it to display the HTML file in Excel. This is referred to as round tripping the HTML file back to the application in which it was created.
More About: Viewing Web Pages Created in Excel
To view static Web pages created in Excel, you can use any browser. To view dynamic Web pages created in Excel, you must have the Microsoft Office Web Components and Microsoft Internet Explorer 4.01 or later installed on your computer.
File Management Tools in Excel
It was not necessary to create a new folder in the previous set of steps. The Web page could have been saved on the Data Disk in drive A in the same manner files were saved on the Data Disk in drive A in the previous projects. Creating a new folder, however, allows you to organize your work.
Another point concerning the new folder created in the previous set of steps is that Excel automatically inserts the new folder name in the Save in box when you click the OK button in the New Folder dialog box (Figure 7).
Finally, once you create a folder, you can rightclick it while the Save As dialog box is active and perform many file management tasks directly in Excel (Figure 8). For example, once the shortcut menu appears, you can rename the selected folder, delete it, copy it, display its properties, and perform other file management functions.
Page 233
Viewing the Static Web Page Using a Browser
With the static Web page saved in the Web Feature folder on drive A, the next step is to view it using a browser as shown in the following steps.
To View and Manipulate the Static Web Page Using a Browser
• If necessary, insert the Data Disk in drive A.
• Click the Start button on the Windows taskbar, point to All Programs on the Start menu, and then click Internet Explorer on the All Programs submenu.
• When the Internet Explorer window appears, type a:\web feature\home wireless fidelity quarterly sales static web page.mht in the Address box and then press the ENTER key.
The browser displays the Web page, Home Wireless Fidelity Quarterly Sales Static Web Page.mht, with the Quarterly Sales sheet active (Figure 9). FIGURE 9
• Click the 3-D Pie Chart tab at the bottom of the window.
• Use the scroll arrows to display the lower portion of the chart.
The browser displays the 3-D Pie chart as shown in Figure 10.
• Click the Close button on the right side of the browser title bar to close the browser.
Page 234
You can see from Figures 9 and 10 on the previous page that a static Web page is an ideal way to distribute information to a large group of people. For example, the static Web page could be published on a Web server connected to the Internet and made available to anyone with a computer, browser, and the address of the Web page. It also can be e-mailed easily, because the Web page resides in a single file, rather than in a file and folder. Publishing a static Web page of a workbook thus is an excellent alternative to distributing printed copies of the workbook.
Figures 9 and 10 show that, when you instruct Excel to save the entire workbook (see the Entire Workbook option button in Figure 7 on page EX 231), it creates a Web page with tabs for each sheet in the workbook. Clicking a tab displays the corresponding sheet. If you want, you can use the Print command on the File menu in your browser to print the sheets one at a time.
Q&A:
Q: Can I view the source code of a static Web page created in Excel?
A: Yes. To view the HTML source code for a Web page created in Excel, use your browser to display the Web page, click View on the menu bar, and then click Source.
Saving an Excel Chart as a Dynamic Web Page
This section shows how to publish a dynamic Web page that includes Excel functionality and interactivity. The objective is to publish the 3-D Pie chart that is on the 3-D Pie Chart sheet in the Home Wireless Fidelity Quarterly Sales workbook. The following steps use the Publish button in the Save As dialog box, rather than the Save button, to illustrate the additional publishing capabilities of Excel.
To Save an Excel Chart as a Dynamic Web Page
Excel opens the workbook and displays the File menu (Figure 11).
Page 235
• When Excel displays the Save As dialog box, type Home Wireless Fidelity Quarterly Sales Dynamic Web Page in the File name text box.
• If necessary, click the Save in box arrow, select 31/2 Floppy (A:) in the Save in list, and then select the Web Feature folder.
Excel displays the Save As dialog box as shown in Figure 12.
• Click the Publish button.
• When Excel displays the Publish as Web Page dialog box, click the Choose box arrow and then click Items on 3-D Pie Chart.
• Click the Add interactivity with check box in the Viewing options area.
• If necessary, click the Add interactivity with box arrow and then click Chart functionality.
Excel displays the Publish as Web Page dialog box as shown in Figure 13.
• Click the Publish button, click the Close button on the right side of the Excel title bar, and if necessary, click the No button in the Microsoft Excel dialog box.
Excel saves the dynamic Web page in the Web Feature folder on the Data Disk in drive A. The Excel window is closed.
Page 236
Excel allows you to save an entire workbook, a sheet in the workbook, or a range on a sheet as a Web page. In Figure 12 on the previous page, the Save area provides options that allow you to save the entire workbook or only a sheet. These option buttons are used with the Save button. If you want to be more selective in what you save, then you can disregard the option buttons in the Save area in Figure 12 and click the Publish button as described in Step 3. The Choose box in the Publish as Web Page dialog box in Figure 13 on the previous page provides additional options for you to select what to include on the Web page. You also may save the Web page as a dynamic Web page (interactive) or a static Web page (noninteractive) by selecting the appropriate options in the Viewing options area. The check box at the bottom of the dialog box gives you the opportunity to start your browser automatically and display the newly created Web page when you click the Publish button.
More About: Dynamic Web Pages
When you change a value in a dynamic Web page, it does not affect the saved workbook or the saved HTML file. If you change a value on a dynamic Web page and then save the Web page using the Save As command on the browser's File menu, Excel will save the original version, not the modified one that appears on the screen.
Viewing and Manipulating the Dynamic Web Page Using a Browser
With the dynamic Web page saved in the Web Feature folder on drive A, the next step is to view and manipulate the dynamic Web page using a browser, as shown in the following steps.
To View and Manipulate the Dynamic Web Page Using a Browser
• When the Internet Explorer window appears, type a:\web feature\home wireless fidelity quarterly sales dynamic web page.mht in the Address box, and then press the ENTER key.
The browser displays the Web page, Home Wireless Fidelity Quarterly Sales Dynamic Web Page.mht, as shown in Figure 14. The 3-D Pie chart appears at the top of the Web page. The rows and columns of the worksheet that determine the size of the slices appear immediately below the 3-D Pie chart.
Page 237
• Click cell A2 and then enter 1600000 as the new value.
The number 1,600,000 replaces the number 274,132 in cell A2. The formulas in the worksheet portion recalculate the totals in row 6 and the slices in the 3-D Pie chart change to agree with the new totals (Figure 15).
Figure 14 shows the result of saving the 3-D Pie chart as a dynamic Web page. Excel displays a slightly rounded version of the 3-D Pie chart and automatically adds the columns and rows from the worksheet that affect the chart directly below the chart. As shown in Figure 15, when a number in the worksheet that determines the size of the slices in the 3-D Pie chart is changed, the Web page instantaneously recalculates all formulas and redraws the 3-D Pie chart. For example, when cell A2 is changed from 274,132 to 1,600,000, the Web page recalculates the totals in row 6. The slice representing Quarter 1 is based on the number in cell A6. Thus, when the number in cell A6 changes from 997,681 to 2,323,549 because of the change made in cell A2, the slice representing Quarter 1 changes to a much larger slice in relation to the others. The interactivity and functionality allow you to share a workbook’s formulas and charts with others who may not have access to Excel, but do have access to a browser.
Modifying the Worksheet on a Dynamic Web Page
As shown in Figure 15, the Web page displays a toolbar immediately above the rows and columns in the worksheet. This toolbar, called the Spreadsheet toolbar, allows you to invoke the most commonly used worksheet commands. For example, you can select a cell immediately below a column of numbers and click the AutoSum button to sum the numbers in the column. Cut, copy, and paste capabilities also are available. Table 1 on the next page summarizes the functions of the buttons on the Spreadsheet toolbar shown in Figure 15.
More About: Creating Links
You can add hyperlinks to an Excel workbook before you save it as a Web page. The hyperlinks in the Excel workbook can link to a Web page, a location in a Web page, or an e-mail address that automatically starts the viewer's e-mail program.
More About: The Quick Reference
For a table that lists how to complete the tasks covered in this book using the mouse, menu, shortcut menu, and keyboard, see the Quick Reference Summary at the back of this book or visit the Excel 2003 Quick Reference Web page (scsite.com/ex2003/qr).
Page 238
In general, the Spreadsheet toolbar allows you to add formulas, format, sort, and export the Web page to Excel. Many additional Excel capabilities are available through the Commands and Options dialog box. You display the Commands and Options dialog box by clicking the Commands and Options button on the Spreadsheet toolbar. When Excel displays the Command and Options dialog box, click the Format tab. The Format sheet makes formatting options, such as bold, italic, underline, font color, font style, and font size, available through your browser for the purpose of formatting cells in the worksheet below the 3-D Pie chart on the Web page.
Modifying the dynamic Web page does not change the makeup of the original workbook or the Web page stored on disk, even if you use the Save As command on the browser’s File menu. If you do use the Save As command in your browser, it will save the original mht file without any changes you might have made. You can, however, use the Export to Excel button on the Spreadsheet toolbar to create a workbook that will include any changes you made in your browser. The Export to Excel ...
Januszek66