Ejemplo n.º 1
0
def test_end_2_end(excel, fixture_xls_path):
    # load & compile the file to a graph, starting from D1
    for excel_compiler in (ExcelCompiler(excel=excel),
                           ExcelCompiler(fixture_xls_path)):

        # test evaluation
        assert -0.02286 == round(excel_compiler.evaluate('Sheet1!D1'), 5)

        excel_compiler.set_value('Sheet1!A1', 200)
        assert -0.00331 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
Ejemplo n.º 2
0
def test_pickle_file_rebuilding(excel):

    input_addrs = ['Sheet1!A11']
    output_addrs = ['Sheet1!D1']

    excel_compiler = ExcelCompiler(excel=excel)
    excel_compiler.trim_graph(input_addrs, output_addrs)
    excel_compiler.to_file()

    pickle_name = excel_compiler.filename + '.pkl'
    yaml_name = excel_compiler.filename + '.yml'

    assert os.path.exists(pickle_name)
    old_hash = excel_compiler._compute_file_md5_digest(pickle_name)

    excel_compiler.to_file()
    assert old_hash == excel_compiler._compute_file_md5_digest(pickle_name)

    os.unlink(yaml_name)
    excel_compiler.to_file()
    new_hash = excel_compiler._compute_file_md5_digest(pickle_name)
    assert old_hash != new_hash

    shutil.copyfile(pickle_name, yaml_name)
    excel_compiler.to_file()
    assert new_hash != excel_compiler._compute_file_md5_digest(pickle_name)
Ejemplo n.º 3
0
def test_validate_calcs_empty_params():
    data = [
        x.strip().split() for x in """
        =INDEX($G$2:$G$4,D2) =INDEX($G$2:$I$2,,D2) =INDEX($G$2:$I$2,$A$6,D2)
        =INDEX($G$2:$G$4,D3) =INDEX($G$2:$I$2,,D3) =INDEX($G$2:$I$2,$A$6,D3)
        =INDEX($G$2:$G$4,D4) =INDEX($G$2:$I$2,,D4) =INDEX($G$2:$I$2,$A$6,D4)
        =MATCH(E2,$F$2:$F$4) 0 1 a b c
        =MATCH(E3,$F$2:$F$4) 2 2 b
        =MATCH(E4,$F$2:$F$4) 4 3 c
        =IF(0,,2) =IF(1,,2) =IF(,1,2) =IF(,0,2)
    """.split('\n')[1:-1]
    ]

    wb = Workbook()
    ws = wb.active
    ws['A2'], ws['B2'], ws['C2'] = data[0]
    ws['A3'], ws['B3'], ws['C3'] = data[1]
    ws['A4'], ws['B4'], ws['C4'] = data[2]
    ws['D2'], ws['E2'], ws['F2'], ws['G2'], ws['H2'], ws['I2'] = data[3]
    ws['D3'], ws['E3'], ws['F3'], ws['G3'] = data[4]
    ws['D4'], ws['E4'], ws['F4'], ws['G4'] = data[5]
    ws['A5'], ws['B5'], ws['C5'], ws['D5'] = data[6]

    excel_compiler = ExcelCompiler(excel=wb)

    assert (NA_ERROR, ) * 3 == excel_compiler.evaluate('Sheet!A2:C2')
    assert ('b', ) * 3 == excel_compiler.evaluate('Sheet!A3:C3')
    assert ('c', ) * 3 == excel_compiler.evaluate('Sheet!A4:C4')
    assert (2, 0, 2, 2) == excel_compiler.evaluate('Sheet!A5:D5')
