Section 24.1. Putting Worksheets on the Web


24.1. Putting Worksheets on the Web

HTML (short for Hypertext Markup Language ) is the language of the Web. Web authors use it to craft pages with text, links, and graphics. In fact, HTML is so popular that it's no longer restricted to the Web. Even desktop programs often use it as an all-purpose way to display information. A typical Windows computer uses HTML to build help files, format email messages, display operating system updates, and even create fancy desktops.

In the early days of the Web, most programs had export-to-HTML features that weren't worth a second glance. They distorted formatting, mangled text, and generated HTML so ugly that professional Web developers fainted at the sight of it. Fortunately, the situation's improved. Though Excel's HTML exporting might never match the graphical flair of the most talented Web artists , it's still downright impressive. Best of all, you can have your data ready for Web surfers in a matter of minutes.


Tip: It often makes sense to create HTML pages from your spreadsheets even if you don't intend to put them on the Internet. Maybe you want to email some information to another person who doesn't have Excel. Or maybe your company wants to put a collection of Web pages on a local intranet, so that everyone on the company network can use them. Either way, other people who don't have Excel can easily use a Web browser like Firefox or Internet Explorer to open HTML versions of your worksheets.
UP TO SPEED
Putting a Page on the Web

As you probably already suspect, if you want an HTML document to appear on the Web, it's not enough to just create the file. No one else will be able to find it unless you place that file on a Web site . Once your Web page turns up on a live Web site, surfers across the globe can go straight to it.

Personal computers rarely host Web sites. Instead, that job is usually handled by high- powered computers called Web

servers . If you want to make your Web pages available over the Internet, you need to find a Web-hosting company that'll give you a little patch of space on one of their Web servers. You can then transfer your HTML pages to their computers so that other people can find them. (Ask around to find a reputable Web-hosting service, or search Google.)


24.1.1. Saving and Publishing HTML

Excel provides two similar, but slightly different choices for saving an HTML file. Before you begin, you need to decide which approach you want to use.

  • Publish . This method is good when want to create an HTML "snapshot" of a regular Excel file. When you publish a file, you create an HTML version of it, but if you subsequently edit the regular file, Excel doesn't copy those edits to the HTML file. Thus, if you make changes to your worksheet and want them to appear in the HTML version, you need to export your data again.

  • Direct Save . This method is good when you want to keep making changes to the HTML version of your worksheet. When you use this approach, Excel, as with the publish method, creates an HTML version of your worksheet. The difference is that Excel also keeps track of the fact that your last save operation used the HTML format. Thus, if you make changes to your data and save your workbook again, Excel updates only the HTML file, not the original .xls workbook file. If you want to update the .xls file, you need to use the Save As command.

Most people find that it makes most sense to use the publish approach, since any changes you make to your data always get saved in your original workbook, and Excel doesn't create a new, HTML version until you decide you want one.

If you do decide to use the direct save approach to keep your workbook in the HTML format all the time, here are a couple of things to keep in mind:

  • Make sure your HTML file has all the data . Don't choose to save a single worksheet or a range of cells , because you'll lose the rest of the workbook's information, which you might want to edit later on. (Of course, you'll still have this information in your original .xls workbook. But there's nothing more confusing than trying to keep the data in two different files up to date.)

  • If you want to open your data in Excel, don't just double-click your HTML spreadsheet file . If you do, you'll only end up opening the Web page in a Web browser. Instead, select File Open in Excel, and then select the HTML file. Or, right-click the HTML file on your desktop, and choose Open With Microsoft Office Excel to send the file straight to Excel's welcoming arms (see Figure 24-1).

Figure 24-1. This HTML file (BudgetReport.htm) contains all the data of a full-fledged Excel workbook. Excel not only knows how to create itit also knows how to open it in its original Excel format, without losing a single byte of information. But if you double-click the icon, Windows assumes that you're trying to view the Web page in a Web browser. To open the file in Excelwhere you can edit itright-click it and use the Open With option to choose Excel. If the HTML file wasn't created in Excel, this option won't work.


  • Don't edit your HTML in another program . If you do, you might alter the structure enough that Excel won't be able to recognize it anymore and won't be able to open it.

Whichever method you usepublish or direct saveExcel's HTML export feature supports a remarkable feature called round-trips . Thanks to the round-trip feature, you can save your workbook as an HTML page, and then open that HTML page in Excel. Excel automatically recognizes that this HTML page isn't an ordinary piece of Web content and restores your workbook so that it looks and actsand in fact isa regular Excel file. Amazingly, you won't loose any information, formatting, or workbook preferences! Even charts and macro code can make the transition from Excel to HTML and back to Excel seamlessly.

24.1.2. Saving a Simple Web Page

