Exporting data to Excel¶

Export the contents of any data grid — a table, a view, or the result of a query — to a Microsoft Excel spreadsheet.

To export a grid to Excel:

Right-click the grid and choose Export to Excel.
In Excel export options, give the file a name and click OK.

The grid's columns become the worksheet's columns, with the column names as the header row.

Include BLOB data¶

A BLOB has no text form, so a spreadsheet cell cannot hold one. Left to itself the export writes the word (blob) where the value was, and the bytes are not in the file.

Tick Include BLOB data and each BLOB is written instead as x'…' — its bytes in hexadecimal — which the spreadsheet import reads back as a BLOB. That is what makes a workbook a round trip rather than a picture of one.

It changes what the sheet is, so it is off to begin with:

	Include BLOB data off	Include BLOB data on
What is exported	the grid, as you see it	the data, as the table holds it
Columns	the ones showing, in the order showing	every column, in the table's own order
Sorting, grouping, filtering	as applied in the grid	not applied
Colours, fonts, column widths	as in the grid	plain
A BLOB cell	`(blob)`	`x'…'`
An image in a BLOB column	drawn in the cell	`x'…'`
A JSONB column	its JSON	`x'…'`

Two limits are worth knowing:

A spreadsheet cell holds 32,767 characters, and hexadecimal costs two of them per byte. A BLOB over 16,382 bytes therefore cannot go in a cell at all: it gets the (blob) marker and the export tells you so when it finishes. Truncating the hexadecimal would produce a file that read back as a different, shorter value, which is worse than saying nothing.
One marked cell takes its column with it. The import works out each column's type from the values in it, and a column that is x'…' in most rows and (blob) in one is not all one thing — so it comes back as a column of text. If the round trip matters and any value might be that large, use an SQL script instead.

Tip

These export commands work on any data grid — a table's Data tab or the results you get on the SQL tab — so you can first run a query to get exactly the rows and columns you want, then export them.