QueryStorm comes equipped with a SQLite engine that can work with Excel tables as if they were database tables.
Clicking the SQL button in the ribbon pops up the QueryStorm IDE and creates a new SQLite script that you can use to run queries against the tables in the current workbook.
Once connected, we can start querying. It's important to note that the SQLite engine will only see data inside Excel tables. Data that isn't marked as a table will not be visible.
To turn a range into a table, select it and press
You can use SQL to query and modify data inside tables. All four SQL data operations are supported:
delete. Any changes that your commands make to the data inside workbook tables will be immediately visible in Excel.
When using the SQLite engine, all workbook tables get an additional column named
__address. This column contains the original address of the row in Excel. The
__address column is hidden, meaning it is not included in the results if you only specify
* in the select list; you must include it in the select list explicitly if you need it (e.g.
select *, __address from...).
This column serves two purposes:
- Double-clicking the address in the results grid will scroll to the range in Excel and select it
- The address information can be used for formatting ranges from SQL (described below)
While the SQLite engine primarily works with Excel tables, it can also work with cells via the
xlcells() table-valued function. This is primarily useful when working with unstructured data.
The following query returns a list of cells in the current selection:
We can also return a list of cells in a specified range, like so:
Here's what the returned data looks like:
For each cell in the selection, one row is returned in the results. Each cell is described with the following attributes:
formula. We can use this information to search for cells that satisfy particular criteria.
Aside from reading, the
xlcells function can also be used for updating cell values. In that case,
xlcells is used as a table in the
update query, and its parameter is specified in the
For example, the following query will add 100 to all cells in a range that have a numeric value:
1 2 3 4 5 6 7
Under the hood, table-valued-functions in SQLite are implemented as virtual tables. Virtual tables can be referenced using the table syntax or the function syntax. When using the table syntax (as shown above), the function's parameters are specified in the
Formatting rows and cells
Since the SQLite engine is running in-process with Excel, it can interact with Excel objects. A typical use case for this is modifying formatting. Two functions are provided for this purpose:
Here's an example:
1 2 3 4 5 6 7 8 9 10
And the resulting formatting looks like this:
We can use the same approach to set the background color of individual cells:
1 2 3 4 5 6 7 8
Having the formatting functions in the select list might look peculiar. Formatting functions don't return any interesting results, but having them in the select list ensures that they have access to rows that satisfy the
whereclause. SQL was not designed for imperative code, so, unfortunately, there isn't a more appropriate syntax for this.
Formatting row-by-row or cell-by-cell can be quite slow (~1s for 1k rows). When formatting tens of thousands of rows, it's faster to group them and apply formatting in bulk. For that purpose, you can use the
group_address function. This is an aggregate function that converts a group of addresses into a single address with a special syntax. Formatting functions understand this syntax and can use it to minimize the number of calls to Excel that are needed to perform the formatting.
The only change that's needed in the query is to wrap the __address column with the
1 2 3 4 5 6
This small change in the query can make formatting 10k rows take ~200ms instead of ~10s.
All columns of Excel tables are automatically indexed by the SQLite engine. This makes joins and searches very fast. However, all of the indexes are single-column indexes and no additional indexes can be defined by the user (for workbook tables).
This is because QueryStorm uses SQLite's "virtual table" mechanism for representing workbook tables. Virtual tables have the indexing logic baked in and user-defined indexes are not supported.
If different indexing is needed, you can create a copy of the table and add indexes to the copy, though this is rarely required.