Import data from Sharepoint list to excel

You can import and analyze data from the list on the Microsoft Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 website. For example, you can import a parts inventory list from a Sharepoint site, and then create a PivotTable in Microsoft Office Excel 2007 (pivot Report: provides a chart for interactive data analysis, similar to a PivotTable. You can change the data view, view detailed data of different levels, or drag fields and display or hide items in fields to re-organize the chart layout .) To summarize and compare the data.

Overview

To import data from a Sharepoint site, you must have Office EXCEL 2007 installed on the computer on which you want to use the Sharepoint list.

After importing data from a Sharepoint site to Office EXCEL 2007, you can analyze the data in Office EXCEL 2007, however, any changes made in Excel are not reflected in the list on the SharePoint site.

To use an office program to manipulate data in the list on a Sharepoint site and reflect these changes on a Sharepoint site, you can use Microsoft Office Access 2007 or Visual Basic for Applications (VBA).

In addition, you can operate data in the Sharepoint list in the workbook that is saved as an Excel 97-2003 work book (.xls format. However, if you save an Excel 2003 workbook as an Office EXCEL 2007 worksheet (.xlsx format), you can only provide read-only connections from the Sharepoint list.

When the list is updated on the website, you can update the workbook with the latest data from the SharePoint website. When updating data from a Sharepoint site, the raw data from the SharePoint site and all the changes you made in Office EXCEL 2007 will be overwritten.

In Office EXCEL 2007, You can import data to an existing worksheet or create a new worksheet. If the list on the SharePoint site contains folders, the structure is not displayed in the generated Excel worksheet.

When you import data from the Sharepoint list with read-only connections to the list, the "project type" and "path" columns are added to the data on the worksheet. Use these two columns to filter and sort data based on the data type and its location on the website (for example, the data is located in the subfolders of the list.

To import data from a list on a Sharepoint site, you must connect to the SharePoint site and have the permission to read the list.

 

Import data from the Sharepoint list

You can import data from the list on the Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 website. When importing data from the Sharepoint list, some steps may vary depending on the version running on the SharePoint website.

How to determine the version running on the SharePoint website

· In the upper-right corner of the Windows SharePoint Services 3.0 website, a menu containing your user name or account may be displayed.

· On the Windows SharePoint Services 2.0 website, the top-level navigation in the upper left corner may contain "home page", "document and list", and "help" links.

　　NoteThe website owner or administrator can customize your website. If you are not sure which version you are using, contact the website owner or administrator.

1. Perform one of the following operations on the SharePoint Website:

Windows SharePoint Services 3.0

1. If the list has not been opened, click its name on "Quick Start. If the list name is not displayed, click "View All website content" and then click the list name.

2. on the "operations" menu, click "export to Workbook ".

3. If the system prompts you to confirm the operation, click OK ".

Windows SharePoint Services 2.0

1. If the list has not yet been opened, click "document and list" and then click the name of the list.

2. on the page where the list is displayed, click Export to workbook under "operations ".

2. In the "File Download" dialog box, click "open ".

3. When the system prompts you whether to enable the data connection on your computer, if you believe that the data connection to the SharePoint website can be securely enabled, click Enable ".

4. Perform one of the following operations:

· If the workbook is not opened, Excel creates a blank workbook and inserts data in the new worksheet as a table.

· If the workbook has been opened, perform the following operations in the displayed "import data" dialog box:

1. Under "select the display mode of the data in the workbook", click "table", "PivotTable", or "pivot and PivotTable ".

2. Under "data placement", click "existing worksheet", "new worksheet", or "New Workbook ".

If you click "existing worksheet", click the cell in the upper-left corner of the list to be placed.

3. Click OK ".

 

Update data from the Sharepoint list

When other users continue to update the list on the SharePoint site, you can use the latest changes to update the data in the worksheet. However, this will overwrite the data originally imported from the Sharepoint list and the changes made to the data in Excel.

1. Click any location in the table to be updated.

2. on the "External table data" tab, click "refresh ".

　　PromptIf you no longer need to connect a worksheet to a Sharepoint site, you can disconnect the table. On the "External table data" tab, click "unlink ".