xlsx

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

pandas read_excel is best for bulk data in and out, while openpyxl is required for formulas, formatting, and cell-level edits. Reading a model with openpyxl takes two loads: data_only=True for cached values and the default mode for formula strings.

openpyxl writes formulas as strings with no cached values, so data_only=True returns None until the file is recalculated. Run a LibreOffice-based recalculation script to compute every formula and rewrite the file in place.

Excel-2007-era functions like SUMIFS, INDEX, MATCH, IFERROR, and SUMPRODUCT work without a prefix. Six newer functions need an _xlfn. prefix, while XLOOKUP, XMATCH, SORT, FILTER, UNIQUE, and SEQUENCE should never be used.

Macros are lost unless you pass keep_vba=True to load_workbook. Without that flag, saving an .xlsm file strips its VBA project permanently.

openpyxl strips cached values from cells referencing other files, so LibreOffice cannot resolve them and writes #NAME? while deleting the links. Copy those cell values from the original before saving, or use --force to accept the loss.