Programmatic Excel Formulas with Python: From Arithmetic to Array Functions

Using Python to Automate Excel Formulas and Functions: A Complete Guide

Using Python to Automate Formulas and Functions in Excel

In modern data processing work, Excel formulas and functions are core tools for performing calculations, data analysis, and business logic. However, manually entering and managing a large number of formulas is not only time-consuming but also error-prone. By automating the insertion and management of Excel formulas with Python, developers can batch-process spreadsheets, dynamically generate calculation logic, and ensure the accuracy and consistency of data processing.

This article will introduce how to use Python to insert various types of formulas and functions into Excel worksheets, including basic arithmetic operations, built-in functions, array formulas, and the use of named ranges. These techniques are applicable to scenarios such as financial statement automation, data analysis pipeline construction, and batch data processing.

Environment Preparation

First, you need to install the Spire.XLS for Python library:

pip install Spire.XLS

This library provides a complete API for Excel file operations, supporting formula insertion, calculation execution, and file format conversion.

Inserting Basic Formulas

Excel formulas start with an equals sign (=) and can contain constants, cell references, operators, and function calls. When using Python to insert formulas, simply assign the formula string to the cell's Formula property.

The following example demonstrates how to insert various basic formulas into a worksheet:

from spire.xls import *
from spire.xls.common import *

# Create a workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set test data
sheet.Range["B2"].NumberValue = 7.3
sheet.Range["C2"].NumberValue = 5
sheet.Range["D2"].NumberValue = 8.2

# Insert a string formula
sheet.Range["A4"].Text = '="hello"'
sheet.Range["B4"].Formula = '="hello"'

# Insert an arithmetic formula
sheet.Range["A5"].Text = "=1+2+3+4+5-6-7+8-9"
sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"

# Insert a multiplication operation
sheet.Range["A6"].Text = "=33*3/4-2+10"
sheet.Range["B6"].Formula = "=33*3/4-2+10"

# Reference another cell
sheet.Range["A7"].Text = "=Sheet1!$B$2"
sheet.Range["B7"].Formula = "=Sheet1!$B$2"

# Reference a cell range and calculate the average
sheet.Range["A8"].Text = "=AVERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"

# Save the file
workbook.SaveToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

In this example, we demonstrated:

String constants: Use double quotes to wrap text
Arithmetic operations: Support addition, subtraction, multiplication, division, and parenthesis precedence
Cell references: Use the $ symbol to create absolute references
Range references: Use a colon to specify a continuous range of cells

Using Built-in Functions

Excel provides hundreds of built-in functions covering areas such as mathematics, statistics, date and time, logical judgment, and text processing. These functions can be flexibly inserted using Python.

Math and Statistical Functions

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

currentRow = 1

# COUNT function - counts the number of arguments
sheet.Range[f"A{currentRow}"].Text = "=Count(3,5,8,10,2,34)"
sheet.Range[f"B{currentRow}"].Formula = "=Count(3,5,8,10,2,34)"
currentRow += 1

# SUM function - sum
sheet.Range[f"A{currentRow}"].Text = "=SUM(18,29)"
sheet.Range[f"B{currentRow}"].Formula = "=SUM(18,29)"
currentRow += 1

# AVERAGE function - average
sheet.Range[f"A{currentRow}"].Text = "=AVERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=AVERAGE(12,45)"
currentRow += 1

# MAX and MIN functions
sheet.Range[f"A{currentRow}"].Text = "=MAX(10,30)"
sheet.Range[f"B{currentRow}"].Formula = "=MAX(10,30)"
currentRow += 1

sheet.Range[f"A{currentRow}"].Text = "=MIN(5,7)"
sheet.Range[f"B{currentRow}"].Formula = "=MIN(5,7)"
currentRow += 1

# ROUND function - rounding
sheet.Range[f"A{currentRow}"].Text = "=ROUND(7,3)"
sheet.Range[f"B{currentRow}"].Formula = "=ROUND(7,3)"
currentRow += 1

