Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Prevent Formula Injection in Excel Exports from Python

User-supplied text that starts with "=" becomes a live formula when an export writes it to Excel: openpyxl, xlsxwriter and pandas all do this by default. Find which writers turn strings into formulas, store data as text in .xlsx files, neutralise dangerous prefixes in CSV exports, and test that no data cell ever becomes a formula.
Logging, testing & packagingWriting Excel files

An export takes rows from a database or a web form and writes them to a spreadsheet. If a value typed by a user starts with =, the Python writer may store it as a formula, and when a colleague opens the file, the spreadsheet evaluates it. A formula can display a convincing link to a phishing page, pull data from other cells into a visible message, and in some configurations try to reach external resources. This is known as formula injection or CSV injection, and it is the export's responsibility to stop it: the person opening the file cannot tell a malicious cell from a real one. This page shows which writers are affected and how to write data safely. It is part of Error Handling and Logging in Excel Automation.

Prerequisites

Bash
pip install pandas openpyxl xlsxwriter

feedback.pkl stands in for data from a customer feedback form. Two values were entered maliciously or mischievously: a "name" that is a HYPERLINK formula and a comment that is arithmetic. Two more are innocent but start with characters spreadsheets treat specially: a phone number starting with + and a comment starting with @.

Step 1 — See what the writers do by default

Write the DataFrame with each pandas engine, and with openpyxl directly, then ask openpyxl which cells were stored as formulas (data_type == "f"):

Python
from openpyxl import Workbook, load_workbook

feedback = pd.read_pickle("feedback.pkl")

def formula_cells(path):
    ws = load_workbook(path).active
    return [c.coordinate for row in ws.iter_rows() for c in row if c.data_type == "f"]

for engine in ("openpyxl", "xlsxwriter"):
    feedback.to_excel(f"export_{engine}.xlsx", index=False, engine=engine)
    print(f"pandas + {engine:10}", formula_cells(f"export_{engine}.xlsx"))

wb = Workbook()
for row in [feedback.columns.tolist()] + feedback.values.tolist():
    wb.active.append(row)
wb.save("export_append.xlsx")
print("openpyxl append     ", formula_cells("export_append.xlsx"))

Every route stores the two values that start with = as formulas, in the same two cells. The HYPERLINK cell would show "Reset your password" as a clickable link in the Customer column. In .xlsx files, values starting with +, - or @ were stored as text by all three writers; the formula rule for .xlsx output is "starts with =".

How a form entry becomes a live formula A user types text starting with an equals sign into a web form. It is stored in the database as text. The export writes it with a library that turns strings starting with equals into formulas. When the file is opened, the spreadsheet evaluates it, for example showing a phishing link. Web form user types =HYPERLINK(...) Database stored as text Export writer makes it a formula Spreadsheet evaluates on open Reader sees a real-looking link

Step 2 — Write data as text in .xlsx files

The fix for .xlsx output is to write data values as strings, so no user value is ever stored as a formula, while still writing the report's own formulas explicitly.

pandas with xlsxwriter: turn off strings_to_formulas. Data strings are written with write_string; numbers and dates are unaffected:

