Spreadsheets (.xlsx)¶

A PDF is what you send when the numbers are final. A spreadsheet is what you send when the recipient has to work with them: sort, filter, re-total, check line by line. A budget, a price table, a reconciliation, a report export — all of it arrives as .xlsx, and almost always gets assembled with openpyxl by hand.

Assembling by hand costs three things, always the same three:

Row arithmetic. Every write is a (row, column) pair you track. Insert a line near the top and every constant below it shifts.
Styling that drifts. Four assignments (font, fill, alignment, border) repeated on every cell, and the thousandth row stops matching the first.
The wrong number format. "#,##0.00" looks right and is a trap: Excel resolves that mask with the locale of whoever opens the file. The workbook you built in São Paulo shows 1.234,56 at home and 1,234.56 on a colleague's en-US laptop. The value is the same; the document is wrong, and nobody notices.

tempest_fastapi_sdk.spreadsheet fixes all three: a row cursor, columns declared once, and masks pinned to pt-BR.

And the other way round, to read a spreadsheet — the .xlsx a user uploads, or the sheet someone keeps in Google — see Reading an .xlsx file and Reading a Google Sheet.

Extra required

uv add "tempest-fastapi-sdk[spreadsheet]"

Brings openpyxl. The engine is imported at first use, so importing the module — and defining your project's columns and theme — works without it.

Your first spreadsheet¶

# scripts/budget.py

from decimal import Decimal

from tempest_fastapi_sdk.spreadsheet import (
    BR_CURRENCY_FORMAT,
    Column,
    SheetWriter,
    new_workbook,
    workbook_to_bytes,
)


def main() -> None:
    """Write a two-item price table to disk."""
    workbook = new_workbook("Orçamento")
    writer = SheetWriter(
        workbook["Orçamento"],
        columns=[
            Column("Item", width=48, wrap=True),
            Column("Qty.", width=12, horizontal="center"),
            Column("Unit price", width=20, number_format=BR_CURRENCY_FORMAT),
        ],
    )

    writer.title_block(["PREFEITURA MUNICIPAL DE EXEMPLO", "Pregão 1/2026"])
    writer.header_row()
    writer.write_row(["Installation service", 2, Decimal("2930.00")])
    writer.write_row(["Monthly maintenance", 12, Decimal("450.50")])
    writer.total_row(["Total", None, Decimal("11266.00")])
    writer.apply_widths()

    with open("budget.xlsx", "wb") as handle:
        handle.write(workbook_to_bytes(workbook))


if __name__ == "__main__":
    main()

uv run python scripts/budget.py

Open the file: the title centred across all three columns, a navy header with white text, amounts right-aligned as R$ 2.930,00, and the total row picked out in amber.

What you did not write

No (row, column) pairs. No Font, PatternFill or Border. No mask repeated per cell. The cursor belongs to SheetWriter, the styling comes from the theme, the format comes from the column.

`new_workbook` and the ghost sheet¶

openpyxl always creates a workbook with one sheet called Sheet. Forgetting to remove it ships a document with a stray empty tab — the kind of detail that gives away that a file was generated by a script.

from tempest_fastapi_sdk.spreadsheet import new_workbook

workbook = new_workbook("Analysis", "Budget", "Feasibility")
print(workbook.sheetnames)  # ['Analysis', 'Budget', 'Feasibility']

With no arguments the default sheet is kept — handy when you name it later.

Columns: declare them once¶

Column specifies the column, not a cell. It applies to every body row, which is why the format cannot drift between the first row and the thousandth.

from tempest_fastapi_sdk.spreadsheet import (
    BR_CURRENCY_FORMAT,
    BR_PERCENT_FORMAT,
    Column,
    TEXT_FORMAT,
)

columns = [
    Column("Process", width=18, number_format=TEXT_FORMAT),
    Column("Description", width=52, wrap=True),
    Column("Discount", width=12, number_format=BR_PERCENT_FORMAT),
    Column("Amount", width=18, number_format=BR_CURRENCY_FORMAT),
]

Field	What it does
`title`	Header text, used by `header_row()`
`width`	Width in characters; `None` keeps the default (which truncates)
`number_format`	Mask applied to every body cell
`horizontal`	`"left"`, `"center"`, `"right"`; `None` lets Excel decide
`wrap`	Text wrapping — on for the description, off elsewhere

wrap=True on a short column makes rows tall for nothing

Row height follows the tallest cell. A two-word column with wrapping on stretches the whole row and buys nothing.

Numbers, not strings¶

The temptation is to format in Python and write the finished text. The cell then holds "R$ 2.930,00", which is text: the recipient cannot sum, sort or filter on it, and Excel's SUM returns zero for the whole column.

from decimal import Decimal

from tempest_fastapi_sdk.spreadsheet import (
    BR_CURRENCY_FORMAT,
    Column,
    SheetWriter,
    new_workbook,
)
from tempest_fastapi_sdk.utils import format_currency_br

workbook = new_workbook("Budget")
writer = SheetWriter(
    workbook["Budget"],
    columns=[
        Column("Item", width=48),
        Column("Amount", width=18, number_format=BR_CURRENCY_FORMAT),
    ],
)

# ❌ becomes text: no sum, no sorting, no filtering
writer.write_row(["Service", format_currency_br(Decimal("2930.00"))])

# ✅ write the number and let the mask present it
writer.write_row(["Service", Decimal("2930.00")])

On screen both rows read the same R$ 2.930,00; in the file only the second one is a number.

Use format_currency_br for prose

tempest_fastapi_sdk.utils.format_currency_br exists for text headed to a PDF, an e-mail or a page. A spreadsheet cell takes a number.

Brazilian formats¶

