Excel Services part 6: Building applications with Excel Web Services

Excel Services(第6部分)：用Excel Web Services创建应用程序

While talking about scenarios for Excel Services, I mentioned “Reusing the logic encapsulated in Excel spreadsheets in custom applications” – which means accessing spreadsheets and their contents server-side via web services in a way that’s scalable and manageable. I would like to elaborate on this topic over a couple of posts. Today, I will introduce “Excel Web Services”; in a follow-up post I will show how to develop a small sample application using said web services.

在介绍Excel Services 中的方案时，我提到过”在用户自定义程序中重复利用Excel电子表格的逻辑封装”——可升级和管理通过网络服务器访问电子表格和存储信息服务器端。我在上面的一些文章中就这个主题作过详细的介绍。今天，我将介绍“Excel Web Services”，接下来的文章展示如何用网络服务器开发小的应用程序示例。

In a nutshell…

概述

Let’s briefly revisit Excel Services’ architecture. The Excel Calculation Service is the “engine” of Excel Services; it is the component that loads and calculates spreadsheets. In the “interact with the spreadsheet in your browser” scenarios covered in previous posts, Excel Calculation Services loads and calculates a spreadsheet and then hands it off to the Excel Web Access, which is the component that produces the HTML that ends up in your browser. The point of today’s post (and the post that will follow) is that developers can also use Excel Calculation Services without needing to interact with the Excel Web Access. Specifically, we have built a web service API directly on top of the Excel Calculation Services so that developers can call server-side spreadsheets directly from their own applications. For example, developers could write code that opens a spreadsheet on a server, sets cells and ranges to specific values, controls external data refresh and workbook calculation, and finally retrieves values from the calculated workbook (or retrieves the workbook in its entirety). Let’s look at some more specific examples.

先简单地回顾一下Excel Services架构 。Excel Calculation Service是Excel Services的”引擎”，是装载和计算电子表格的组件。在上篇文章中介绍了”在浏览器中与电子表格相交互”， Excel Calculation Service装载和计算电子表格，并且不需要Excel Web Access(配置在浏览器中生成HTML的组件)。这篇文章接下来介绍开发者不需要Excel Web Access的配合也能运用Excel Calculation Service。具体地讲，我们在Excel Calculation Service中已经直接创建了一个网络服务器API，以便于开发者能在他们自己的应用程序中直接调用网络服务器端的电子表格。例如，开发者能够写用来打开服务器上的电子表格的代码，设置单元格和区域为指定值，控制外部数据更新和工作薄计算，并且最后从已经过计算的工作簿中取回数值(或取回整个工作薄)。下面列举了一些更详细的例子。

What sorts of things are these web services good for?

这些网络服务有哪些功能？

• Excel Web Services enables many interesting scenarios. Here are two examples that we have heard repeatedly from our customers.

Using the logic in Excel spreadsheets in a server application – without the “traditional” need to recode this logic in a programming language. In this scenario, the business expert who authored the spreadsheet can keep maintaining the model in Excel; the administrator can protect that model on the server using the appropriate set of users, roles, permissions, and a firewall; and the developer can call Excel Web Services to incorporate the logic in the spreadsheet into the rest of a custom solution. Another variation on this would be providing custom UI to Excel-based server applications which use Excel Web Services to interact with a server-side calculation session.

Excel Web Service能处理一些有趣的情况。下面有两个经常从我们的客户那里听到的例子。

在服务器应用程序中的电子表格上运用逻辑——不需要使用”传统的”方法，即用编程语言重新编

写这些逻辑代码。在这种情况下，创建电子表格的商务专家能在Excel中维护这个模型；管理员

能在服务器上使用合适的用户权限设置来保护模型；开发者能调用Excel Web Services在用户

解决方案部分加入电子表格逻辑。在这方面的另一个变化是为基于Excel服务器应用程序提供了

自定义用户界面，该服务器应用程序使用Excel Web Services与服务器端计算进程进行交互。

• Automating spreadsheet updates on servers. This works especially well in combination with the new Open XML file format, which greatly simplifies the task of programmatically creating an Excel file from scratch or using a template. Once the new file has been created, it often needs to be calculated – for example, if there are external data feeds that need to be updated. It is straightforward for developers to write code to use Excel Web Services to do all of this, then retrieve an up-to-date copy of the calculated file and save it back to the server, or deliver it to any other destination.

