Web-enabled Excel Examples
 
   
 
 
The WEBSERVICE() Function
  Microsoft Excel spreadsheets can run parameterized queries on SQL databases and REST APIs.
Excel's WEBSERVICE() function can use PHP scripts to execute calculated queries and process the results.
Queries can run directly in the Excel application or the spreadsheet can be converted to a web form.
Spreadsheets or web forms can both dynamically interact with unlimited data sources.

   -  integrate Excel's calculation engine with open source PHP, PDO, CURL, SQL
   -  originally modeled with Excel 365, can utilize 220 of Excel's functions in calculations
   -  any cell can interact with data entered in to a field or returned from a SQL or API query
   -  the data returned from SQL or REST API queries can be parsed back in to calculations
   -  a database abstraction class can be used to simplify database communications
   -  a PDO data-access abstraction layer uses prepared statements for queries
   -  the abstraction layer can use create, update, delete in transactions
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  MySQL, SQLServer, Oracle, PostgreSQL, SQLlite PDO drivers
   -  optionally convert the spreadsheet to HTML, CSS, JavaScript form
   -  no VBA, no ODBC or JDBC, no subscriptions, ultra-simple deployment

The examples below demonstrate the web form version's mechanics and show the scripts used.
Simple, extremely low-code methods for building precision SQL and REST API-integrated user interfaces.
 
A Customer Look-up Example
     
 
 
     
   
   
   
   
   
   
  Account Credit Limit 
  Current Accounts Payable 
  Available Credit 
     
  This script looks-up a customer's billing, shipping, and accounts payable information.
  <?php
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Origin: *");
header("Cache-Control: no-cache, must-revalidate");
$host = '127.0.0.1';
$db   = 'classicmodels';
$user = 'classicmodelsuser01';
$pass = 'USER01password';
$charset = 'utf8';
$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";
$pdo = new PDO($dsn, $user, $pass, $options);
$customerNumber = $_GET["customerNumber"];
$sql = "select customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, AddressLine2, city, state, postalCode, country, creditLimit, accountsPayable from customers WHERE customerNumber = :customerNumber";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':customerNumber',(int) $customerNumber, PDO::PARAM_INT);
$stmt->execute();
$records = $stmt->fetchAll();
$json = json_encode($records);
$filter1 = str_replace ( ":null", ":\"null\"", $json);
echo $filter1;
?>
A Currency API Example
  This example uses the URL below to request currency 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 request and load the JSON result.    
 
   
  Select the currency to use for this order form or invoice's calculations…    
 
   
     
     
A Dynamic Invoice Example
  International Bicycle Equipment Warehouse, LLC    
  12345 Airport Loop Road     Tonokset Township   Alaska, USA   98765   800-888-7777  
         
       
         
       
         
         
                                 
                                 
       
  Product #   Product Name         Price On Hand   Ordered Extension    
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
 
     
  Instructions or Notes   Subtotal     
    Discount %     
    Discount Amount     
    Adjusted     
    Sales Tax %     
    Sales Tax Amount    
    Subtotal     
    Shipping     
    Order Total     
 
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();
}
?>
A Multi-value 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 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;
?> 		 
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=UTF8");
header("Cache-Control: no-cache, must-revalidate");
$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;
?>
 
An Auxiliary Control Example
  Web-enabled spreadsheets can add calculation, look-up, or data interaction interfaces to data in other systems.
  The Order Form, Transaction and Query examples add missing functionality to the example inventory system below.
 
     
Mobile API Client Examples
               
  Mobile Excel API clients run on any mobile device with a web browser. 
 
  Scan the QR Codes to test mobile versions of the previous examples .
 
 
 
   
  Currency Exchange Calculator
   
   
   
   
 
 
 
   
  Multi-parameter Price Look-up
   
   
   
   
 
 
 
   
  Key Performance Indicators Query
   
   
   
   
Additional Projects & Examples
  Example 1 is a web-enabled spreadsheet that calculates the value of an item of fine jewelry  
 
   
     
  Example 2 is a web-enabled SQL database showcasing various components and capabilities  
 
 
 
  Example 3 searches 1,000,000 company records and pages results ("%" or "_" are wildcards).  
 
  Enter Part of a Company Name Sort By Direction Offset  
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  The script below returns the results above using a whitelist method that, used with prepared statements, protects against SQL injection.
  <?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
$host = '127.0.0.1';
$db   = 'contacts';
$user = 'user01';
$pass = 'USER01password';
$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";
$pdo = new PDO($dsn, $user, $pass, $options);
$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;
?>
 
Summary & Contact Information
  A Microsoft Excel spreadsheet can be configured to utilize the WEBSERVICE() function to send calculated parameters to PHP scripts that execute SQL or REST API queries based on the parameters and then format and return the results back in to the spreadsheet's calculations.

Excel spreadsheets can execute queries directly or can optionally be converted to HTML and JavaScript, resulting in calculating web forms, like this document, that merge Excel's calculation engine, formulas, and functions with PHP's connection and execution methods.

Optionally integrating the PDOModel database abstraction class further 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 API connections.

Whether creating new applications or enhancing legacy systems, Excel web forms can provide an extremely cost-effective and adaptive programmable canvas, running on premises or cloud.

"A relatively simple way to integrate the world's most popular business application with the world's most popular database language and the world's most popular programming languages."