xlsx

Use openpyxl to write formulas as strings, such as sheet['B10'] = '=SUM(B2:B9)', rather than hardcoding computed values. After saving, recalculate the file with LibreOffice so formula cells contain cached values readable by pandas and previewers.

Use openpyxl when you need formulas, formatting, charts, or cell-level edits; use pandas read_excel and to_excel for bulk data movement in and out of sheets. For a quick read-only look at a sheet, the markitdown command renders each sheet without cell coordinates.

openpyxl writes formulas as strings with no cached values, so data_only=True reads return None until the file is recalculated. Running a LibreOffice-based recalculation pass computes every formula and rewrites the file in place with values.

Excel-2007-era functions like SUMIFS, INDEX, MATCH, IFERROR, and SUMPRODUCT work directly. Six newer functions need an _xlfn. prefix (TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, MINIFS), while XLOOKUP, XMATCH, SORT, FILTER, UNIQUE, and SEQUENCE must be avoided entirely.

Yes, but you must pass keep_vba=True to load_workbook, otherwise the macros are stripped on save. The same caution applies to workbooks with external file links, whose cached values openpyxl removes when re-saving.

Recalculation is refused when the workbook links to another file and linked cells have lost their cached values, because resolving them would produce #NAME? errors and delete the links. Copy the values from the original file first, or use --force to accept the loss.