python解析excel函数
目录
python解析excel函数
目前主流解析excel文件的第三方库:
本文主要使用的第三方库:
具体代码:
Notes:
在现在的开发的工作中,随着数据的重要性日益凸显,经常需要与excel文件打交道,目前的大多数第三方库只提供读取excel文件的方法,但有时候需要将预先定义好的 “excel函数” 插入到excel文件中,此时大多数第三方库无法支持。
本文中,主要提供一种解决这类问题的思路,并简单举例
-
目前主流解析excel文件的第三方库:
- xlrd、xlwt、xlutils:只能处理xls格式,不能处理xlsx格式,不能读取公式,只能读取公式计算后的数值,这样保存后的excel文件丢失了公式。
- openpyxl:以处理xlsx格式,使用data_only=False参数打开xlsx格式,能读取公式;data_only=True读取公式的数值,注意:openpyxl 本身无法解析 excel公式并进行运算,但可以通过更改原本excel公式中的变量数值(单元格的数值)实现计算的功能。(excel软件保存文件时,会记录公式、数值和运算)
- xlsxwriter:理可以写公式,但是不能读取excel文件,只能用于创建新的excel文件。
-
本文主要使用的第三方库:
- formulas:实现的excel函数的解析功能,并且可以高度自定义自己的公式,本文中,主要通过重写excel基础函数的功能实现解析excel函数的功能。(链接地址:https://formulas.readthedocs.io/en/stable/doc.html)
-
具体代码:
# -*- coding: utf-8 -*- import math import formulas class ParseFormulas: """ 此示例主要通过自定义公式的方法讲述如何解析excel公式,具体在excel中使用可以参考formulas标准库的使用 """ def __init__(self, formulas_str): self._formulas_str = formulas_str self._base_formulas() def _base_formulas(self): """ 定义excel的基础函数逻辑 :return: """ base_func = formulas.get_functions() base_func['ROUNDUP'] = lambda x: math.ceil(x) base_func['MAX'] = lambda *x: max(list(x)) base_func['MIN'] = lambda *x: min(list(x)) base_func['IF'] = lambda x, t, f: self._base_formulas_if(x, t_value=t, f_value=f) base_func['AND'] = lambda x, y, *z: self._base_formulas_and((x, y), *z) base_func['OR'] = lambda x, y, *z: self._base_formulas_or((x, y), *z) @staticmethod def _base_formulas_and(judge_tuple, *args): """ Excel AND 函数的解析逻辑 :param judge_tuple: 判断条件(最少两个) :param args: 两个之外的判断条件 :return: bool """ judge_list = [a for a in judge_tuple if not a] judge_list.extend([b for b in args if not b]) if len(judge_list) == 0: return True else: return False @staticmethod def _base_formulas_or(judge_tuple, *args): """ Excel OR 函数的解析逻辑 :param judge_tuple: 判断条件(最少两个) :param args: 两个之外的判断条件 :return: bool """ judge_list = [a for a in judge_tuple if a] judge_list.extend([b for b in args if b]) if len(judge_list) > 0: return True else: return False @staticmethod def _base_formulas_if(judge_tuple, t_value, f_value): """ Excel IF 函数的解析逻辑 :param judge_tuple: 判断条件 :param t_value: 条件成立的返回值 :param f_value: 条件不成立的返回值 :return: """ return t_value if judge_tuple else f_value def new_formulas(self): """ 解析输入的公式 :return: 公式计算结果 """ new_func = formulas.Parser().ast(self._formulas_str)[1]pile() print('formulas_value: ', new_func()) return new_func() if __name__ == '__main__': formulas_str0 = '=AND(2>1, 3<4)' formulas_str1 = '=MAX(1,4,8,5,MIN(12, 15), ROUNDUP(12.346))' formulas_str2 = '=MIN(2,6,12,7)' formulas_str3 = '=OR(2>1, 5<7, 12>6,)' formulas_str4 = '=ROUNDUP(1.145)' formulas_str5 = '=MIN(2,6,12,7)' formulas_str6 = '=IF(OR(AND(1<0, 2>3), OR(4>7, 6>7)),MAX(ROUNDUP(12.32),2, 9),7)' ParseFormulas(formulas_str0).new_formulas() ParseFormulas(formulas_str1).new_formulas() ParseFormulas(formulas_str2).new_formulas() ParseFormulas(formulas_str3).new_formulas() ParseFormulas(formulas_str4).new_formulas() ParseFormulas(formulas_str5).new_formulas() ParseFormulas(formulas_str6).new_formulas() """ 运行结果: formulas_value: True formulas_value: 13 formulas_value: 2 formulas_value: True formulas_value: 2 formulas_value: 2 formulas_value: 7 """
-
Notes:
- openpyxl 开启 data_only=False 时,如果是通过 openpyxl 创建的且有输入excel公式(字符串),通过excel软件是可以看到 公式的计算数值的,此时不要以为openpyxl 可以解析excel公式,实际上是excel软件解析了 excel公式 并计算出结果。如果不通过excel软件再次保存,再次通过data_only=True 查看数值时,只会得到None,而不是计算后的数据,openpyxl本身无法解析excel函数。
- 示例:
# -*- coding: utf-8 -*- import openpyxl class ExcelOpener: def __init__(self, f_path): self.f_path = f_path self.creater() self.opener() def creater(self): """ 创建 excel 文件 :return: """ wb = openpyxl.Workbook() sheet = wb.active sheet["A1"] = 200 sheet["A2"] = 300 sheet["A3"] = 200 sheet["A4"] = 500 sheet["A5"] = "=SUM(A1:A4)" wb.save(self.f_path) def opener(self): """ 读取 excel 文件 :return: """ # 展示公式, data_only 默认为 False wbFormulas = openpyxl.load_workbook(self.f_path) sheet = wbFormulas.active print(sheet["A5"].value) # 结果: =SUM(A1:A4) # 展示数值 wbDataOnly = openpyxl.load_workbook(self.f_path, data_only=True) sheet = wbDataOnly.active x = (sheet["A5"].value) print(x) # 结果:None? 而不是 1200? if __name__ == '__main__': f_path = "writeFormula.xlsx" ExcelOpener(f_path)
更多推荐
python解析excel函数
发布评论