Web-enabled Excel Spreadsheets
  Web-enabled spreadsheets use Excel's WEBSERVICE() function to calculate queries and parse results.

A single spreadsheet can interact with virtually unlimited SQL or REST APIs.

   -  extend Excel with open source PHP, PDO, SQL, Javascript, JSON
   -  model solutions in Excel, have access to over 220 functions in calculations
   -  any cell can interact with data entered in 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
   -  the 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, PostgreSQL, SQLlite, native PDO drivers
   -  mobile or desktop clients only require a web browser

The examples below demonstrate the key capabilities of web-enabled spreadsheets.
 
Currencies REST API Example
  All examples retrieve user-requested data and pass results back to calculations.
  The first 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 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.  
SQL 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:",""),"}]",""))  
SQL 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.
  Enter Product Numbers between 1 and 40 in the light grey fields to trigger inventory look-ups.
  
  Product Number   Product Name      Price On Hand   Ordered Extension   
     
     
     
     
     
     
     
     
     
     
      
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 SQL error code if there is an error or Null if the transaction was successful.
 
SQL Report Query Examples
  Example 1 runs 4 aggregate queries, example 2 retrieves a list with calculated totals.  
 
  
  The result   
     
  Distinct Products  Total In Inventory  Average Retail Price Total Retail Value     
     
  Product Number Product Name Total Item Retail Value     
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  
Complex SQL Query Example
  <?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;
?>
  Select "Get Summarized Data" to run the query.
  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;
?>
 
SQL Search Grid Array Example
  This example searches 1,000,000 records and pages results ("%" or "_" are wildcards).  
  Enter Part of a Company Name Sort By Direction Offset  
 
 
 
  Delimiter1 Delimiter2 Delimiter3 Delimiter4 Delimiter5 Delimiter6
  {"Company":" ","Address":" ","City":" ","County":" ","ZIP":" ","Phone1":"
 
 
 
 
 
 
 
 
 
 
 
 
  The script submits queries using a whitelist method that, when used with prepared statements, helps protect 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   = '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;
?>