Ejemplo n.º 4
0
def test_round_trip_through_json_yaml_and_pickle(excel, fixture_xls_path):
    excel_compiler = ExcelCompiler(excel=excel)
    excel_compiler.evaluate('Sheet1!D1')
    excel_compiler.extra_data = {1: 3}
    excel_compiler.to_file(file_types=('pickle', ))
    excel_compiler.to_file(file_types=('yml', ))
    excel_compiler.to_file(file_types=('json', ))

    # read the spreadsheet from json, yaml and pickle
    excel_compiler_json = ExcelCompiler.from_file(excel.filename + '.json')
    excel_compiler_yaml = ExcelCompiler.from_file(excel.filename + '.yml')
    excel_compiler = ExcelCompiler.from_file(excel.filename)

    # test evaluation
    assert -0.02286 == round(excel_compiler_json.evaluate('Sheet1!D1'), 5)
    assert -0.02286 == round(excel_compiler_yaml.evaluate('Sheet1!D1'), 5)
    assert -0.02286 == round(excel_compiler.evaluate('Sheet1!D1'), 5)

    excel_compiler_json.set_value('Sheet1!A1', 200)
    assert -0.00331 == round(excel_compiler_json.evaluate('Sheet1!D1'), 5)

    excel_compiler_yaml.set_value('Sheet1!A1', 200)
    assert -0.00331 == round(excel_compiler_yaml.evaluate('Sheet1!D1'), 5)

    excel_compiler.set_value('Sheet1!A1', 200)
    assert -0.00331 == round(excel_compiler.evaluate('Sheet1!D1'), 5)
Ejemplo n.º 5
0
def test_unbounded_countifs(fixture_xls_copy):
    wb = Workbook()
    ws = wb.active
    ws['A1'] = 1
    ws['A2'] = 2
    ws['A3'] = 3
    ws['A4'] = 4
    ws['A5'] = 5
    ws['B1'] = '1'
    ws['B2'] = '2'
    ws['B3'] = '3'
    ws['B4'] = '4'
    ws['B5'] = '5'
    ws['C1'] = '=COUNTIFS(B:B,">3")'
    ws['C2'] = '=SUMIFS(A:A,B:B,">3")'
    excel_compiler = ExcelCompiler(filename='test_unbounded_countifs',
                                   excel=wb)

    output_addrs = 'Sheet!C1', 'Sheet!C2'
    assert (2, 9) == excel_compiler.evaluate(output_addrs)
    excel_compiler.recalculate()
    assert (2, 9) == excel_compiler.evaluate(output_addrs)

    # read the spreadsheet from pickle
    excel_compiler.to_file(file_types=('pickle', ))
    excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)

    # test evaluation
    assert (2, 9) == excel_compiler.evaluate(output_addrs)
    excel_compiler.recalculate()
    assert (2, 9) == excel_compiler.evaluate(output_addrs)
Ejemplo n.º 6
0
def test_gen_graph(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    excel.set_sheet('trim-range')
    excel_compiler._gen_graph('B2')

    with pytest.raises(ValueError, match='Unknown seed'):
        excel_compiler._gen_graph(None)
Ejemplo n.º 7
0
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
Ejemplo n.º 8
0
def test_lookup_ws(fixture_xls_copy):
    INDIRECT_FORMULA_ADDRESS = AddressCell('Offset!B53')
    compiler = ExcelCompiler(fixture_xls_copy('lookup.xlsx'))

    # do an INDIRECT() before other cells are loaded to verify it can load what it needs
    result = compiler.validate_calcs([INDIRECT_FORMULA_ADDRESS])
    assert result == {}

    # now load and check everything
    result = compiler.validate_serialized()
    assert result == {}

    # use indirect to an existing range
    loaded = ExcelCompiler.from_file(compiler.filename)
    loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'B2:F6')
    indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS)
    assert indirect == loaded.evaluate('Offset!B2')

    # use indirect to a non-pre-existing and empty range
    loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'H1:H2')
    indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS)
    assert indirect is None

    # use indirect to a non-pre-existing range to existing cells
    loaded.set_value(INDIRECT_FORMULA_ADDRESS.address_at_offset(1, 0), 'D3:E3')
    indirect = loaded.evaluate(INDIRECT_FORMULA_ADDRESS)
    assert indirect == 8
Ejemplo n.º 9
0
 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}")
Ejemplo n.º 10
0
def test_circular_mismatch_warning(fixture_xls_path,
                                   fixture_xls_path_circular):

    with mock.patch('pycel.excelcompiler.pycel_logger') as log:
        assert log.warning.call_count == 0

        ExcelCompiler(fixture_xls_path, cycles=False)
        assert log.warning.call_count == 0

        ExcelCompiler(fixture_xls_path, cycles=True)
        assert log.warning.call_count == 1

        ExcelCompiler(fixture_xls_path_circular, cycles=False)
        assert log.warning.call_count == 2

        ExcelCompiler(fixture_xls_path_circular, cycles=True)
        assert log.warning.call_count == 2
