Skip to content

Data integrity

Data Validation

write_worksheet(
    rows,
    "out.xlsx",
    data_validations={
        "status": {"type": "list", "values": ["open", "closed", "blocked"],
                   "input_message": "Pick one"},
        "qty":    {"type": "whole_number", "criteria": ">=", "value": 0,
                   "error_message": "Quantity cannot be negative"},
        "score":  {"type": "decimal", "criteria": "between", "min": 0, "max": 100},
    },
)

Types: list (the dropdown), whole_number, decimal, text_length, custom, any. Every rule also takes input_title, input_message, error_title, error_message, error_style (stop, warning, information), ignore_blank and show_dropdown.

Rules cover the column's data rows, never the header. Two limits Excel imposes are raised rather than written into a file it would refuse to open: an inline list is capped at 255 characters including separators, and a fraction given to whole_number or text_length is rejected instead of truncated.

Records must carry the same keys

The header comes from the first record, because a generator gives no way to look ahead. Later records are matched to it by key name, so one whose keys arrive in another order still lands in the right columns:

rows = [{"id": 1, "name": "Alice"}, {"name": "Bob", "id": 2}]
# Bob stays under "name", 2 stays under "id".

A record missing a key leaves that cell empty. A record carrying a key the first one did not have raises, because the header row has already been written and a column cannot be given a title after the fact:

FastExcel("out.xlsx").sheet("S", [{"a": 1}, {"a": 2, "b": 3}]).save()
# ValueError: record 1: no column 'b'. The header comes from the first record (a) …

Normalise the records first, or hand over a DataFrame, which carries its own schema and has no first-record problem.

Whole numbers above 2^53

Excel stores every number as a 64-bit float, so an integer larger than 9,007,199,254,740,992 loses its last digits, and nothing reports it: the file is valid either way:

write_worksheet([{"id": 9007199254740993}], "out.xlsx")
# reads back as 9007199254740992

This is the file format, not this library; xlsxwriter and Excel itself behave the same. Long identifiers, account numbers and phone numbers belong in a text column, where they also keep any leading zeros:

write_worksheet([{"id": str(9007199254740993)}], "out.xlsx")

Decimals: exact in CSV, floats in xlsx

Excel has no decimal type: every number is a 64-bit float. A Python Decimal, and a decimal column in a DataFrame, are written to xlsx as the nearest float. Trailing zeros go, and so does every digit past the 15th or 16th significant one:

write_worksheet([{"price": Decimal("1.10")}], "out.xlsx")   # cell holds 1.1

For money that is usually fine, provided the cell is formatted to show the cents: column_formats={"price": Format().set_num_format("0.00")}. When the exact digits matter in xlsx, pass the text instead: str(value).

CSV is text, so nothing has to be lost there. A Decimal is written as its own text, which Decimal(text) reads back exactly, trailing zeros and exponent included. A DataFrame decimal column is written with as many fractional digits as its scale:

write_csv([{"price": Decimal("1.10")}], "out.csv")            # price\n1.10
write_csv([{"n": Decimal("12345678901234567890.12")}], "out.csv")
# n\n12345678901234567890.12

A Decimal NaN or infinity takes na_rep and inf_value, as a float does. Before 0.9 CSV got the nearest float too, so 1.10 came out as 1.1; a pipeline that compared the text will see the trailing zeros now.

Dates, times and timezones

Fractions of a second are kept. Excel stores a datetime as a day count with the time as the fraction, which holds about a microsecond; its number formats show at most milliseconds, so ask for them with hh:mm:ss.000. CSV writes .ffffff, the way isoformat() does, and nanoseconds from an Arrow column as .fffffffff.

A datetime.time is written as an Excel time, formatted hh:mm:ss, so it can be formatted, summed and compared like any time. CSV writes HH:MM:SS. A column_formats entry overrides the time format.

Timezones survive in CSV and not in xlsx, which has no timezone. The xlsx rule is one sentence: an aware datetime is written as its own local wall clock, and the offset is dropped.

