Displaying Excel Spreadsheet Content in a Web Part<\/strong><\/span><\/p>\nDisplaying Excel data on a Web page can come handy but displaying content in a Web part can be\u00a0even more useful. There are several advantages to this technique but perhaps the biggest advantage is that you can simply modified the content in an Excel spreadsheet and the content displayed in the Web part can be quickly updated. SharePoint can display data in columns but does not have the ability to display certain types of content, such as charts. Imagine having an Excel chart that can not only be displayed in SharePoint, it can be dynamically updated by modifying the Excel chart in a spreadsheet.<\/p>\n
The following step-by-step procedure demonstrates how you can display content in an Excel spreadsheet directly in a SharePoint Web part.<\/p>\n
1. Perform steps 1-10, if you haven’t already.<\/p>\n
2. In step 11 above the assumption was that clients do not have Excel installed on their computers and therefore we checked the box\u00a0“Open in the client application.”\u00a0Now we will assume that the clients have Excel on their computers, which is the more likely scenario, so let’s check the box “Open in the client application.”<\/p>\n
3. Add a Web part to the page you are working with.<\/p>\n
4. Select the Excel Web Access Web part under the Business Data section and click Add.<\/p>\n
5. Open the tool pane by clicking the link “Click here to open the tool pane.”<\/p>\n
![\"excell_web_access\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/04\/excellwebaccess.jpg\" "\\"excell_web_access\\"")
<\/p>\n
6. In the Workbook box click the browse button and add the Excel spreadsheet. In my example I added the workbook called “Sales by Region – 2008”. You can either use URLs or UNC in this box. I typically use URLs.<\/p>\n
7. You can enter the name of\u00a0a defined region that you want it loaded as default\u00a0in\u00a0Named Item box. For example, I typed the name Chart 1<\/em> as my default\u00a0named range so when you refresh the page Chart 1 will be displayed.<\/p>\n8. You can modify the title of the Web part under Appearance if you want.<\/p>\n
9. Click OK to apply the changes.<\/p>\n
10. If you get\u00a0the following Access Denied error, see the troubleshooting section\u00a0in this article for more details.<\/p>\n
You do not have permissions to open this file on Excel Services.
\n<\/span>Make sure that the file is in an Excel Services trusted location and that you have access to the file.<\/span><\/em><\/p>\n![\"ecs_access_denied\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/04\/ecs_access_denied.jpg\" "\\"ecs_access_denied\\"")
<\/span><\/em><\/p>\n11. At this time you should be able to see the content of your Excel spreadsheet in a SharePoint Web part, thanks to Excel Calculation Services and Excel Web Access Web part. You can select a different Named Range from the View drop-down menu to modify the view.<\/span><\/p>\n![\"web_part_view\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/04\/web_part_view.jpg\" "\\"web_part_view\\"")
<\/span><\/p>\nTroubleshooting<\/strong><\/span><\/span><\/p>\nAccess Denied Error<\/strong><\/span><\/span><\/p>\nOne of the more common errors that you might see is the Access Denied error. If you try to open an Excel workbook in the Web part and get Access Denied error, there are several things you can do to fix the problem.<\/span><\/p>\nNOTE<\/span>: If you are a Site Owner or a Business Manager who doesn’t have access to the MOSS 2007, you’ll need help from your administrator because these steps need to be performed on the server.<\/em><\/span><\/p>\n1. Make sure that the Excel\u00a0Services have been started for the SSP that\u00a0is configured for the Web application you are working with.<\/span><\/p>\n2. Go to the SSP and click Edit\u00a0Excel Service settings under the Excel Service Settings section.\u00a0If you are\u00a0using Impersonation for the File Access Method, switch to\u00a0the Process account instead. The File Access Method is used by Excel Calculation Services to retrieve workbooks from all non-Windows SharePoint Services trusted file locations. Impersonation<\/em> accesses the files as the end user account. Process account<\/em> accesses the files as Excel Calculation Services process account. If this change doesn’t make a difference, switch the setting back to the default.<\/span><\/p>\n3.\u00a0Depending on the type of authentication you are using, you may want to configure the Unattended Service Account\u00a0under the External Data section of\u00a0Excel Services settings. The Unattended Account is a special account used by\u00a0Excel Services when it tries to authenticate to a data source that is not using Integrated Windows authentication. This account is used to connect to data sources that require authentication, without this configuration the connections to the data source can fail. In general, keep in mind that you’ll only need\u00a0to configure unattended account\u00a0if you want to enable connections that have their authentication set to either None<\/em> or SSO<\/em> (Single Sign-On), where the SSO application ID is not using Windows credentials.\u00a0When you configure this account, enter it in the format\u00a0Domain\\AccountName<\/em>, as shown below.<\/span><\/p>\n![\"unattended_srvc_acct\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/04\/unattended_srvc_acct.jpg\" "\\"unattended_srvc_acct\\"")
<\/span><\/p>\n4. Yet another reason for this error could be related to the Trusted file location<\/em>.\u00a0Go to the\u00a0Trusted File Location (located in SSP Administration site under Excel Services Settings section. Check the Children trusted<\/em> box under Trust Children. Location type should be Windows SharePoint Services.<\/span><\/p>\n5. When you add the location of the Trusted File Location you may have to try a few different tricks. If the full path is not working, try using only the name of the Document Library. You can also try adding the path to the entire SharePoint site. If you are working with a subsite and or if in general the URL has a space, you may need to enter %20 in place of empty space.<\/span><\/p>\n6. If the file is uploaded to a SharePoint library and you are pointing to a file in the library, make sure that your Location Type is set to Windows SharePoint Services, not UNC or HTTP. For example, let’s say you want to add path to a subsite called Training in the SeattlePro1.com domain. You will enter the URL http:\/\/www.seattlepro1.com\/training<\/em><\/span> in the Address box and then leave the Location Type to the default Windows SharePoint Services<\/em>. Keep in\u00a0mind that you can add multiple Trusted File Locations if you want.<\/span><\/p>\n<\/span><\/p>\nMiscellaneous<\/strong><\/span><\/span><\/p>\n1. On the Trusted File Location page verify that\u00a0your workbook is not larger than the default value of 10MB. By default, Excel Services cannot\u00a0open Excel files that are\u00a0larger than 10MB. Change the\u00a0Maximum Workbook Size setting under Workbook Properties accordingly. The allowed values range between 1 and 2000 in MB.<\/span><\/p>\n2. If you are displaying a\u00a0chart, keep in mind that by default Excel Services can only open charts that are no larger than 1MB in size. You may have to adjust this setting to display the chart.<\/span><\/p>\n
\nCopyright \u00a92009 Zubair Alexander. All rights reserved.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"Excel Services is one of the coolest features in\u00a0Microsoft Office SharePoint Server (MOSS) 2007. The primary purpose of Excel Services is\u00a0control, secure, and manage access to Excel workbooks. Microsoft defines Excel Services as an enterprise-class application server that allows\u00a0rendering of\u00a0workbooks (.xslx, xslb), and it enables you to easily reuse workbook components, such as charts and […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[43,54,11],"tags":[],"class_list":["post-1051","post","type-post","status-publish","format-standard","hentry","category-articles","category-sharepoint","category-tips-tricks"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/posts\/1051","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/comments?post=1051"}],"version-history":[{"count":0,"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/posts\/1051\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/media?parent=1051"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/categories?post=1051"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.zubairalexander.com\/blog\/wp-json\/wp\/v2\/tags?post=1051"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"publish_to_excel_services\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/03\/publish_to_excel_services.jpg\" "\\"publish_to_excel_services\\"")
<\/p>\n![\"name_a_range\"](\"https:\/\/www.zubairalexander.com\/blog\/wp-content\/uploads\/2009\/03\/name_a_range.jpg\" "\\"name_a_range\\"")
<\/a><\/p>\n