You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Now that I have spent some time talking about goals and architecture, I want to spend some time talking about the features provided by Excel Services, starting with how you get a spreadsheet on a server in the first place.
There are two ways to get a spreadsheet to the server.
(Click to enlarge)
So, for example, if you authored a spreadsheet and wanted to publish it to a server running Excel Services, you just need to bring up the dialog, enter a path and filename, and press Publish. Note that the ”Open this workbook in my browser“ checkbox is checked by default, so you will see the workbook produced by the server in your browser immediately after publishing. This provides a great way to see if the workbook loads and looks the way you want. If it doesn’t, you can tweak it in Excel and republish it. It is that easy.
Note that publishing is actually saving a copy of the file to the server, which, after it has finished, leaves you working on your original version of the file. If you make further changes to the file and wish to re-publish, you simply need to launch the dialog and click Publish again. The publish dialog remembers any settings you have made for that file (they are actually persisted with the spreadsheet). Let’s go into some more details about what you can do at publish time.
Controlling what is visible on the serverWhen you publish a spreadsheet to Excel Services, the entire spreadsheet is always saved to the server because it is required for data refresh or calculation. However, the spreadsheet author can control which parts of the spreadsheet are visible when it is viewed in the browser or accessed through the web services API. (Limiting the viewable area of the spreadsheet offers clarity and security benefits described in the previous post). Excel 12 provides three choices for controlling the viewable area of the spreadsheet on the server:
If the author chooses “entire workbook”, the entire workbook is viewable on the server.
If the author chooses “sheets”, only the sheets they select are viewable on the server. Note that this does not affect how the spreadsheet looks in Excel 12 client, only how it looks on the server. This is useful when you have workbooks that contain lots of “behind the scenes” sheets that hold intermediate calculations, source data, etc., but only a few sheets that you would like users to see. If the author needs to make changes to the workbook over time, however, they still get the full experience in Excel 12, and can see the entire workbook without having to unhide sheets.
If the author chooses “items in workbook”, only the items they then select are viewable on the server in a mode that we call “Named Object View” (working title – actual feature name may change). In this view, the user is provided with a drop-down in their browser that allows them to select the item they wish to see.
For example, if I saved a spreadsheet to the server and selected a table, a PivotTable, and a named range (“DetailTable”, “PivotTable1”, and “SalesDashboard”), the user would see this.
Each item is displayed one at a time, which makes them perfect for setting up dashboards. Even though only a single item at a time is shown, the entire workbook is loaded on Excel Services, so the objects are fully refreshable and interactive.
Defining parameters While users will be able to interact with spreadsheets in the browser (full details next post), they will not be able directly edit cells in the grid. In order to enable a greater number of scenarios, we added the ability to specify parameters as part of the publish process. Spreadsheet parameters allow the author to expose specific cells whose values can then be changed by the user. Excel Services provides a built-in “task pane” for this purpose. Once the value has been changed, the spreadsheet calculates new values, and the user sees the results in the browser.
It is important to note that not all cells are eligible to be exposed as parameters. Here are the restrictions:
Marking a cell as a parameter for Excel Services is done in the publish dialog. The parameter tab provides a place where you can add, edit, and delete parameters for that workbook.
Clicking the “Add” button will present you with a list of available cells that meet the criteria above. We have also added object model support for setting a named cell as a parameter.
One thing worth noting is that even if the location of the parameterized cell falls into a range of the workbook that is not marked as visible, it can be edited on the server i.e. a user may not necessarily be able to view that cell, but they will always be able to set its value. This allows a user to set a parameter without showing the underlying data so that the workbook can be presented in a clean and professional manner.
Next post, I will spend some time explaining the other features that allow users to interact with spreadsheets in a browser.
Comments: (11) Collapse
Can workbooks saved in a SharePoint document library or published to Office server contain external references to other workbooks? If so, will both features also save copies of the other referenced files to the server?
Presumably user-defined functions in VBA modules won't be allowed. You've mentioned that it won't support VSTO code, and I'm extrapolating.
The obvious question : how do you secure parameters and/or worksheet visibility with user credentials? (such that "Joe" can see table1, "Mary" can see table1 and table2, and so on)
Another question : publish dependencies anytime a workbook being published has one or more external references in formulas. Question is : is this supported at all? if not, is there a warning message?
Will it be possible to save each user's selections to a table in the worksbook?
Will custom views be accessible by the user ?
Can a button on a sheet (that is calling a VBA procedure) be activated by the user ?
Jean
It's a shame that parameters can only be single cells. I'd have thought that most non-trivial algorithms would require lists of data, such as cash flows, prices, etc. Any chance of removing that restriction (at least for the web service interface)? I'm having a hard time thinking of any of my models that don't take lists of data for their inputs.
Salutations,
Harlan, the workbooks saved in a SharePoint document library can include references to external workbooks but in this case, Excel Services will not be able to load and calculate them. You are correct with respect to VBA not being supported. Our plan is to support managed code UDFs.
Anon, you are right, it would be great to be able to secure visibility with user credentials, but this will not be supported in v12. The differentiation is between having the right to open the spreadsheet and thus see everything vs. having only the right to view the spreadsheet or calculate it on the server and then only the visible parts are returned. Also, as I replied above to Harlan, linked workbooks are not supported. The server returns a error when you publish a spreadsheet with external references. The “quickest” way to see it is keep the “open in browser” option checked when you publish. We designed the check as server side and not client side to allow for flexibility in upgrading the server to support additional client features orthogonally to changing client code.
Graham, I am unclear as to what you mean by user selection?
Jean, VBA code and buttons are not supported on the server and neither are custom views. I will be posting a complete list of unsupported features in the future.
Stephen, Parameters in the web browser interface are single cell. We think this answers the scenarios we had in mind for dashboards (more in a few posts) and simple what-if style analysis. The web service interface does let you set and get ranges in addition to single cells. I will be posting more details the API soon, so stay tuned.
<i>The web service interface does let you set and get ranges in addition to single cells. </i>
Excellent!
<i>Our plan is to support managed code UDFs. </i>
In Excel 12 as well, or just the server? So all we'll need to do is make sure the managed UDFs have the same function signature as the VBA/XLL functions and it will 'just work' ?
Can you explain how this compares to shared workbooks in the current version of excel? I currently use excel and the shared documents feature to save workbooks on our network to collabrate with my team (about 12 people). We all access the same file and put in information (basically just a list of data).
Also my company now is using sharepoint sites, but I've yet to see the real advantage to a sharepoint site compared with what I was doing with a shared workbook/excel/and a network drive. From fooling around with sharepoint, seemed like only one person could have an excel file checked out at a time.
Hi,
I'm wondering how easy it would be to use Excel to conduct surveys? Would it be easy to rig things up so that the user could come along, make some selections on combo-boxes or whatever, and have the data append to a list in a hidden sheet. Or is the data read-only?
G
Stephen
Managed code UDFs work from Excel XP - just use a class library project and register them (in the registry)(sample project (C#) on the codematic site).
David will the manged UDF mechanism be the same in Excel 12 as currently, or as Stephen suggests more like the XLL register approach?
Supporting manged UDFs on the server is a great move btw.
cheers
Simon
Howdy everyone.
Stephen, glad you like it. More details later this week when I will post specifically about the web service. Direct support for managed UDFs is only planned for the server this release. Excel 12 will continue to support the existing UDF interfaces. And yes, the design is such that the same spreadsheet will work client and server, even if there are UDFs being called, as long as the function signature is the same. The only exceptions are cases for which we do not support the parameter type on the server or if there is a need for type casting that is not supported.
cld2b, shared workbooks targets a different set of scenarios than new Excel Services. Shared workbooks is designed for multi-user authoring of spreadsheets. It allows multiple Excel users to open a single spreadsheet file at the same time. It is opened in memory on the each machine and changes are persisted to the single file copy. Excel Services, on the other hand, loads and calculates spreadsheets on the server and multiple users can access the spreadsheet that is in server memory, but they are working with an isolated state of that spreadsheet and cannot save their changes. Excel Services is designed for multi-user viewing and browsing of spreadsheets, but not authoring.
SharePoint sites provide many features beyond just a shared file location. This includes collaboration, personalization, web-based lists etc. They also provide document management functionally and here again, it is a different scenario than the shared workbooks feature. With SharePoint document management you can control versions and making sure that only one person is editing the file at any given time. This is useful for managing critical spreadsheets, for example.
Graham, if you are referring to Excel Services (and not Excel client) it does not allow you to edit values in the cells and persist them back into the spreadsheet file. While you could build a custom solution using our new web service API, this wouldn't be the best way to do it in Office 12. SharePoint actually has a built in feature for surveys. That is probably the quickest route. This is already available in existing versions of SharePoint and you can even take the results into Excel for further analysis.
Simon, no change change in how Excel 12 (client) accesses managed UDFs.
Comments: (loading) Collapse