Dot Net team at Maven Infosoft has developed many extenions for Excel Services with the SharePoint Server. How to integrate Excel Services with SharePoint, has mentioned in this article.
Excel Services provides a server-side paradigm for Excel in which spreadsheets published to SharePoint can calculate and render on the server, letting users pull results down to their browsers. In this new scenario, analysts can publish their worksheets to SharePoint, and allow Excel Services to kick in, meaning that anybody who browses to the site containing the spreadsheet can access the full functionality of the sheet whether or not they have Excel installed. In this scenario, the formulae that analysts enter become the code that everyone will run. You don’t need a developer to reemployment the formulae or create a new application.
Creating and Configuring SharePoint for Excel Services:-
First of all create the Blank Site. At the top right of the screen you can see the Site Action menu dropdown list. Select “Create” from the dropdown and you will be taken to a screen containing several categories of options for creating various types of item (See below Figure).
Select “Document Library” from this screen to create a new Document Library. On the Document Library screen, give the new document library a name and a Document Template. Choose any name you like (the examples use “Analytics,” and select “Microsoft Office Excel Worksheet” as the document template).
And Click on Create.
Running Excel Services:
Before Excel Services will work with this repository; you must make sure that the services are up and running correctly. To do this, return to the Central Administration screen, and select the Operations tab. From here, select the “Services on Server” screen. You’ll see a list of services at the bottom of the screen. Make sure that “Excel Calculation Services” is started. If it isn’t, use the link beside it to start up the service.
Additionally, you’ll have to configure a trusted file location for your Excel workbooks. You do this by returning to Central Administration, and selecting the “Create or configure this farm’s shared services” link. You’ll see the “SharedServices1 (Default)” entry on this screen. Drop this down, by clicking the arrow to the right of it, and select the “Open Shared Services Admin Site” option. This opens the administration screen for these shared services. On the right side of this screen you’ll see the “Trusted File Locations” link which you can use to set up where you can store the Excel files securely. Select “Add Trusted File Location” and you’ll see the screen below.
Use the URL address of the document library that you set up earlier (for example: http://win2k3sp:17964/sites/DevX1/Analytics) and make sure to specify the Location Type as “Windows SharePoint Services.” Click “OK” and the SharePoint will create the trusted file location as shown in Figure.
Creating and Publishing a Spreadsheet:-
Now you need a spreadsheet to use for shared calculations. For this example, you’ll build a very simple example spreadsheet that serves to demonstrate how the calculation services work. The spreadsheet performs a simple analytics calculation, called a “P/E Ration,” which simply divides the price of a stock by its earnings. It’s a useful analytic that tells you how many years you’ll need to own a stock before it pays for itself in earnings. So, for example, if your stock costs $100 and earns $10 a year, it will take 10 years to pay for itself in earnings. This ratio (or analytic) is usually seen as a great indicator of the value of a stock. In a real-world scenario, the user will provide a stock ticker, and the application would retrieve the values for price and earnings from a database. The spreadsheet could then divide price by earnings using a formula. For simplicity, this example doesn’t use a database—you’ll simply provide the spreadsheet with the price and earnings values. The P/E will be a simple Excel formula that divides the named ranges for the Price and Earnings values.
I’ve added a chart to show the relative values of Price and Earnings, and you can see the spreadsheet in Figure.
When you’ve completed the spreadsheet, publish it to SharePoint using the Excel Services option on the “Publish” menu see Figure.
When you select this, the “Save As” dialog will appear. You can use this dialog to specify the save location this should be the location that you configured as a Trusted Location earlier. However, before you do this, you should select the “Excel Services Options” button at the bottom of the dialog. You use these options to specify the items within the sheet that you want to have available to browsers. This is important, as you may have some proprietary stuff that you do not want to publish. You can simply uncheck that from this dialog and it will not be published to SharePoint, and thus kept private.
You’re now ready to publish. For the file name, specify the path to the SharePoint site that you set up earlier. The dialog will refresh and show you the document library. You can then save the document into the library. (See below Figure).
SharePoint publishes your workbook into the document library. If you browse to this document from IE, you’ll see an Excel-like generated interface. Remember, your clients do not need to have Excel installed to use the application that you’ve just built in Excel that’s part of the magic of using SharePoint and Excel services.
You may open the worksheet from the Document Library (See Figure).
In this article you saw the procedures to configure and use Excel Services with SharePoint 2007. This is a terrific and incredibly useful service that allows you to publish your Excel documents to a server library and let users run them, taking advantage of the Excel’s calculation power and formulae. This is important on a number of fronts. First, it prevents version drift of documents a common problem for companies that use a lot of spreadsheets. Having a centralized repository means that all users can share the same version. Additionally, because the calculations can now run on the server, many users don’t even need to have Excel installed! They just need to be able to browse to the SharePoint repository and render the sheet within their browsers. The user who specifies the calculations also becomes the developer of the functionality available to the end users. So, instead of having a developer translate Excel spreadsheets and formulae into a language suitable for Web applications and building a custom UI, your formula experts can simply publish their spreadsheets to Excel and have end users consume them directly.