Value xlsx CSV
naive datetime as given as given, no offset
aware Python datetime its wall-clock time wall-clock time and offset, as isoformat() writes it
aware DataFrame column its wall-clock time in the column's zone the UTC time with +00:00

So a polars column converted with dt.convert_time_zone("Asia/Jakarta"), which shows 19:00, is written to xlsx as 19:00, the same value polars' dt.replace_time_zone(None) and pandas' dt.tz_localize(None) give. A DataFrame is converted that way automatically, column by column, using the library's own timezone database; the frame you passed is not modified. In a pandas object column of aware datetime values, each value keeps its own wall clock, as it would in a list of dicts.

The wall clock drops the instant: 12:00+00:00 and 12:00+07:00 both become 12:00 in xlsx. To line values up across zones, convert them to one zone first:

rows = [{**r, "at": r["at"].astimezone(timezone.utc)} for r in rows]
df = df.with_columns(pl.col("at").dt.convert_time_zone("UTC"))      # polars
df["at"] = df["at"].dt.tz_convert("UTC")                             # pandas

An Arrow stream that is not a polars or pandas DataFrame, such as a pyarrow Table, cannot be converted for you; an aware column there raises ValueError naming the column and its zone. Drop the zone first with pyarrow.compute.local_timestamp, or pass a DataFrame.

Before 0.9 a DataFrame's aware column was written as its UTC time, out of step with both libraries and with a list of dicts. CSV keeps the instant in every case, and datetime.fromisoformat reads back the value you wrote.

Dates before 1900 cannot be stored in xlsx; Excel's calendar starts on 1900-01-01 and ends on 9999-12-31. A date outside it raises ValueError, from a list of dicts and from a DataFrame alike, naming the value and where it came from:

record 41, key 'born': 1850-01-01 is outside Excel's calendar (1900-01-01 to 9999-12-31), …
column 'born': 1850-01-01 is outside Excel's calendar (1900-01-01 to 9999-12-31), …

Earlier versions wrote such a DataFrame cell empty without an error. To keep old dates in xlsx, write the column as text. CSV has no calendar floor and writes them as they are. The one date CSV refuses is a year outside 0 to 9999, which an ISO-8601 date has no digits for: polars can hold one, and so can a pandas column with a non-nanosecond unit. That raises ValueError naming the column (or the record and key) and the date.

Column types a DataFrame can hold

A DataFrame column whose type has no cell to go in raises ValueError naming the column, instead of writing it empty:

Column type Written as
pandas category, polars Categorical and Enum their values
time of day an Excel time / HH:MM:SS
decimal the nearest float in xlsx, the exact digits in CSV
an all-null column empty cells
duration (timedelta) ValueError: cast it to a string, or to a number of seconds
binary, list, struct, and anything else ValueError

Earlier versions wrote all of these, categories and times included, as empty cells and reported success. A duration is refused rather than guessed, because a guess at the unit would be silently wrong.

Missing values, NaN and infinity

Excel has no cell type for NaN or inf, so both are written as an empty cell. That is the default, and it means a column of missing data is indistinguishable from a column of blanks. Name a representation to keep them apart:

FastExcel("out.xlsx").format(na_rep="N/A", inf_value="INF").sheet("S", df).save()

write_csv(df, "out.csv", na_rep="N/A", inf_value="INF")

na_rep covers None, an Arrow null and a float NaN as one setting, on purpose: pandas turns NaN in a float column into an Arrow null, so a knob that caught only true NaN would do nothing on the most common input there is. inf_value is separate — infinity is a value, not a missing one — and -inf takes the same text with a leading -, matching Excel's own INF/-INF.

CSV carries no formatting, so every Excel-only option is dropped — float_format, column_formats, header_format, freeze panes, merges, banding, row heights and formats, password, dedupe_strings. Only delimiter and sanitize_formulas apply. Switching a target from .xlsx to .csv therefore silently changes the output, so the builder warns and names what it discarded:

FastExcel("out.csv").format(float_format="0.00").sheet("S", rows).save()
# UserWarning: CSV/TSV output ignores Excel-only options: float_format. …

The data is still written correctly — only the styling is gone.