Web Enabled Excel Spreadsheets
 
  
Excel WEBSERVICE() Function
  Web-enabled spreadsheets, like this one, can simplify interactions with SQL databases and REST APIs.

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

One spreadsheet can interact with data from virtually unlimited data sources.

   -  integrate Excel with open source PHP, PDO, SQL, Javascript, 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 SQL or REST API query
   -  the JSON returned from a SQL or REST API query can be parsed into Excel calculations
   -  a database abstraction class can be used to simplify database communications
   -  data-access abstraction layer uses prepared statements to execute queries
   -  the abstraction layer uses SQL transactions for create, update, delete
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  MySQL, SQLServer, PostgreSQL, SQLlite, native PDO drivers
   -  mobile or desktop clients only require a web browser

The examples below demonstrate web-enabled speadsheets in action and show the scripts used.
 
A Web Service API Example
  All examples retrieve user-requested data and pass results back to calculations.
  This example uses the URL below 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.   
   
   
  The selected currency will be used to calculate prices for the rest of the examples.  
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 below parses and converts the JSON response.       
  VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JSONRESPONSE,CHAR(34),""),"[{Price:",""),"}]",""))  
  The PDOModel version of 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);
?> 		 
  The native PDO  version of the pricequery.php script that contains the query and returns the result in JSON format  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$series = "Diamonds";
$gemtype = "Diamond";
$cuttype = $_GET["CutType"];
$weight = $_GET["Weight"];
$quality = $_GET["Quality"];
$color = $_GET["Color"];
$sql = "select Price from grid WHERE Series = :series and GemType = :gemtype and CutType = :cuttype and WeightLower <= :weight and WeightUpper > :weight and Quality = :quality and Color = :color";
$stmt = $pdo->prepare($sql);
$stmt->execute(['series' => $series, 'gemtype' => $gemtype, 'cuttype' => $cuttype, 'weight' => $weight, 'quality' => $quality, 'color' => $color]);
$records = $stmt->fetchAll();
$json = json_encode($records);
echo $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 PDOModel version of the 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);
?> 		 
  The native PDO version of the script that script that contains the query and returns the record in JSON format.
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseuser';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$productnumber = $_GET["productnumber"];
$sql = "select ProductNumber, ProductName, RetailPrice, QuantityOnHand from Products WHERE productnumber = :productnumber";
$stmt = $pdo->prepare($sql);
$stmt->execute(['productnumber' => $productnumber]);
$records = $stmt->fetchAll();
$json = json_encode($records);
echo $json;
?>
A Transaction Example
  This example updates 3 database records using 1 transaction. If an individual record's update fails the transaction rolls back. 
  The first 3 products from the Order Form Example appear below. Enter new Quantity On Hand values to test transactions.
  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 32767.
   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 PDOModel version of the script that updates QuantityOnHand values and timestamps the updated rows.
  <?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);
?>
  The native PDO version of the script that updates QuantityOnHand values and timestamps the updated rows.
  <?php
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
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"];
try{
    $pdo->beginTransaction();
    $sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand1,
            $status,
            $productnumber1
        )
    );
$sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand2,
            $status,
            $productnumber2
        )
    );
$sql = "UPDATE Products SET QuantityOnHand = ?, Status = ? WHERE ProductNumber = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $quantityonhand3,
            $status,
            $productnumber3
        )
    );
    $pdo->commit(); 
}
catch(Exception $e){
    echo $e->getMessage();
    $pdo->rollBack();
}
?>
Report Query Examples
  Example 1 runs 4 aggregate queries, Example 2 retrieves a list with calculated totals.  
  The PDOModel 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     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  
  The PDOModel 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);
?> 		  
Complex Query Examples
  The first query requests a list of customers that ordered Product 1 that also ordered Product 2.  
  The PDOModel version of the 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  
 
 
  The second query returns summarized product sales totals. The minimized native PDO script is below.  
     
  The result  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 
  This script is reduced to the minimum required to execute the query and return the result.
         
  <?php
