Excel服務(wù)VI――用Excel Web Services創(chuàng)建應(yīng)用程

Excel服務(wù)VI――用Excel Web Services創(chuàng)建應(yīng)用程

譯者：fanjy? 來源：http://blogs.msdn.com/excel 發(fā)表于：2006年7月7日

Excel Services part 6: Building applications with Excel Web Services Excel Services(第6部分)：用Excel Web Services創(chuàng)建應(yīng)用程序

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 中的方案時，我提到過”在用戶自定義程序中重復(fù)利用Excel電子表格的邏輯封裝”——可升級和管理通過網(wǎng)絡(luò)服務(wù)器訪問電子表格和存儲信息服務(wù)器端。我在上面的一些文章中就這個主題作過詳細的介紹。今天，我將介紹“Excel Web Services”，接下來的文章展示如何用網(wǎng)絡(luò)服務(wù)器開發(fā)小的應(yīng)用程序示例。

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架構(gòu) 。Excel Calculation Service是Excel Services的”引擎”，是裝載和計算電子表格的組件。在上篇文章中介紹了”在瀏覽器中與電子表格相交互”， Excel Calculation Service裝載和計算電子表格，并且不需要Excel Web Access(配置在瀏覽器中生成HTML的組件)。這篇文章接下來介紹開發(fā)者不需要Excel Web Access的配合也能運用Excel Calculation Service。具體地講，我們在Excel Calculation Service中已經(jīng)直接創(chuàng)建了一個網(wǎng)絡(luò)服務(wù)器API，以便于開發(fā)者能在他們自己的應(yīng)用程序中直接調(diào)用網(wǎng)絡(luò)服務(wù)器端的電子表格。例如，開發(fā)者能夠?qū)懹脕泶蜷_服務(wù)器上的電子表格的代碼，設(shè)置單元格和區(qū)域為指定值，控制外部數(shù)據(jù)更新和工作薄計算，并且最后從已經(jīng)過計算的工作簿中取回數(shù)值(或取回整個工作薄)。下面列舉了一些更詳細的例子。

What sorts of things are these web services good for? 這些網(wǎng)絡(luò)服務(wù)有哪些功能？

? 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能處理一些有趣的情況。下面有兩個經(jīng)常從我們的客戶那里聽到的例子。 在服務(wù)器應(yīng)用程序中的電子表格上運用邏輯——不需要使用”傳統(tǒng)的”方法，即用編程語言重新編 寫這些邏輯代碼。在這種情況下，創(chuàng)建電子表格的商務(wù)專家能在Excel中維護這個模型；管理員 能在服務(wù)器上使用合適的用戶權(quán)限設(shè)置來保護模型；開發(fā)者能調(diào)用Excel Web Services在用戶 解決方案部分加入電子表格邏輯。在這方面的另一個變化是為基于Excel服務(wù)器應(yīng)用程序提供了 自定義用戶界面，該服務(wù)器應(yīng)用程序使用Excel Web Services與服務(wù)器端計算進程進行交互。

? 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. 在服務(wù)器中自動更新電子表格。這項功能相當好地組合在全新Open XML文件格式中，可非常簡單地自動編程完成從稿紙或模板創(chuàng)建一個Excel文件的任務(wù)。一旦創(chuàng)建了這個新文件，就經(jīng)常需要計算它——例如，如果有需要更新的外部數(shù)據(jù)，開發(fā)者可以直接寫代碼去指令Excel Web Services完成所有這些工作，然后取回這個已經(jīng)過計算的文件副本并存儲在服務(wù)器上，或者將它傳送到另一個目標上。

What specifically can the web service do? 網(wǎng)絡(luò)服務(wù)器具體處理什么工作？

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進行會話，設(shè)置單元格和區(qū)域的值，處理工作薄，獲得計算結(jié)果或整個工作薄并反饋到你的應(yīng)用程序。

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:獲取所安裝的網(wǎng)絡(luò)服務(wù)器API架構(gòu)的版本字符串。

? 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:打開服務(wù)器端計算會話。這個方法返回工作薄文件路徑和一些其它參數(shù)，并返回一個服務(wù)器會話ID。

? GetSessionInformation: Get a few properties of the server session, primarily the language context of the session. ? GetSessionInformation:獲取一些服務(wù)器會話屬性，主要是會話語言文本。

? 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:對工作薄中某一工作表上的單元格設(shè)置值。有兩種方法：一種使用單元格地址(如”B52”)或命名區(qū)域(如“Interest”)；另一種使用整數(shù)坐標值，有些情況下使用這種方法是更方便的(特別是當你在代碼中有索引號并且想使用它們引用工作表時)。