Constant	Renders	For
`BR_CURRENCY_FORMAT`	`R$ 1.234,56`	Money with the symbol
`BR_CURRENCY_FORMAT_NO_SYMBOL`	`1.234,56`	Column whose header already says `(R$)`
`BR_QUANTITY_FORMAT`	`1.234,56`	Non-monetary quantity
`BR_INTEGER_FORMAT`	`1.234`	Counts, whole numbers
`BR_PERCENT_FORMAT`	`30,00%`	Percentages
`BR_DATE_FORMAT`	`14/08/2026`	Dates
`BR_DATETIME_FORMAT`	`14/08/2026 19:30`	Date and time
`TEXT_FORMAT`	exactly what you wrote	An identifier that looks numeric

What makes these masks work is the pt-BR language code, [$-416], at the start of every one of them — currency, percent and dates included. It pins the dot as thousands separator, the comma as decimal and the slash as date separator inside the file, so the document does not depend on the machine of whoever opens it.

Measured by opening the same workbook in LibreOffice 7.4 headless under three locales:

Constant	en-US	de-DE	pt-BR
`BR_CURRENCY_FORMAT`	`R$ 1.234,56`	`R$ 1.234,56`	`R$ 1.234,56`
`BR_PERCENT_FORMAT`	`30,00%`	`30,00%`	`30,00%`
`BR_DATE_FORMAT`	`14/08/2026`	`14/08/2026`	`14/08/2026`
`0.00%` (no code)	`30.00%`	`30,00 %`	`30,00%`
`DD/MM/YYYY` (no code)	`14/08/2026`	`14.08.2026`	`14/08/2026`
`[$R$-416] #,##0.00`	`R$ 1,234.56`	`R$ 1.234,56`	`R$ 1.234,56`

The date slash is not a literal

In a date mask, / is the locale's date separator, not the slash character. Without [$-416], a German reader sees 14.08.2026.

Why the currency mask does not use [$R$-416]

[$R$-416] is the "currency symbol with language" form and looks like the natural way to write reais. It does not pin the separators: the last row of the table shows LibreOffice reading 1,234.56 under en-US. So BR_CURRENCY_FORMAT is [$-416]"R$ "#,##0.00 — the language code first and the symbol as a quoted literal. Excel was not measured.

A percent cell holds the ratio, not the percentage

Excel multiplies by 100 itself. A cell with BR_PERCENT_FORMAT must receive Decimal("0.30"), not 30 — writing 30 displays 3000,00%. It reads like a typo but it is a unit error.

TEXT_FORMAT saves leading zeros

A CPF, a process number (0001/2026), a bank branch. Without it Excel normalizes them to numbers and the zeros are gone for good.

The rows a document has¶

from decimal import Decimal

from tempest_fastapi_sdk.spreadsheet import (
    BR_CURRENCY_FORMAT,
    Column,
    SheetWriter,
    new_workbook,
)

workbook = new_workbook("Budget")
writer = SheetWriter(
    workbook["Budget"],
    columns=[
        Column("Item", width=48),
        Column("Qty.", width=10, horizontal="center"),
        Column("Amount", width=18, number_format=BR_CURRENCY_FORMAT),
    ],
)

writer.title_block(["AGENCY", "Pregão 1/2026", "Annex I"])  # merged, centred
first_item_row = writer.header_row()                        # table header
writer.group_row(["GROUP 1 — HANDICRAFT"])                  # in-table subheading
writer.write_row(["Item", 2, Decimal("10.00")])             # body
writer.total_row(["Total", None, Decimal("20.00")])         # emphasis
writer.blank_rows(2)                                        # breathing room

All of them return the next free row — that is how first_item_row got the position of the first item without anyone counting lines.

A None cell in the middle of a row is still styled: that is how a total row skips the middle columns without losing its fill.

Live formulas¶

A string starting with = becomes a real formula:

from tempest_fastapi_sdk.spreadsheet import Column, SheetWriter, new_workbook

workbook = new_workbook("Budget")
writer = SheetWriter(workbook["Budget"], [Column("Item"), Column("Amount")])
writer.write_row(["Checked sum", "=SUM(B5:B24)"])

Worth it for closing checks. An auditor who edits a value sees the number react, instead of reading a constant that was true only at generation time.

Theme¶

SheetStyle is plain data — hex colours, integer sizes, no openpyxl objects. That is what makes your project's theme definable, testable and comparable without the extra installed.

from tempest_fastapi_sdk.spreadsheet import SheetStyle, SheetWriter

CORPORATE = SheetStyle(
    header_background="0B3D2E",
    header_foreground="FFFFFF",
    group_background="D6E9DF",
    total_background="F3E5AB",
    border_color="C0C0C0",
    font_name="Calibri",
)

Pass it to the constructor: SheetWriter(sheet, columns, style=CORPORATE).

Colours follow the openpyxl convention

RRGGBB or AARRGGBB, without a leading #.

Serving it as a download¶

None of this touches disk: workbook_to_bytes returns the bytes and the handler ships them.

from fastapi import APIRouter
from fastapi.responses import Response

from tempest_fastapi_sdk.spreadsheet import (
    XLSX_MEDIA_TYPE,
    new_workbook,
    workbook_to_bytes,
)
from tempest_fastapi_sdk.utils import build_content_disposition

router = APIRouter()


@router.get("/budgets/{budget_id}/spreadsheet")
async def download_budget(budget_id: int) -> Response:
    """Stream the budget as an .xlsx download."""
    workbook = new_workbook("Orçamento")
    return Response(
        content=workbook_to_bytes(workbook),
        media_type=XLSX_MEDIA_TYPE,
        headers={
            "Content-Disposition": build_content_disposition(
                f"budget-{budget_id}.xlsx",
            ),
        },
    )

