Python Excel AutomationPractical guides for developers
Start learning
Guide
Getting Started With Python Excel AutomationDeep dive

Read and Write .ods Files with Python

Handle OpenDocument spreadsheets from Python with pandas and odfpy — reading sheets, writing output, the formatting limits, and converting .ods to .xlsx when you need more.

OpenDocument spreadsheets arrive from LibreOffice users, from Google Sheets exports, and from public-sector data portals where ODF is the mandated format. Python reads and writes them, but the support is shallower than for .xlsx in a way that matters: you get the values, and very little else. This guide covers the reading and writing calls, the formatting ceiling you will hit, and the conversion step that is usually the right answer for a reporting pipeline. It completes the format coverage in Handling Excel File Formats and Conversions.

What Python can write to .ods compared with .xlsx A five-row capability matrix. Cell values and multiple sheets are fully supported in both formats. Number formats are partial in ods and full in xlsx. Cell styling, charts and conditional formatting are unavailable through the ods writer but fully available in xlsx through openpyxl and xlsxwriter. capability .ods (odfpy) .xlsx cell values and sheet structure full full multiple sheets in one file full full number and date formats partial full fills, fonts, borders, column widths none full charts and conditional formatting none full

Prerequisites

Bash
pip install pandas odfpy               # read and write .ods
pip install python-calamine            # optional: faster reads, no writes

To produce a test file, save any spreadsheet from LibreOffice Calc as "ODF Spreadsheet (.ods)", or from Google Sheets via File → Download → OpenDocument.

Step 1 — Read an .ods into a DataFrame

pandas routes on the extension, so the plain call already works once odfpy is installed:

Python
import pandas as pd

df = pd.read_excel("budget.ods")
print(df.head())

Being explicit costs nothing and documents the dependency for whoever reads the script next:

Python
df = pd.read_excel("budget.ods", engine="odf")

Every option you know from .xlsx reads applies unchanged — sheet selection, header rows, column subsets:

Python
# One named sheet, skipping a two-row title block above the header.
df = pd.read_excel("budget.ods", sheet_name="Q3", skiprows=2, engine="odf")

# Every sheet at once, as {name: DataFrame}.
sheets = pd.read_excel("budget.ods", sheet_name=None, engine="odf")
for name, frame in sheets.items():
    print(f"{name:<16} {frame.shape}")

# Only the columns you need.
df = pd.read_excel("budget.ods", usecols=["region", "amount"], engine="odf")

The header and skip-row mechanics are identical to the .xlsx case covered in skipping rows and setting the header when reading Excel.

For a large .ods where you only want the numbers, the calamine engine is markedly faster because it is a Rust parser rather than a Python XML walk:

Python
df = pd.read_excel("budget.ods", engine="calamine")

Step 2 — Write an .ods

Writing is the same to_excel call with an .ods destination. pandas selects the odf engine from the suffix:

Python
import pandas as pd

df = pd.DataFrame({
    "region": ["North", "South", "West"],
    "revenue": [159.92, 247.50, 137.44],
})

df.to_excel("summary.ods", index=False)

Multiple sheets work through ExcelWriter, exactly as with .xlsx:

Python
import pandas as pd

summary = pd.DataFrame({"region": ["North", "South"], "revenue": [159.92, 247.50]})
detail = pd.DataFrame({"order": [1, 2, 3], "amount": [40.0, 61.5, 58.42]})

with pd.ExcelWriter("report.ods", engine="odf") as writer:
    summary.to_excel(writer, sheet_name="Summary", index=False)
    detail.to_excel(writer, sheet_name="Detail", index=False)

What you cannot do is style it. There is no .ods equivalent of the openpyxl styling in styling Excel cells with openpyxl, no conditional formatting, no charts, no frozen panes, no column widths. The odf writer emits data.

Step 3 — Handle the .ods quirks

Three behaviours differ from .xlsx in ways that bite.

Three OpenDocument quirks and how to handle them Three paired panels. Repeated empty cells in the XML expand into hundreds of unnamed trailing columns, fixed by dropping columns whose names start with Unnamed. Formulas return the cached result when one exists and the formula text otherwise, fixed by recalculating in LibreOffice. Dates sometimes arrive as strings, fixed by passing parse dates or calling to datetime afterwards. phantom columns ODF encodes runs of blanks as a repeat count they expand into Unnamed: 7, 8, 9 … drop Unnamed columns formula cells ODF stores the formula and a cached result no cache means you get the formula text back recalculate once, then read date columns typed dates parse cleanly text dates do not object dtype where you expected datetime64 pd.to_datetime after read

Phantom trailing columns. OpenDocument compresses runs of empty cells into a single element with a repeat count. Some writers emit generous counts, so a six-column sheet reads back with two hundred Unnamed: n columns full of NaN:

Python
import pandas as pd

df = pd.read_excel("export.ods", engine="odf")

# Drop the artefacts: unnamed AND entirely empty.
junk = [c for c in df.columns
        if str(c).startswith("Unnamed:") and df[c].isna().all()]
df = df.drop(columns=junk)
print(f"dropped {len(junk)} phantom columns; {df.shape[1]} remain")

The same trick removes all-blank rows; the fuller treatment is in removing blank rows from Excel with pandas.

Formula cells. ODF stores a formula alongside its last computed result. pandas reads the cached result when one exists. Files written by scripts or by portals that never opened the sheet may have no cache, and then the formula string comes through as text. Detect it rather than letting a "=SUM(B2:B9)" string reach a numeric aggregation:

