Excel service: Develop computing engines for your applications

This article discusses: Excel as a server-based application Excel service architecture and API Create a managed User-Defined Function Use Excel to build custom solutions This article uses the following technologies: Excel Service Get the sample code for this article. New: getting e the sample code online! -Or- Code download location:Excelservices2007_08.exe (226kb) DirectoryExcel Service Architecture Excel Services API Use custom solutions of Excel services Excel pre-Compiler Excel Web service client In-depth code Exploration Performance and scalability   MicrosoftExcelPerform the following activities: perform complex calculations, view information using charts, pivot tables, and many other custom tasks. However, in the past, if you were to implement a computing engine, you would need to get the services provided by developers who would use the algorithms provided by business analysts to design code. Now, use Office SharepointWith the Excel Service Technology in Server 2007, business analysts can implement the required computing engine by themselves, thus reducing the implementation cost and making it easier to maintain computing algorithms than before. In addition, through the Excel service, the custom algorithms in the Excel Workbook can run on the Web server, so that users can remotely access them. As expected, this means that more users can use the software from more locations.   Excel Service Architecture Let's take a look at how the Excel service architecture achieves this flexibility. The Excel service consists of three layers: Web Front-end, application server, and database (see figure 1 ). The SharePoint content database forms the database layer. To implement Excel behavior on the server, place the workbook in a trusted SharePoint location or network file sharing. Some features (such as other security features) can only be obtained through Sharepoint. Figure 1 Excel Web Service Architecture The Application Server contains Excel calculation Services, which is responsible for loading the given workbook and performing any required calculations. A Workbook instance can be connected to an external data source. The Web Front-end is responsible for displaying the relevant parts of the workbook in HTML format through SharePoint Web parts. The Web Front-end is also responsible for displaying web service endpoints that allow remote access to Excel calculation services. An important aspect of the Excel services architecture is its integration with SharePoint 2007. As mentioned above, to implement some server-side behaviors, you need to store the workbook in the SharePoint content database. In this way, you can use SharePoint content management functions, such as version control, check-in/checkout, and security roles and permissions in the Excel Workbook environment. Similarly, Excel calculation services is based on the SharePoint Shared Service Provider (SSP) model. SSP is a mechanism to package SharePoint functions as services, making it easier to manage and use SharePoint functions across different sites. Therefore, you can use the Excel calculation service instance across multiple SharePoint sites and manage it through the SharePoint management site. You should also note that excel services has some restrictions on Excel. Excel services does not support add-ons Based on macros and unmanaged code, such as Visual Basic for Applications (VBA ). On the contrary, Excel Services supports hosting server-side user-defined functions (udfs). This interface allows custom computing to be called from the server-side workbooks. In the following section, we will view a UDF code example. By building a managed UDF to encapsulate the unmanaged code, you can overcome the constraints related to the unmanaged code add-on. It is very difficult to restrict the use of VBA and macros, but this is actually a good thing because it can prevent the computing logic on the server from becoming difficult to process. Excel Services API Now let's take a look at the web service-based excel Services API for interacting with server-side workbooks. Use the code segment in Figure 2 as the basis for the following discussion. Please note that the code has been canceled for clarity. First, make sure that the client can access the workbook. Using the Excel Service API (an integral part of Microsoft. Office. Excel. server. WebServices), you can access any workbook stored on the server. With the release function, the Excel 2007 client makes publishing workbooks easier. The advantage of the publishing mechanism is that you can use the Excel Services API to control the accessible parts of a workbook (tables, views, pivot tables, and so on ). The main class in the API is the excelservice class, as shown in 2. This class represents a server instance in the memory of the workbook. To allow multiple users to interact with one workbook simultaneously, a session-based access model has been executed. Each user opens a separate session with the workbook using the openworkbook method of the excelservice class. The openworkbook method returns a unique session ID associated with the opened session. This session identifier is required when any subsequent method is called to interact with the opened workbook. To set a naming area in a workbook, you can use the rangecoordinates class to define the boundaries of the naming area. Setrange uses rangecoordinates and an array containing the values to be passed as parameters. One variant of the setrange method is the setrangea1 method, which uses the Excel region specification "A1" instead of the Region coordinates used by setrange. Once all required region values are specified, you can use calculateworkbook to force the workbook calculation formula. You can call a cancelrequest method to cancel the latest calculateworkbook method. You can use the getrange method to obtain the value calculated based on a region where the workbook is opened. Once all calculated values are retrieved, you can use the closeworkbook method to close the workbook session. Excel services can be expanded by adding udfs. These udfs can be accessed as cell formulas similar to built-in Excel functions. To create a UDF, you must create. NET Framework Assembly, which contains at least one class marked with udfclassattribute and at least one method marked with udfmethodattribute. See the following code snippet. Here, we define converttoupper as a UDF method. After registering a UDF properly, the converttoupper function can be included in an Excel services workbook instance:   using Microsoft.Office.Excel.Server.Udf;[UdfClass]public class Util{ [UdfMethod] public string ConvertToUpper(string name) { return name.ToUpper(); }}   Here, we only introduce a small part of the Excel Services API. For more information, see msdnDocumentation. Use custom solutions of Excel services The main purpose of developing a custom solution is to allow business analysts to write computing (such as financial models) into an Excel Formula directly. So far, business analysts rely mainly on pseudocode instruction algorithms in the form of text. Then, the developer converts the pseudo code to the code. Using Excel services, we can overcome the limitations inherent in the process of creating formulas in Excel, so that developers do not have to convert pseudo code into real code. One of the major challenges that allow non-developers to write computing logic is to use the ability to enforce a stable structure to strike a balance between the flexibility and simplicity of writing. To provide this structure, we need a method to define the input and output "interfaces", which represent the data conventions of computing algorithms. Business analysts can only process the named regions that are part of the data conventions for inbound and outbound computing instance data. The obvious choice is to use the named cell or area in Excel to construct a data convention. The naming area should not only be consistent with the granularity required for writing calculations in Excel, but also be the basic data structure based on Excel Services API methods (such as setrange and getrange. However, the challenge of using a named region is that there is no standard format or language for defining the interface, such as XML schema definition (XSD) or Web Service Description Language (WSDL ). Worse, the type of the named area (and therefore the Excel Service API method) is inherently insecure. For example, you cannot force a data type check on a given naming area. Finally, there is no built-in method to enforce conventions on the computation (inside Excel) and Excel services client programs. To overcome these limitations, we have developed a two-part custom solution. The first part is an Excel pre-compiler designed to generate a named region based on the defined interface. The second part is a general Excel Web service client that is used to call the computation in the workbook when following this interface. Excel pre-Compiler All semantics defined by the XML architecture are rich and simple, and it seems to be an ideal choice for defining interfaces. We decided to use the XML schema structure to define Input and Output conventions. Next, we need a way to convert the XML schema to a named region. First, we should consider using the xmlmap function introduced with Excel 2003. Xmlmap allows you to map cells in Excel to elements in the imported XML schema. Unfortunately, xmlmap is not available for Excel services. Therefore, another option is to create a naming area in an Excel Workbook. We have developed a pre-compiler component that can generate a template workbook Based on the schema using the required naming area. The generated template workbook has three tables for input, output, and computing. The input table contains the name area corresponding to the calculated input. Similarly, the output table contains the name area corresponding to the computing output. The calculation table is used to store the calculated value (see figure 3 ). Figure 3 input table, computing table, and output table of the workbook (click the image to get a small view) Figure 3 input table, computing table, and output table of the workbook (click the image to obtain a large view) As mentioned above, programming structures (such as loops) are not available for Excel services. The pre-compiler makes up for this restriction by converting input XML fields to formats that can be accessed without complex programming structures. For example, you can convert an XSD element set to a dimension in the named area. Figure 4 describes an XSD code segment, which is part of the input data conventions of the computing engine. The elements typea and typeB are part of the computing input. Pay attention to defining the Custom Attributes rangeheight and rangewidth of the dimension of the named area. The pre-compiler uses this information to generate the dimension of the named region. The pre-compiler can also cancel the reference of each index field and divide it into individual columns-each column represents an index value. One noteworthy aspect of the pre-compiler is that it can rebuild the input and output tables while retaining existing calculations. As shown in figure 5, the development of computing algorithms is an iterative process. Business analysts and developers work together to define the original data conventions. During the development of the workbook, you may need to modify the input and output conventions. These changes take effect only after the workbook is regenerated. The pre-compiler supports this type of iterative development by regenerating the workbook while retaining the computing table. Figure 5 use a pre-compiler to generate a workbook The input table shown in Figure 3 has a pre-generated naming area. A computing table has a calculation that references the named regions defined in the input table. Then, the output table references the computation from the computing table. Excel Web service client The Excel Web service client uses the Excel Services API to call internal computation within the workbook. When performing this operation, it explains the XSD-based input conventions to map schema elements to appropriate naming areas. After the calculation is complete, the client immediately maps the output naming area back to the XSD-based output convention. Figure 6 describes the role of the Excel Web service client. Typed Dataset (based on input schema conventions) is input. Data contained in dataset will be mapped to the input naming area. After the calculation is complete, the output naming area is used to fill the output dataset. The Excel Web service client is responsible for applying the rules defined for the pre-compiler. You may also insert custom data transformations to change the aforementioned mappings between XSD and the named regions. We have discussed the lack of programming structures available for the author of the workbook above and need to compensate for this. Custom Data Conversion allows you to change mappings, making it easier for business analysts to create computing logic. Figure 6 call a custom computing Engine In-depth code Exploration The solution we built consists of four projects. The precompiler project contains all the code used to generate a workbook with the required naming area based on the input and output architectures. It considers the preceding custom attributes, such as rangeheight, when generating a naming area. The precompiler project then relies on spreadsheetml (an XML-based language used to represent information in a workbook) to generate a workbook. The spreadsheetml project contains simple classes used to encapsulate spreadsheetml components (such as workbooks and worksheets. Client project, as its name implies, is the client code of the Excel Web service. It sets the value of the input named area, forces the workbook to recalculate and retrieve the value of the output named area. You will remember that we have discussed the need to convert data to make it easier for business analysts to develop and compute. To allow Custom Data Conversion for each computing engine, we define an idatatransformer interface to externalize the data conversion logic, as shown below:   public interface IDataTransformer { object getRangeData(string RangeName); object[] getRangeData(string RangeName, int width); object[,] getRangeData(string RangeName, int width, int height); string getInputSchema(); string getOutputSchema(); string getInputSchemaPrefix(); string getOutputSchemaPrefix(); }   The Assembly name that includes the idatatransformer interface is passed in as the input of the client program. Next, the client program calls back the appropriate method for the class that implements the idatatransformer interface. In this way, the client obtains the value used to fill the named area. The implementation logic inside the idatatransformer method is responsible for converting the data in the input dataset to the appropriate named region value. For example, you may need to filter some rows in the able before filling in the appropriate naming area. Alternatively, the rows in the datatable table may need to be sorted before being passed to the workbook. Using the idatatransformer interface, all such data conversion requirements can be met. Another important class to be discussed here is excelservicefacade. This class hides the Excel Service API details from the caller. Another important function of this class is to merge each setrange call into an aggregate setrange call. Each call to setrange will result in operations to and from the server, which is critical to reducing network latency. By publishing a local setrange call that is eventually converted to an aggregate setrange call, excelservicefacade can greatly shorten the response time. Figure 7 describes the relevant excelservicefacade code. The internal buffer is maintained by the excelservicefacade class. This class is attached every time you use a "local" setrange call. After all input naming areas are filled, the internal buffer is sent to the server as part of a single call. After calculation, the similarity mechanism is used to retrieve the output name. We only retrieve all values in the output table at a time, instead of retrieving the values in the output naming area one by one. Performance and scalability We found that the actual computation in the workbook is very fast. In an unscientific test that contains an important computing set Workbook (about one hundred naming areas involved), the response time is less than one second; most of the time is spent on the operations to and from the Excel server. As the computing complexity and number of concurrent executions increase, the load on the system also increases. Therefore, you can scale the solution by using various topology options provided by Excel services. Different topology options allow you to select the location of each logical Excel services layer (presentation layer, application layer, and database layer. For small installations (mainly for testing), you can deploy all these three layers on a single server. For medium-sized installation, the presentation layer and application layer can be installed on a single server, and the database layer can be installed on a separate server. For large installations, you can install each layer on a separate server. In addition, you can expand the presentation layer by adding more servers using the Network Load Balancing Program. You can also use the Server Load balancer architecture supported by the SSP framework to expand the application layer containing Excel calculation services. Figure 8 shows a large installation, where each layer is installed on a separate server. In addition, the presentation layer and application layer are extended using the load balancing architecture. Figure 8 Excel Web Services Installation For a workload that requires a large amount of computing, you can also combine Excel services with the computing cluster server to seamlessly distribute work to the computing node, as shown in figure 9. Figure 9 Excel services high-performance Installation You can see that using Excel services to customize computing engine components can greatly improve efficiency. This allows you to access custom workbook functions from anywhere, without the need for developers to implement logic, and allows you to expand the solution as needed. Try it. We believe that you will be satisfied with the higher flexibility and efficiency it provides. For more information, see the "resource" column. Resources   Service Technology Overview Determine resource requirements to support Excel services Create XML ing in Excel 2003   Close [x] New: getting e the sample code online!-Or-Code download location:Excelservices2007_08.exe (226kb) Vishwas LeleIs the CTO of Applied Information Sciences (AIS) in Reston, Va. He helps organizations anticipate, design, and implement enterprise solutions based on Microsoft. NET technology. Vishwas is Microsoft's regional manager in Washington. You can contact him via vlele@acm.org. Pyush KumarHe is the chief System Architect of Watson Wyatt Worldwide. Recently, he has been working on grid computing and large-scale software design for. NET Framework. You can contact him via pyush.kumar@watsonwyatt.com. From August 2007 journal msdn magazine. Back to TopQj:070805 2007 Microsoft Corporation and CMP media, LLC. All rights reserved; full text or part of content shall not be reproduced.

Articles in msdn Office apps:Extend your VBA code with vsto by Paul Stubbs and Kathleen McGrath Office space:Building Office open XML files by Ted Pattison Gathering MOSS:New Dev-centric features in Office Sharepoint Server keep your apps rolling by Ted Pattison Ribbonx API:Extend the 2007 Office System with your own ribbon tabs and controls by Eric faller Basic instincts:Server-side generation of Word 2007 docs by Ted Pattison Infopath 2007:Designing form templates with the new features of infopath by Scott Robert TS and Hagen green Office space:Features for Sharepoint by Ted Pattison Microsoft offices 2000:Create dynamic digital dashboards using office, OLAP, and DHTML by Todd Abel