在服务器中自动更新电子表格。这项功能相当好地组合在全新Open XML文件格式中，可非常简单地自动编程完成从稿纸或模板创建一个Excel文件的任务。一旦创建了这个新文件，就经常需要计算它——例如，如果有需要更新的外部数据，开发者可以直接写代码去指令Excel Web Services完成所有这些工作，然后取回这个已经过计算的文件副本并存储在服务器上，或者将它传送到另一个目标上。

What specifically can the web service do?

网络服务器具体处理什么工作？

Here is the summary: your code can start a session with the Excel Calculation Service, set values into cells and ranges, process the workbook, and get calculated values or the entire workbook back into your application.

概括地讲：你的代码能够开始与Excel Calculation Service进行会话，设置单元格和区域的值，处理工作薄，获得计算结果或整个工作薄并反馈到你的应用程序。

Here is the longer answer… a full list of what your code can do with Excel Web Services:

下面是Excel Web Services代码的全部列表：

• GetApiVersion: Get a version string of the installed web service API build.

• GetApiVersion:获取所安装的网络服务器API架构的版本字符串。

• sessionId = OpenWorkbook: Open a server-side calculation session. The method takes a workbook file path, and a few other arguments, and returns a sessionId.

• sessionId = OpenWorkbook:打开服务器端计算会话。这个方法返回工作薄文件路径和一些其它参数，并返回一个服务器会话ID。

• GetSessionInformation: Get a few properties of the server session, primarily the language context of the session.

• GetSessionInformation:获取一些服务器会话属性，主要是会话语言文本。

• SetCell: Set a value into a cell on one of the workbook’s sheets. Two flavors of this method exist: one takes a cell address (e.g “B52”) or a named range (e.g. “Interest”), and the other accepts integer coordinates, for cases where it is more convenient for your code to use them (typically when you have indexes in the code and want to use them to index the sheet).

• SetCell:对工作薄中某一工作表上的单元格设置值。有两种方法：一种使用单元格地址(如”B52”)或命名区域(如“Interest”)；另一种使用整数坐标值，有些情况下使用这种方法是更方便的(特别是当你在代码中有索引号并且想使用它们引用工作表时)。

• SetRange: Same as SetCell, but for setting values into an entire contiguous range. Same two flavors exist.

• SetRange:除了为整个相邻区域设置值外，其它与SetCell相同。

• Refresh: Read data from an external data connection (or all of the workbook’s connections) and refresh the values in the relevant cells, e.g. in PivotTable cells or in the results of cube formulas.

• Refresh:从外部数据连接(或所有工作薄连接中)读取数据，并且更新相关单元格的值，例如，数据透视表单元格或三维公式的结果。

• Calculate: Recalculate the formulas in a specific range or in the entire workbook. Useful when the workbook author has turned off automatic calculation. Two flavors – using a string or integer coordinates to refer to a range – much like in the Set methods.=

• Calculate:重新计算在整个工作薄或指定单元格区域中的公式。用于工作薄作者关闭自动计算功能的情形。两种方法——运用字符串或整数坐标指定单元格区域——与Set方法相同。

• CalculateWorkbook: Calculate the entire workbook, using one of two calculation methods:

• CalculateWorkbook:计算整个工作薄，运用下面的两种计算方法之一：

• Recalculate: Calculate only formulas that have dependencies that changed (aka “dirty” formulas).

• Recalculate:仅计算已发生变化的公式。

• CalculateFull: Calculate all formulas, regardless of dependency changes.

• CalculateFull:计算所有的公式，无论是否发生变化。

• GetCell: Get a value out of a cell. The two regular addressing flavors exist. You can either get formatted string values, or the raw binary values.

• GetCell:获取某单元格位置值。有两种常用的寻址方式，获取格式字符串值或原二进制值。

• GetRange: Get a set of values out of a contiguous range of cells. Same addressing flavors.

• GetRange:获取一组连续单元格区域位置值。采取同样的寻址方式。

• GetWorkbook: Get the entire calculated workbook into your application memory, as a byte array. You can either get the live result, or a snapshot – essentially, a workbook with the layout of the original workbook, with all the original formatting and with up-to-date values – but with all the formulas and external connections stripped, and without the portions of the workbook that were marked not for viewing during publish. More on snapshots in a future post.

• GetWorkbook:在应用程序内存中获取整个已经过计算的工作簿，作为一个字节数组。能够获取实时的结果或者屏幕快照——一个带有原先的工作簿页面布局和所有的初始格式以及最新数据值的工作薄——但所有的公式和外部连接除外，并且不带有在发布时没有标记为视图的工作簿部分。在接下来的文章中将有更多关于屏幕快照方面的内容介绍。

