Excel export

functional

A workbook that looks like the grid: formats, widths, frozen panes, colors, live formulas, and the pivot on its own sheet.

An Excel export from SQLly is the grid you were looking at, as a workbook. The columns are in the order you arranged them, at the widths you set, with your number and date formats, your conditional colors, and your frozen columns. Computed columns arrive as live Excel formulas, and if the result has a pivot, the pivot gets a sheet of its own. You shouldn't have to spend the first ten minutes in Excel putting it back together.

Where it comes from

Every way of sending a result to Excel builds the same workbook:

Export… (right-click the grid, or the results toolbar), with Excel (XLSX) chosen and Save to file…. See Export.
Open in Spreadsheet, when the spreadsheet format is XLSX. See Open in spreadsheet.
Save Results in the XLSX format.

The first sheet: the grid as shown

The first sheet is named after the result: its -- sqlly name if it has one, otherwise Results 1, Results 2 and so on. It holds a banded Excel table with filter buttons on the header row, and keeps:

In the grid	In the workbook
Column order	The order on screen, including columns you dragged.
Column widths	The widths on screen.
Hidden columns	Exported as hidden columns after the visible ones, so the data is there when you unhide them.
Frozen columns	Frozen panes over the same columns, and the header row frozen too.
Sort and filters	The rows in the order and selection on screen.
Numbers	Real numbers, including `DECIMAL` and money columns, so they add up. Decimal places and thousands separators from `-- sqlly format: number=` or the grid's own number format become an Excel number format; `style=percent` becomes a percentage format.
Dates and times	Real Excel dates, so they sort, filter and subtract in Excel. A `date=` pattern is converted to the matching Excel format code (`%d/%m/%Y` becomes `dd/mm/yyyy`); otherwise dates read `yyyy-mm-dd`.
Text casing and yes/no labels	`text=upper`, `lower` and `title` are applied to the text, and `boolean=Yes/No` labels are written in place of TRUE and FALSE.
Conditional formatting	Native Excel conditional formatting, so it keeps working when the numbers change: the query's `conditional:` rules first, then your saved rules (see below).
Computed columns	Live Excel formulas (see below).
Masked columns	Exported masked, as on screen.

An exported result open in Excel: the grid's column order and widths, frozen first column and header row, number formats, a data bar and color rules, and a computed column showing its formula in Excel's formula bar.

Conditional formatting in Excel

Each rule becomes the Excel rule you would have made by hand, in the same order, so the first match still wins:

SQLly rule	Excel rule
Greater than, less than, between, is (text)	Highlight Cells Rules on the cell value
Is empty / is not empty	Blanks / no blanks
Text contains	Text that Contains
Color scale	Two-color scale from the lowest to the highest value
Data bar	Data bar from the lowest to the highest value

Text color, fill and bold carry over. Rules from the query's conditional: lines come before the rules you saved in the Conditional Formatting dialog, as they do in the grid.

Computed columns become formulas

A computed column is written as the Excel formula it stands for, with its value already filled in, so the file reads correctly even before Excel recalculates:

In SQLly	In the workbook (sheet row 7)
`!Qty * !Price`	`=B7*C7`
`!Sales - !Sales[1]`	`=D7-D6`
`!Sales / !Sales[$1]`	`=D7/D$2`
`SUM(!Sales[2]:!Sales)`	`=SUM(D5:D7)`
`!Amount * @rate`	`=E7*rate`, with `rate` a workbook name holding the query's value
`COALESCE(!Email, !Phone)`	Nested `IF`s that pick the first non-empty value

The letters follow the sheet, so a reference still points at the right column after you reordered or hid columns in the grid.
A reference above the first row, like !Sales[1] on the first data row, points at an always-empty cell at the bottom of the sheet, so it reads blank exactly as in SQLly.
@variables become defined names (Formulas › Name Manager). Change the name's value in Excel and every formula that uses it recalculates.
Newer functions such as IFS, SWITCH, TEXTJOIN, CONCAT, TEXTBEFORE and TEXTAFTER are stored the way Excel stores them. An old version of Excel that doesn't have one shows #NAME? after it recalculates.
Formulas that read other rows need the query's row order. !Sales[1] means the row the database sent before, so it is only written as a formula when the rows go out in the query's order. If you sorted or filtered the grid, those columns are exported as their values instead. Formulas that only read the same row are always written as formulas.
Cells that hold an error, such as #DIV/0!, are written as real Excel errors.

The second sheet: the pivot

When the result has a pivot set up, a second sheet named <result name> pivot holds it exactly as displayed: collapsed and expanded groups, sort, filters, subtotals and grand totals, row labels indented by level, totals in bold, and the label column frozen. It's the numbers, not an Excel PivotTable, so it looks the same in any spreadsheet app and needs no refresh.

The pivot sheet of an exported workbook: nested row labels indented by level, column headers, subtotals and grand totals in bold.

Charts are not exported. The data behind them is on the first sheet, ready for Excel's own charts.