Ejemplo n.º 11
0
def test_gen_gexf(excel, tmpdir):
    excel_compiler = ExcelCompiler(excel=excel)
    filename = os.path.join(str(tmpdir), 'test.gexf')
    assert not os.path.exists(filename)
    excel_compiler.export_to_gexf(filename)

    # ::TODO: it would good to test this by comparing to an fixture/artifact
    assert os.path.exists(filename)
Ejemplo n.º 12
0
def test_trim_cells_warn_address_not_found(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['trim-range!D5', 'trim-range!H1']
    output_addrs = ['trim-range!B2']

    excel_compiler.evaluate(output_addrs[0])
    excel_compiler.log.warning = mock.Mock()
    excel_compiler.trim_graph(input_addrs, output_addrs)
    assert 1 == excel_compiler.log.warning.call_count
Ejemplo n.º 13
0
def test_recalculate(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    out_address = 'Sheet1!D1'

    assert -0.02286 == round(excel_compiler.evaluate(out_address), 5)
    excel_compiler.cell_map[out_address].value = None

    excel_compiler.recalculate()
    assert -0.02286 == round(excel_compiler.cell_map[out_address].value, 5)
Ejemplo n.º 14
0
def test_circular_order_random(fixture_xls_copy):
    """This is hoping to find a way to reproduce #126"""
    excel_compiler = ExcelCompiler(fixture_xls_copy('circular.xlsx'),
                                   cycles=True)
    to_verify = list(excel_compiler.formula_cells())

    randgen = random.Random(1234)

    for i in range(100):
        addrs = copy.copy(to_verify)
        randgen.shuffle(addrs)
        excel_compiler = ExcelCompiler(fixture_xls_copy('circular.xlsx'),
                                       cycles=True)
        failed_cells = excel_compiler.validate_serialized(
            tolerance=excel_compiler.cycles['tolerance'],
            output_addrs=addrs,
        )
        assert (failed_cells, addrs) == ({}, addrs)
Ejemplo n.º 15
0
def test_trim_cells_info_buried_input(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['trim-range!B1', 'trim-range!D1']
    output_addrs = ['trim-range!B2']

    excel_compiler.evaluate(output_addrs[0])
    excel_compiler.log.info = mock.Mock()
    excel_compiler.trim_graph(input_addrs, output_addrs)
    assert 2 == excel_compiler.log.info.call_count
    assert 'not a leaf node' in excel_compiler.log.info.mock_calls[1][1][0]
Ejemplo n.º 16
0
def test_circular_disabled(fixture_xls_copy):
    excel_compiler = ExcelCompiler(fixture_xls_copy('circular.xlsx'),
                                   cycles=False)

    failed_cells = excel_compiler.validate_serialized()
    assert failed_cells == {
        'exceptions': {
            'RecursionError: Do you need to use cycles=True ?':
            [('Sheet1!B10', '=IF(B3=0, 0, B10+0.001)',
              'Do you need to use cycles=True ?')]
        }
    }
Ejemplo n.º 17
0
def test_trim_cells_exception_input_unused(excel):

    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['trim-range!G1']
    output_addrs = ['trim-range!B2']
    excel_compiler.evaluate(output_addrs[0])
    excel_compiler.evaluate(input_addrs[0])

    with pytest.raises(
            ValueError,
            match=' which usually means no outputs are dependant on it'):
        excel_compiler.trim_graph(input_addrs, output_addrs)
Ejemplo n.º 18
0
def test_structured_ref(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['sref!F3']
    output_addrs = ['sref!B3']

    assert 15 == excel_compiler.evaluate(output_addrs[0])
    excel_compiler.trim_graph(input_addrs, output_addrs)

    assert 15 == excel_compiler.evaluate(output_addrs[0])

    excel_compiler.set_value(input_addrs[0], 11)
    assert 20 == excel_compiler.evaluate(output_addrs[0])
Ejemplo n.º 19
0
def main():

    xlsname = "C:/Users/anubhav/Desktop/Projects/Saturn/Saturn/TestModel_v2.xlsx"

    excel = ExcelCompiler(xlsname)
    cell2 = 'Sheet1!B11'
    excel.evaluate(cell2)
    logging.info("\n\n-----SET VALUE-----")
    excel.set_value('Sheet1!C4', '5')
    logging.info("\n\n----EVALUATE------")
    ret = excel.evaluate(cell2)
    print("Final value is: {}".format(ret))
Ejemplo n.º 20
0
def test_trim_cells(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['trim-range!D5']
    output_addrs = ['trim-range!B2']

    old_value = excel_compiler.evaluate(output_addrs[0])

    excel_compiler.trim_graph(input_addrs, output_addrs)
    excel_compiler._to_text(is_json=True)

    new_value = ExcelCompiler._from_text(
        excel_compiler.filename, is_json=True).evaluate(output_addrs[0])

    assert old_value == new_value
Ejemplo n.º 21
0
def test_filename_extension_errors(excel, fixture_xls_path):
    with pytest.raises(ValueError, match='Unrecognized file type'):
        ExcelCompiler.from_file(excel.filename + '.xyzzy')

    excel_compiler = ExcelCompiler(excel=excel)

    with pytest.raises(ValueError, match='Only allowed one '):
        excel_compiler.to_file(file_types=('pkl', 'pickle'))

    with pytest.raises(ValueError, match='Only allowed one '):
        excel_compiler.to_file(file_types=('pkl', 'yml', 'json'))

    with pytest.raises(ValueError, match='Unknown file types: pkly'):
        excel_compiler.to_file(file_types=('pkly',))
Ejemplo n.º 22
0
def test_validate_count():
    wb = Workbook()
    ws = wb.active
    ws['A1'] = 0
    ws['A2'] = 1
    ws['A3'] = 1.1
    ws['A4'] = '1.1'
    ws['A5'] = True
    ws['A6'] = False
    ws['A7'] = 'A'
    ws['A8'] = 'TRUE'
    ws['A9'] = 'FALSE'
    ws['B1'] = '=COUNT(A2)'
    ws['B2'] = '=COUNT(A2:A3)'
    ws['B3'] = '=COUNT(A2:A3,A3)'
    ws['B4'] = '=COUNT(A1:A9,A4,A5,A6,A7,A8,A9)'

    excel_compiler = ExcelCompiler(excel=wb)
    assert excel_compiler.evaluate('Sheet!B1:B4') == (1, 2, 3, 3)

    # Test missing calcPr in WorkbookPackage
    wb.calculation = None
    excel_compiler = ExcelCompiler(excel=wb)
    assert excel_compiler.evaluate('Sheet!B1:B4') == (1, 2, 3, 3)
Ejemplo n.º 23
0
def test_validate_calcs(excel, capsys):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = ['trim-range!D5']
    output_addrs = ['trim-range!B2']

    excel_compiler.trim_graph(input_addrs, output_addrs)
    excel_compiler.cell_map[output_addrs[0]].value = 'JUNK'
    failed_cells = excel_compiler.validate_calcs(output_addrs)

    assert {'mismatch': {
        'trim-range!B2': ('JUNK', 136, '=B1+SUM(D4:E4)+D5')}} == failed_cells

    out, err = capsys.readouterr()
    assert '' == err
    assert 'JUNK' in out
Ejemplo n.º 24
0
def test_gen_dot(excel, tmpdir):
    from unittest import mock

    excel_compiler = ExcelCompiler(excel=excel)
    with pytest.raises(ImportError, match="Package 'pydot' is not installed"):
        excel_compiler.export_to_dot()

    import sys
    mock_imports = (
        'pydot',
    )
    for mock_import in mock_imports:
        sys.modules[mock_import] = mock.MagicMock()

    with mock.patch('networkx.drawing.nx_pydot.write_dot'):
        excel_compiler.export_to_dot()
Ejemplo n.º 25
0
def test_reset(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    in_address = 'Sheet1!A1'
    out_address = 'Sheet1!D1'

    assert -0.02286 == round(excel_compiler.evaluate(out_address), 5)

    in_value = excel_compiler.cell_map[in_address].value

    excel_compiler._reset(excel_compiler.cell_map[in_address])
    assert excel_compiler.cell_map[out_address].value is None

    excel_compiler._reset(excel_compiler.cell_map[in_address])
    assert excel_compiler.cell_map[out_address].value is None

    excel_compiler.cell_map[in_address].value = in_value
    assert -0.02286 == round(excel_compiler.evaluate(out_address), 5)
    assert -0.02286 == round(excel_compiler.cell_map[out_address].value, 5)
Ejemplo n.º 26
0
def test_compile_error_message_line_number(excel):
    excel_compiler = ExcelCompiler(excel=excel)

    input_addrs = ['trim-range!D5']
    output_addrs = ['trim-range!B2']

    excel_compiler.trim_graph(input_addrs, output_addrs)

    filename = excel_compiler.filename + '.pickle'
    excel_compiler.to_file(filename)

    excel_compiler = ExcelCompiler.from_file(filename)
    formula = excel_compiler.cell_map[output_addrs[0]].formula
    formula._python_code = '(x)'
    formula.lineno = 3000
    formula.filename = 'a_file'
    with pytest.raises(
            FormulaEvalError, match='File "a_file", line 3000'):
        excel_compiler.evaluate(output_addrs[0])
Ejemplo n.º 27
0
def test_evaluate_from_non_cells(excel):
    excel_compiler = ExcelCompiler(excel=excel)

    input_addrs = ['Sheet1!A11']
    output_addrs = ['Sheet1!A11:A13', 'Sheet1!D1', 'Sheet1!B11', ]

    old_values = excel_compiler.evaluate(output_addrs)

    excel_compiler.trim_graph(input_addrs, output_addrs)

    excel_compiler.to_file(file_types='yml')
    excel_compiler = ExcelCompiler.from_file(excel_compiler.filename)
    for expected, result in zip(
            old_values, excel_compiler.evaluate(output_addrs)):
        assert expected == pytest.approx(result)

    range_cell = excel_compiler.cell_map[output_addrs[0]]
    excel_compiler._reset(range_cell)
    range_value = excel_compiler.evaluate(range_cell.address)
    assert old_values[0] == range_value
Ejemplo n.º 28
0
def test_filename_ext(excel, fixture_xls_path):
    excel_compiler = ExcelCompiler(excel=excel)
    excel_compiler.evaluate('Sheet1!D1')
    excel_compiler.extra_data = {1: 3}
    pickle_name = excel_compiler.filename + '.pkl'
    yaml_name = excel_compiler.filename + '.yml'
    json_name = excel_compiler.filename + '.json'

    for name in (pickle_name, yaml_name, json_name):
        try:
            os.unlink(name)
        except FileNotFoundError:
            pass

    excel_compiler.to_file(excel_compiler.filename)
    excel_compiler.to_file(json_name, file_types=('json', ))

    assert os.path.exists(pickle_name)
    assert os.path.exists(yaml_name)
    assert os.path.exists(json_name)
Ejemplo n.º 29
0
def test_value_tree_str(excel):
    out_address = 'trim-range!B2'
    excel_compiler = ExcelCompiler(excel=excel)
    excel_compiler.evaluate(out_address)

    expected = [
        'trim-range!B2 = 136',
        ' trim-range!B1 = 24',
        '  trim-range!D1:E3 = [[1, 5], [2, 6], [3, 7]]',
        '   trim-range!D1 = 1',
        '   trim-range!D2 = 2',
        '   trim-range!D3 = 3',
        '   trim-range!E1 = 5',
        '   trim-range!E2 = 6',
        '   trim-range!E3 = 7',
        ' trim-range!D4:E4 = [4, 8]',
        '  trim-range!D4 = 4',
        '  trim-range!E4 = 8',
        ' trim-range!D5 = 100'
    ]
    assert expected == list(excel_compiler.value_tree_str(out_address))
Ejemplo n.º 30
0
def test_trim_cells_range(excel):
    excel_compiler = ExcelCompiler(excel=excel)
    input_addrs = [AddressRange('trim-range!D4:E4')]
    output_addrs = ['trim-range!B2']

    old_value = excel_compiler.evaluate(output_addrs[0])

    excel_compiler.trim_graph(input_addrs, output_addrs)

    excel_compiler._to_text()
    excel_compiler = ExcelCompiler._from_text(excel_compiler.filename)
    assert old_value == excel_compiler.evaluate(output_addrs[0])

    excel_compiler.set_value(input_addrs[0], [5, 6])
    assert old_value - 1 == excel_compiler.evaluate(output_addrs[0])

    excel_compiler.set_value(input_addrs[0], [4, 6])
    assert old_value - 2 == excel_compiler.evaluate(output_addrs[0])

    excel_compiler.set_value(tuple(next(input_addrs[0].rows)), [5, 6])
    assert old_value - 1 == excel_compiler.evaluate(output_addrs[0])