The purpose of this tutorial is to show how database connections can be built into SpreadsheetWEB applications.
Many Excel workbooks contain large data sets used in calculations. Excel has various functions like VLOOKUP, HLOOKUP, OFFSET, INDEX & MATCH to pull the right information from the data tables based on specific input selections. But since the entire data tables should be placed in the workbook, this increases the size of the workbook unnecessarily. Excel has 1,048,576 rows limit per worksheet. But as most Excel experts know such a file will be in tens of megabytes in size.
A more preferable approach is to keep this data in a database and query it from Excel. There are built-in database connection capabilities in Excel. However, SpreadsheetWEB has a different way of handling database connections.
DBOpen and DBGet are custom SpreadsheetWEB functions used for database calls. While DBOpen establishes a connection between the application and the database, DBGet uses this connection to send and receive query results.
Let’s take a shipping cost calculator used by a logistics company. It uses origination and destination zip codes to calculate shipping rates. There are 3 data tables: Lookups table has 65,000 rows and 5 columns. Options table has 23715 rows and 2 columns. And finally; Tables table has 152 rows and 9 columns. This the main reason why this file is over 2MB. SUMIFS, INDEX and VLOOKUP functions pull the rates from these tables based on the zip codes entered.
If we remove these tables and place them in a database, the file size is reduced to 22.3KB. SUMIFS, INDEX and VLOOKUP functions are replaced with DBOPEN and DBGET functions. Remember, these two formulas are specific to SpreadsheetWEB and cannot be executed in Excel.
DBOpen is used to establish a connection with the data source. The function returns a seed which is used by DBGet function to query the database and set them to the application. If connection is not successful then the formula returns “0”. The function contains 3 parameters:
- connection_string: String that specifies a data source and the means to connect to it. You can use 2 types of strings:
- Standard connection string like "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Northwind; Integrated Security=SSPI"
- Generated Key for internal SpreadsheetWeb connections. This key allows for connecting all tables in a site without revealing critical information, such as database credentials. This key is only accessible by the System Admin user and applies to connecting tables in SpreadsheetWEB database server. A standard connection string is required to connect all external databases.
- pooling: A logic value that enables or disables the use of a connection pool. A connection pool is a cache of database connections that allows connections to be reused when required. Connection pools are used to enhance the performance of executing commands in a database. It’s set to TRUE by default.
- output_range: String value that specifies the cell reference for the query result. If empty, the result is populated in the host cell as a regular function. (e.g. "Sheet1!C3", "My Connection Sheet!F25"). Its default value is an empty string (“”).
Syntax: =DBOpen(connection_string, pooling, output_range)
It is recommended to use the generated key for connection string, and leaving pooling and output_range empty: =DBOpen(“i6aUrykpt4qd+zUGcTp96N/”,,). This usage can be seen in cell B16 below.
Please note that a named range needs to be used for reference cell. Below is an example with the connectionString named range used in the formula.
DBGet executes the query against the data source connected via DBOpen. If the query has been successfully executed; the function will populate a two dimensional array to a range, defined as the third parameter of the function. Although the function returns “0” to the original cell; it can print values or return a JSON string into a range (or cell) which is defined in the output_range parameter. The function contains 4 parameters:
- dbopen_result: Cell reference for the DBOpen output range. Use DBOpen in its default form (connection_string,,).
- query_string: SQL query string. Please note that DBGet queries are limited to SELECT statements.
- output_range: String value that specifies the cell(s) where the result will be populated (e.g. "Sheet1!C3:G10", "My Connection Sheet!F25:T42").
- single_cell: Set this to TRUE to get query results as a single string in JSON format. Result is printed to the first cell of the range identified with output_range. JSON format can be used in custom applications with JavaScript, and improves performance in some situations. This is an optional parameter and its default value is FALSE.
Syntax: =DBGet(dbopen_result, query_string, output_range, [single_cell])
The cell B17 in the sample workbook demonstrates the formula use. A named range is used for cell reference and dbopenToken refers to cell B16 with the DBOpen function. You can see the use of named ranges in the queries above. Excel’s database and lookup functions can be easily replaced with simple SELECT-WHERE queries.
Because DOpen and DBGet are custom functions which are not native to Excel; they will appear as #NAME?.
Once the workbook is uploaded to a SpreadsheetWEB server, these cells will populate with actual query results.
Please note that due to security settings of SpreadsheetWEB public cloud, making database calls using DBOpen and DBGet formulas are only allowed in applications deployed on private servers.
Decoupling database elements from Excel workbooks can result in much smaller files and significantly faster load speeds when deployed on the web. This approach also removes the row and column limitations of Excel, allowing for more complicated applications.
