Exporting an Excel 2007 Table to a SharePoint Site

Creating a SharePoint list from within Excel 2007 is known as exporting an Excel table. In Excel 2003, this was known as publishing an Excel list. Once the table data is placed on the SharePoint site, users can see the Excel 2007 data without opening Excel 2007. As in the first exercise of this chapter, you can maintain a link between the SharePoint list and the Excel 2007 data, but any changes in the Excel 2007 spreadsheet are not reflected in the SharePoint list. You can only synchronize changes in the SharePoint list to the Excel 2007 spreadsheet.

In the following exercise, you will export a spreadsheet to a SharePoint list by using Excel 2007 and a two-step wizard.

BE SURE TO start Excel 2007 before beginning this exercise.

USE the  Sales_Figures.xlsx document in the practice file folder for this topic. This practice file is located in the Documents\Microsoft Press\SBS_WSSv3\Chapter 12 folder. Remember to use your SharePoint site location in place of http://wideworldimporters in the exercise.

OPEN the  Sales_Figures.xlsx document.

1. In Excel 2007, click any cell within the data.

2. On the Home tab, in the Styles group, click Format as Table and then choose a table style.

Tip 

By selecting one cell in the data, Excel 2007 automatically selects a range of cells that contain data; however, you can select a different range of cells to use when creating a table. In addition, if your data does not contain headers, Excel 2007 creates them for you and labels them as Column1, Column2, and so on. If the data you want to export is already found within an Excel 2007 table, you do not need to complete the first three tasks of this exercise.

3. When the Format As Table dialog box appears, click OK.

Excel 2007 converts the data in the workbook into a table. An Excel 2007 table was previously known as a list in Excel 2003. Each column header contains an AutoFilter arrow and a black border surrounding the data, which means that the table is active on the worksheet.

The Table Tools, Design contextual tab appears.

Tip 

If the table is not active on the worksheet, then the Design tab disappears. To export a table, it must be active on the worksheet. To make a table active, click any cell in the table.

4. On the Design tab, in the External Table Data group, click Export.

5. From the drop-down menu, click Export Table to SharePoint List.

The first step of the two-step Export Table to SharePoint Site Wizard is displayed.

6. In the Address textbox, type http://wideworldimporters.

7. Select the Create a read-only connection to the new SharePoint List check box.

Important  

If the “Create A Read-Only Connection To The New SharePoint List” check box is selected, the spreadsheet is linked to the SharePoint list and you can synchronize updates from the SharePoint list to the spreadsheet. However, once the SharePoint list is created, you cannot link the spreadsheet to the SharePoint list. Therefore, if you wish to synchronize updates between the list and the spreadsheet, be sure to select this check box now.

8. In the Name textbox, type SalesFigures. In the Description text box, type This list contains furniture sales for this year.

9. Click the Next button.

Excel 2007 checks the data in each column to ensure that the data belongs to a data type supported by Windows SharePoint Services. If it doesn’t, Excel 2007 usually applies the Text data type to each column. Excel 2007 also checks whether each column contains only one type of data. If a column contains a mixture of data types, such as numbers and text, then Excel 2007 chooses Text as the data type. Once Excel 2007 completes its check, the second step of the two-step Export Table to SharePoint Site Wizard is displayed.

In the Key Cell column, notice that cell $C$4 in column Q2 contains a different data type from the rest of the cells in this column. Also, the formulas are removed from the Total column.

Tip 

At this point, you can click the Cancel button, correct the erroneous data, and then restart the export process. Also, because Excel 2007 removes formulas during the export process, you may consider deleting the Total column and creating a calculated column once you have completed the export process and the data is on your SharePoint site.

10. Click the Finish button. A Windows SharePoint Services dialog box is displayed with the URL of your new SharePoint List.

Important  

The new SharePoint list does not appear on the Quick Launch.

11. Click the http://wideworldimporters/Lists/SalesFigures/Allitemsg.aspx link. A new Internet Explorer window opens displaying the new SharePoint list.

Important  

Before you close Excel 2007, notice that the spreadsheet contains two extra columns. When you export a spreadsheet that is linked to a SharePoint list, Item Type and Path columns are added to your spreadsheet. On the Design contextual tab, use the External Table data group to alter the properties of a range of cells, open the connected SharePoint list in a browser, or unlink a list.

CLOSE all browser windows.

CLOSE the Windows SharePoint Services dialog box by clicking OK.

CLOSE Excel 2007 and save changes to the spreadsheet.