Python
suspect = df["total"].astype(str).str.startswith("=")
if suspect.any():
    raise ValueError(
        f"{suspect.sum()} formula cells have no cached value — "
        "open the file in LibreOffice, recalculate, and re-save."
    )

Dates. A properly typed date cell parses to datetime64. A date somebody typed as text does not, and you get object dtype:

Python
df["invoiced"] = pd.to_datetime(df["invoiced"], errors="coerce")
bad = df["invoiced"].isna().sum()
if bad:
    print(f"warning: {bad} unparseable dates coerced to NaT")

The full set of date-handling techniques lives in working with dates and times in Excel data.

Step 4 — Convert .ods to .xlsx at the boundary

Normalising every input format at the ingest boundary Three input formats — ods from LibreOffice, legacy xls, and csv — all pass through a single normalise step that writes xlsx. Everything downstream of that boundary, including cleaning, formatting, charting and delivery, sees only xlsx. The alternative, where each downstream stage handles three formats, multiplies the special cases. whatever arrives .ods .xls .csv normalise read with the right engine write .xlsx everything downstream: .xlsx only clean · validate · aggregate style · chart · export to PDF schedule · email · archive one format, no per-stage special cases

For anything beyond a one-off read, convert. Your pipeline then has one format to reason about, and everything downstream gains styling, charts and the streaming writers:

Python
from pathlib import Path
import pandas as pd

def ods_to_xlsx(src, out_dir="converted"):
    """Convert an OpenDocument spreadsheet to .xlsx, all sheets."""
    src = Path(src)
    out = Path(out_dir)
    out.mkdir(parents=True, exist_ok=True)
    dest = out / (src.stem + ".xlsx")

    sheets = pd.read_excel(src, sheet_name=None, engine="odf")
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        for name, frame in sheets.items():
            frame.to_excel(writer, sheet_name=str(name)[:31], index=False)
    return dest

for path in Path("inbox").glob("*.ods"):
    print(path.name, "->", ods_to_xlsx(path).name)

That is a values-only conversion, with the same caveats as the .xls converter in convert .xls to .xlsx with Python. If the .ods is a formatted document rather than a data dump, use headless LibreOffice instead — it is the native application for the format and preserves everything:

Bash
soffice --headless --convert-to xlsx --outdir converted budget.ods

Common pitfalls and fixes

SymptomCauseFix
ImportError: Missing optional dependency 'odf'odfpy not installedpip install odfpy
Hundreds of Unnamed: columnsODF repeat-count encoding of blank cellsDrop unnamed, all-NaN columns after the read.
Formulas come back as textNo cached result in the fileOpen and recalculate in LibreOffice, then re-save.
to_excel ignores styling argumentsThe odf writer supports values onlyWrite .xlsx if the output must be formatted.
Dates are object dtypeText dates, not typed date cellspd.to_datetime(col, errors="coerce") after reading.
File opens as a zip, not a spreadsheetExtension renamed from .xlsxSniff the archive contents: content.xml means ODF, an xl/ prefix means OOXML.
Sheet name rejected on conversionExcel's 31-character and character limitsTruncate and sanitise before to_excel.

Performance and scale notes

odfpy parses the whole content.xml into a DOM before pandas sees a single row, so memory scales with file size and then some — expect several times the on-disk size in peak RSS. There is no streaming or read-only mode equivalent to openpyxl's read-only mode.

Two practical consequences. Read one sheet, not all of them, when you only need one:

Python
# Parses and materialises only "Detail".
df = pd.read_excel("large.ods", sheet_name="Detail", engine="odf")

And for files above a few tens of megabytes, use calamine or convert first:

Python
import time
import pandas as pd

for engine in ("odf", "calamine"):
    start = time.perf_counter()
    frame = pd.read_excel("large.ods", engine=engine)
    print(f"{engine:<10} {time.perf_counter() - start:6.2f}s  {frame.shape}")

On a typical multi-megabyte export, calamine finishes in a fraction of the odfpy time and holds far less memory, because it streams the archive instead of building a document tree. The trade is that it returns values only — no cell-level introspection — which for an ingest step is exactly what you want anyway.

Conclusion

.ods is a first-class input format in Python and a second-class output one. Reading is a plain pd.read_excel with odfpy installed, and every familiar option works; writing produces correct data with no formatting at all. Watch for phantom columns from the repeat-count encoding, formula cells with no cached result, and text dates. Then convert to .xlsx at ingest so that the styling, charting and streaming tools the rest of this site covers are available to you.

Frequently asked questions

Which package do I need to read .ods in pandas?odfpy. Install it with pip install odfpy, then pandas routes .ods files to engine="odf" automatically. python-calamine also reads .ods and is faster, but it cannot write.

Can I style .ods output the way I style .xlsx? Not through pandas. The odf writer emits values and basic number formats only — no fills, borders, conditional formatting or charts. If the output needs to look like a report, write .xlsx instead.

Why do my .ods formulas come back as text? OpenDocument stores both the formula and its cached result. pandas reads the cached value where one exists; where the file was written by a tool that saved no cache, you get the formula string. Recalculate the file in LibreOffice once to populate the cache.

Is .ods a zip file like .xlsx? Yes. Both are zip archives of XML. An .ods contains content.xml, styles.xml and a mimetype entry, whereas an .xlsx has an xl/ directory. That difference is how you tell them apart by content rather than extension.

Should I standardise on .ods or .xlsx?.xlsx, in almost every case. It has far richer Python tooling for formatting and charts, and LibreOffice reads it perfectly. Treat .ods as an input format you convert at ingest.