convert Excel formula in a cell to function in a Python script

Question

I have been trying to convert formulas in an Excel workbook to equivalent Python functions or statements in a Python script.

At my workplace we have older Excel workbooks that we use for calculations in engineering processes (like designing concrete structures). These calculations involve many formulas and references between formulas. I want to convert the formulas in those workbooks to 'equivalent' Python functions, the result being that those Excel workbooks are then replaced by Python scripts (for reasons concerning maintainability of these technical documents and workflow validation).

To demonstrate the principle with a basic example.

If there is an Excel workbook with one sheet (name = Sheet1)

value in cell A1 = 2
value in cell A2 = 3
value in cell A3 = '=A1+A2' = 5

I want to convert this to the following Python function:

def A3(A1,A2):
    return A1+A2

Alternatively using a global variable

A3 = 0

def A3(A1,A3):
   A3 = A1 + A3

Another more complex example involving a conditional statement:

Excel workbook with two sheets ('Sheet1' and 'Sheet2')

Sheet1!A1 = 2   
Sheet1!A2 = 3    
Sheet1!A3 = False

Sheet2!A1 = '=IF(Sheet1!A3 == True; Sheet1!A1+Sheet1!A2; Sheet1!A1*Sheet1!2)' = 6

In Python would become (or something similar):

def Sheet2!A1(Sheet1!A1,Sheet1!A2):

   if Sheet!A3 == True:
   return Sheet1!A1+Sheet1!A2

   else:
   return Sheet1!A1 * Sheet1!A2

I have tried using openpyxl (for reading workbooks) in combination with formulas (the module) and pycel, but I cannot figure out how to get such a conversion.

If I understand correctly the formulas package and pycel package can identify and convert Excel functions (to Python right?). Or maybe they just do so during runtime?

Specifically I want to know how to, essentially, compile the Excel formulas to Python functions.

The general question: is there a way to automate the conversion of Excel workbooks to equivalent Python scripts?

Stephen Rauch · Accepted Answer · 2021-10-23 18:45:27Z

7

Pycel can be used to compile an xlsx file to Python code, but maybe not in the way you are hoping for.

Using your example as example.xlsx, executing the following code:

from pycel import ExcelCompiler

excel_compiler = ExcelCompiler('example.xlsx')
excel_compiler.to_file(file_types='yaml')

will result in this yaml file being generated:

cycles: false
excel_hash: b99b0d1174e4bfaceddeeb70bde09c9f
cell_map:
  Sheet1!A1: 2
  Sheet1!A2: 3
  Sheet1!A3: false
  Sheet2!A1: =if_(_C_("Sheet1!A3") == True, _C_("Sheet1!A1") + _C_("Sheet1!A2"), _C_("Sheet1!A1") * _C_("Sheet1!A2"))
filename: example.xlsx

The dictionary values for cell_map are Python code and are in fact executed by pycel when a cell is evaluated. However, since the execution model for Excel code varies somewhat from Python, there are some minor bits of magic that happen during that execution.

_{Disclaimer: I am the maintainer for pycel}

answered Oct 23, 2021 at 18:45

Stephen Rauch♦

50.1k32 gold badges119 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FrankvdNeut Over a year ago

This is already pretty useful, much closer to the desired result. These results in combination with plain search and replace (or converting these results using regex in separate program) could be a satisfactorily workflow for converting Excel Workbooks to Python.

Collectives™ on Stack Overflow

convert Excel formula in a cell to function in a Python script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related