# SQRT function - square root
sheet.Range[f"A{currentRow}"].Text = "=SQRT(40)"
sheet.Range[f"B{currentRow}"].Formula = "=SQRT(40)"
currentRow += 1

workbook.SaveToFile("MathFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Date and Time Functions

Processing date and time data is a common requirement in Excel automation. The following code demonstrates how to use date and time functions:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# NOW function - current date and time
sheet.Range["A1"].Text = "=NOW()"
sheet.Range["B1"].Formula = "=NOW()"
sheet.Range["B1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"

# DATE function - construct a date
sheet.Range["A2"].Text = "=DATE(2024,1,15)"
sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"

# TIME function - construct a time
sheet.Range["A3"].Text = "=TIME(14,30,0)"
sheet.Range["B3"].Formula = "=TIME(14,30,0)"
sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"

# YEAR, MONTH, DAY functions - extract date parts
sheet.Range["A4"].Text = "=YEAR(NOW())"
sheet.Range["B4"].Formula = "=YEAR(NOW())"

sheet.Range["A5"].Text = "=MONTH(NOW())"
sheet.Range["B5"].Formula = "=MONTH(NOW())"

sheet.Range["A6"].Text = "=DAY(NOW())"
sheet.Range["B6"].Formula = "=DAY(NOW())"

# HOUR, MINUTE, SECOND functions - extract time parts
sheet.Range["A7"].Text = "=HOUR(NOW())"
sheet.Range["B7"].Formula = "=HOUR(NOW())"

sheet.Range["A8"].Text = "=MINUTE(NOW())"
sheet.Range["B8"].Formula = "=MINUTE(NOW())"

sheet.Range["A9"].Text = "=SECOND(NOW())"
sheet.Range["B9"].Formula = "=SECOND(NOW())"

workbook.SaveToFile("DateTimeFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Logical and Conditional Functions

Logical functions are used to implement conditional judgments and business rules:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# IF function - conditional judgment
sheet.Range["A1"].Text = "=IF(10>5, \"大于\", \"小于\")"
sheet.Range["B1"].Formula = "=IF(10>5, \"大于\", \"小于\")"

# AND function - logical AND
sheet.Range["A2"].Text = "=AND(TRUE, TRUE)"
sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"

# OR function - logical OR
sheet.Range["A3"].Text = "=OR(FALSE, TRUE)"
sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"

# NOT function - logical NOT
sheet.Range["A4"].Text = "=NOT(FALSE)"
sheet.Range["B4"].Formula = "=NOT(FALSE)"

workbook.SaveToFile("LogicFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Text Processing Functions

Text functions are used for string operations and data cleaning:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# LEN function - string length
sheet.Range["A1"].Text = '=LEN("Hello World")'
sheet.Range["B1"].Formula = '=LEN("Hello World")'

# MID function - extract substring
sheet.Range["A2"].Text = '=MID("Hello World",7,5)'
sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'

# VALUE function - text to number
sheet.Range["A3"].Text = '=VALUE("123")'
sheet.Range["B3"].Formula = '=VALUE("123")'

workbook.SaveToFile("TextFunctions.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Using Array Formulas

Array formulas can perform calculations on multiple values and return a single result or multiple results. This is very useful when performing matrix operations and batch data processing.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Prepare data
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["A3"].NumberValue = 3

sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6

sheet.Range["C1"].NumberValue = 7
sheet.Range["C2"].NumberValue = 8
sheet.Range["C3"].NumberValue = 9

# Insert an array formula - LINEST function performs linear regression analysis
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# Calculate all formula values
workbook.CalculateAllValue()

workbook.SaveToFile("ArrayFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Key points:

Use the FormulaArray property instead of Formula to insert array formulas
Array formulas need to span multiple cell ranges
Call the CalculateAllValue() method to ensure formulas are calculated correctly

Using the SUBTOTAL Function

The SUBTOTAL function can perform aggregate calculations on a data list and can ignore hidden rows, which is very useful when creating interactive reports.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Prepare data
for row in range(1, 4):
    sheet.Range[f"A{row}"].NumberValue = row
    sheet.Range[f"B{row}"].NumberValue = row + 3
    sheet.Range[f"C{row}"].NumberValue = row + 6

# Insert different SUBTOTAL functions
# 1 represents AVERAGE
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"

# 2 represents COUNT
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"

# 5 represents MIN
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

# Calculate formulas
workbook.CalculateAllValue()

workbook.SaveToFile("SubtotalFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The first argument of the SUBTOTAL function specifies the calculation type:

1: AVERAGE
2: COUNT
3: COUNTA
4: MAX
5: MIN
9: SUM

Using Named Ranges and Formulas

Named ranges can improve the readability and maintainability of formulas. By replacing complex cell references with meaningful names, formulas become easier to understand.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set data
sheet.Range["A1"].Value = "10"
sheet.Range["A2"].Value = "20"

# Create a named range
namedRange = workbook.NameRanges.Add("SumRange")
namedRange.RefersToRange = sheet.Range["A1:A2"]

# Use the named range in a formula
sheet.Range["C1"].Formula = "=SUM(SumRange)"

# You can also directly define a named formula
namedFormula = workbook.NameRanges.Add("TotalCalc")
namedFormula.NameLocal = "=SUM(A1+A2)"
sheet.Range["C2"].Formula = "TotalCalc"

workbook.SaveToFile("NamedRangeFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The advantages of this method are:

Improved readability: =SUM(SumRange) is clearer than =SUM(A1:A2)
Easier maintenance: Modifying the reference of a named range will automatically update all formulas using that name
Reduced errors: Avoid manually entering complex cell references

Practical Tips

Formatting Formula Cells

To distinguish formula cells from data cells, you can apply special formatting:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Insert a formula
sheet.Range["A1"].Formula = "=SUM(B1:B10)"

# Set the background color of the formula cell
sheet.Range["A1"].Style.Color = Color.get_LightYellow()

# Add borders
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

workbook.SaveToFile("FormattedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Forcing Calculation of Formula Values

In some cases, you may need to get the calculated result of a formula immediately instead of keeping the formula itself:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

sheet.Range["A1"].NumberValue = 10
sheet.Range["A2"].NumberValue = 20
sheet.Range["A3"].Formula = "=A1+A2"

# Calculate all formulas
workbook.CalculateAllValue()

# At this point, the value of A3 is already 30
result = sheet.Range["A3"].Value
print(f"Calculation result: {result}")

workbook.SaveToFile("CalculatedFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Handling Cross-Worksheet References

When a formula needs to reference data from another worksheet, use the syntax of the worksheet name followed by an exclamation mark:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()

# Create two worksheets
sheet1 = workbook.Worksheets[0]
sheet1.Name = "Data"
sheet2 = workbook.Worksheets.Add("Calculation")

# Set data in the first worksheet
sheet1.Range["A1"].NumberValue = 100
sheet1.Range["A2"].NumberValue = 200

# Reference the data from the first worksheet in the second worksheet
sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"

workbook.CalculateAllValue()
workbook.SaveToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Summary

This article introduced various techniques for automating formulas and functions in Excel using Python, including:

Inserting basic arithmetic formulas and cell references
Using mathematical, statistical, date and time, logical, and text processing functions
Executing array formulas for complex calculations
Utilizing the SUBTOTAL function to create flexible aggregate calculations
Improving formula readability and maintainability through named ranges

These techniques enable developers to:

Batch-generate spreadsheets containing complex calculation logic
Automate financial models and data analysis reports
Dynamically build calculation formulas based on conditions
Improve the consistency and accuracy of data processing

By combining Python's programming capabilities with Excel's calculation functions, you can build powerful data processing automation solutions, significantly improving work efficiency and reducing human error.