Formatting via SQL¶
Since the SQLite engine is running in-process with Excel, it can interact with Excel objects. The primary use case for this is to modify formatting.
As mentioned before, each table has a hidden
__address column which we can use to selectively apply formatting to rows.
Let's set the background color for movies that grossed more than $40M:
--first clear any previous formatting select ClearBackgroundColor('movies'); --apply new formatting select *, SetBackgroundColor(__address, 'Orange') from movies where gross > 400000000 order by __row asc
In the example, we're using the
ClearBackgroundColor function to clean any previously applied formatting, after which we apply formatting to the desired rows via the
It might seem strange to use a
SELECT statement to modify row formatting, but it's fairly easy to get used to, and there doesn't seem to be a more elegant way of doing it via SQL.
We can use the
SetBackgroundColor function to format cells as well. Here's an example of coloring cells based on their values:
select *, (case when value > 100000 then SetBackgroundColor(Address, 'blue') else SetBackgroundColor(Address, 'red') end) color from cells() where type = 'Double'
Here I'm formatting cells that contain a number in different colors, depending on their value.