Back
Excel

How do I publish workbooks to the server?

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.

  • You can use “Save†or “Save As†to store the spreadsheet in a SharePoint document library or any other location accessible by the server.  Type in a path and a filename (using a UNC, or Universal Naming Convention path or a HTTP path) and you are done. 
  • You can use a feature that is new to Excel 12 – our “Publish to Office Server†feature.  Why the new feature?  Publishing not only saves out a copy of the workbook to the server but also provides additional functionality for defining how the workbook behaves when it is loaded on the server.  The “Publish to Office Server†command can be found in under the File menu.  Here is a shot of the dialog in current builds.


(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 server
When 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:

  • The entire workbook (this is the default setting)
  • A subset of sheets (as many or as few as you like)
  • A set of named items (these could be named ranges, charts, tables, PivotTables, and PivotCharts)

If the author chooses “entire workbookâ€, the entire workbook is viewable on the server.


(Click to enlarge)

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. 


(Click to enlarge)

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. 


(Click to enlarge)

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.


(Click to enlarge)

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. 


(Click to enlarge)

It is important to note that not all cells are eligible to be exposed as parameters.  Here are the restrictions:

  • The parameter must be a single cell, and cannot be a range
  • The cell must not contain a formula
  • It must be a regular spreadsheet cell, not a cell in a Pivot Table, Table, Chart, etc.
  • The cell must have a defined name

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.


(Click to enlarge)

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.