Creating a Web Service

You are here:

Web services are essentially software systems designed to support machine-to-machine interaction over a network. Web services often are Internet Application Programming Interfaces (API) that can be accessed over a network, such as the Internet, and executed on a remote system hosting the requested services.

Using web services in SpreadsheetWEB, you can essentially expose a calculation engine that is based on an Excel file to allow for an external application to connect to this web service and retrieve information. In the Control Panel’s Applications tab, all Web Services are underlined in blue.

To add a web service, click Create a New Application and then select Web Service. A new page will appear where you need to upload the converted Excel spreadsheet.

Step 1: Select application type

Step 2: Naming and file upload


Before uploading your file, please make sure that you first run the Conversion Wizard, define the web UI, and save it. Here, click on the Excel Spreadsheet box, or simply drag and drop your file into it.
Application name field will be automatically populated with the document name. You can change it from the Name of the Application field.

Step 3: Application options

Advanced options regarding application handling can be determined on this step. Leaving all these options as default will work in almost all situations.

Status

Online: The web application is published at a specific web address and can be viewed by others in a web browser

Offline: The web application is stored in your account but is not available to the end users.

Access

Unrestricted: Anyone who knows the URL of your web application can open and view it in a browser.

Restricted: Only registered users can access the web application (i.e. open it in a browser, view it and/or perform calculations)

Availability Select if a web application will be published only for a certain period of time or until the it’s removed.
Disable Cache SpreadsheetWEB can cache HTML pages on the web server. This improves initial loading of web applications. It is disabled by default.
Auto Load When enabled, the workbook for this application will be pre-loaded on all SSWebNodes at ObjectServer startup.

Step 4: Upload complete!

Your document has been successfully transferred to the web! The application key is entirely unique and will be required before connecting to the web service.

Let’s take a very simple workbook with 2 input cells (A1, B1) and one calculated cell (C1). Click to download the workbook.

We will now create a web application using this workbook in the backend. The application will have two text boxes similar to the Excel file as show below. User enters numbers and presses Calculate button. At that point these 2 numbers are sent to the Excel file already uploaded to the SpreadsheetWEB server. Web service will return the calculated number in cell C1 back to the web application.

Now, let’s have a look at the code behind this simple web application.

JSON Support

The following snippet is a sample console application that connects to the workbook through the JSON-based web service. The application key is entered in line 21 and the server address can be seen when instantiating the SpreadsheetWebApiAdaptor class.

using System;
using System.Collections.Generic;
using System.Linq;
using Pagos.SpreadsheetWeb;
using Pagos.SpreadsheetWeb.Web.Api.Objects.Authentication;
using Pagos.SpreadsheetWeb.Web.Api.Objects.Calculation;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var api = new SpreadsheetWebApiAdaptor("http://www1.spreadsheetweb.com/api");
                AuthenticationResponse authResponse = api.Authenticate();
                CalculationRequest request = new CalculationRequest();
                request.ApplicationKey = "7ba38719c66f4d5891a4b734a0e5fcbf";
                RangeReference input1 = new RangeReference
                {
                    Ref = "input0",
                    Value = new[]
                    {
                        new[]
                        {
                            new CellValue
                            {
                                Type = "auto",
                                Value = "2"
                            }
                        }
                    }
                };

                RangeReference input2 = new RangeReference
                {
                    Ref = "input1",
                    Value = new[]
                    {
                        new[]
                        {
                            new CellValue
                            {
                                Type = "auto",
                                Value = "3"
                            }
                        }
                    }
                };
                zn request.Inputs = new List { input1, input2 };
                request.Outputs = new List { "output0" };
                CalculationResponse response = api.Calculate(request);
                var total = response.Outputs.FirstOrDefault(x = > x.Ref == "output0");
                Console.WriteLine("Total:" + total.Value[0][0].Value);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.Read();

        }
    }
}