XLSX_MEDIA_TYPE comes ready from the SDK. Do not trust a guess from the extension: Python's built-in table has no .xlsx, and mimetypes only gets it right when the host has /etc/mime.types — python:3.13-slim does not (details in Downloads).

No temp file, no race

Two concurrent requests would write the same temporary path. In memory the problem does not exist — and there is nothing to clean up.

Reading a Google Sheet¶

So far the road went from code to spreadsheet. The opposite shows up early in every project: someone keeps the price list or the stock in a Google Sheet, and the service has to read it.

The hand-written version usually goes like this: cut the link at /edit, fish the gid out of the fragment, build /export?format=csv&gid=... and hand it to pandas. It works — until the day it does not, for reasons that do not show when reading the code:

#gid= is a fragment. The browser never sends it to the server; pass the whole link along and the chosen tab is lost on the way.
The export answers with a redirect. docs.google.com replies 307 to a *.googleusercontent.com host. An httpx.AsyncClient built without follow_redirects hands you that 307 as if it were the response.
Errors do not look like errors. An ID that does not exist answers 404 with an HTML page; a gid that is no tab at all answers 400, also as HTML. Skip the Content-Type check and you end up parsing HTML as data.

read_google_sheet_as handles all three and validates each row into a Pydantic model on top.

No extra

Reading uses only httpx (a base dependency) and the standard library's csv module. It needs neither [spreadsheet] nor openpyxl.

The code¶

The sample sheet is public and has three columns: item, valor (price) and tamanho (size).

# scripts/stock.py

import asyncio

from pydantic import BaseModel

from tempest_fastapi_sdk.spreadsheet import read_google_sheet_as

SHEET_URL: str = (
    "https://docs.google.com/spreadsheets/d/"
    "1h0ATstw2f6ryXvbwV-DW6zwsBRIF-2k5zHcm2uTEge8/edit?usp=sharing"
)


class Product(BaseModel):
    """One row of the stock sheet."""

    item: str
    valor: int | None = None
    tamanho: str


async def main() -> None:
    """Read the sheet and print each product."""
    products: list[Product] = await read_google_sheet_as(SHEET_URL, Product)
    for product in products:
        print(product.item, product.valor, product.tamanho)


if __name__ == "__main__":
    asyncio.run(main())

Running it (real output, 2026-10-03):

Bota forza 500 41
Bota new forza 1000 41
Macacao forza 1700 X
Protetor de coluna 300 uni
macacao dainese 1000 XL
Jaqueta x11 Masc 200 consultar
Jaqueta x11 Fem 200 consultar
Bota Forma 400 vendida
Luva x11 Fem L 250 M
Luva alpinestar Gp Pro L 250 M
Capacete Ls2 62 arrow*** None consultar

Piece by piece¶

The link. Pass the link Google shows under Share, as it comes. google_sheet_export_url accepts /edit?usp=sharing, ?gid= or #gid=, the link without /edit, the /u/<n>/ prefix of multi-account users, the link without https://, or just the spreadsheet ID. A link that is not a spreadsheet raises ValueError before any request is made.

from tempest_fastapi_sdk.spreadsheet import google_sheet_export_url

url: str = google_sheet_export_url(
    "https://docs.google.com/spreadsheets/d/abc123/edit?usp=sharing#gid=42"
)
assert url == (
    "https://docs.google.com/spreadsheets/d/abc123/export?format=csv&gid=42"
)

The tab. Each call reads one tab: the one the link's gid names. To read another tab, open it in the browser and copy the link — the gid changes. Without a gid the URL picks no tab and the choice is Google's (on the sample sheet, which has a single tab, the answer matched gid=0).

The schema. The header (row 1) becomes the field names: column item feeds field item. For a header that is not a valid identifier (Unit price), use Field(validation_alias="Unit price").

Note the two types that are not the obvious ones:

tamanho: str, not int. The column mixes numbers and text — 41, X, uni, consultar. People type spreadsheets, and the schema describes what the sheet holds, not what you wish it held.
valor: int | None = None. The last row leaves the price cell empty. By default (omit_blank=True) an empty cell means absent: the field falls back to its default, and a required field reports missing. Pass omit_blank=False to hand the empty string to the validator instead.

The row error. Typing tamanho as int fails — and the error says where:

import asyncio

from pydantic import BaseModel

from tempest_fastapi_sdk.spreadsheet import GoogleSheetRowError, read_google_sheet_as

SHEET_URL: str = (
    "https://docs.google.com/spreadsheets/d/"
    "1h0ATstw2f6ryXvbwV-DW6zwsBRIF-2k5zHcm2uTEge8/edit?usp=sharing"
)


class ProductWithNumericSize(BaseModel):
    """Types the size as a number — and the sheet disagrees."""

    item: str
    valor: int | None = None
    tamanho: int


async def main() -> None:
    """Show the row that failed."""
    try:
        await read_google_sheet_as(SHEET_URL, ProductWithNumericSize)
    except GoogleSheetRowError as exc:
        print(exc.details["row"], exc.details["errors"][0]["input"])


if __name__ == "__main__":
    asyncio.run(main())

4 X

details["row"] is the number the sheet shows on its side (header = 1, Macacao forza = 4), counting the blank rows too, which are skipped. details["errors"] is Pydantic's error list for that row.

Dictionaries, no schema. read_google_sheet returns list[dict[str, str]], each cell exactly as the CSV carries it (an empty cell is ""). A sheet with only a header, or none at all, returns [] — an empty collection is success.

When the sheet does not open¶

Any answer that is not a successful text/csv raises GoogleSheetAccessError (code GOOGLE_SHEET_UNAVAILABLE, status 502). Its details keep what actually came back:

