def do_compilation(filename, seed, sheet=None):
excel = ExcelComWrapper(filename, app=xl_app())
c = ExcelCompiler(filename=filename, excel=excel)
sp = c.gen_graph(seed, sheet=sheet)
sp.save_to_file(filename + ".pickle")
sp.export_to_gexf(filename + ".gexf")
return sp
def do_compilation(filename, seed, sheet=None):
excel = ExcelComWrapper(filename, app=xl_app())
c = ExcelCompiler(filename=filename, excel=excel)
sp = c.gen_graph(seed, sheet=sheet)
sp.save_to_file(filename + ".pickle")
sp.export_to_gexf(filename + ".gexf")
return sp
from pycel.excellib import *
from pycel.excelcompiler import ExcelCompiler
from os.path import normpath,abspath
if __name__ == '__main__':
dir = os.path.dirname(__file__)
fname = os.path.join(dir, "../example/example.xlsx")
print "Loading %s..." % fname
# load & compile the file to a graph, starting from D1
c = ExcelCompiler(filename=fname)
print "Compiling..., starting from D1"
sp = c.gen_graph('D1',sheet='Sheet1')
# test evaluation
print "D1 is %s" % sp.evaluate('Sheet1!D1')
print "Setting A1 to 200"
sp.set_value('Sheet1!A1',200)
print "D1 is now %s (the same should happen in Excel)" % sp.evaluate('Sheet1!D1')
# show the graph usisng matplotlib
print "Plotting using matplotlib..."
sp.plot_graph()
# export the graph, can be loaded by a viewer like gephi
print "Exporting to gexf..."
from pycel.excellib import *
from pycel.excelcompiler import ExcelCompiler
from os.path import normpath,abspath
if __name__ == '__main__':
dir = os.path.dirname(__file__)
fname = os.path.join(dir, "../example/example.xlsx")
print "Loading %s..." % fname
# load & compile the file to a graph, starting from D2
c = ExcelCompiler(filename=fname)
print "Compiling..., starting from D4"
sp = c.gen_graph('A1:Z500',sheet='Sheet1')
# test evaluation
print "D9 is %s" % sp.evaluate('Sheet1!D9')
print "Setting A4 to 10"
sp.set_value('Sheet1!A4',10)
print "D9 is now %s (the same should happen in Excel)" % sp.evaluate('Sheet1!D9')
# show the graph usisng matplotlib
print "Plotting using matplotlib..."
sp.plot_graph()
# export the graph, can be loaded by a viewer like gephi
print "Exporting to gexf..."
class XLSXReader:
"""
Provides (almost) universal interface to read xlsx file cell values.
For formulae, tries to get their precomputed values or, if none,
to evaluate them.
"""
# Interface.
def __init__(self, path: Path):
self.__path = path
self.__book = load_workbook(self.__path, data_only=False)
def get_cell_value(self, address: str, sheet: str = 'Scheda'):
# If no sheet given, work with active one.
if sheet is None:
sheet = self.__book.active.title
# If cell doesn't contain a formula, return cell value.
if not self.__cell_contains_formula(address, sheet):
return self.__get_as_is(address, sheet)
# If cell contains formula:
# If there's precomputed value of the cell, return it.
precomputed_value = self.__get_precomputed(address, sheet)
if precomputed_value is not None:
return precomputed_value
# If not, try to compute its value from the formula and return it.
# If failed, report an error and return empty value.
try:
computed_value = self.__compute(address, sheet)
except:
logging.warning(
MESSAGES.CANT_EVALUATE_CELL.format(address=address))
logging.debug(format_exc())
return None
return computed_value
# Private part.
def __cell_contains_formula(self, address, sheet):
cell = self.__book[sheet][address]
return cell.data_type is cell.TYPE_FORMULA
def __get_as_is(self, address, sheet):
# Return cell value.
return self.__book[sheet][address].value
def __get_precomputed(self, address, sheet):
# If the sheet is not loaded yet, load it.
if not hasattr(self, '__book_with_precomputed_values'):
self.__book_with_precomputed_values = load_workbook(self.__path,
data_only=True)
# Return precomputed value.
return self.__book_with_precomputed_values[sheet][address].value
def __compute(self, address, sheet):
# If the computation engine is not created yet, create it.
if not hasattr(self, '__formulae_calculator'):
self.__formulae_calculator = ExcelCompiler(self.__path)
# Compute cell value.
computation_graph = self.__formulae_calculator.gen_graph(address,
sheet=sheet)
return computation_graph.evaluate(f"{sheet}!{address}")
"""
Created on 3 Mar 2015
@author: mack0242
"""
from __future__ import division
from pycel.excelutil import *
from pycel.excellib import *
import os
from pycel.excelcompiler import ExcelCompiler
from os.path import normpath, abspath
if __name__ == "__main__":
fname = normpath(abspath("D:\\gis_files\\redfearn.xls"))
print "Loading %s..." % fname
# load & compile the file to a graph, starting from D1
c = ExcelCompiler(filename=fname)
sheet_name = "E,N Zne to Latitude & Longitude"
print "Compiling..., starting from B3"
sp = c.gen_graph("B3", sheet=sheet_name)
print "Setting E3 to 54"
print sp
sp.set_value(sheet_name + "!E3", 54)
# test evaluation
print "LAT,LNG is %s,%s" % (sp.evaluate(sheet_name + "!F43"), sp.evaluate(sheet_name + "!P43"))
from pycel.excellib import *
from pycel.excelcompiler import ExcelCompiler
from os.path import normpath,abspath
if __name__ == '__main__':
dir = os.path.dirname(__file__)
fname = os.path.join(dir, "../example/FQ Pricing Simulation.xlsx")
print "Loading %s..." % fname
# load & compile the file to a graph
c = ExcelCompiler(filename=fname)
print "Compiling..., starting from E42"
sp = c.gen_graph('E42',sheet='Fields Master')
# test evaluation
print "E42 is %s" % sp.evaluate('Fields Master!E42')
print "breakpoint!"
print "Setting A1 to 200"
#sp.set_value('Sheet1!A1',200)
#print "D1 is now %s (the same should happen in Excel)" % sp.evaluate('Sheet1!D1')
# show the graph usisng matplotlib
print "Plotting using matplotlib..."
sp.plot_graph()
# export the graph, can be loaded by a viewer like gephi
from pycel.excellib import *
from pycel.excelcompiler import ExcelCompiler
from os.path import normpath, abspath
if __name__ == '__main__':
dir = os.path.dirname(__file__)
fname = os.path.join(dir, "../example/western_rater_13.6.3.xlsm")
print "Loading %s..." % fname
# load & compile the file to a graph, starting from D1
c = ExcelCompiler(filename=fname)
print "Compiling..., starting from D1"
sp = c.gen_graph('P62', sheet='Quote')
# test evaluation
print "Premium is %s" % sp.evaluate('Quote!P62')
print "Setting Q7 to 20000"
sp.set_value('Input!Q7', 20000)
print "Premium is now %s (the same should happen in Excel)" % sp.evaluate(
'Quote!P62')
# show the graph usisng matplotlib
#print "Plotting using matplotlib..."
#sp.plot_graph()
## export the graph, can be loaded by a viewer like gephi
