Library to calculate excel formulas
Project description
Excel-formulas-calculator
Excel-formulas-calculator (EFC) is a high-level Py23 library that allows you to calculate Excel formulas on any OS.
Openpyxl interface
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
Custom interface
- 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.
Functionality
- Arithmetic:
-, +, *, /, ^, ()
- Comparison:
<>, >, >=, <, <=, =
- String concatenation:
&
- Functions:
ABS, AND, AVERAGE, AVERAGEIFS, COLUMN, CONCATENATE, COUNT, COUNTA, COUNTIF, COUNTIFS, COUNTBLANK, FLOOR, IF, IFS, IFERROR, INDEX, ISBLANK, ISERROR, HLOOKUP, LARGE, LEN, LEFT, LOWER, MATCH, MAX, MID, MIN, MOD, NOT, OFFSET, OR, RIGHT, ROUND, ROUNDDOWN, ROW, SEARCH, SMALL, SUBSTITUTE, SUM, SUMIF, SUMIFS, TRIM, VLOOKUP, YEARFRAC, UNIQUE, 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
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Close
Hashes for excel_formulas_calculator-0.5.0.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 210a94dfa216b7a5f490e8a92ff48d82cdade88a1f6a8ca5f6735048d1797c93 |
|
MD5 | 6215633a0e1f73d155c3cf3f9e747004 |
|
BLAKE2b-256 | 4de0acad3fc2c3abb94fb217c57786e0ab4e58425346ad7ff4395491c79c4f3a |
Close
Hashes for excel_formulas_calculator-0.5.0-py2.py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 768d726cc6d1c9e21e532c2c11a59860c72e7ea5bb0e30e6e5bf25d24e616af7 |
|
MD5 | 388c0efc4bad9a2d4766d7ed4d9d03fd |
|
BLAKE2b-256 | 7811f5b5448a0979069497824aa83818f7c3daa05f5edf5b9791cc06c9a5c02b |