• CancelRequest: If your application runs the Excel Web Services session in a separate thread, and wishes to abort a long-running server request (e.g. a long calculation that the user got tired of waiting to) – it can do so by calling this method.

• CancelRequest:如果你的应用程序以单线程运行Excel Web Services会话，并希望中止长期运行服务器的请求(例如长时间的计算将使用户疲于等待)，那么你就能够调用该方法进行处理。

• CloseWorkbook: Tell the server to close the workbook that it opened for this session, thereby also allowing the server to release all the resources that it maintained for the context of your session.

• CloseWorkbook:告诉服务器关闭为会话打开的工作簿，因此，也允许服务器发布为维持会话文本的所有资源。

Error handling

错误处理

Errors are exposed to a developer’s application in three ways:

显示在开发者的应用程序中的错误有三种方式：

1. Excel calculation errors show up just like they do in Excel – as cell error values (e.g. #VALUE!). When you call GetCell or GetRange and ask for formatted values, you’ll get the #-style error string; when you ask for unformatted values, you’ll get an enumerated error code.

2. An error in processing one of the web service methods (which does not enable the method to finish successfully) is exposed as a SOAP exception that your code can catch.

3. Less critical errors, which do not prevent the method from returning normal results, are returned as part of the method arguments (specifically, as an output argument). The reason for this is that an exception would divert the code from its normal execution path, and this is not desirable with these non-critical errors. Checking for them is optional.

1. 计算错误的显示与Excel中一样——当单元格包含错误值时(例如#VALUE!)。当你调用GetCell或GetRange并且要求有格式的值时，你会得到#开头样式的错误字符串；当你要求无格式的值时，你会得到一个枚举错误代码。

2. 处理一个网络服务器方法过程中(不能够使方法成功地完成)的某个错误除了能捕获你的代码外显示为SOAP。

3. 不会阻止方法获得正常的结果的次要错误，其返回值作为方法参数的部分(具体地讲，作为一个输出参数)。原因是将代码从它的正常执行路径转移，这些次要的错误都不可取的。检查这些错误是可选择的。

Sessions

会话

One thing that developers will need to be aware of is the way Excel Calculation Services maintains sessions for performance reasons. A good way to understand the benefit of server state is to think about a user who interacts with an Excel spreadsheet in a web browser. Each time the user takes the next interactive step, e.g. drills down a PivotTable, changes an input parameter, refreshes data connections and so on – we want the server to only compute the difference between what the user saw on the screen before taking the current step, and what they should see as a result of this step. For performance reasons, we do not want the server to read the workbook file from disk again, or to recalculate formulas that do not need recalculation.

开发者需要注意使Excel Calculation Services为了性能而继续会话的方法。一种利于理解服务器状态的方法是考虑用户在网络浏览器中与Excel电子表格交互。每次用户执行下一交互步骤，例如，调整数据透视表、改变输入参数、更新数据连接等——我们希望服务器仅计算不同之处─—执行当前步骤以前用户在屏幕中所看到的，和执行当前步骤后他们将看到的。从性能方面考虑，我们不希望服务器重复从磁盘中读取工作簿，或者在不需要重新计算时重新计算公式。

This is also desirable when an Excel server-side calculation is performed by the server for a custom solution that uses the Excel Web Services. For example, if the solution code sets a cell to a new value, we only want the server to calculate formulas that depend on that cell’s value – and nothing else. So the server keeps the “state”, or context, of a custom application’s calculation in server memory. This context is called a session. In order to let the application tell the server which session it is working with, a session id is used. The server returns this id to the application when it starts a new session (by opening a workbook), and then the application code passes this session id to subsequent web service calls.

通过使用Excel Web Services自定义解决方案执行Excel服务器端计算也是可取的。例如，如果解决方案代码给单元格设置新的值，我们希望服务器仅依靠单元格值去计算公式。因此，服务器保存自定义应用程序计算结果的“状态”或上下文关系在服务器内存中。这个上下文关系被称作会话，使用会话ID让应用程序告诉服务器处理哪个会话。当开始新的会话时(通过打开工作簿)，服务器给应用程序返回这个ID，然后应用程序代码通过会话ID调用网络服务器。

What’s next?

下一步的内容

I will review a sample application that provides a specific example of how to use Excel Web Services.

我将介绍一个如何应用Excel Web Services应用程序的详细示例。