An Ebsilon Macro which uses Excel

Let existing engineering spreadsheets do calculations for Ebsilon

The Ebsilon Add-in and (if you like coding) EbsOpen with VBA are great for driving Ebsilon from Excel. But what about the other direction — what about having Ebsilon send data to an existing spreadsheet and return calculated results back to Ebsilon?

Watch this 1.5 minute video to see this magic happen, then I’ll explain how it works:

The Pieces

Download the Ebsilon - Python - Excel.zip file from my Ebsilon Tips OneDrive and unzip the contents to any folder on your computer (keep all the files in the same folder). It contains the following:

Excel file

I created a super-simple Excel file to get the square and the cube of a number. I gave names to the cells that hold the input and the two outputs (you don’t need to use Named Ranges, but doing so is FAR more future-proof… it keeps your code from breaking if you add or delete rows).

Python script

The xlWings_test.py file can run stand-alone in a Python shell, which is helpful for development and debug.

Ebsilon model 1

The model “Python + Excel (Single Excel call).ebs” is the simpler of the two. I created it to develop and test the data flow and processing.

It doesn’t do any calculations with pipes. It has a macro with a Pre-Run EbsScript which uses the Python script to push an specified “x” to the Excel file and return two calculated values as Results.

Ebsilon model 2

The model “Python + Excel (Excel called every iteration).ebs” is the one in the demo video. It DOES interact with pipes, so needs a Kernel Script. Rather than pushing an arbitrary “x” value to Excel, it pushes the macro’s inlet pressure (P1) and sets the exit pressure (P2) to one of the two values calculated by the spreadsheet.

To keep this example simple, the macro’s KS code sets the exit mass flow and enthalpy equal to the inlet values.

This is obviously overkill for the task, but it simple and demonstrates the basic principles for integrating more detailed Excel-based calculations into an Ebsilon model.

Pre-Requisites

Python base installation

This installs Python and the files needed for Ebsilon to interact with Python.

Look for the model “python.ebs” in the Ebsilon’s Examples folder (Program Files… Ebsilon… EBSILON Professional… Data… Examples). Be sure you do ALL the steps in the Text-Field of that model, and that you can run the internal EbsScript found via F8… EbsScript… Open Internal… select “EbsScript_Code”. Hit F5.

If you did these steps correctly, you should see some names and numbers appear in the output pane.

xlwings Library

This installs xlwings, which Python uses to interact with Excel.

Follow the xlwings installation instructions found here.

You don’t need the xlwings Excel Add-in.

---