One way to use the SharePoint workbook as the data source in the workbook client!

Because the workbook data is embedded in an Excel file, this method can be used as offline data analysis (even if the source data is unavailable, it cannot be synchronously updated, however, you can still use imported workbook data), but there are also some disadvantages. For example, a large Excel file may be generated. Generally, the size of the Excel file in the workbook is more than MB, however, Excel files displayed only as reports are usually about 5 MB in size, and it is difficult to adapt to the following application scenarios:

1.Avoid repeated data. For example, when developing multiple workbooks, You need to generate different report views and use the same data. However, you may not want to copy the same data in all workbooks, this will cause more memory to be occupied on the server, and more scheduled tasks need to be arranged to refresh the data, which will increase the workload (such as modifying the metric value, you must replace and update multiple workbooks ).

2.Data needs to be locked. For example, some metric values are very sensitive and can only be used by specific user groups, that is, data permissions. You can lock permissions in SharePoint and assign them to different levels of data as needed.

3.Avoid performance issues when opening the workbook to refresh. If the data is updated in the workbook, but the data is not refreshed in the Excel file, the data or chart in the Excel file will not change, and you need to force refresh.

These will lead to inefficiency and will become very slow in many cases. Is there a way to present data only in Excel or charts that do not contain the workbook data? The answer is yes. This article provides a thin workbook that uses the workbook data in SharePoint as the Excel Data Source (instead of embedded, it is used as the core workbook, you can develop different view reports based on the core Workbook. The procedure is as follows:

1. Download a workbook from Sharepoint to a local machine, for example, workbook healthcare audit.xlsx, for example:

The size of the workbook healthcare audit.xlsx file is about 4.8m, for example:

 

 

Open the connection property of this workbook and you can see Data Source = $ embedded $, which is an embedded data source, such:

 2. Change the data source = $ embedded $ of the connection attribute of this workbook to Data Source = http://portal.contoso.uat/sites/cockpit/PowerPivot/PowerPivot healthcare audit.xlsx, such:

After you press OK, the OLAP data will be refreshed, and the data source will be refreshed from the workbook data in Sharepoint, as shown in,

After the preceding steps, save it as "workbook healthcare audit test.xlsx". Note that the sizes of the two files are the same (the workbook data is still embedded in the Excel file), for example:

3. Use the compression software to open the workbook healthcare audit test.xlsx, remove the embedded workbook data, and use NotePad to open item1.data under the XL \ customdata directory, clear all data of this file and save it to the compression software, for example:

After that, the workbook healthcare audit test.xlsx does not contain the workbook data, but only the Excel data. The file capacity will be greatly reduced, for example:

The "thin Workbook" has been completed through the above steps. Next, verify whether the setting is successful. Open the workbook healthcare audit test.xlsx again to check whether the data source of the connection attribute is a workbook of SharePoint, for example:

 You can see that the data source is successfully set, slice it, and save the workbook to see if the file capacity changes, as shown in:

After the operation is complete, save the result, such:

From the above, we can see that the file capacity has changed, 3 K less than before. open the file again to see if there is any workbook data, such:

 

From the above, we can see that there is no data in the workbook, that is, the data is refreshed through the SharePoint workbook. You only need to configure a refresh task in the SharePoint workbook to automatically synchronize data to the SharePoint workbook, such:

 

By integrating with Sharepoint, You can implement lightweight workbook, especially the ability to unify data sources (create "core" workbooks), reduce file capacity, and improve performance.

 

this blog is original in software life. You are welcome to reprint it. For more information, see http://www.cnblogs.com/nbpowerboy/archive/2013/04/29/3050943.html. Assume or use it for commercial purposes, but you must keep the signed software life (including links) in this article ). If you have any questions or authorization negotiation, please leave a message for me.