Excel SQL & REST API Clients
 
   
The WEBSERVICE() Function
  Excel spreadsheets can interact with SQL and REST Application Programming Interfaces (APIs).
Excel's WEBSERVICE() function can use PHP scripts to execute queries on APIs and process the results.
Queries can execute from Excel or spreadsheets can be converted to web forms, like the examples below.
Excel spreadsheets and web forms can both dynamically interact with unlimited data sources.

   -  integrate Excel's calculation engine with open source PHP, PDO, SQL, JSON
   -  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 REST API query
   -  data returned from SQL or REST API queries can be inserted in spreadsheet calculations
   -  a database abstraction class can be used to simplify database communications
   -  a data-access abstraction layer uses prepared statements for SQL queries
   -  the abstraction layer can Create, Read, Update, Delete in transactions
   -  calculated output can POST to other spreadsheets, forms, APIs
   -  MySQL, SQLServer, Oracle, PostgreSQL, SQLlite PDO drivers
   -  optionally convert speadsheets to HTML, CSS, JavaScript web forms
   -  no VBA, no ODBC or JDBC, minimal dependencies, ultra-simple deployment

This examples website demonstrates the web form version's mechanics and show the scripts used.
Simple, efficient, extremely low-code methods for building precision SQL and REST API client applications.
 
A REST API Query Example
  Excel API clients can query SQL or REST APIs and filter the result for specific data.  
  This example is a currency exchange look-up that parses rates back to calculations.
  The URL below requests base-rated currency information from the fixer.io REST 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 next examples.  
A SQL Look-up Value Example
  This example uses a calculated URL to run a PHP script that executes 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;
?>
 
A SQL Calculation 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 SQL 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();
}
?>
SQL KPI & Summary Examples
  The first example executes 4 aggregate queries, the second returns a table 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 grand total is calculated in the spreadsheet from the query result.    
  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 SQL 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;
?>
 
Paging a SQL Response Example
  Excel API clients are most efficient working with aggregate query results but returned record sets can optionally be paged.   
  This example searches 1,000,000 company records and pages results ("%" or "_" are wildcards).  
   
  Enter Part of a Company Name Sort By Direction Offset    
 
   
  This is the calculated URL that is sent to the PHP script.  
   
   
   
               
  Delimiter1 Delimiter2 Delimiter3 Delimiter4 Delimiter5 Delimiter6  
  {"Company":" ","Address":" ","City":" ","County":" ","ZIP":" ","Phone1":"  
   
   
   
   
   
   
   
   
   
   
   
   
  These scripts both return 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");
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;
?>
 
   
PHP Auxiliary Interface Example
  Excel API clients can add calculation, look-up, or data interaction interfaces to data stored in other systems.
  The Order Form, Transaction, and Query examples all add new functionality to the example inventory system below.
 
     
Mobile Interface Examples
               
  Mobile Excel API clients run on any mobile device. 
               
  Scan the QR Codes to test mobile versions of the previous examples .
 
 
 
   
  Currency Exchange Calculator
   
   
   
   
 
 
 
   
    Multi-parameter Price Look-up
   
   
   
   
 
 
 
   
    Key Performance Indicators Query
   
   
   
   
An API Response Parser Example
Excel's text functions make it relatively easy to parse information returned from SQL or REST API queries.
This example queries the CoinLore API with an id and parses the result using field names as text delimiters.
  Delimiter Value
  id":"
    symbol":"
    name":"
Parse the data for use in calculations…   nameid":"
  rank":
  price_usd":"
  percent_change_24h":"
Enter a quantity to purchase…   percent_change_1h":"
  percent_change_7d":"
  market_cap_usd":"
Exchange rate from REST API Query Example…   volume24":"
  volume24_native":"
  csupply":"
  price_btc":"
This example merges data from 2 REST APIs.   tsupply":"
Easily adapted to incorporate SQL data.    msupply":"
Formulas using relative and absolute cell references make it possible to set up the first row and drag down to automatically parse. Nice!
LEFT(MID($B$4,FIND(D4,$B$4)+LEN(D4),100),FIND($G$2,MID($B$4,FIND(D4,$B$4)+LEN(D4),100))-1)
LEFT(MID($B$4,FIND(D5,$B$4)+LEN(D5),100),FIND($G$2,MID($B$4,FIND(D5,$B$4)+LEN(D5),100))-1)
LEFT(MID($B$4,FIND(D6,$B$4)+LEN(D6),100),FIND($G$2,MID($B$4,FIND(D6,$B$4)+LEN(D6),100))-1)
LEFT(MID($B$4,FIND(D7,$B$4)+LEN(D7),100),FIND($G$2,MID($B$4,FIND(D7,$B$4)+LEN(D7),100))-1)
 
Summary and Contact Information
  A Microsoft Excel spreadsheet can be configured to utilize the WEBSERVICE() function to send calculated parameters to SQL or REST APIs via PHP scripts that execute queries based on the parameters and return the JSON results back in to the spreadsheet's calculations.

Excel spreadsheets can execute queries natively or optionally be converted to calculating web forms, like the examples above, 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 API clients 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 language. Nice!"