This is a Microsoft Excel spreadsheet that has been converted to a dynamic web form by SpreadSheetConverter www.spreadsheetconverter.com 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 results. Queries can run 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. - model process solutions with Excel 365, utilize 240 Excel functions in calculations - use the WEBSERVICE() function to send query parameters to PHP, PDO, SQL, cURL scripts - the data returned from SQL or REST API queries can be parsed to trigger calculation updates - PHP Data Objects database access layer drastically simplifies database communications - PDO uses typed parameters and prepared statements to avoid SQL injection - prepared statements can safely create, update, delete in transactions - send calculated output to spreadsheets, databases, forms, APIs - MySQL, SQLServer, Oracle, PostgreSQL, SQLlite PDO drivers - no VBA, no ODBC or JDBC, no subscriptions, ultra-simple deployment Simple, reliable low-code methods for building securable SQL and REST API-integrated user interfaces.
Selecting a Customer Name from the green drop-down menu performs a VLOOKUP that returns the customer's number. The number is passed to a PHP script that executes a query that returns the customer's information from a MySQL database. The returned PHP array is parsed into spreadsheet cells. Company Name Alpha Cognac American Souvenirs Inc Amica Models & Co. ANG Resellers Anna's Decorations, Ltd Anton Designs, Ltd. Asian Shopping Network, Co Asian Treasures, Inc. Atelier graphiques Austin City Limits Australian Collectables, Ltd Australian Collectors, Co. Australian Gift Network, Co Auto Canal+ Petit Auto-Moto Classics Inc. AV Stores, Co. Baane Mini Imports Bavarian Collectables Imports, Co. Bay Area Bombers Bellevue Bikes BG&E Collectables Big Shiny Wheels Blauer See Auto, Co. Boards & Toys Co. Brown & Sons CAF Imports Cambridge Collectables Co. Canadian Gift Exchange Network Chain & Sprocket Chico Flyers Classic Gift Ideas, Inc Classic Legends Inc. Clover Collections, Co. Collectable Mini Designs Co. Collectables For Less Inc. Corinthian Technologies Corporate Gift Ideas Co. Corrida Auto Replicas, Ltd Cruz & Sons Co. Daedalus Designs Imports Danish Wholesale Imports Der Hund Imports Diecast Classics Inc. Diecast Collectables Double Decker Gift Stores, Ltd Down Under Souveniers, Inc Dragon Souveniers, Ltd. Enaco Distributors EP Cycle Shop Euro+ Shopping Channel Extreme Desk Decorations, Ltd Fabri Creations Feuer Online Stores, Inc Franken Gifts, Co Frau da Collezione FunGiftIdeas.com Gift Depot Inc. Gift Ideas Corp. Gifts4AllAges.com giftsbymail.co.uk GiftsForHim.com Glendale Bike Sales Handji Gifts& Co Havel & Zbyszek Co Heintze Collectables Herkku Gifts Iberia Gift Imports, Corp. Jacques Ferrer Jones Data Services, LLC Kelly's Gift Shop King Kong Collectables, Co. Kommission Auto Kremlin Collectables, Co. La Corne D'abondance, Co. La Rochelle Gifts Land of Toys Inc. Lisboa Souveniers, Inc Long Beach Cycle Services L'ordine Souveniers Lyon Souveniers Marseille Mini Autos Marta's Replicas Co. McKellars Men 'R' US Retailers, Ltd. Messner Shopping Network Microscale Inc. Mini Auto Werke Mini Caravy Mini Classics Mini Creations Ltd. Mini Gifts Distributors Ltd. Mini Wheels Co. Motor Mint Distributors Inc. Moulin Rouge Muscle Machine Inc Norway Gifts By Mail, Co. Online Diecast Creations Co. Online Mini Collectables Oregon Bicycle Parts Osaka Souveniers Co. Oulu Toy Supplies, Inc. Outer Banks Bikes Palm Springs Ventures Petit Auto Portland Toys Porto Imports Co. Precious Collectables Raanan Stores, Inc Redmond Distribution Services Reims Collectables Rounder's Bike Store Rovelli Gifts Royal Canadian Collectables, Ltd. Royale Belge Salzburg Collectables SAR Distributors, Co Saveley & Henriot, Co. Scandinavian Gift Ideas Schuyler Imports "" Account Credit Limit Current Accounts Payable Available Credit
This first example uses the calculated URL below to request currency exchange rates from the fixer.io API. This calculated URL queries the API for an initial base-rated JSON array that is parsed for a specific currency. "" It uses the VLOOKUP formula below to match the selected currency to it's unique 3-character currency code. VLOOKUP(BASESELECTOR,B22:C191,2,FALSE) Select a Base Rate Currency below to trigger the request and load the JSON result. * United States Dollar Afghan Afghani Albanian Lek Algerian Dinar Angolan Kwanza Argentine Peso Armenian Dram Aruban Florin Australian Dollar Azerbaijani Manat Bahamian Dollar Bahraini Dinar Bangladeshi Taka Barbadian Dollar Belarusian Ruble Belize Dollar Bermudan Dollar Bhutanese Ngultrum Bitcoin Bolivian Boliviano Bosnia-Herzegovina Convertible Mark Botswanan Pula Brazilian Real British Pound Sterling Brunei Dollar Bulgarian Lev Burundian Franc Cambodian Riel Canadian Dollar Cape Verdean Escudo Cayman Islands Dollar CFA Franc BCEAO CFA Franc BEAC CFP Franc Chilean Peso Chilean Unit of Account (UF) Chinese Yuan Colombian Peso Comorian Franc Congolese Franc Costa Rican Colón Croatian Kuna Cuban Convertible Peso Cuban Peso Czech Republic Koruna Danish Krone Djiboutian Franc Dominican Peso East Caribbean Dollar Egyptian Pound Eritrean Nakfa Ethiopian Birr Euro Falkland Islands Pound Fijian Dollar Gambian Dalasi Georgian Lari Ghanaian Cedi Gibraltar Pound Gold (troy ounce) Guatemalan Quetzal Guernsey Pound Guinean Franc Guyanaese Dollar Haitian Gourde Honduran Lempira Hong Kong Dollar Hungarian Forint Icelandic Króna Indian Rupee Indonesian Rupiah Iranian Rial Iraqi Dinar Israeli New Sheqel Jamaican Dollar Japanese Yen Jersey Pound Jordanian Dinar Kazakhstani Tenge Kenyan Shilling Kuwaiti Dinar Kyrgystani Som Laotian Kip Latvian Lats Lebanese Pound Lesotho Loti Liberian Dollar Libyan Dinar Lithuanian Litas Macanese Pataca Macedonian Denar Malagasy Ariary Malawian Kwacha Malaysian Ringgit Maldivian Rufiyaa Manx pound Mauritanian Ouguiya Mauritian Rupee Mexican Peso Moldovan Leu Mongolian Tugrik Moroccan Dirham Mozambican Metical Myanma Kyat Namibian Dollar Nepalese Rupee Netherlands Antillean Guilder New Belarusian Ruble New Taiwan Dollar New Zealand Dollar Nicaraguan Córdoba Nigerian Naira North Korean Won Norwegian Krone Omani Rial Pakistani Rupee Panamanian Balboa Papua New Guinean Kina Paraguayan Guarani Peruvian Nuevo Sol Philippine Peso Polish Zloty Qatari Rial Romanian Leu Russian Ruble Rwandan Franc Saint Helena Pound Salvadoran Colón Samoan Tala São Tomé and Príncipe Dobra Saudi Riyal Serbian Dinar Seychellois Rupee Sierra Leonean Leone Silver (troy ounce) Singapore Dollar Solomon Islands Dollar Somali Shilling South African Rand South Korean Won Special Drawing Rights Sri Lankan Rupee Sudanese Pound Surinamese Dollar Swazi Lilangeni Swedish Krona Swiss Franc Syrian Pound Tajikistani Somoni Tanzanian Shilling Thai Baht Tongan Paʻanga Trinidad and Tobago Dollar Tunisian Dinar Turkish Lira Turkmenistani Manat Ugandan Shilling Ukrainian Hryvnia United Arab Emirates Dirham United States Dollar Uruguayan Peso Uzbekistan Som Vanuatu Vatu Venezuelan Bolívar Fuerte Vietnamese Dong Yemeni Rial Zambian Kwacha Zambian Kwacha (pre-2013) Zimbabwean Dollar Select the currency to use for this order form or invoice's calculations… * United States Dollar Afghan Afghani Albanian Lek Algerian Dinar Angolan Kwanza Argentine Peso Armenian Dram Aruban Florin Australian Dollar Azerbaijani Manat Bahamian Dollar Bahraini Dinar Bangladeshi Taka Barbadian Dollar Belarusian Ruble Belize Dollar Bermudan Dollar Bhutanese Ngultrum Bitcoin Bolivian Boliviano Bosnia-Herzegovina Convertible Mark Botswanan Pula Brazilian Real British Pound Sterling Brunei Dollar Bulgarian Lev Burundian Franc Cambodian Riel Canadian Dollar Cape Verdean Escudo Cayman Islands Dollar CFA Franc BCEAO CFA Franc BEAC CFP Franc Chilean Peso Chilean Unit of Account (UF) Chinese Yuan Colombian Peso Comorian Franc Congolese Franc Costa Rican Colón Croatian Kuna Cuban Convertible Peso Cuban Peso Czech Republic Koruna Danish Krone Djiboutian Franc Dominican Peso East Caribbean Dollar Egyptian Pound Eritrean Nakfa Ethiopian Birr Euro Falkland Islands Pound Fijian Dollar Gambian Dalasi Georgian Lari Ghanaian Cedi Gibraltar Pound Gold (troy ounce) Guatemalan Quetzal Guernsey Pound Guinean Franc Guyanaese Dollar Haitian Gourde Honduran Lempira Hong Kong Dollar Hungarian Forint Icelandic Króna Indian Rupee Indonesian Rupiah Iranian Rial Iraqi Dinar Israeli New Sheqel Jamaican Dollar Japanese Yen Jersey Pound Jordanian Dinar Kazakhstani Tenge Kenyan Shilling Kuwaiti Dinar Kyrgystani Som Laotian Kip Latvian Lats Lebanese Pound Lesotho Loti Liberian Dollar Libyan Dinar Lithuanian Litas Macanese Pataca Macedonian Denar Malagasy Ariary Malawian Kwacha Malaysian Ringgit Maldivian Rufiyaa Manx pound Mauritanian Ouguiya Mauritian Rupee Mexican Peso Moldovan Leu Mongolian Tugrik Moroccan Dirham Mozambican Metical Myanma Kyat Namibian Dollar Nepalese Rupee Netherlands Antillean Guilder New Belarusian Ruble New Taiwan Dollar New Zealand Dollar Nicaraguan Córdoba Nigerian Naira North Korean Won Norwegian Krone Omani Rial Pakistani Rupee Panamanian Balboa Papua New Guinean Kina Paraguayan Guarani Peruvian Nuevo Sol Philippine Peso Polish Zloty Qatari Rial Romanian Leu Russian Ruble Rwandan Franc Saint Helena Pound Salvadoran Colón Samoan Tala São Tomé and Príncipe Dobra Saudi Riyal Serbian Dinar Seychellois Rupee Sierra Leonean Leone Silver (troy ounce) Singapore Dollar Solomon Islands Dollar Somali Shilling South African Rand South Korean Won Special Drawing Rights Sri Lankan Rupee Sudanese Pound Surinamese Dollar Swazi Lilangeni Swedish Krona Swiss Franc Syrian Pound Tajikistani Somoni Tanzanian Shilling Thai Baht Tongan Paʻanga Trinidad and Tobago Dollar Tunisian Dinar Turkish Lira Turkmenistani Manat Ugandan Shilling Ukrainian Hryvnia United Arab Emirates Dirham United States Dollar Uruguayan Peso Uzbekistan Som Vanuatu Vatu Venezuelan Bolívar Fuerte Vietnamese Dong Yemeni Rial Zambian Kwacha Zambian Kwacha (pre-2013) Zimbabwean Dollar 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.
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 Enter Product Numbers between 1 and 40 into the Product# input fields to execute SQL queries that return the Product Name along with current Unit Price and Units On Hand information in real-time. Delete the line's Product Numbers to clear the query responses. Discount % Discount Amount Adjusted Sales Tax % Sales Tax Amount Subtotal Shipping Credit Limit Balance Available Order Total Edit Order Submit Order
This example updates 3 SQL database records using 1 transaction. If an individual record's update fails the transaction rolls back. The first 3 products from the Dynamic Invoice 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. Edit Execute Select Edit to change Quantity On Hand values, Execute to run the transaction, Edit again to see updated values above. The calculated URL The SQL error message if there is an error or transaction completed message if successful.
Selecting "Refresh" from the green drop-down menu executes 4 queries in sequence. Returns a string result which is parsed into 4 cells. The WEBSERVICE() URL Clear Refresh The query result Total retail value Units in stock Average retail price Distinct products
This example uses a calculated URL to connect to a PHP script and run a SQL query that returns a value. 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 * Round Brilliant Cushion Emerald Heart Ideal Marquise Oval Pear Princess Radiant * IF VVS1 VVS2 VS1 VS2 SI1 SI2 I1 I2 I3 * D E F G H I J K L M The calculated URL with parameters
Clear Summarize This example executes an aggregate query and returns the sorted result. Select "Summarize" above to run. Product Name Total Sales
Excel's text functions make it relatively easy to parse information returned from SQL or REST API queries. Selecting a currency from the green menu queries the CoinLore API with an id and parses the result using field names as text delimiters. * 0x Aave Algorand Arweave Audius Avalanche Axie Infinity Bancor Basic Attention Token Binance Coin Binance USD Bitcoin Bitcoin Cash Bitcoin Gold Bitcoin SV BitTorrent Cardano Celo ChainLink Chiliz Compound Cosmos Crypto.com Chain Curve DAO Token Dash Decentraland Decred Digibyte Dogecoin Elrond eGold Enjin Coin EOS Ethereum Ethereum Classic Fantom Filecoin Flow FTX Token Harmony Hedera Hashgraph Holo Horizen Huobi Token ICON IOStoken IOTA Klaytn KuCoin Shares Kusama Litecoin Maker Matic Network Monero Nano NEAR Protocol NEM Neo Nexo OKB OmiseGO Ontology PancakeSwap Polkadot Qtum Quant Ravencoin Raydium renBTC Shiba Inu Siacoin Solana Stacks Stellar Sushi Symbol Synthetix Network Token Telcoin Terra TerraUSD Tether Tezos The Graph The Sandbox Theta Fuel Theta Token THORChain TRON TrueUSD UMA Uniswap UNUS SED LEO USD Coin VeChain Waves WhiteCoin Wrapped Bitcoin XRP yearn.finance Zcash Zilliqa 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)
This example searches 1,000,000 company records and pages results. Use "%" (multiple characters) or "_" (single character) as wildcards. Enter Part of a Company Name Sort By Direction Offset Company Address City County ZIP Phone ASC DESC 0 10 20 30 40 50 60 70 80 90 100
These examples use either the PHPMailer code library or the TextMagic API to send e-mail or SMS messages. E-mail Phone Subject Message "" "" Clear Send E-mail Send SMS - Select a method from the green drop-down menu to send an e-mail or SMS message E-mail Response SMS Response
This example uses the libsodium library to encrypt and decrypt data using the AES-256-GCM algorithm. Select "Encrypt" from the left drop-down menu to encrypt, "Decrypt" from the right menu to decrypt. The text to encrypt This is test data with some more higher Unicode characters included: wijze ; größeren ; pingüino españa זה כיף סתם לשמוע איך תנצח קרפד עץ טוב בגן. japanese : 私は寿司が好きだ。 Replace CHAR(10) with --- "" Un-encrypted reference text Clear Encrypt Clear Decrypt The encrytion array "" Encrypted text from array "" Encryption key from array Encryption nonce from array The decrypted text ""
Microsoft Excel spreadsheets can use the WEBSERVICE() function to send calculated parameters to PHP scripts that utilize PDO (PHP Data Objects) to execute SQL queries or cURL to execute REST API queries based on the parameters and then format and return the results back to the spreadsheet cells or calculations. Excel spreadsheets can execute queries from the desktop application or can optionally be converted to HTML with SpreadSheetConverter, resulting in calculating web forms (like this document) that integrate Excel's calculation engine, formulas, and native functions with PHP's connection and data interaction methods. Using the PDO database access layer drastically simplifies SQL database communications, making it much easier to design and build securable web applications that can include simple or very complex calculations, custom math or business logic, with unlimited real-time SQL database or REST API connections. Whether creating new applications or enhancing legacy systems, dynamic SpreadSheetConverter forms can provide an extremely cost-effective and adaptive programmable canvas, running on desktops or servers. "a relatively straight-forward way to make the world’s most popular business application work with the world’s most popular data interaction languages and server programming technologies."