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