Use Excel service to read external data sources (SQL database)

Source: Internet
Author: User
In the Excel Web Access Function and Application Section of the Excel service, you can see that using Excel for data reporting and data analysis is a good solution, there are a variety of chart forms in it, but the data in it basically needs to be processed manually for data input operations and other work, so there are some inconveniences, at the same time, there is also a problem of data synchronization. If the data can be directly obtained from the database, it will solve this bottleneck problem. Of course, Ms will also take this into consideration, because some EXCEL provides a data connection method to connect to obtain external data sources. The following describes how to connect Excel services to read External SQL database data. For detailed steps, see the following operations:
Step 1:
Open the SharePoint management center and enter the sharing server, as shown in:

Go to edit Excel services settings, such:

Set the account name and password in "external data", as shown in:

OK.
Step 2:
Open the "Excel database" document library, click "new", create an Excel document, and click the menu on the toolbar, data ----> from other sources ----> from SQL Server, as shown in:

Open the SQL logon verification dialog box, such:

Next step

Click Finish. In the "Import Data" dialog box, do not select a table (external data tables are not supported, as described in the help document). To use a data transparent table, select:

In the SQL logon box, enter the Logon account and password, and click OK, for example:

Place the Department and amount in the tool menu on the right to the corresponding position, and select the desired image, as shown in:

Click the data item ----> connection in the tool menu, for example:

Select to allow the background to refresh and refresh data when opening the file. As shown in:

In the define option box, select Save Password. For example:

Click Verify settings of Excel services to set the verification type to none, as shown in:

OK, OK, and close. Save the document ,:

Save.
Step 3:
On the Sharepoint Server, open the CMD command line and enter:
Stsadm-o set-ecssecurity-accessmodel delegation-SSP sharedservices2 (the default access model is trusted subsystem)
Stsadm-O execadmsvcjobs

Step 4:
View results. Open the "Excel database" document library and open the "reimbursement by Department summary Reimbursement Form" document just created. The results are shown in:

Check whether the result is correct in the database table, as shown in:

I don't need to talk more about the answer. I hope my little experiences will help you.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.