Python
with pd.ExcelWriter("safe_xlsxwriter.xlsx", engine="xlsxwriter",
                    engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
    feedback.to_excel(writer, index=False, sheet_name="Feedback")
    ws = writer.sheets["Feedback"]
    ws.write("B8", "Average score")
    ws.write_formula("C8", "=AVERAGE(C2:C6)")           # the report's own formula, written on purpose
print("formula cells:", formula_cells("safe_xlsxwriter.xlsx"))

Only C8, the formula the report meant to write, is a formula; the HYPERLINK text is stored as text and shown as text.

openpyxl: after assigning a value, set the cell's data_type to "s" for every string that came from data. A helper that writes a row keeps it in one place:

Python
def append_as_data(ws, values):
    ws.append(values)
    for cell in ws[ws.max_row]:
        if isinstance(cell.value, str):
            cell.data_type = "s"

wb = Workbook()
ws = wb.active
append_as_data(ws, feedback.columns.tolist())
for row in feedback.values.tolist():
    append_as_data(ws, row)
ws["C8"] = "=AVERAGE(C2:C6)"
wb.save("safe_openpyxl.xlsx")
print("formula cells:", formula_cells("safe_openpyxl.xlsx"))
print("B3 stored as:", repr(load_workbook("safe_openpyxl.xlsx").active["B3"].value))

The value is kept exactly as the user typed it, =1+1, but as text. Nothing is altered, which matters for exports people search, filter and reconcile. Note that ws.append still decides the type first and the helper then overrides it, so the helper must be used for every data row; a single row appended directly brings the problem back. The test in Step 4 is what catches that.

Step 3 — CSV exports need escaping

A CSV file has no cell types: the spreadsheet decides what each value is when it opens the file, and it treats a leading = as a formula. Excel also treats values starting with +, - and @ as formulas, as documented in the OWASP guidance on CSV injection; LibreOffice's default import evaluates =. The standard defence is to prefix any value starting with one of those characters, or with a tab or carriage return, with a single quote, so it is read as text:

Python
DANGEROUS = ("=", "+", "-", "@", "\t", "\r")

def neutralise(value):
    if isinstance(value, str) and value.startswith(DANGEROUS):
        return "'" + value
    return value

safe_csv = feedback.map(neutralise)
safe_csv.to_csv("feedback_safe.csv", index=False)
from pathlib import Path
print(Path("feedback_safe.csv").read_text(encoding="utf-8"))

The prefix changes the data: the phone number now carries a leading apostrophe. That is the trade-off for CSV. If the file is going to people who will open it in a spreadsheet, prefer an .xlsx export with typed text cells from Step 2, which needs no alteration. If a program consumes the CSV, the prefix is noise it must strip; agree the convention with the consumer.

What each output needs A table of output formats and the protection each needs. For xlsx written by xlsxwriter, turn off strings_to_formulas and write intended formulas with write_formula. For xlsx written by openpyxl, set data_type to s for data strings. For CSV opened in a spreadsheet, prefix values starting with equals, plus, minus, at, tab or carriage return with a single quote. Output Risky values Protection xlsx via xlsxwriter start with = strings_to_formulas off xlsx via openpyxl start with = data_type = 's' CSV for spreadsheets = + - @ tab CR prefix with '

Why fix it in the export, not in the form

It is tempting to reject = in the web form and consider the problem solved. It does not hold up. Exports combine data from many sources: imports from partners, records created before the validation existed, values edited directly in the database, fields nobody thought of as free text. And legitimate values start with these characters all the time: phone numbers with +, negative adjustments with -, social handles with @, a surname typed with a stray =. Input validation that rejects them annoys users and still misses the next source.

The export is the one place where every value passes, and the only place that knows the output format. Encoding for the output there is the same principle as escaping HTML when rendering a page rather than when accepting a comment: store what people typed, and make it safe for each destination at the moment it is written.

Validate on the way in, encode on the way out Two panels. Validating input at the form catches some values but misses imports, old records and direct edits, and rejects legitimate values. Encoding at the export sees every value regardless of source and keeps the data unchanged in the database. Reject at the form misses imports and old rows rejects +44 and -12.50 one source of many Encode at the export every value passes through data stays as typed knows the output format

Step 4 — Test that data never becomes a formula

The protection is easy to lose in a refactor: someone switches engines, or a new column is written with a different helper. A test that injects hostile values into every text column and checks the output catches that:

Python
def assert_no_injected_formulas(export, allowed=()):
    hostile = feedback.copy()
    for col in hostile.select_dtypes(include=["object", "string"]).columns:
        hostile[col] = "=1+1"
    path = export(hostile)
    bad = [c for c in formula_cells(path) if c not in allowed]
    assert not bad, f"data written as formulas: {bad}"
    return "ok"

def my_export(frame, path="under_test.xlsx"):
    with pd.ExcelWriter(path, engine="xlsxwriter",
                        engine_kwargs={"options": {"strings_to_formulas": False}}) as writer:
        frame.to_excel(writer, index=False)
        writer.sheets["Sheet1"].write_formula("C8", "=AVERAGE(C2:C6)")
    return path

print(assert_no_injected_formulas(my_export, allowed={"C8"}))

The allowed set lists the report's own formula cells, so the test fails only on formulas that came from data. Test Excel Output with pytest shows how to run checks like this in a test suite.

Common pitfalls

  • Stripping or rejecting the characters. A name like =Ahmed or a phone number with + is legitimate; store it as text instead of deleting characters.
  • Protecting only one column. Every text column that contains user input needs it, including names and addresses.
  • Using the CSV apostrophe in .xlsx files. Written from Python, the apostrophe becomes part of the value; use text cells instead.
  • Forgetting report formulas. With strings_to_formulas off, write intended formulas with write_formula, or they become text too; see Fix Formulas Showing as Text in Excel Output from Python.
  • Trusting the recipient's security settings. Some warnings can be disabled; the export should not depend on them.

Conclusion

openpyxl, xlsxwriter and pandas all write strings that start with = as formulas by default, so user input can become a live formula in an export. For .xlsx files, write data strings as text — strings_to_formulas: False with xlsxwriter, data_type = "s" with openpyxl — and write the report's own formulas explicitly. For CSV files opened in spreadsheets, prefix values starting with =, +, -, @, tab or carriage return with a single quote. Keep a test that feeds hostile values through the export.

Frequently asked questions

What is formula injection in Excel? When untrusted text that starts with a formula character is written to a spreadsheet and the application evaluates it as a formula. It can be used to show misleading links or messages to whoever opens the file.

Does pandas to_excel write strings starting with = as formulas? Yes, with both the openpyxl and xlsxwriter engines. Pass engine_kwargs={"options": {"strings_to_formulas": False}} with xlsxwriter to write them as text.

How do I stop openpyxl treating a string as a formula? Assign the value, then set cell.data_type = "s". The value is stored unchanged as text.

How do I protect a CSV export against CSV injection? Prefix any value starting with =, +, -, @, a tab or a carriage return with a single quote, following the OWASP recommendation, or export .xlsx with text cells instead.

More on logging, testing & packaging

More on logging, testing & packaging →