Excel-formulas-calculator (EFC) is a high-level Py23 library that allows you to calculate Excel formulas on any OS.
from openpyxl import load_workbook from efc.interfaces.iopenpyxl import OpenpyxlInterface wb = load_workbook('test.xlsx') interface = OpenpyxlInterface(wb=wb, use_cache=True) # e.g. A1 stores formula '=1 + 2', then result = 3 result = interface.calc_cell('A1', 'Worksheet1') # EFC does not change the source document print(wb['Worksheet1']['A1'].value) # prints '=1 + 2' # If you need to replace a formula in a workbook with a value, # you need to do this wb['Worksheet1']['A1'].value = interface.calc_cell('A1', 'Worksheet1') print(wb['Worksheet1']['A1'].value) # prints '3' # The EFC does not track changes to values in the workbook. # If the use_cache=True option is used, the calculated formulas # are not recalculated again when they are accessed. # e.g. A2 = 2, A3 = 1, A4 = A2 + A3 print(interface.calc_cell('A4', 'Worksheet1')) # prints '3' wb['Worksheet1']['A2'].value = 1234 print(interface.calc_cell('A4', 'Worksheet1')) # prints '3' # If you have made changes to the workbook, then you need to reset # the cache to get up-to-date results interface.clear_cache() print(interface.calc_cell('A4', 'Worksheet1')) # prints '1235' # You can disable caching of results, # but then when you run a large number of related formulas, # the calculation speed will decrease significantly
- Inherit from efc.interface.BaseExcelInterface in your excel file class and implement abstract methods. This class will be used to get data from excel file using any library you want.
- Use calc_cell to calculate cell’s formula.
-, +, *, /, ^, ()
<>, >, >=, <, <=, =
- String concatenation:
ABS, AND, AVERAGE, AVERAGEIFS, CONCATENATE, COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK, FLOOR, IF, IFS, IFERROR, INDEX, ISBLANK, ISERROR, LARGE, LEN, LEFT, LOWER, MATCH, MAX, MID, MIN, MOD, NOT, OFFSET, OR, RIGHT, ROUND, ROUNDDOWN, SEARCH, SMALL, SUBSTITUTE, SUM, SUMIF, SUMIFS, TRIM, VLOOKUP, YEARFRAC, UPPER
- All variations of the spelling of the cell and range addresses (linked docs will be skipped)
- Formula cell offset – this can be useful when calculating shared formulas