{
  "detail": "The Google Sheet could not be downloaded. Check the link and share the sheet as 'Anyone with the link'.",
  "code": "GOOGLE_SHEET_UNAVAILABLE",
  "details": {
    "export_url": "https://docs.google.com/spreadsheets/d/<unknown-id>/export?format=csv",
    "status_code": 404,
    "content_type": "text/html; charset=utf-8"
  }
}

That is the body a route returns under register_exception_handlers for an ID that does not exist (measured). A gid that matches no tab arrives with status_code: 400. Both exceptions are AppExceptions, so inside a route they become the SDK's error envelope with no try at all; with a MessageCatalog registered, detail comes out translated.

Private sheet: not measured

The behaviour of a sheet that is not shared was not measured — only the 404 of an unknown ID and the 400 of an invalid gid. That is why the reader does not guess the cause: everything that is not CSV becomes the same error. If that is your case, share it as Anyone with the link (viewer).

Reuse the HTTP client

Pass client= to reuse a service-wide httpx.AsyncClient. The reader follows the redirect per request (follow_redirects=True on the call), so your client does not need to be configured for it, and it never closes it. Without client=, it creates one with timeout=30.0 (tunable through timeout=) and closes it when done. A network failure (timeout, DNS) surfaces as httpx.HTTPError.

A tab that is too large¶

A huge tab, or a link that points at something larger than you expected, must not become a list of millions of dicts in the service's memory. That is why the CSV path has two limits, on by default:

Parameter	Default	What it measures	When it checks
`max_bytes`	`10 MiB`	bytes of the export's body	during the download, streamed
`max_rows`	`100,000`	data rows of the tab (a blank row does not count)	during the parse, on the first row past the limit

# scripts/stock_limit.py

import asyncio

from tempest_fastapi_sdk.spreadsheet import SpreadsheetTooLargeError, read_google_sheet

SHEET_URL: str = (
    "https://docs.google.com/spreadsheets/d/"
    "1h0ATstw2f6ryXvbwV-DW6zwsBRIF-2k5zHcm2uTEge8/edit?usp=sharing"
)


async def main() -> None:
    """Read the same tab with a row limit below and above its size."""
    try:
        await read_google_sheet(SHEET_URL, max_rows=5)
    except SpreadsheetTooLargeError as error:
        print(error.status_code, error.code, error.details)
    rows: list[dict[str, str]] = await read_google_sheet(SHEET_URL, max_rows=20)
    print(len(rows), "rows read")


if __name__ == "__main__":
    asyncio.run(main())

Running it (real output, 2026-10-04):

413 SPREADSHEET_TOO_LARGE {'limit': 'rows', 'max': 5, 'actual': 6, 'sheet': None, 'row': 7}
11 rows read

Refuse, never truncate. Past a limit, SpreadsheetTooLargeError is raised (413, code SPREADSHEET_TOO_LARGE) — the same error as the .xlsx readers, described in Limits: zip bombs and huge sheets. No reader quietly returns "the first rows". On CSV, details["sheet"] is None: the export does not carry the tab's name.

The download. The body is streamed and counted. A Content-Length over max_bytes is refused without reading the body; without the header, the transfer is closed on the first chunk past the limit. details["limit"] is "download_bytes".

The rows. The CSV is decoded and parsed bit by bit, straight from the downloaded bytes: at row max_rows + 1 reading stops, and the rest of the body never becomes a str, a list or a dict. details["row"] is the sheet row where reading stopped, blank rows included. In read_google_sheet_as the limit is checked before any validation: a tab that is too large answers 413, not the 422 of the first invalid row.

Where the defaults come from. Measured on CPython 3.11, peak RSS above what the interpreter already holds, parse only (body included), three runs per file:

On the 8-column tab used to measure .xlsx (id, two texts, three numbers, a date, a status), each row costs ~94 bytes of CSV and ~1,014 bytes of memory: about 11 bytes of memory per byte of CSV. 100,000 rows are 9,212,287 bytes and peaked at 96.7 MB; 110,000 rows (10,166,854 bytes, still under 10 MiB) stopped at row 100,001. In that shape the row limit trips first — the same split as .xlsx.
The worst case is a tab of many two-character cells: ~31.5 bytes of memory per byte of CSV. 100,000 rows of 34 such cells (10,300,127 bytes) peaked at 309.4 MB. That is why the CSV default is smaller than the 32 MiB of .xlsx: CSV is not compressed, and every cell becomes a str object inside a dict.
The 16-tab public sheet used on this page has, in its largest tab, 18,577 bytes and 1,004 data rows of CSV: read tab by tab with the defaults (2,580 rows in all), none was refused.

Raise it or turn it off. Both limits are per call, and None turns one off (max_bytes=None, max_rows=None). The defaults are the public constants DEFAULT_GOOGLE_CSV_MAX_DOWNLOAD_BYTES and DEFAULT_XLSX_MAX_ROWS — the same one the .xlsx readers use. Zero or a negative value raises ValueError before the request.

CSV or .xlsx?

CSV brings one tab per request, picked by gid, and each cell as text formatted by the sheet's locale ("1.234,56", "04/10/2026"). It runs with no extra at all. .xlsx brings the whole workbook in one request, tabs by name, and each cell with the type the sheet stores (number, date, boolean). It needs [spreadsheet]. For one tab of plain text, CSV is enough; for several tabs, or for amount and date columns, see The whole Google workbook in one request.

Reading an `.xlsx` file¶

The most common way a spreadsheet reaches a service is not Google: it is a user uploading an .xlsx to an import endpoint. And the version written by hand with openpyxl trips over the same spots every time:

The blank row. The user leaves an empty row in the middle. Skipping it is easy; the hard part is still reporting the error with the number the sheet shows on its side, not the list index.
The file that is not a spreadsheet. A CSV renamed to .xlsx, an empty upload. openpyxl raises BadZipFile or KeyError, and the endpoint answers 500.
The tab that does not exist. Asking for workbook["Vendas"] in a workbook whose tab is called Planilha1 is another KeyError, another 500.

