Wednesday, May 5, 2010

SharePoint Document Library Open Excel File in Browser

The excel file uploaded to the document library (Excel) by default will be opened in the Microsoft Office Excel Application. To open any newly added excel file in browser, following configuration is required.

Open Library --> Settings --> Document Library Settings --> Advanced Settings (under general settings) --> Browser-Enabled Documents section --> Display as web page --> hit OK button

Then the excel file which needs to be open in browser must be published to the library rather than uploading directly. To publish the excel file follow the below steps.

Open Excel File --> Office Button --> Publish --> Excel Services
Then a dialog box opens and asks for the file name to save at specific location shown in address bar of that dialog box.
To publish the file in document library type in the URL of the document library URL (http://site/library name) in the address bar and hit ENTER. Now the document should be saved in the library.

Note: Publishing via Excel services unlocks the possibility of making available named cells as parameters to the excel sheet on the web. Named cells can be exposed as parameters by clicking on the "Excel Services Options" box in Microsoft Office Excel 2007, during the publish process. Cells can be named using the DefineName thing on the Formula ribbon/bar. Also, if you wish to limit the publishing to specific sheets, or part of a sheet - you need to use a version of Office that has the ability to publish to Excel services.

After the above settings if the published excel file is tried to open in a web page it gives the following error.
Error : You do not have permissions to open this file on Excel Services.
Make sure that the file in an Excel Services trusted location and that you have access to the file.

To resolve the error/issue few more settings need to be changed/configured in Central Administration. To do so follow the below steps.
1. In SharePoint Central Administration, go to 'Shared Services Administration'
2. Select your shared service. (Default is SharedServices1)
3. In 'Excel Services Settings' section select 'Edit Excel Services settings'.
4. Ensure that File Access method is Process Account. Click OK.
5. Back in 'Excel Services Settings' section and select 'Trusted File Locations'
6. Add a new Trusted file Location:
    URL: Specigy the specific library or the site URL in which the library exists
    Location Type: Windows SharePoint Services
    Trust Children: Children Trusted checkbox should be ON/YES
7. Reset IIS

All the documents published to the library with above settings will be opened in browser when the users clicks on the link of the Excel file in library.

1 comment: