Web Enabled Excel Spreadsheets
 
  
Excel WEBSERVICE() Function
  Custom data services web-enable Excel spreadsheets to communicate with SQL databases and REST APIs.

They use Excel's WEBSERVICE() function to execute calculated queries and process results.

A single spreadsheet can connect to and interact with data from multiple sources.

   -  they work by integrating Excel, PHP, PDO, SQL, Javascript, and JSON
   -  initially modeled in Excel 2019, can use 220 of Excel's functions in calculations
   -  any cell can interact with data entered in to the spreadsheet or from a database or REST API
   -  the JSON returned from a database or REST API can be parsed into live calculations
   -  a database abstraction class is integrated to improve database communications
   -  the abstraction class uses prepared statements to safely execute queries
   -  the class can utilize SQL transactions for create, update and delete
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  the forms are hosted on standard PHP-equipped servers
   -  clients only require an SSL-capable web browser

The examples below demonstrate the fundamentals and show the scripts used.
 
A Web Service API Example
  All examples retrieve user-requested data and pass the results back in to calculations.
  This example uses this URL to request currency exchange rates from the fixer.io API.
  "https://data.fixer.io/api/latest?access_key=FIXERIOAPIKEY&base="&BASERATECODE      
  
  Select a Base Rate Currency below to trigger the API request and load the JSON result.   
 
  
  Select the target currency to parse the currency exchange rate from the JSON result.   
 
  
   
   
  Enter an amount in the base currency  below to calculate the converted amount.   
   
   
A Price Look-up Example
  This example uses a calculated URL to connect to a PHP script and run a SQL query that returns the result in JSON.  
  Changing any of the selectors below re-calculates the URL and triggers the price per carat look-up.  
  Cut Type Clarity Color Weight Price Per Carat   Extended Price    
 
    
The calculated URL with parameters     
  
  The JSON response to the WEBSERVICE() function below.       
        IFERROR(WEBSERVICE(URL),"")       
  The formula that parses and converts the JSON response.       
  VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JSONRESPONSE,CHAR(34),""),"[{Price:",""),"}]",""))  
  The pricequery.php script that contains the query and returns the result in JSON format  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_GET["CutType"];
$weight = $_GET["Weight"];
$quality = $_GET["Quality"];
$color = $_GET["Color"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("select Price from grid WHERE Series = ? and GemType = ? and CutType = ? and WeightLower <= ? and WeightUpper > ? and Quality = ? and Color = ?", array($series,$gemtype,$cuttype,$weight,$weight,$quality,$color));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		 
An Order Form Example
  This example order form requests product information from a SQL inventory database via a PHP script.
  Each line has a calculated URL which sends query parameters, the product number in this example, to the script.
  Line 1 WEBSERVICE() function and URL. Enter a Product Number between 1 and 40 in to the light grey fields to trigger a look-up.
  WEBSERVICE("https://xlforms.net/productquery.php?productnumber="&PRODUCTNUMBER01)  
      
  Product Line 1 JSON result which gets parsed in to the first row's fields.
  
    
  Product Number   Product Name      Price On Hand   Ordered Extension   
     
     
     
     
     
     
     
     
     
     
      
  The productquery.php script that contains the query and returns the record in JSON format.
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$productnumber = $_GET["productnumber"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("select ProductNumber, ProductName, RetailPrice, QuantityOnHand from products WHERE productnumber = ?", array($productnumber));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		 
A Transaction Example
  This example updates 3 records using 1 transaction. If an individual record's update fails the transaction rolls back. 
  If you load the first 3 product lines in the previous Order Form Example that product information will appear below.
  Product Number Product Data  
 
 
 
   
  Product Number New Quantity On Hand  
   Enter values in the New Quantity On Hand fields to build the URL.
   All 3 fields need to have a numeric value between 0 and 65535.
   Non-numeric, empty or larger numbers cause the transaction to fail.
 
 
  Select Execute to run the transaction, Refresh to see updates above.
  The calculated URL
 
  The SQL error code if there is an error or Null if the transaction was successful.
 
  The PHP script that updates the QuantityOnHand values and timestamps the updated rows using PDOModel dbTransaction.
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
date_default_timezone_set('America/Los_Angeles');
$status = date('m/d/Y h:i:s a', time());
$productnumber1 = $_GET["productnumber1"];
$quantityonhand1 = $_GET["quantityonhand1"];
$productnumber2 = $_GET["productnumber2"];
$quantityonhand2 = $_GET["quantityonhand2"];
$productnumber3 = $_GET["productnumber3"];
$quantityonhand3 = $_GET["quantityonhand3"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername,$username,$password,$dbname);
$pdomodel->dbTransaction = true;
$pdomodel->where("ProductNumber", $productnumber1 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand1,"Status"=> $status));
$pdomodel->where("ProductNumber", $productnumber2 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand2,"Status"=> $status));
$pdomodel->where("ProductNumber", $productnumber3 );
$pdomodel->update("products", array("QuantityOnHand"=> $quantityonhand3,"Status"=> $status));
$pdomodel->commitTransaction();
$json=$pdomodel->arrayToJson($pdomodel->error);
print_r($json);
?>
A Report Query Example
  The first example runs 4 aggregate queries, the second retrieves a list with calculated totals.  
  Below is the PHP script that contains the first query and returns the results in a custom JSON array.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$count = $pdomodel->executeQuery("SELECT COUNT(ProductNumber) AS DistinctProducts FROM Products");