? SetRange: Same as SetCell, but for setting values into an entire contiguous range. Same two flavors exist. ? SetRange:除了為整個相鄰區(qū)域設(shè)置值外，其它與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:從外部數(shù)據(jù)連接(或所有工作薄連接中)讀取數(shù)據(jù)，并且更新相關(guān)單元格的值，例如，數(shù)據(jù)透視表單元格或三維公式的結(jié)果。

? 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:重新計算在整個工作薄或指定單元格區(qū)域中的公式。用于工作薄作者關(guān)閉自動計算功能的情形。兩種方法——運用字符串或整數(shù)坐標指定單元格區(qū)域——與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:僅計算已發(fā)生變化的公式。

? CalculateFull: Calculate all formulas, regardless of dependency changes. ? CalculateFull:計算所有的公式，無論是否發(fā)生變化。

? 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:獲取一組連續(xù)單元格區(qū)域位置值。采取同樣的尋址方式。

? 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:在應(yīng)用程序內(nèi)存中獲取整個已經(jīng)過計算的工作簿，作為一個字節(jié)數(shù)組。能夠獲取實時的結(jié)果或者屏幕快照——一個帶有原先的工作簿頁面布局和所有的初始格式以及最新數(shù)據(jù)值的工作薄——但所有的公式和外部連接除外，并且不帶有在發(fā)布時沒有標記為視圖的工作簿部分。在接下來的文章中將有更多關(guān)于屏幕快照方面的內(nèi)容介紹。

? 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:如果你的應(yīng)用程序以單線程運行Excel Web Services會話，并希望中止長期運行服務(wù)器的請求(例如長時間的計算將使用戶疲于等待)，那么你就能夠調(diào)用該方法進行處理。

? 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:告訴服務(wù)器關(guān)閉為會話打開的工作簿，因此，也允許服務(wù)器發(fā)布為維持會話文本的所有資源。

Error handling 錯誤處理

Errors are exposed to a developer’s application in three ways: 顯示在開發(fā)者的應(yīng)用程序中的錯誤有三種方式：

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!)。當你調(diào)用GetCell或GetRange并且要求有格式的值時，你會得到#開頭樣式的錯誤字符串；當你要求無格式的值時，你會得到一個枚舉錯誤代碼。 2. 處理一個網(wǎng)絡(luò)服務(wù)器方法過程中(不能夠使方法成功地完成)的某個錯誤除了能捕獲你的代碼外顯示為SOAP。 3. 不會阻止方法獲得正常的結(jié)果的次要錯誤，其返回值作為方法參數(shù)的部分(具體地講，作為一個輸出參數(shù))。原因是將代碼從它的正常執(zhí)行路徑轉(zhuǎn)移，這些次要的錯誤都不可取的。檢查這些錯誤是可選擇的。

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. 開發(fā)者需要注意使Excel Calculation Services為了性能而繼續(xù)會話的方法。一種利于理解服務(wù)器狀態(tài)的方法是考慮用戶在網(wǎng)絡(luò)瀏覽器中與Excel電子表格交互。每次用戶執(zhí)行下一交互步驟，例如，調(diào)整數(shù)據(jù)透視表、改變輸入?yún)?shù)、更新數(shù)據(jù)連接等——我們希望服務(wù)器僅計算不同之處─—執(zhí)行當前步驟以前用戶在屏幕中所看到的，和執(zhí)行當前步驟后他們將看到的。從性能方面考慮，我們不希望服務(wù)器重復(fù)從磁盤中讀取工作簿，或者在不需要重新計算時重新計算公式。

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自定義解決方案執(zhí)行Excel服務(wù)器端計算也是可取的。例如，如果解決方案代碼給單元格設(shè)置新的值，我們希望服務(wù)器僅依靠單元格值去計算公式。因此，服務(wù)器保存自定義應(yīng)用程序計算結(jié)果的“狀態(tài)”或上下文關(guān)系在服務(wù)器內(nèi)存中。這個上下文關(guān)系被稱作會話，使用會話ID讓應(yīng)用程序告訴服務(wù)器處理哪個會話。當開始新的會話時(通過打開工作簿)，服務(wù)器給應(yīng)用程序返回這個ID，然后應(yīng)用程序代碼通過會話ID調(diào)用網(wǎng)絡(luò)服務(wù)器。

What’s next? 下一步的內(nèi)容

I will review a sample application that provides a specific example of how to use Excel Web Services. 我將介紹一個如何應(yīng)用Excel Web Services應(yīng)用程序的詳細示例。

Published Thursday, November 17, 2005 9:20 AM by David Gainer

非常感謝Kevin版主的幫助和指導(dǎo)！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。