read_xlsx_as reads one tab, validates each row into a Pydantic model and turns those three cases into typed errors.

Extra required

Reading .xlsx uses the openpyxl of the [spreadsheet] extra. Without it the module still imports, and the call raises ImportError naming the extra to install.

The code¶

The example builds the spreadsheet with SheetWriter itself (so it runs without any file) and reads it back:

# scripts/sales.py

from datetime import date
from decimal import Decimal

from pydantic import BaseModel

from tempest_fastapi_sdk.spreadsheet import (
    BR_CURRENCY_FORMAT,
    BR_DATE_FORMAT,
    Column,
    SheetWriter,
    new_workbook,
    read_xlsx_as,
    workbook_to_bytes,
)


class Sale(BaseModel):
    """One row of the Vendas tab."""

    data: date
    produto: str
    quantidade: int
    total: Decimal
    pago: bool


def build_spreadsheet() -> bytes:
    """Build the spreadsheet a user would upload."""
    workbook = new_workbook("Vendas")
    writer = SheetWriter(
        workbook["Vendas"],
        columns=[
            Column("data", number_format=BR_DATE_FORMAT),
            Column("produto"),
            Column("quantidade"),
            Column("total", number_format=BR_CURRENCY_FORMAT),
            Column("pago"),
        ],
    )
    writer.header_row()
    writer.write_row([date(2026, 10, 1), "Café", 2, Decimal("10.50"), True])
    writer.blank_rows()
    writer.write_row([date(2026, 10, 2), "Bolo", 1, Decimal("7.00"), False])
    return workbook_to_bytes(workbook)


def main() -> None:
    """Read the Vendas tab and print each sale."""
    sales: list[Sale] = read_xlsx_as(build_spreadsheet(), Sale, sheet="Vendas")
    for sale in sales:
        print(sale.data, sale.produto, sale.quantidade, sale.total, sale.pago)


if __name__ == "__main__":
    main()

Running it (real output):

2026-10-01 Café 2 10.5 True
2026-10-02 Bolo 1 7 False

And the import endpoint uses the async variant, read_xlsx_as_async, fed the upload's bytes — same arguments, same return value, with await:

# app/main.py

from datetime import date
from decimal import Decimal

from fastapi import FastAPI, UploadFile
from pydantic import BaseModel

from tempest_fastapi_sdk import register_exception_handlers
from tempest_fastapi_sdk.spreadsheet import read_xlsx_as_async


class Sale(BaseModel):
    """One row of the Vendas tab."""

    data: date
    produto: str
    quantidade: int
    total: Decimal
    pago: bool


app: FastAPI = FastAPI()
register_exception_handlers(app)


@app.post("/sales/import")
async def import_sales(upload: UploadFile) -> list[Sale]:
    """Validate the uploaded spreadsheet and return the sales read."""
    content: bytes = await upload.read()
    return await read_xlsx_as_async(content, Sale, sheet="Vendas")

Inside async def, use the _async variant

openpyxl parses in pure Python, and a large tab takes seconds. read_xlsx_as called straight from an async def route holds the event loop the whole time — the worker stops serving every request, not just this one. Measured here on a 99,999-row tab (8 columns, 4 MB): the read took 5.4 s and a 10 ms ticker ran once instead of ~540 times; with read_xlsx_async, the same read let the ticker run 473 times.

read_xlsx_async, read_xlsx_as_async and read_xlsx_sheets_async read in a worker thread (asyncio.to_thread). The name without _async is still right in a script, a synchronous queue task, or a def route.

Piece by piece¶

