How to use pycel - 10 common examples

def test_ref_wrap(value, result):
    def r_test(*args):
        return args

    name_space = locals()
    name_space['_R_'] = lambda a: 'R:{}'.format(a)
    name_space['_C_'] = lambda a: 'C:{}'.format(a)

    func = apply_meta(
        excel_helper(ref_params=1)(r_test), name_space=name_space)[0]
    assert func(*value) == result

        ((0, 1), (NUM_ERROR, DIV0), NUM_ERROR),
        ((0, 1), (DIV0, NUM_ERROR), DIV0),
        ((0,), (1, DIV0), "args: (1, '#DIV/0!')"),
        ((1,), (1, DIV0), DIV0),
    )
)
def test_error_string_wrapper(arg_nums, f_args, result):

    def f_test(*args):
        return 'args: {}'.format(args)

    assert error_string_wrapper(f_test, arg_nums)(*f_args) == result

def test_address_range():
    a = AddressRange('a1:b2')
    b = AddressRange('A1:B2')
    c = AddressRange(a)

    assert a == b
    assert b == c

    assert b == AddressRange(b)
    assert b == AddressRange.create(b)

    assert AddressRange('sh!a1:b2') == AddressRange(a, sheet='sh')
    assert AddressCell('C13') == AddressCell('R13C3')

    with pytest.raises(ValueError):
        AddressRange(AddressRange('sh!a1:b2'), sheet='sheet')

    a = AddressRange('A:A')
    assert 'A' == a.start.column
    assert 'A' == a.end.column
    assert 0 == a.start.row
    assert 0 == a.end.row

    b = AddressRange('1:1')
    assert '' == b.start.column

def test_address_pickle(tmpdir):
    addrs = [
        AddressRange('B1'),
        AddressRange('B1:C1'),
        AddressRange('B1:B2'),
        AddressRange('B1:C2'),
        AddressRange('sh!B1'),
        AddressRange('sh!B1:C1'),
        AddressRange('sh!B1:B2'),
        AddressRange('sh!B1:C2'),
        AddressRange('B:C'),
        AddressRange('2:4'),
        AddressCell('sh!XFC1048575'),
        AddressCell('sh!XFD1048576'),
        AddressCell('sh!A1'),
        AddressCell('sh!E5'),
        AddressCell('sh!F6'),
    ]

    filename = os.path.join(str(tmpdir), 'test_addrs.pkl')
    with open(filename, 'wb') as f:
        pickle.dump(addrs, f)

    with open(filename, 'rb') as f:
        new_addrs = pickle.load(f)

    assert addrs == new_addrs

def test_address_range():
    a = AddressRange('a1:b2')
    b = AddressRange('A1:B2')
    c = AddressRange(a)

    assert a == b
    assert b == c

    assert b == AddressRange(b)
    assert b == AddressRange.create(b)

    assert AddressRange('sh!a1:b2') == AddressRange(a, sheet='sh')
    assert AddressCell('C13') == AddressCell('R13C3')

    with pytest.raises(ValueError):
        AddressRange(AddressRange('sh!a1:b2'), sheet='sheet')

    a = AddressRange('A:A')
    assert 'A' == a.start.column

def test_evaluate_conditional_formatting(cond_format_ws):
    cells_addrs = [
        AddressCell('B2'),
        AddressCell('Sheet1!B3'),
        AddressRange('Sheet1!B4:B6'),
    ]
    formats = cond_format_ws.eval_conditional_formats(cells_addrs)
    formats2 = cond_format_ws.eval_conditional_formats((a for a in cells_addrs))
    assert formats == list(formats2)
    assert len(formats) == 3
    assert len(formats[2]) == 3

    # read the spreadsheet from yaml
    cond_format_ws.to_file(file_types=('yml', ))
    cond_format_ws_yaml = ExcelCompiler.from_file(
        cond_format_ws.filename + '.yml')
    cells_addrs[0] = AddressCell('Sheet1!B2')
    formats3 = cond_format_ws_yaml.eval_conditional_formats(tuple(cells_addrs))
    assert formats2 == formats3

    # read the spreadsheet from pickle

def test_address_range_and(left, right, result):
    result = AddressRange(result)
    assert AddressRange(left) & AddressRange(right) == result
    assert AddressRange(left) & right == result
    assert left & AddressRange(right) == result

def test_address_cell_addr_inc():

    cell_addr = AddressCell('sh!C2')

    assert MAX_COL - 1 == cell_addr.inc_col(-4)
    assert MAX_COL == cell_addr.inc_col(-3)
    assert 1 == cell_addr.inc_col(-2)
    assert 5 == cell_addr.inc_col(2)
    assert 6 == cell_addr.inc_col(3)

    assert MAX_ROW - 1 == cell_addr.inc_row(-3)
    assert MAX_ROW == cell_addr.inc_row(-2)
    assert 1 == cell_addr.inc_row(-1)
    assert 5 == cell_addr.inc_row(3)
    assert 6 == cell_addr.inc_row(4)

def test_address_pickle(tmpdir):
    addrs = [
        AddressRange('B1'),
        AddressRange('B1:C1'),
        AddressRange('B1:B2'),
        AddressRange('B1:C2'),
        AddressRange('sh!B1'),
        AddressRange('sh!B1:C1'),
        AddressRange('sh!B1:B2'),
        AddressRange('sh!B1:C2'),
        AddressRange('B:C'),
        AddressRange('2:4'),
        AddressCell('sh!XFC1048575'),
        AddressCell('sh!XFD1048576'),
        AddressCell('sh!A1'),
        AddressCell('sh!E5'),
        AddressCell('sh!F6'),
    ]

    filename = os.path.join(str(tmpdir), 'test_addrs.pkl')
    with open(filename, 'wb') as f:
        pickle.dump(addrs, f)

    with open(filename, 'rb') as f:
        new_addrs = pickle.load(f)

    assert addrs == new_addrs

def test_offset(crwh, refer, rows, cols, height, width):
    expected = crwh
    if isinstance(crwh, tuple):
        start = AddressCell((crwh[0], crwh[1], crwh[0], crwh[1]))
        end = AddressCell((crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1,
                           crwh[0] + crwh[2] - 1, crwh[1] + crwh[3] - 1))

        expected = AddressRange.create(
            '{0}:{1}'.format(start.coordinate, end.coordinate))

    result = offset(refer, rows, cols, height, width)
    assert result == expected

    refer_addr = AddressRange.create(refer)
    if height == refer_addr.size.height:
        height = None
    if width == refer_addr.size.width:
        width = None
    assert offset(refer_addr, rows, cols, height, width) == expected