Importing Data from an Excel 2007 Spreadsheet to a List in SharePoint

In many situations, you might already have data within a spreadsheet, but later find that you need to share the data with other members of your team. SharePoint provides the ability to import data from an Excel 2007 spreadsheet into a SharePoint list. Those users who have appropriate permissions may read the SharePoint list, while others may even revise the list or enter additional data. You can choose to import all of the data held on a worksheet, a range of cells, a named range, or an Excel 2007 table.

In the following exercise, you will use your browser to create a SharePoint Custom list that contains data imported from an Excel 2007 spreadsheet.

OPEN the SharePoint site to which you would like to import data from the Excel 2007 spreadsheet. Remember to use your SharePoint site location in place of http://wideworldimporters in the exercises. If prompted, type your user name and password, and click OK.

USE the  Furniture_Price.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.

BE SURE TO install and activate the 2007 Microsoft Office suite before beginning any of the exercises in this chapter.

BE SURE TO verify that you have sufficient rights to create a new list. If in doubt, see the Appendix on page 435.

Tip 

This exercise works when using Microsoft Office Excel 2003 as well as Excel 2007, but you must install the Compatibility Pack for the 2007 Microsoft Office System to import the xlsx file.

1. Click Site Actions and then click Create. The Create page is displayed.

2. Under the Custom Lists, click Import Spreadsheet. The New page is displayed.

3. In the Name textbox, type FurniturePrice.

Tip 

Any URL in SharePoint is limited to 260 characters. The Name that you type here is used to create both the URL and the Title of the list. Later in this exercise, you will alter the title with a user-friendly name.

4. In the Description textbox, type This list contains the furniture items in stock together with their unit prices.

Important  

If you import a spreadsheet into a site based on the Meeting Workspace template, an option appears on the New page to share the same items for all meetings. If you choose not to share the same items for all meetings, then each meeting displays the list with only the items added for that date. Once items become series items for a list, you cannot change the setting back to list items for a specific date.

5. Click the Browse button. The Choose file dialog box appears.

The Choose file dialog box displays your Documents folder or the last folder that you accessed.

6. If the Documents folder is not displayed in the Choose file dialog box, under Favorite Links, click the Documents icon.

7. Double-click Microsoft Press, SBS_WSSv3, Chapter 12, and then double-click the  Furniture_Price.xlsx file.

8. On the New page, click the Import button.

Excel 2007 opens  Furniture_Price.xlsx and displays the Import to Windows SharePoint Services List dialog box.

9. From the Range Type drop-down list, check that Table Range is selected.

10. Click in the Selected Range drop-down list, choose Stock!FurniturePriceTable, and then click Import.

The All Items view of the FurniturePrice list is displayed, and the URL in the Address box is http://wideworldimporters/Lists/FurniturePrice/AllItems.aspx.

Tip 

If you import a range of cells from an Excel 2007 spreadsheet and want the Excel 2007 column names to become the SharePoint list column names, you should first edit the spreadsheet and convert the range of cells to an Excel 2007 table. See the last exercise in this chapter for instructions on converting a range of cells into an Excel 2007 table.

11. To change the title of the list, click Settings, and then click List Settings. The Customize Furniture page appears.

12. Under General Settings, click Title, description and navigation. The List General Settings: FurniturePrice page appears.

13. In the Name and Description area, in the Name textbox, type a user-friendly Furniture Price List.

14. Under the Navigation area, click the Yes option to display this list on the Quick Launch.

15. Click the Save button at the bottom of the Web page. The Customize Furniture Price List page appears.

16. On the breadcrumb, click the Furniture Price List link. The All Items view of the Furniture Price List appears.

The title of the list has changed to Furniture Price List, but the URL remains set as http://wideworldimporters/Lists/PlantPrice/AllItems.aspx.

CLOSE the browser.