Web Enabled Excel Spreadsheets
 
  
Excel WEBSERVICE() Function
  XLSQL forms are web-enabled Excel spreadsheets that connect data from databases and web services.

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

Here are some of the important features and benefits to keep in mind.

   -  they work by integrating Excel, PHP, PDO, SQL, JavaScript, and JSON components
   -  they are initially modeled in Excel and can utilize 220 of Excel's functions in calculations
   -  any cell can interact with data entered in to the XLSQL form or from a database or a web service
   -  the JSON returned from a database or web service can be parsed into live calculations
   -  a database abstraction class is integrated to simplify database communications
   -  the abstraction class uses prepared statements to safely execute queries
   -  the class can utilize SQL transactions for create, update and delete
   -  the output from one form can load directly in to another form
   -  the forms are hosted on standard PHP-equipped servers
   -  network clients only require a regular web browser
   -  no subscriptions required, you own the code

The examples below demonstrate the fundamentals and show the scripts used.
 
A Web Service API Example
  All of the examples retrieve user-requested data and pass the results back in to calculations.
  This example uses the URL template below to request currency exchange rates from the fixer.io web service.
  "https://data.fixer.io/api/latest?access_key=FIXERIOAPIKEY&base="&BASERATECODE     
 
  Select a Base Rate Currency below to trigger the web service 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://xlsql.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 SQL 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 Simple Query Example
  This example executes a query requesting the retail value for all items currently in stock.  
  The PHP script that contains the query and returns the result in a custom 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");
$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);
?>
 
  Found Count  Total Value  
 
 
  The result  
  
 
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
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  
 
 
Additional Useful Functionality
  XLSQL forms can generate QRCodes from calculations. Enter text in the field below to build the code.  
  
          
       
  XLSQL forms can incorporate external components like this SQL inventory interface.
 
   
Summary & Contact Information
  A Microsoft Excel spreadsheet can be configured to use the WEBSERVICE() function to send calculated parameters to web service APIs or to PHP scripts that execute queries based on the parameters and return the 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.

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 shared applications, XLSQL spreadsheets provide an extremely cost-effective, highly-adaptable and connectable canvas, running on your servers or the cloud.

Please use this form to send questions or upload files for project estimates.