利用Python实现添加或读取Excel公式_Python

excel公式是数据处理的核心工具。从简单的加减运算到复杂的逻辑判断，掌握基础语法是高效工作的起点。例如使用sum函数快速求和，或通过if函数实现条件判断。通过公式计算数据能减少人工计算的错误率。本文将介绍如何通过python在excel中添加各种公式/函数、或者读取excel中的公式。

python excel 库安装

要在python应用程序中操作excel文档，需要用到spire.xls for python库。可以直接使用以下pip命令安装：

pip install spire.xls

python 在 excel 中添加公式/函数

spire.xls for python 提供的 worksheet.range[].formula 属性可用于为 excel 工作表中的特定单元格添加公式。支持添加多种常见公式如 sum、average、count、if等，此外还支持日期和时间公式、数学和三角函数等。

步骤如下：

创建一个excel工作簿。
通过 workbook.worksheets[sheetindex] 属性获取指定工作表。
通过 worksheet.range[rowindex, columnindex].text 和 worksheet.range[rowindex, columnindex].numbervalue 属性在指定单元格中添加文本和数字数据。
通过 worksheet.range[rowindex, columnindex].formula 属性在指定单元格中添加公式。
保存结果文件。

python代码：

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

# 创建工作簿
workbook = workbook()

# 获取第一张工作表
sheet = workbook.worksheets[0]

# 定义两个变量
currentrow = 1
currentformula = ""

# 在单元格中添加文本并设置单元格样式
sheet.range[currentrow, 1].text = "测试数据:"
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

# 在单元格中添加数据
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

# 在单元格中添加文本并设置单元格样式
sheet.range[currentrow, 1].text = "公式"
sheet.range[currentrow, 2].text = "计算结果"
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

# 添加表达式
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函数，用于计算一组数值的平均值
currentformula = "=average(sheet1!$d$2:f$2)"
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

# 添加count计数，用于统计包含数字的单元格个数
currentformula = "=count(3,5,8,10,2,34)"
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

# 添加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函数，用于生成一个大于等于 0 且小于 1 的随机小数
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函数，用于对多个条件进行逻辑或运算（当给定的条件中至少有一个为 true 时，函数就会返回 true；只有当所有条件都为 false 时，函数才会返回 false。）
currentformula = "=or(true)"
sheet.range[currentrow, 1].text = "'" + currentformula
sheet.range[currentrow, 2].formula = currentformula
currentrow += 1

# 添加and函数，用于对多个条件进行逻辑与运算（只有当所有指定的条件都为 true 时，and 函数才会返回 true；只要有一个条件为 false，函数就会返回 false。）
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函数，用于计算自然常数 e（约等于 2.71828）的指定次幂
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

# 设置列宽
sheet.setcolumnwidth(1, 32)
sheet.setcolumnwidth(2, 16)
sheet.setcolumnwidth(3, 16)

# 应用样式
style = workbook.styles.add("style")
style.horizontalalignment = horizontalaligntype.left
sheet.applystyle(style)

# 保存生成文档
workbook.savetofile("excel公式.xlsx", excelversion.version2016)
workbook.dispose()

生成结果：

python 读取 excel 中的公式/函数

我们可以遍历工作表中的所有单元格，然后通过 cell.hasformula 属性找到包含公式的单元格，再使用 cellrange.formula 属性获取单元格的公式。

步骤如下：

加载 excel 文件。
通过 workbook.worksheets[sheetindex] 属性获取指定工作表。
通过 workheet.allocatedrange 属性获取工作表的使用范围。
创建一个空列表。
遍历使用范围内的所有单元格。
通过 cell.hasformula 属性查找包含公式的单元格。
使用 cellrange.rangeaddresslocal 和 cellrange.formula 属性获取单元格的名称和公式。
将读取内容添加到列表中，然后写入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

# 创建列表
list = []

# 遍历工作表使用范围内的单元格
for cell in usedrange:
    # 检查单元格是否有公式
    if(cell.hasformula):
        # 获取单元格名称
        cellname = cell.rangeaddresslocal
        # 获取公式
        formula = cell.formula
        # 将单元格名称和公式添加到列表中
        list.append(cellname + " 公式为: " + formula)

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

workbook.dispose()

读取结果：