The source. read_xlsx_as takes bytes (the upload's await upload.read(), an HTTP response body), a path (str or Path), or an open binary file — UploadFile.file works too.

The _async variant. Every .xlsx reader has one: read_xlsx_async, read_xlsx_as_async and read_xlsx_sheets_async. They take the same arguments, raise the same errors, and run the synchronous reader in a worker thread behind a semaphore: at most DEFAULT_MAX_CONCURRENT_XLSX_READS (4) reads at once per event loop. The semaphore is not there for speed — the parse holds the GIL, and measured here four concurrent reads took 25.0 s against 21.6 s for four in a row — it bounds memory (each read holds its whole tab) and keeps the thread pool the rest of the service shares from filling up. For a different bound, pass your own semaphore=asyncio.Semaphore(n), shared across the calls.

The tab. sheet= takes the tab's name or its position (from 0; negative works). Without sheet=, the first tab is read. The name is compared as is: a tab shown as Setembro may be called "Setembro ", with a trailing space — SheetNotFoundError lists the real names in details["available"].

The cell type. This is the difference from CSV: a cell arrives with the value the sheet stores, not the text it shows. That is why total: Decimal and data: date validate directly, with no R$ or dd/mm/yyyy to undo. What arrives, measured on the .xlsx export of a public Google Sheet with 16 tabs:

In the sheet	Arrives as
number (currency included)	`int` or `float` — `30.0` and `50` in the same column
percentage `40%`	`float` holding the ratio: `0.4`
date `02/04/2025`	`datetime(2025, 4, 2, 0, 0)`
time	`datetime.time`
boolean	`bool`
text	`str`, with whatever spaces it has
empty cell	`None`

Two consequences for the schema. Declare a number as float or Decimal, not int: the export writes 30.0 and 50 in the same column (an int field accepts 30.0 but refuses 30.5). And a column that mixes numbers and text, like a size 41 or X, becomes str | int: .xlsx hands over 41.0 (a float), which a str field refuses — the CSV schema is not portable without that tweak.

Formulas. What counts is the result stored in the file. The Google export stores the result of every formula: of the 1,429 formulas in the measured sheet, the 975 that arrived as None were all IF(...; ""; ...) with an empty result. A file written by openpyxl (SheetWriter included) stores no result, so its formulas arrive as None until someone opens and saves the file in Excel or LibreOffice.

Rows and header. The rules are CSV's, because the code is the same:

row 1 is the header, and a name counts as written (" NOME" with the space);
an empty header cell becomes the key "", and a repeated name keeps the last column's value;
a blank row is skipped, but still counts toward the numbering;
a row shorter than the header is padded with None; a cell past the header's last column is ignored;
a merged cell keeps its value in the top-left corner only — the other cells of the range arrive as None.

A space is not a blank row

Only None and "" count as empty; 0 and False are values. A formula answering " " (one space) keeps the row: in the measured sheet, 884 rows of one tab were just that. If your sheet has that pattern, filter out the rows missing the required field before using them — or let the schema's missing point at the first one.

The error. The three cases from the top become AppExceptions with status 422, and inside a route they become the SDK's error envelope. The real answer of the endpoint above, for each:

A CSV sent as .xlsx:

{
  "detail": "The file is not a valid .xlsx spreadsheet.",
  "code": "SPREADSHEET_INVALID",
  "details": {"reason": "File is not a zip file"}
}

A workbook without the Vendas tab:

{
  "detail": "The spreadsheet has no sheet 'Vendas'.",
  "code": "SPREADSHEET_SHEET_NOT_FOUND",
  "details": {"sheet": "Vendas", "available": ["Planilha1"]}
}

A date typed as text ("01/10/2026") on row 2:

{
  "detail": "Row 2 of sheet 'Vendas' failed validation.",
  "code": "SPREADSHEET_ROW_INVALID",
  "details": {
    "row": 2,
    "sheet": "Vendas",
    "errors": [
      {
        "type": "date_from_datetime_parsing",
        "loc": ["data"],
        "msg": "Input should be a valid date or datetime, invalid character in year",
        "input": "01/10/2026"
      }
    ]
  }
}

SpreadsheetRowError has the same contract as the CSV reader's GoogleSheetRowError (details["row"] with header = 1, Pydantic's details["errors"]) and also names the tab in details["sheet"]. GoogleSheetRowError is a subclass of it, so one except SpreadsheetRowError covers both readers.

No schema. read_xlsx returns one tab as list[dict[str, XlsxCellValue]], and read_xlsx_sheets returns all of them, in a dict keyed by tab name, in tab order (chart sheets are left out). An empty tab, or one with only a header, returns [].

Limits: zip bombs and huge sheets¶

An .xlsx is a ZIP. The size of the upload says nothing about the size openpyxl will decompress and parse — and that is exactly what an attacker exploits. Measured here: a 1.7 MB file holding a 505 MB sheet1.xml (5 million identical rows) took the unguarded reader to 2,278 MB of RSS and 151 s of CPU; with the process memory capped at 2 GB, it ended in MemoryError after 113 s. A legitimate sheet with millions of rows does the same, only slower.

That is why the .xlsx readers have three limits, all on by default:

Parameter	Default	What it measures	When it is checked
`max_uncompressed_bytes`	`100 MiB`	sum of the decompressed sizes of the ZIP's parts	before opening, from the central directory
`max_compression_ratio`	`100`	decompressed ÷ compressed, per part of at least 1 MiB	before opening, from the central directory
`max_rows`	`100,000`	data rows per tab (blank rows do not count)	while reading, on the first row past the limit

The same 1.7 MB file now answers in 0.1 s, with the process's peak RSS at 126 MB (the openpyxl import included), without decompressing anything.

The code¶

# scripts/limits.py

from tempest_fastapi_sdk.spreadsheet import (
    SpreadsheetTooLargeError,
    new_workbook,
    read_xlsx,
    workbook_to_bytes,
)


def build_workbook(rows: int) -> bytes:
    """Build a Vendas tab with the requested number of rows."""
    workbook = new_workbook("Vendas")
    sheet = workbook["Vendas"]
    sheet.append(["produto", "quantidade"])
    for number in range(rows):
        sheet.append([f"Produto {number}", number])
    return workbook_to_bytes(workbook)


def main() -> None:
    """Read the same workbook with a limit below and one above its size."""
    workbook: bytes = build_workbook(150)
    try:
        read_xlsx(workbook, max_rows=100)
    except SpreadsheetTooLargeError as error:
        print(error.status_code, error.code, error.details)
    rows = read_xlsx(workbook, max_rows=200)
    print(len(rows), "rows read")


if __name__ == "__main__":
    main()

Running it (real output):

413 SPREADSHEET_TOO_LARGE {'limit': 'rows', 'max': 100, 'actual': 101, 'sheet': 'Vendas', 'row': 102}
150 rows read

Piece by piece¶

Refuse, never truncate. Going past a limit raises SpreadsheetTooLargeError; no reader silently returns "the first 100,000 rows". Reading stops at row 100,001, and no row after it ever becomes a dict.

The error. SpreadsheetTooLargeError is a subclass of FileTooLargeException, so it answers 413 like the SDK's upload limit — and one except FileTooLargeException catches both. details["limit"] says which limit it was:

"uncompressed_bytes" and "compression_ratio": details["actual"] is what the ZIP declares; for the ratio, details["member"] names the part;
"rows": details["sheet"] is the tab (None on the CSV path) and details["row"] the sheet row where reading stopped;
"download_bytes": the Google download (see the next section).

The import endpoint from the previous section, without changing a line, answers a 208,786-byte zip bomb like this:

{
  "detail": "The spreadsheet decompresses to 209731887 bytes; the limit is 104857600.",
  "code": "SPREADSHEET_TOO_LARGE",
  "details": {
    "limit": "uncompressed_bytes",
    "max": 104857600,
    "actual": 209731887
  }
}