Using Excel, you can convert a single worksheet, a range of cells, or a whole workbook to an HTML file. Here's how:

  1. Select the portion of the Excel file you want to convert to HTML.

    If you want to export a single worksheet, move to that worksheet. Or, if you want to export a range of cells, select those cells. Step 3 tells you how to export the entire workbook.

  2. Select File Save as Web Page.

    This action opens the Save As dialog box and selects "Web Page" in the "Save as type" list, as shown in Figure 24-2.

    Figure 24-2. Excel's Save as Web Page options let you export a single worksheet or a selection; set the Web page's title; and publish the workbook instead of performing a direct save (the distinction between the two is described on Section 24.1.1). (You'll learn about the "Add interactivity" checkbox in the next section.)


    In the rare case that your workbook includes features that can't be exported to HTML (like password-protection or custom views), Excel will warn you before you get any further. In this case, just click OK to continue.

  3. Choose which portion of your workbook you want to export to HTML.

    If you want to export every worksheet, select "Entire Workbook." If you just want to export the current worksheet, select "Selection: Sheet." If you've already selected a range of cells, you won't see the "Selection: Sheet" option. Instead, you'll see an option for the range of cells you chose, like "Selection: $A$2:$B$5."

  4. If you want to add a title, click the Change Title button. When the Set Page Title dialog box appears, type in a title for your Web page, and then click OK.

    If you add a descriptive title, it appears in large bold font centered over the rest of your content. There are no restrictions on titles, so feel free to use something clear and descriptive like "Blue Skies Budget Report" or "Bankruptcy Projections for 2005." This title will also appear in the title bar of the Web browser window. (Without the title information, most Web browsers will simply show the Web page file name in their title bar.)

  5. Browse to the location where you want to save the Web page.

    Any of the places you normally save files will work.

  6. In the "File name" box, enter the name of the HTML file you want to create.

    Depending on the content in your worksheet, Excel might create more than one file. For example, if your worksheet contains embedded graphics or charts, or if you are printing the entire workbook, Excel will create additional files. These files will be placed in a newly created folder that has the same name as your file, plus the text "_files."

    For example, if you save the Web page BudgetReport.htm, Excel creates a folder named BudgetReport_files to hold the extra files. You need to keep the HTML file and this folder together at all times, because the folder contains some information that the HTML file uses. (The box on Sidebar 24.2 has more on these folders.)

  7. You now have a choice to either save or publish your Web page.

    If you want to publish your file, which creates a copy of your data in the HTML format, click Publish. The Publish as Web Page dialog box appears, as shown in Figure 24-3. You've already set all the options you need, so you can just click Publish to save your HTML file.

Figure 24-3. Most of the options in the Publish as Web Page dialog box duplicate those in the Save As dialog box (Section 24.1.2). You have a last-minute chance to select the portion of the workbook you want to publish and change the file name or Web page title. You can also add interactivity, which is covered in the next section. If you select "AutoRepublish every time this workbook is saved," Excel saves the HTML file every time you save the .xls workbook file. If you select "Open published Web page in browser," Excel previews the HTML file in your Web browser when you save it.


If you want to perform a direct save of your file, effectively converting your current workbook into the HTML format, click the Save button. The original copy of your workbook remains in an .xls file, but Excel won't update it again unless you choose File Save As and explicitly select it.


Tip: Before you start the export process, you can get a good idea of what your converted Web page will look like by choosing File Web Page Preview. Excel exports a temporary copy of your data, and opens a new Web browser window to show it to you. When you use this command, Excel exports the full workbookunless you only have one worksheet of data, in which case Excel exports just that worksheet.

The exported copy of your worksheet is amazingly faithful. Excel preserves the formatting, layout, and content of your original worksheet. If your worksheet contains pictures or charts, Excel saves a separate graphic file for each object and displays it in the same Web page using the linking power of HTML. Figure 24-4 shows an exported worksheet that includes a chart.

Figure 24-4. The HTML version of a spreadsheet, as displayed in Internet Explorer, (top) mimics its appearance in Excel (bottom) with surprising accuracy. There are a few minor changes you need to get used tofor example, the HTML version doesn't show any gridlines and can't correctly display fonts if they aren't installed on the computer that's viewing the Web page. As you can see by looking at the URL in the address bar, this page isn't stored on the Internetinstead, it's located right on this computer's desktop.



Note: Open this file in a Web editor like Macromedia Dreamweaver or Microsoft FrontPage to fine-tune Excel's HTML. However, doing so might make you unable to reopen your HTML workbook in Excel to edit it later.

If you save an entire workbook, Excel is smart enough to create a Web page that includes separate frames for each worksheet, as shown in Figure 24-5.

Figure 24-5. If you save an entire workbook, Excel adds a set of links that look like worksheet tabs at the bottom of the page. You can click these links to view the different worksheets. If a worksheet doesn't contain any data, Excel won't include a tab for it. Technically, Excel uses an HTML feature called frames to allow it to load separate worksheet pages in the same page.


GEM IN THE ROUGH
Saving Web Pages with Their HTML Data

When you save your Excel data in an HTML page, Excel usually creates several filesone each for pictures and charts. Web browsers don't know anything about Excel charts, so Excel turns those into .gif files that any browser can display. Excel also uses additional files if you save an entire workbook, in which case it places each worksheet in a separate file.

In order to keep everything organized, Excel stores all these extra files in a linked folder. For example, if you save your Excel file as the Web page BudgetReport.htm, Excel creates a folder called BudgetReport_files to hold extra files. Usually, you won't need to worry about which files are in this folder. Instead, you'll just load the main page (the one called BudgetReport.htm) in your Web browser.

You do, however, need to make sure the folder is always available, because the main page links to the files it contains. That means if you want to move your HTML page to another location or another computer, you need to make sure that you copy the linked folder with it.

Keeping track of all these files can be a bit of a headache . For example, what if you want to email your Excel Web page to another person? You'll not only need to email all the files, but you'll also need to explain how to create the required directory to hold all these files.

Fortunately, there's an easier solution. The trick is to save everything using a special Web archive format called .mht. When you save a .mht file, everything is combined into one compound file, including graphics. There's no easy way to edit .mht files, and they are only supported in Internet Explorer 4.0 or later (other browsers, like Netscape Navigator or Opera, need not apply). However, .mht files are a great way to package everything up into one file when you need to send it in an email.

To save a .mht file, select File Save as Web Page. In the "Save as type" list, choose "Single File Web Page." Choose any other options you want, and click Save or Publish to finish the job.



Tip: When you publish your Web page, Excel saves all the choices you made in the Save As or Publish as Web Page dialog boxes. If at any point you want to publish your Web page again and you want to make changes to the settings in these dialog boxes, click File Save as Web Page, and then immediately click the Publish button. You can then change your settings in the Publish Web Page dialog box. If you forget to click the Publish button first and try to specify new settings in the Save As dialog box, Excel will discard them when you click the Publish button, in favor of the options you used last time.

24.1.3. Creating an Interactive Web Page

Excel lets you save your spreadsheet as an interactive Web page. This means that people who surf to your Web page can not only read your datathey can perform some limited editing as well.

Interactive Web pages sound like a great innovation, but they have some significant drawbacks (Figure 24-6). The problem is that an interactive Web page isn't a real HTML file. Instead, when you load the Web page, your Web browser loads a special Excel component and sends the block of data to that component. Technically, a scaled-down version of Excel runs right inside your Web browser. In order to make this technology work, Excel uses a Microsoft technology called ActiveX. If you try to use an interactive Excel Web page with a browser that doesn't support ActiveX, it won't work.

Figure 24-6. Top : This interactive Web page includes a chart. Excel shows the chart, followed by a scaled-down Excel grid including its data. The person using this Web page can change the numbers in the grid and see how they affect the chart. The grid's toolbar options are explained in Figure 24-7.
Bottom : The same Web page, but on a computer that doesn't have Excel installed.


Here are some of the problems that interactive Web pages present:

  • If you open the Web page on a computer that doesn't have Excel, the Web browser won't be able to find the component it needs, and the page won't work. You won't even see the ordinary HTML version of the pageinstead, you'll be left with nothing but an error message.

  • Although you can edit an interactive Web page in another HTML editor, you can't change any of the Excel data. That data is embedded into the mysterious <object> tag , which represents an Excel component that lies beyond your reach.

  • The interactive Web page won't load quite as quickly as an ordinary HTML file, because it needs to tap into some of Excel's processing power.

Here's how to create an interactive Web page from an Excel file:

  1. Select File Save as Web Page.

    The Save As dialog box appears.

  2. Click the Publish button.

    The Publish as Web Page dialog box appears.

  3. From the "Item to publish" list, choose the item you want to save in your interactive Web page.

    If you choose to publish an entire workbook or a single worksheet, Excel includes only the data. It doesn't include embedded objects like picture and charts. Excel doesn't impose this restriction when you save ordinary Web pages.

    If you do want to publish a chart, you must choose that chart object from the "Item to publish" list. When you do so, Excel creates a Web page that includes that chart and the portion of the worksheet containing the chart data. There's no way to create an interactive Web page with more than one chart, or with a chart and other unrelated cells.

  4. Turn on the "Add interactivity with" checkbox.

    If necessary, you can also choose the type of interactivity that Excel adds. Usually, Excel chooses the option that makes most sense. However, if you are creating an interactive Web page that includes a pivot chart (Section 20.3.6), you should choose "PivotTable functionality" instead of "Spreadsheet functionality."

  5. If you want to add a title, click the Change button.

    When the Set Page Title dialog box appears, enter a title for your Web page, and then click OK.

  6. Click the Browse button to set the location where you want to save the Web page.

    When the Publish As dialog box appears, browse to the folder you want, enter a file name, and click OK.

  7. If you want to tell Excel to create this interactive Web page every time you save the corresponding workbook, turn on the "AutoRepublish every time this workbook is saved" checkbox.

  8. If you want to preview the Web page Excel creates, turn on the "Open published Web page in browser" checkbox.

  9. Click Publish.

    When you look at the Web page in a browser, you'll see a small window that shows a grid of cells, and has a toolbar with a few basic options. Figure 24-7 explains these options.

Figure 24-7. Unlike Excel (the program), the Web control doesn't include a menu, so you're limited to edits and actions that you can make through this toolbar.



Tip: You can control the size of the grid window that appears inside the browser, but doing so is a little awkward . First, resize your browser window. Then, click the browser's Refresh button. Your browser will reload the control and size it to fit inside the current window. The big downside? You'll loose any changes you made prior to resizing.


Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net