快捷導(dǎo)航

利用Python實現(xiàn)添加或讀取Excel公式

更新時間：2025年03月19日 14:53:46 作者：Eiceblue

Excel公式是數(shù)據(jù)處理的核心工具,從簡單的加減運算到復(fù)雜的邏輯判斷,掌握基礎(chǔ)語法是高效工作的起點,下面我們就來看看如何使用Python進(jìn)行Excel公式的添加與讀取吧

Excel公式是數(shù)據(jù)處理的核心工具。從簡單的加減運算到復(fù)雜的邏輯判斷，掌握基礎(chǔ)語法是高效工作的起點。例如使用SUM函數(shù)快速求和，或通過IF函數(shù)實現(xiàn)條件判斷。通過公式計算數(shù)據(jù)能減少人工計算的錯誤率。本文將介紹如何通過Python在Excel中添加各種公式/函數(shù)、或者讀取Excel中的公式。

Python Excel 庫安裝

要在Python應(yīng)用程序中操作Excel文檔，需要用到Spire.XLS for Python庫?？梢灾苯邮褂靡韵聀ip命令安裝：

pip install Spire.XLS

Python 在 Excel 中添加公式/函數(shù)

Spire.XLS for Python 提供的 Worksheet.Range[].Formula 屬性可用于為 Excel 工作表中的特定單元格添加公式。支持添加多種常見公式如 SUM、AVERAGE、COUNT、IF等，此外還支持日期和時間公式、數(shù)學(xué)和三角函數(shù)等。

步驟如下：

創(chuàng)建一個Excel工作簿。
通過 Workbook.Worksheets[sheetIndex] 屬性獲取指定工作表。
通過 Worksheet.Range[rowIndex, columnIndex].Text 和 Worksheet.Range[rowIndex, columnIndex].NumberValue 屬性在指定單元格中添加文本和數(shù)字?jǐn)?shù)據(jù)。
通過 Worksheet.Range[rowIndex, columnIndex].Formula 屬性在指定單元格中添加公式。
保存結(jié)果文件。

Python代碼：

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

# 創(chuàng)建工作簿
workbook = Workbook()

# 獲取第一張工作表
sheet = workbook.Worksheets[0]

# 定義兩個變量
currentRow = 1
currentFormula = ""

# 在單元格中添加文本并設(shè)置單元格樣式
sheet.Range[currentRow, 1].Text = "測試數(shù)據(jù):"
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

# 在單元格中添加數(shù)據(jù)
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

# 在單元格中添加文本并設(shè)置單元格樣式
sheet.Range[currentRow, 1].Text = "公式"
sheet.Range[currentRow, 2].Text = "計算結(jié)果"
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

# 添加表達(dá)式
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