Where the defaults come from. Measured with openpyxl 3.1.5 on CPython 3.11, one run per size, on a generated 8-column tab (id, two texts, three numbers, a date, a status):

200,000 rows: 291.5 MB peak RSS, 15.6 s; 1,000,000 rows: 959.1 MB, 78.1 s. Hence ~834 bytes of RSS and ~78 µs per row, on top of about 125 MB the interpreter and openpyxl already hold. With the default, the 200,000-row tab stopped at row 100,001 after 10.8 s, peaking at 214 MB.
Each row is ~404 bytes of XML, so memory grows by ~2 bytes per byte of XML: at the 100 MiB ceiling, somewhere near 215 MB of rows (estimated from the arithmetic, not measured). On a narrow tab the row limit trips first (100,000 rows are ~40 MB of XML); the byte limit is what holds wide tabs, many tabs and large shared-string tables.
The compression ratio of legitimate files stayed between 7.2 and 14.7 (the tab of 200,000 identical rows, the most repetitive case); the zip bomb's was 294.5. 100 leaves more than six times of headroom.
The 16-tab public sheet used on this page has 2,580 rows and is 6.1 MB decompressed: it passes with room to spare.

Worst case within the defaults

A file built to stay just under both byte limits (99 MiB decompressed, ratio kept in check) and without the <dimension> tag still costs something: openpyxl scans the whole tab on open when the tag is missing (the Google export does not write it), and reading stops at row 100,001. Measured: 11.4 s and 248 MB peak. If your endpoint cannot pay that, lower the limits.

The central directory can lie. The sizes come from the ZIP's central directory, which whoever builds the file writes as they please. The lie does not get through because zipfile stops decompressing a part at the declared size and checks the CRC there (measured on CPython 3.11 to 3.14): a part that declares 4 KiB and holds 50 MB reads 4 KiB and fails — wrong CRC, or XML cut short — and both become InvalidSpreadsheetError (422). A part that declares more than it holds is refused by the sum.

Raising the limit for a trusted workbook. Pass the value that fits your case, per call:

# app/imports.py

from tempest_fastapi_sdk.spreadsheet import (
    DEFAULT_XLSX_MAX_ROWS,
    XlsxCellValue,
    read_xlsx,
)


def read_internal_report(content: bytes) -> list[dict[str, XlsxCellValue]]:
    """Read the report the system itself generates, larger than an upload."""
    return read_xlsx(
        content,
        max_rows=DEFAULT_XLSX_MAX_ROWS * 5,
        max_uncompressed_bytes=500 * 1024 * 1024,
    )

None disables a limit (max_rows=None). Do that only for files from a source you control: the three exist because the upload is the common case. The defaults are public constants — DEFAULT_XLSX_MAX_ROWS, DEFAULT_XLSX_MAX_UNCOMPRESSED_BYTES, DEFAULT_XLSX_MAX_COMPRESSION_RATIO — so you can write the limit as a multiple of them. A zero or negative value raises ValueError.

The request body limit still applies

The reader's limits measure what the ZIP turns into; the size of the upload itself is the request body limit, which sits earlier, in BodySizeLimitMiddleware. The two complement each other.

The whole Google workbook in one request¶

CSV reads one tab per request, and for that you need each tab's gid. A sales sheet with one tab per month means twelve links, twelve requests — and the amounts arrive as formatted text.

download_google_sheet_xlsx downloads the whole workbook once, as .xlsx; the readers from the previous section read as many tabs as you want from the same bytes.

The code¶

The same public stock sheet as the CSV example, now through .xlsx:

# scripts/stock_xlsx.py

import asyncio

from pydantic import BaseModel

from tempest_fastapi_sdk.spreadsheet import (
    download_google_sheet_xlsx,
    read_xlsx_as_async,
)

SHEET_URL: str = (
    "https://docs.google.com/spreadsheets/d/"
    "1h0ATstw2f6ryXvbwV-DW6zwsBRIF-2k5zHcm2uTEge8/edit?usp=sharing"
)


class Product(BaseModel):
    """One row of the stock sheet, read from the .xlsx."""

    item: str
    valor: int | None = None
    tamanho: str | int


async def main() -> None:
    """Download the workbook once and read the stock tab."""
    workbook: bytes = await download_google_sheet_xlsx(SHEET_URL)
    products: list[Product] = await read_xlsx_as_async(
        workbook, Product, sheet="Página1"
    )
    for product in products:
        print(product.item, product.valor, repr(product.tamanho))


if __name__ == "__main__":
    asyncio.run(main())

Running it (real output, 2026-10-04):

Bota forza 500 41
Bota new forza 1000 41
Macacao forza 1700 'X'
Protetor de coluna 300 'uni'
macacao dainese 1000 'XL'
Jaqueta x11 Masc 200 'consultar'
Jaqueta x11 Fem 200 'consultar'
Bota Forma 400 'vendida'
Luva x11 Fem L 250 'M'
Luva alpinestar Gp Pro L 250 'M'
Capacete Ls2 62 arrow*** None 'consultar'

To look at every tab without a schema, read_google_sheet_xlsx downloads and reads it all at once:

# scripts/tabs.py

import asyncio

from tempest_fastapi_sdk.spreadsheet import XlsxCellValue, read_google_sheet_xlsx

SHEET_URL: str = (
    "https://docs.google.com/spreadsheets/d/"
    "1d6VsFORSnFrn3GY2MADbeQ2uuifv8alenn9LECDao8A/edit"
)


async def main() -> None:
    """Read every tab in one request and count each one's rows."""
    tabs: dict[str, list[dict[str, XlsxCellValue]]] = await read_google_sheet_xlsx(
        SHEET_URL
    )
    for name, rows in tabs.items():
        print(f"{name!r}: {len(rows)} rows")
    print(tabs["Abril"][0])


