Backend · Python

Programmatic Excel Formulas with Python: From Arithmetic to Array Functions

By 用户835629078051 · Jul 10, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Teams that still produce Excel reports by hand or through fragile VBA macros can replace that with a Python script that inserts, formats, and evaluates formulas deterministically. The same pipeline works for nightly financial models, multi-sheet data marts, and any compliance-heavy output where a single manual edit creates audit risk.

Summary

A Python-to-Excel formula pipeline replaces manual cell entry with programmatic insertion of arithmetic, statistical, date-time, logical, and text functions. The Spire.XLS library assigns formula strings to cell objects, supports array formulas via `FormulaArray`, and handles cross-sheet references with standard Excel syntax. Named ranges and SUBTOTAL functions add readability and report-friendly aggregation that ignores hidden rows.

Formatting formula cells with background colors and borders makes generated sheets auditable at a glance. Calling `CalculateAllValue()` forces immediate evaluation, so downstream code can read computed results without opening the file in Excel. The approach suits batch financial models, automated data reports, and any workflow where spreadsheet logic must be generated consistently at scale.

Takeaways

— Assigning an Excel formula string to a cell's `Formula` property inserts it; the `Text` property shows the formula as a label for documentation.

— Absolute references use `$` notation (e.g., `$B$2`), and range references use a colon (e.g., `B2:D2`).

— Array formulas require the `FormulaArray` property and must span a multi-cell range; `CalculateAllValue()` forces evaluation.

— SUBTOTAL's first argument is a numeric code (1 for AVERAGE, 9 for SUM) and the function ignores hidden rows, making it useful for interactive reports.

— Named ranges created via `workbook.NameRanges.Add()` let formulas reference `=SUM(SumRange)` instead of raw cell addresses.

— Cross-sheet formulas use the syntax `=SheetName!Cell`, and the library supports adding and naming multiple worksheets programmatically.

— Formatting formula cells with `Style.Color` and borders visually separates logic from data in the generated workbook.

Conclusions

Spire.XLS treats formula insertion as a string assignment, which means any valid Excel formula can be generated through Python string manipulation — no domain-specific formula builder is needed.

The library's distinction between `Formula` and `FormulaArray` mirrors Excel's own engine constraints; getting this wrong silently produces incorrect results, so the API forces explicitness.

Named ranges and SUBTOTAL together point toward a pattern where Python generates not just data but a structured, auditable calculation model that non-programmers can inspect and filter in Excel.

Concepts & terms

FormulaArray

A property in Spire.XLS for inserting Excel array formulas that operate on multiple cells and return results across a range. It must be assigned to a multi-cell range, not a single cell.

SUBTOTAL function

An Excel function that performs aggregate calculations (SUM, AVERAGE, COUNT, etc.) on a range while ignoring rows hidden by filters. Its first argument is a numeric code selecting the aggregation type.

Named Range

A user-defined label assigned to a cell or range in Excel, allowing formulas to reference `=SUM(Revenue)` instead of `=SUM(A1:A10)`. Improves readability and centralizes range management.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