header("Access-Control-Allow-Origin: *");
$username  = 'USERNAME';
$password  = 'PASSWORD';
$dbconn = new PDO('mysql:host=localhost;dbname=salesorders', $username, $password);
$stmt = $dbconn->prepare("SELECT Products.ProductName, sum(Order_Details.QuotedPrice * Order_Details.QuantityOrdered) AS TotalSales
FROM Products INNER JOIN Order_Details ON (Products.ProductNumber = Order_Details.ProductNumber)
GROUP BY Products.ProductName ORDER BY TotalSales DESC");
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($data);
$dbconn = null;
echo $json;
?>
 
 
 
 
 
  *  
  AeroFlo ATB Wheels  
  Clear Shade 85-T Glasses  
  Cosmic Elite Road Warrior Wheels  
  Cycle-Doc Pro Repair Stand  
  Dog Ear Aero-Flow Floor Pump  
  Dog Ear Cyclecomputer  
  Dog Ear Helmet Mount Mirrors  
  Dog Ear Monster Grip Gloves  
  Eagle FS-3 Mountain Bike  
  Eagle SA-120 Clipless Pedals  
  Glide-O-Matic Cycling Helmet  
  GT RTS-2 Mountain Bike  
  HP Deluxe Panniers  
  King Cobra Helmet  
  Kool-Breeze Rocket Top Jersey  
  Kryptonite Advanced 2000 U-Lock  
  Nikoma Lok-Tight U-Lock  
  ProFormance ATB All-Terrain Pedal  
  ProFormance Toe-Klips 2G  
  Pro-Sport Dillo Shades  
  Road Warrior Hitch Pack  
  Shinoman 105 SC Brakes  
  Shinoman Deluxe TX-30 Pedal  
  Shinoman Dura-Ace Headset  
  StaDry Cycling Pants  
  TransPort Bicycle Rack  
  Trek 9000 Mountain Bike  
  True Grip Competition Gloves  
  Turbo Twin Tires  
  Ultimate Export 2G Car Rack  
  Ultra-2K Competition Tire  
  Ultra-Pro Rain Jacket  
  Victoria Pro All Weather Tires  
  Viscount C-500 Wireless Bike Computer  
  Viscount CardioSport Sport Watch  
  Viscount Microshell Helmet  
  Viscount Mountain Bike  
  Viscount Tru-Beat Heart Transmitter  
  Wonder Wool Cycle Socks  
  X-Pro All Weather Tires  
An Auxiliary Control Example
  Web-enabled spreadsheets can add calculation, look-up, or data interaction interfaces to data in other systems.
  In this spreadsheet the Order Form, Transaction and Query examples add functionality to the inventory system below.
 
   
Summary & Contact Information
  A Microsoft Excel spreadsheet can be configured to utilize the WEBSERVICE() function to send calculated parameters to REST APIs or to PHP scripts that execute SQL 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.

Optionally integrating the PDOModel database abstraction class simplifies database communications, making it easier to design and build web applications that can include complex calculations, very custom math or business logic, with unlimited database or web service connections.

Creating new workgroup applications or enhancing legacy systems, web-enabled spreadsheets provide extremely cost-effective and adaptive MVC components, running on premises or cloud.

Please use this form to send questions or upload files for project estimates.
Additional Spreadsheet Examples
  Example 1 is a custom calculator application that estimates the value of an item of fine jewelry  
 
  
        
  Example 2 is a look-up grid that returns wholesale gem prices based on characteristics and weight  
 
  
 
  Example 3 searches 1,000,000 records and pages results ("%" or "_" are wildcards).  
  
  Enter Company Name Sort By Direction Offset Mode  
 
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Both scripts below return the results above using a whitelist method that, used along with prepared statements, fights SQL injection.  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => TRUE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$searchterm = $_GET["searchterm"];
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
$limit = 10;
$offset = $_GET["offset"];
$stmt = $pdo->prepare("SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE :searchterm ORDER BY ".$orderby." ".$direction." LIMIT :limit OFFSET :offset");
$stmt->bindValue(':searchterm', "$searchterm%");
$stmt->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->execute();
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?> 		 
  
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT");
$host = '127.0.0.1';
$db   = 'databasename';
$user = 'databaseusername';
$pass = 'databaseuserpassword';
$charset = 'utf8mb4';
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$whitelist = ["Company","Address","City","County","ZIP","Phone1"];
$selection = array_search($_GET['orderby'], $whitelist);
$orderby = $whitelist[$selection];
$direction = $_GET['direction'] == 'DESC' ? 'DESC' : 'ASC';
$searchterm = $_GET["searchterm"];
$limit = 10;
$offset = $_GET["offset"];
$stmt = $pdo->prepare('SELECT Company, Address, City, County, ZIP, Phone1 FROM customers WHERE Company LIKE ? ORDER BY '.$orderby.' '.$direction.' LIMIT ? OFFSET ?');
$stmt->execute(["%$searchterm%", $limit, $offset]);
$data = $stmt->fetchAll();
$json = json_encode($data);
echo $json;
?>