CODEWITHSUNDEEP
pythonpycel
Enroll SP
Enroll SP

Reputation: 21

Python - Pycel issues

I did a pip install of Pycel in conda.

I am performing this in Jupyter Notebook.

%matplotlib inline
from pycel import ExcelCompiler
from IPython.display import FileLink
import matplotlib.pyplot as plt

filename = "../python/pycel_files/example.xlsx"
print("Loading {}...".format(filename))

# load & compile the file to a graph
excel = ExcelCompiler(filename=filename)

# test evaluation
print("B1 is {}".format(excel.evaluate('Sheet1!B1')))
print("B2=(B1+5) is {}".format(excel.evaluate('Sheet1!B2')))

Output : As expected

B1 is 10 B2=(B1+5) is 15

However if I now do this:

print("Setting B1 to 200")
excel.set_value('Sheet1!B1', 200)

print("B2 is now %s (the same should happen in Excel)" % excel.evaluate(
    'Sheet1!B2'))

I get the expected output in Jupyter:

Setting B1 to 200
B2 is now 205 (the same should happen in Excel)

Issues:

  1. However in excel, cell B1 is not updated. I.e. it does not have the value 200 which was supposedly set by Pycel. It still shows 10.
  2. I faced the same issue in their example github .ipynb of Pycel file. Can someone confirm if they are also facing the same issue & this looks like a bug?
  3. Also, if I make any changes to their example excel file & use their .ipynb file nothing shows up in matplotlib graphs. How does the Pycel know which sheet to choose from? Does it default to sheet1 always?

Upvotes: 2

Views: 3048

Answers (1)

Stephen Rauch
Stephen Rauch

Reputation: 49794

So there is a misunderstanding here, of what Pycel is and does. From the Pycel Readme:

Pycel is a small python library that can translate an Excel spreadsheet into executable python code which can be run independently of Excel. The python code is based on a graph and uses caching & lazy evaluation to ensure (relatively) fast execution.

So to answer your issues:

However in excel, cell B1 is not updated.

Pycel does not change the Excel file. It reads from the excel file and creates a "Compiled" spreadsheet from the desired cells.

If I make any changes to their example excel file & use their .ipynb file nothing shows up in matplotlib graphs.

You will likely need to re-compile the excel spreadsheet to get any results to show up in the Python code.

How does the Pycel know which sheet to choose from? Does it default to sheet1 always?

Pycel does really deal with sheets. It works with cells. Those cells can come from any sheet in the workbook.

Upvotes: 1

Related Questions