Tutorials
Python
Spire.XLS for Python
Program Guide
Formula

Python: Add or Read Formulas in Excel

Formulas in Excel are equations or expressions that perform calculations on data within a spreadsheet. They allow you to perform basic arithmetic operations like addition, subtraction, multiplication, and division, as well as more advanced functions like statistical analysis, date and time calculations, and logical evaluations. By incorporating formulas into your Excel spreadsheets, you can save time, eliminate errors, and gain valuable insights from your data. In this article, we will demonstrate how to add or read formulas in Excel in Python using Spire.XLS for Python.

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Add Formulas to Excel in Python

Spire.XLS for Python offers the Worksheet.Range[rowIndex, columnIndex].Formula property to add formulas to specific cells in an Excel worksheet. The detailed steps are as follows.

  • Create an object of the Workbook class.
  • Get a specific worksheet by its index using the Workbook.Worksheets[sheetIndex] property.
  • Add some text and numeric data to specific cells of the worksheet using the Worksheet.Range[rowIndex, columnIndex].Text and Worksheet.Range[rowIndex, columnIndex].NumberValue properties.
  • Add text and formulas to specific cells of the worksheet using the Worksheet.Range[rowIndex, columnIndex].Text and Worksheet.Range[rowIndex, columnIndex].Formula properties.
  • Save the result file using the Workbook.SaveToFile() method.
  • Python
from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Declare two variables: currentRow, currentFormula
currentRow = 1
currentFormula = ""

# Add text to the worksheet and set cell style
sheet.Range[currentRow, 1].Text = "Test Data:"
sheet.Range[currentRow, 1].Style.Font.IsBold = True
sheet.Range[currentRow, 1].Style.FillPattern = ExcelPatternType.Solid
sheet.Range[currentRow, 1].Style.KnownColor = ExcelColors.LightGreen1
sheet.Range[currentRow, 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium
currentRow += 1

# Add some numeric data to the worksheet
sheet.Range[currentRow, 1].NumberValue = 7.3
sheet.Range[currentRow, 2].NumberValue = 5
sheet.Range[currentRow, 3].NumberValue = 8.2
sheet.Range[currentRow, 4].NumberValue = 4
sheet.Range[currentRow, 5].NumberValue = 3
sheet.Range[currentRow, 6].NumberValue = 11.3
currentRow += 2

# Add text to the worksheet and set cell style
sheet.Range[currentRow, 1].Text = "Formulas"
sheet.Range[currentRow, 2].Text = "Results"
sheet.Range[currentRow, 1, currentRow, 2].Style.Font.IsBold = True
sheet.Range[currentRow, 1, currentRow, 2].Style.KnownColor = ExcelColors.LightGreen1
sheet.Range[currentRow, 1, currentRow, 2].Style.FillPattern = ExcelPatternType.Solid
sheet.Range[currentRow, 1, currentRow, 2].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium
currentRow += 1

# Add text and formulas to the worksheet
# Str
currentFormula = "=\"Hello\""
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Int
currentFormula = "=300"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Float
currentFormula = "=3389.639421"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Bool
currentFormula = "=false"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Expressions
currentFormula = "=1+2+3+4+5-6-7+8-9"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

currentFormula = "=33*3/4-2+10"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Cell reference
currentFormula = "=Sheet1!$B$2"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Functions
# AVERAGE
currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# COUNT
currentFormula = "=COUNT(3,5,8,10,2,34)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# NOW
currentFormula = "=NOW()"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD"
currentRow += 1

# SECOND
currentFormula = "=SECOND(0.503)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MINUTE
currentFormula = "=MINUTE(0.78125)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MONTH
currentFormula = "=MONTH(9)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# DAY
currentFormula = "=DAY(10)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# TIME
currentFormula = "=TIME(4,5,7)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# DATE
currentFormula = "=DATE(6,4,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# RAND
currentFormula = "=RAND()"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# HOUR
currentFormula = "=HOUR(0.5)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MOD
currentFormula = "=MOD(5,3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# WEEKDAY
currentFormula = "=WEEKDAY(3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# YEAR
currentFormula = "=YEAR(23)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# NOT
currentFormula = "=NOT(true)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# OR
currentFormula = "=OR(true)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# AND
currentFormula = "=AND(TRUE)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# VALUE
currentFormula = "=VALUE(30)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# LEN
currentFormula = "=LEN(\"world\")"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MID
currentFormula = "=MID(\"world\",4,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# ROUND
currentFormula = "=ROUND(7,3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# SIGN
currentFormula = "=SIGN(4)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# INT
currentFormula = "=INT(200)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# ABS
currentFormula = "=ABS(-1.21)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# LN
currentFormula = "=LN(15)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# EXP
currentFormula = "=EXP(20)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# SQRT
currentFormula = "=SQRT(40)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# PI
currentFormula = "=PI()"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# COS
currentFormula = "=COS(9)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# SIN
currentFormula = "=SIN(45)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MAX
currentFormula = "=MAX(10,30)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# MIN
currentFormula = "=MIN(5,7)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# AVERAGE
currentFormula = "=AVERAGE(12,45)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# SUM
currentFormula = "=SUM(18,29)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# IF
currentFormula = "=IF(4,2,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# SUBTOTAL
currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# Set width of the 1st, 2nd and 3rd columns
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)

# Create a cell style
style = workbook.Styles.Add("Style")
# Set the horizontal alignment as left
style.HorizontalAlignment = HorizontalAlignType.Left
# Apply the style to the worksheet
sheet.ApplyStyle(style)

# Save the result file
workbook.SaveToFile("AddFormulas.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Python: Add or Read Formulas in Excel

Read Formulas in Excel in Python

To read formulas in an Excel worksheet, you need to loop through all the cells in the worksheet, after that, find the cells containing formulas using the Cell.HasFormula property, and then get the formulas of the cells using the CellRange.Formula property. The detailed steps are as follows.

  • Create an object of the Workbook class.
  • Load an Excel file using the Workbook.LoadFromFile() method.
  • Get a specific worksheet by its index using the Workbook.Worksheets[sheetIndex] property.
  • Get the used range of the worksheet using the Worksheet.AllocatedRange property.
  • Create an empty list.
  • Loop through all the cells in the used range.
  • Find the cells containing formulas using the Cell.HasFormula property.
  • Get the names and the formulas of the cells using the CellRange.RangeAddressLocal and CellRange.Formula properties.
  • Append the cell names and formulas to the list.
  • Write the items in the list into a text file.
  • Python
from spire.xls import *
from spire.xls.common import *

# Create an object of the Workbook class
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("AddFormulas.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the used range of the worksheet
usedRange = sheet.AllocatedRange

# Create an empty list
list = []

# Loop through the cells in the used range
for cell in usedRange:
    # Check if the cell has a formula
    if(cell.HasFormula):
        # Get the cell name
        cellName = cell.RangeAddressLocal
        # Get the formula
        formula = cell.Formula
        # Append the cell name and formula to the list
        list.append(cellName + " has a formula: " + formula)

# Write the items in the list into a text file
with open("Formulas.txt", "w", encoding = "utf-8") as text_file:
    for item in list:
        text_file.write(item + "\n")

workbook.Dispose()

Python: Add or Read Formulas in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

See Also

Products
Products
Purchase
Partnership
Support
Corporation
Copyright © 2024 E-ICEBLUE. All Rights Reserved.