Essential SharePoint 2007: A Practical Guide for Users, Administrators and Developers

5.5. Using the Spreadsheet Web Part

Publishing a spreadsheet as an interactive web page is cool, but unfortunately the data on the page can't be saved. You can view the page, make some changes, and see the result, but the next person to view the page won't see your work.

To create interactive spreadsheets that can save data, create a new web part page and add a spreadsheet web part. The spreadsheet web part is included with the Office 2003 web parts download available from Microsoft. Your SharePoint administrator must install that download on your SharePoint server before you can complete the tasks in this section.

To create a page with a spreadsheet web part:

1. From the SharePoint site home page, choose Create Web Part Page .

2. In the web part task pane, select Virtual Server Gallery. You'll see a list of the web parts installed on your server .

3. Drag the Office Spreadsheet web part from the task pane to a web part zone on the page. SharePoint adds the web part to the page, as shown in Figure 5-24 .

4. Close the web part task pane and click OK to save the changes .

Once you've created a web part page containing a spreadsheet web part, you can edit the cells on the spreadsheet directly in the browser. Figure 5-25 shows a Loan Calculator created on the web part page. The main difference between this version of the Loan Calculator and the one shown in Figure 5-23 is that the data can be saved with the page.

There are also other differences between using the Office Spreadsheet web part and publishing a spreadsheet as a web page:

• The web part page includes a navigation bar.

• Only a limited amount of data can be saved in the Office Spreadsheet web part.

• You can cut and paste from Excel to an Office Spreadsheet web part, but some formulas may not work in the web part.

The loan calculator example in Figure 5-23 is a somewhat tired example, but it does illustrate some of these differences well. In order to get it to work as a web part, I had to:

1. Limit it to 15 years of data (~180 rows). Showing a full 30-year amortization table resulted in a storage limit error when saving .

2. Define named ranges for Values_Entered and Number_of_Payments. Those names referred directly to formulas in Excel, but the web part can only store values if they are in a cell .

You may encounter other differences when using the Office Web Components as web parts rather than publishing as a web page.