# 添加單元格引用
currentFormula = "=Sheet1!$B$2"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加AVERAGE函數(shù)，用于計算一組數(shù)值的平均值
currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加SUM函數(shù)，對一組數(shù)值進(jìn)行求和計算
currentFormula = "=SUM(18,29)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加IF函數(shù)，用于根據(jù)指定的條件進(jìn)行判斷，并返回不同的結(jié)果
currentFormula = "=IF(4,2,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加COUNT計數(shù)，用于統(tǒng)計包含數(shù)字的單元格個數(shù)
currentFormula = "=COUNT(3,5,8,10,2,34)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加SUBTOTAL函數(shù)，用于在數(shù)據(jù)列表或數(shù)據(jù)庫中進(jìn)行分類匯總
currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加NOW函數(shù)，用于返回當(dāng)前的日期
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函數(shù)，用于從時間值中提取秒數(shù)
currentFormula = "=SECOND(0.503)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MINUTE函數(shù)，用于從時間值里提取分鐘數(shù)
currentFormula = "=MINUTE(0.78125)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MONTH函數(shù)，用于從日期值中提取月份信息
currentFormula = "=MONTH(9)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加DAY函數(shù)，用于從日期值中提取出具體的日信息
currentFormula = "=DAY(10)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加TIME函數(shù)，通過分別指定小時、分鐘和秒來創(chuàng)建一個標(biāo)準(zhǔn)的時間格式
currentFormula = "=TIME(4,5,7)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加DATE函數(shù)，用于根據(jù)指定的年、月、日構(gòu)建一個日期。
currentFormula = "=DATE(6,4,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加RAND函數(shù)，用于生成一個大于等于 0 且小于 1 的隨機(jī)小數(shù)
currentFormula = "=RAND()"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加HOUR函數(shù)，用于從時間值中提取小時數(shù)
currentFormula = "=HOUR(0.5)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MOD函數(shù)，用于返回兩數(shù)相除的余數(shù)
currentFormula = "=MOD(5,3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加WEEKDAY函數(shù)，用于返回某個日期對應(yīng)的星期數(shù)
currentFormula = "=WEEKDAY(3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加YEAR函數(shù)，用于從給定的日期中提取出對應(yīng)的年份
currentFormula = "=YEAR(23)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加NOT函數(shù)，用于對給定的邏輯值取反
currentFormula = "=NOT(true)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加OR函數(shù)，用于對多個條件進(jìn)行邏輯或運算（當(dāng)給定的條件中至少有一個為 TRUE 時，函數(shù)就會返回 TRUE；只有當(dāng)所有條件都為 FALSE 時，函數(shù)才會返回 FALSE。）
currentFormula = "=OR(true)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加AND函數(shù)，用于對多個條件進(jìn)行邏輯與運算（只有當(dāng)所有指定的條件都為 TRUE 時，AND 函數(shù)才會返回 TRUE；只要有一個條件為 FALSE，函數(shù)就會返回 FALSE。）
currentFormula = "=AND(TRUE)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加VALUE函數(shù)，用于將文本形式的數(shù)字轉(zhuǎn)換為數(shù)值類型
currentFormula = "=VALUE(30)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加LEN函數(shù)，用于返回文本字符串中的字符個數(shù)
currentFormula = "=LEN(\"world\")"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MID函數(shù)，用于從一個文本字符串中指定的起始位置開始，提取指定長度的字符
currentFormula = "=MID(\"world\",4,2)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加ROUND函數(shù)，用于按指定的位數(shù)對數(shù)值進(jìn)行四舍五入
currentFormula = "=ROUND(7,3)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加SIGN函數(shù)，用于判斷一個數(shù)值的正負(fù)性，并返回其符號對應(yīng)的數(shù)值
currentFormula = "=SIGN(4)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加INT函數(shù)，用于將一個數(shù)值向下取整為最接近的整數(shù)，也就是去除數(shù)值的小數(shù)部分，返回不大于該數(shù)值的最大整數(shù)。
currentFormula = "=INT(200)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加ABS函數(shù)，用于返回一個數(shù)的絕對值，也就是將該數(shù)的負(fù)號去除，只保留其數(shù)值大小，不考慮其正負(fù)性。
currentFormula = "=ABS(-1.21)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加LN函數(shù)，用于計算一個數(shù)的自然對數(shù)。
currentFormula = "=LN(15)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加EXP函數(shù)，用于計算自然常數(shù) e（約等于 2.71828）的指定次冪
currentFormula = "=EXP(20)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加SQRT函數(shù)，用于計算一個數(shù)的算術(shù)平方根
currentFormula = "=SQRT(40)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加PI函數(shù)，返回數(shù)學(xué)常數(shù) π（圓周率）的近似值
currentFormula = "=PI()"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加COS函數(shù)，用于計算一個角度的余弦值
currentFormula = "=COS(9)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加SIN函數(shù)，用于計算給定角度的正弦值
currentFormula = "=SIN(45)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MAX函數(shù)，用于返回一組數(shù)值中的最大值
currentFormula = "=MAX(10,30)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 添加MIN函數(shù)，用于返回一組數(shù)值中的最小值
currentFormula = "=MIN(5,7)"
sheet.Range[currentRow, 1].Text = "'" + currentFormula
sheet.Range[currentRow, 2].Formula = currentFormula
currentRow += 1

# 設(shè)置列寬
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
sheet.SetColumnWidth(3, 16)

# 應(yīng)用樣式
style = workbook.Styles.Add("Style")
style.HorizontalAlignment = HorizontalAlignType.Left
sheet.ApplyStyle(style)

# 保存生成文檔
workbook.SaveToFile("Excel公式.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

生成結(jié)果：

Python 讀取 Excel 中的公式/函數(shù)

我們可以遍歷工作表中的所有單元格，然后通過 Cell.HasFormula 屬性找到包含公式的單元格，再使用 CellRange.Formula 屬性獲取單元格的公式。

步驟如下：

加載 Excel 文件。
通過 Workbook.Worksheets[sheetIndex] 屬性獲取指定工作表。
通過 Workheet.AllocatedRange 屬性獲取工作表的使用范圍。
創(chuàng)建一個空列表。
遍歷使用范圍內(nèi)的所有單元格。
通過 Cell.HasFormula 屬性查找包含公式的單元格。
使用 CellRange.RangeAddressLocal 和 CellRange.Formula 屬性獲取單元格的名稱和公式。
將讀取內(nèi)容添加到列表中，然后寫入txt文本文件。

Python代碼：

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

# 加載Excel文檔
workbook = Workbook()
workbook.LoadFromFile("Excel公式.xlsx")

# 獲取第一張工作表
sheet = workbook.Worksheets[0]

# 獲取工作表的使用范圍
usedRange = sheet.AllocatedRange

# 創(chuàng)建列表
list = []

# 遍歷工作表使用范圍內(nèi)的單元格
for cell in usedRange:
    # 檢查單元格是否有公式
    if(cell.HasFormula):
        # 獲取單元格名稱
        cellName = cell.RangeAddressLocal
        # 獲取公式
        formula = cell.Formula
        # 將單元格名稱和公式添加到列表中
        list.append(cellName + " 公式為: " + formula)

# 導(dǎo)入txt文本文件
with open("讀取公式.txt", "w", encoding = "utf-8") as text_file:
    for item in list:
        text_file.write(item + "\n")

workbook.Dispose()

讀取結(jié)果：