CODEWITHSUNDEEP
pycel
rdmolony
rdmolony

Reputation: 771

Compiling a multiple sheet Excel Spreadsheet into pycel

TL;DR - trying to compile this multi-sheet Pycel tests/fixture spreadsheet (I'm particularly interested in capturing inter-sheet dependencies) using into Python code using Pycel

The Problem

The helpful Pycel example in the example/example.py in the Pycel repository runs perfectly and produces a handy .gexf graph, however, I can't work out how to compile a multi-sheet Spreadsheet (in my use case there is terrifying inter-sheet dependencies!) using Pycel.

@Stephen Rauch mentioned that "Pycel does really deal with sheets. It works with cells. Those cells can come from any sheet in the workbook" in this Stackoverflow discussion, however, I couldn't find any detail on this in the Pycel repo or in prior questions.

What I tried

I tried compiling the spreadsheet included in tests/fixtures/excelcompiler.xlsx as this is multi-sheet and it only seems to compile for Sheet1 as the resulting plot is empty (Sheet1 is empty).

import pycel
excel = pycel.ExcelCompiler('excelcompiler.xlsx')
excel.plot_graph()

Is it possible to use Pycel to compile dependencies between sheets?

I'm currently trying to compile this Excel spreadsheet tool1 into Python for modelling purposes2.

1 For more details see The Irish National Building Energy Rating Assessment
2 Running Dublin-wide simulations of the energy/carbon impacts of different building-policy decisions

Upvotes: 1

Views: 1234

Answers (1)

rdmolony
rdmolony

Reputation: 771

TL;DR

Pycel compiles multi-sheet Excel spreadsheets easily. Pycel's function pycel.ExcelCompiler is lazy and so doesn't actually compile Excel into Python until explicitly asked to do so via excel.evaluate('Result!E16')} (i.e. evaluate some cell)

In detail

I tried:

excel = pycel.ExcelCompiler(filename='deap.xlsx')

# for viewing in gephi (quicker runtime)
excel.export_to_gexf('deap.gexf')

# or via matplotlib
excel.plot_graph()

And my result was an empty graph.

I then tried (as in the example at pycel):

excel = pycel.ExcelCompiler(filename='deap.xlsx')

print(f"TotalDeliveredEnergy is {excel.evaluate('Result!E16')}")

# for viewing in gephi (quicker runtime)
excel.export_to_gexf('deap.gexf')

# or via matplotlib
excel.plot_graph()

And this worked!

In other words, my issue was that I was trying to plot a Pycel relationship before it had been evaluated - Pycel is lazy and so only compiles Excel to Python when called upon with excel.evaluate.

Upvotes: 1

Related Questions