$quantity = $pdomodel->executeQuery("SELECT SUM(QuantityOnHand) AS TotalInInventory FROM Products");
$value = $pdomodel->executeQuery("SELECT SUM(RetailPrice * QuantityOnHand) AS TotalRetailValue FROM Products");
$average = $pdomodel->executeQuery("SELECT AVG(RetailPrice) AS AverageRetailPrice FROM Products");
$result = array($count,$quantity,$value,$average);
$json=$pdomodel->arrayToJson($result);
print_r($json);
?> 		  
 
  
  The result   
     
  Distinct Products  Total In Inventory  Average Retail Price Total Retail Value     
     
  Product Number Product Name Total Item Retail Value     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  
  Below is the script that contains the second query and returns the result in a custom JSON array.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("SELECT CONCAT(ProductNumber,'=', ProductName,'---', RetailPrice * QuantityOnHand) AS Item FROM Products ORDER BY ProductName ASC");
$json=$pdomodel->arrayToJson($records);
print_r($json);
?> 		  
A Complex Query Example
  This example requests a list of customers that ordered Product 1 that also ordered Product 2.  
  The PHP script that contains the query and returns the result in JSON format.  
  <?php
require_once 'script/PDOModel.php';
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$productname1 = $_GET["productname1"];
$productname2 = $_GET["productname2"];
$pdomodel = new PDOModel();
$pdomodel->connect($servername, $username, $password, $dbname);
$records = $pdomodel->executeQuery("SELECT Customers.CustomerID,Customers.CustFirstName,Customers.CustLastName FROM Customers WHERE EXISTS(SELECT * FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber WHERE Products.ProductName = ? AND Orders.CustomerID = Customers.CustomerID) AND EXISTS (SELECT * FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber) INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber WHERE Products.ProductName = ? AND Orders.CustomerID = Customers.CustomerID)", array($productname1,$productname2));
$json=$pdomodel->arrayToJson($records);
print_r($json);
?>
  Product 1   Product 2   The number of customers found  
 
 
  The calculated URL with parameters  
 
 
  The result  
 
 
An Auxilliary Process Example
  Custom data services are ideal for adding calculation, look-up, or data manipulation interfaces to data in other systems.
  For instance, the Order Form, Transaction, and Query examples add functionality to this legacy order tracking system.
 
   
Summary & Contact Information
  A Microsoft Excel spreadsheet can be configured to use the WEBSERVICE() function to send calculated parameters to REST APIs or to PHP scripts that execute SQL database queries based on the parameters and return the JSON results back in to the spreadsheet's calculations.

The spreadsheet can then be converted to JavaScript using the SpreadSheetConverter add-in, resulting in calculating web forms, like the examples above, that merge Excel's formulas and functions with PHP's connectivity and execution methods and SQL's data management capabilities.

Integrating the PDOModel abstraction class drastically simplifies database communications, making it much easier to design and build web forms that can include complex calculations, very custom math or business logic, and unlimited database or web service connections.

For creating new workgroup applications or for enhancing legacy systems, custom data services provide extremely cost-effective and adaptive MVC components, running on premises or the cloud.

Please use this form to send questions or upload files for project estimates.
An Example Web Spreadsheet App
  The ValuCalc spreadsheet application calculates the value of an item of fine jewelry based on it's metals, diamonds, gemstones, labor and provenance components.