def __init__(self,
G,
cellmap,
named_ranges,
pointers=set(),
outputs=set(),
inputs=set(),
debug=False,
excel_compiler=False):
self.excel_compiler = excel_compiler
self.G = G
self.cellmap = cellmap
self.named_ranges = named_ranges
addr_to_name = {}
for name in named_ranges:
addr_to_name[named_ranges[name]] = name
self.addr_to_name = addr_to_name
addr_to_range = {}
for c in list(self.cellmap.values()):
if c.is_range and len(
list(c.range.keys())
) != 0: # could be better, but can't check on Exception types here...
addr = c.address() if c.is_named_range else c.range.name
for cell in c.range.addresses:
if cell not in addr_to_range:
addr_to_range[cell] = [addr]
else:
addr_to_range[cell].append(addr)
self.addr_to_range = addr_to_range
self.outputs = outputs
self.inputs = inputs
self.save_history = False
self.history = dict()
self.count = 0
self.pointer_to_remove = ["INDEX", "OFFSET"]
self.pointers = pointers
self.pointers_to_reset = pointers
self.range = RangeFactory(cellmap)
self.reset_buffer = set()
self.debug = debug
self.fixed_cells = {}
# make sure that all cells that don't have a value defined are updated.
for cell in self.cellmap.values():
if cell.value is None and cell.formula is not None:
cell.needs_update = True
def __init__(self,
file,
ignore_sheets=[],
ignore_hidden=False,
debug=False):
print "___### Initializing Excel Compiler ###___"
file_name = os.path.abspath(file)
# Decompose subfiles structure in zip file
archive = read_archive(file_name)
# Parse cells
self.cells = read_cells(archive, ignore_sheets, ignore_hidden)
# Parse named_range { name (ExampleName) -> address (Sheet!A1:A10)}
self.named_ranges = read_named_ranges(archive)
self.Range = RangeFactory(self.cells)
self.pointers = set()
self.debug = debug
def __init__(self,
G,
cellmap,
named_ranges,
pointers=set(),
outputs=set(),
inputs=set(),
debug=False):
super(Spreadsheet, self).__init__()
self.G = G
self.cellmap = cellmap
self.named_ranges = named_ranges
addr_to_name = {}
for name in named_ranges:
addr_to_name[named_ranges[name]] = name
self.addr_to_name = addr_to_name
addr_to_range = {}
for c in self.cellmap.values():
if c.is_range and len(
c.range.keys()
) != 0: # could be better, but can't check on Exception types here...
addr = c.address() if c.is_named_range else c.range.name
for cell in c.range.addresses:
if cell not in addr_to_range:
addr_to_range[cell] = [addr]
else:
addr_to_range[cell].append(addr)
self.addr_to_range = addr_to_range
self.outputs = outputs
self.inputs = inputs
self.save_history = False
self.history = dict()
self.count = 0
self.pointer_to_remove = ["INDEX", "OFFSET"]
self.pointers = pointers
self.pointers_to_reset = pointers
self.Range = RangeFactory(cellmap)
self.reset_buffer = set()
self.debug = debug
self.fixed_cells = {}
def load(fname):
def clean_bool(string):
if string == "0":
return None
else:
return string
def to_bool(string):
if string == "1" or string == "True":
return True
elif string == "0" or string == "False":
return False
else:
return string
def to_float(string):
if string == "None":
return None
try:
return float(string)
except:
return string
mode = "node0"
nodes = []
edges = []
pointers = set()
outputs = None
inputs = None
named_ranges = {}
infile = gzip.GzipFile(fname, 'r')
for line in infile.read().splitlines():
if line == "====":
mode = "node0"
continue
if line == "-----":
cellmap_temp = {n.address(): n for n in nodes}
Range = RangeFactory(cellmap_temp)
mode = "node0"
continue
elif line == "edges":
cellmap = {n.address(): n for n in nodes}
mode = "edges"
continue
elif line == "outputs":
mode = "outputs"
continue
elif line == "inputs":
mode = "inputs"
continue
elif line == "named_ranges":
mode = "named_ranges"
continue
if mode == "node0":
[
address, formula, python_expression, is_range, is_named_range,
is_pointer, should_eval
] = line.split(SEP)
formula = clean_bool(formula)
python_expression = clean_bool(python_expression)
is_range = to_bool(is_range)
is_named_range = to_bool(is_named_range)
is_pointer = to_bool(is_pointer)
should_eval = should_eval
mode = "node1"
elif mode == "node1":
if is_range:
reference = json.loads(
line
) if is_pointer else line # in order to be able to parse dicts
vv = Range(reference)
if is_pointer:
if not is_named_range:
address = vv.name
pointers.add(address)
cell = Cell(address, None, vv, formula, is_range,
is_named_range, should_eval)
cell.python_expression = python_expression
nodes.append(cell)
else:
value = to_bool(to_float(line))
cell = Cell(address, None, value, formula, is_range,
is_named_range, should_eval)
cell.python_expression = python_expression
if formula:
if 'OFFSET' in formula or 'INDEX' in formula:
pointers.add(address)
cell.compile()
nodes.append(cell)
elif mode == "edges":
source, target = line.split(SEP)
edges.append((cellmap[source], cellmap[target]))
elif mode == "outputs":
outputs = line.split(SEP)
elif mode == "inputs":
inputs = line.split(SEP)
elif mode == "named_ranges":
k, v = line.split(SEP)
named_ranges[k] = v
G = DiGraph(data=edges)
print "Graph loading done, %s nodes, %s edges, %s cellmap entries" % (len(
G.nodes()), len(G.edges()), len(cellmap))
return (G, cellmap, named_ranges, pointers, outputs, inputs)
def test_Range_Factory(self):
cellmap = {'A1': 25, 'B1': 34, 'C1': 79}
Range = RangeFactory(cellmap)
range = Range('A1:C1')
print 'RANGE', range
self.assertEqual(range[(1, 'B')], 34)
import unittest
import os
import sys
dir = os.path.dirname(__file__)
path = os.path.join(dir, '../..')
sys.path.insert(0, path)
from koala.Range import RangeFactory
Range = RangeFactory()
class Test_Excel(unittest.TestCase):
def setUp(self):
pass
def test_Range_Factory(self):
cellmap = {'A1': 25, 'B1': 34, 'C1': 79}
Range = RangeFactory(cellmap)
range = Range('A1:C1')
print 'RANGE', range
self.assertEqual(range[(1, 'B')], 34)
def test_Range_getter(self):
range = Range('Sheet1!A1:A3', [10, 20, 30])
print 'RANGE2', range
self.assertEqual(range.values, [10, 20, 30])
def test_Range_setter(self):
range = Range('A1:A3', [10, 20, 30])