if __name__ == "__main__":
    asyncio.run(main())

'Itens': 1 rows
'Eventos': 5 rows
'Trocas': 14 rows
'Produtos': 3 rows
'Mar': 66 rows
'Abril': 166 rows
'Maio': 107 rows
'Junho': 35 rows
'Julho': 0 rows
'Agosto': 99 rows
'Setembro ': 1004 rows
'Outubro': 999 rows
'Compras': 14 rows
'Novembro': 19 rows
'Dezembro': 3 rows
'Caixa': 45 rows
{'Data': datetime.datetime(2025, 4, 1, 0, 0), 'Produto': 'Café ', 'Valor unitário': 1.0, 'Quantidade': 1.0, 'Total': 1, 'Forma de pagamento': 'Pix', 'Vendedor': 'Betania', 'Observação': None, '': None, 'Valor total': 233, 'Total de vendas': 230}

Piece by piece¶

One request. The 16 tabs came in one 785,152-byte .xlsx; the read_google_sheet_xlsx above took 2.8 s on this machine, download included (a single measurement). Setembro and Outubro have ~1,000 rows because a formula in the Total column answers " " down to row 1,000 — see the warning about spaces in the previous section.

The gid is dropped. With a gid in the URL, the .xlsx export returns only that tab (measured: the same sheet with &gid=0 came back with one tab, 71,270 bytes). That is why download_google_sheet_xlsx ignores the link's gid and always asks for the whole workbook. To pick the tab, use sheet= when reading.

The same guarantees as CSV. The 307 redirect is followed per request (also on an injected client=, which is never closed); timeout= applies to the client it creates. Any answer that is not a successful .xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet) becomes GoogleSheetAccessError, status 502 — an unknown ID answered 404 text/html in this format too (measured). And if the body comes with the right media type but does not open as a workbook, read_google_sheet_xlsx also raises GoogleSheetAccessError, not 422: the defect is upstream's, not the caller's.

The download limit. The export's body is streamed and counted: past max_bytes (default DEFAULT_GOOGLE_SHEET_MAX_DOWNLOAD_BYTES, 32 MiB), the transfer is closed and SpreadsheetTooLargeError is raised with details["limit"] == "download_bytes" — a Content-Length over the limit is refused without reading the body. The arithmetic: the 16-tab public sheet compresses 7.7 times as a whole file (785,152 bytes downloaded, 6,072,058 decompressed — the 7.2 to 14.7 range above is per part, not per file); an .xlsx at the 100 MiB decompressed ceiling with that ratio downloads as about 13 MiB, and 32 MiB still admits it compressing only 3.2 times. The 16-tab sheet downloads as 785,152 bytes. read_google_sheet_xlsx also takes max_rows, max_uncompressed_bytes and max_compression_ratio, forwarded to the reader — and the size error comes out as SpreadsheetTooLargeError (413), not as GoogleSheetAccessError. The CSV path has the same max_bytes and max_rows, with a different download default — see A tab that is too large.

The extra. download_google_sheet_xlsx only downloads bytes and runs with no extra; read_google_sheet_xlsx needs [spreadsheet] and checks for it before the download, so the request is not wasted.

Recap¶

new_workbook("Sheet") creates the workbook without openpyxl's ghost tab.
Column declares title, width, mask and alignment once.
SheetWriter owns the cursor: title_block, header_row, group_row, write_row, total_row, blank_rows — all return the next free row.
Write numbers; the mask presents them. Finished text kills sum and filter.
The BR_* masks start with language code [$-416], so the file reads the same under en-US, de-DE and pt-BR (measured in LibreOffice 7.4).
SheetStyle is plain data, so a theme exists without the extra.
workbook_to_bytes hands you bytes — HTTP response, storage, e-mail.
read_google_sheet_as(link, Schema) reads one tab of a Google Sheet shared by link and validates each row; the error names the row number. No extra. The download stops at max_bytes (10 MiB) and the parse at max_rows (100,000), with the same SpreadsheetTooLargeError (413).
read_xlsx_as(bytes, Schema, sheet="Tab") reads one tab of an .xlsx (upload, file, export) with typed cells: number, datetime, bool. A file that is not a spreadsheet, a missing tab and an invalid row become typed 422 errors. Inside async def, use read_xlsx_as_async (and read_xlsx_async / read_xlsx_sheets_async): the read moves to a worker thread and the event loop keeps serving.
The .xlsx readers refuse zip bombs and huge sheets before spending the memory: max_uncompressed_bytes (100 MiB), max_compression_ratio (100) and max_rows (100,000 per tab) become SpreadsheetTooLargeError (413). They never truncate; for a trusted file, raise the limit or pass None.
download_google_sheet_xlsx(link) downloads the whole Google workbook in one request (the gid is dropped), stopping the download at max_bytes (32 MiB); read_google_sheet_xlsx returns every tab by name right away.

To ship the same content as a closed document, see PDF generation. For the currency helpers that format the document's prose, see Brazilian helpers.

Spreadsheets (.xlsx)¶

Your first spreadsheet¶

new_workbook and the ghost sheet¶

Columns: declare them once¶

Numbers, not strings¶

Brazilian formats¶

The rows a document has¶

Live formulas¶

Theme¶

Serving it as a download¶

Reading a Google Sheet¶

The code¶

Piece by piece¶

When the sheet does not open¶

A tab that is too large¶

Reading an .xlsx file¶

The code¶

Piece by piece¶

Limits: zip bombs and huge sheets¶

The code¶

Piece by piece¶

The whole Google workbook in one request¶

The code¶

Piece by piece¶

Recap¶

`new_workbook` and the ghost sheet¶

Reading an `.xlsx` file¶