def test_add_local_named_range(tmpdir):
tmpdir.chdir()
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
named_range.scope = new_sheet
wb.add_named_range(named_range)
dest_filename = 'local_named_range_book.xlsx'
wb.save(dest_filename)
def test_add_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
wb.add_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range in named_ranges_list
def test_get_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
wb.add_named_range(named_range)
found_named_range = wb.get_named_range('test_nr')
assert named_range == found_named_range
def test_write_named_range():
from openpyxl.writer.workbook import _write_defined_names
wb = Workbook()
ws = wb.active
xlrange = NamedRange('test_range', [(ws, "A1:B5")])
wb._named_ranges.append(xlrange)
root = Element("root")
_write_defined_names(wb, root)
xml = tostring(root)
expected = """
<root>
<s:definedName xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="test_range">'Sheet'!$A$1:$B$5</s:definedName>
</root>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def read_named_ranges(xml_source, workbook):
"""Read named ranges, excluding poorly defined ranges."""
sheetnames = set(sheet.title for sheet in workbook.worksheets)
root = fromstring(xml_source)
for name_node in safe_iterator(root, '{%s}definedName' % SHEET_MAIN_NS):
range_name = name_node.get('name')
if DISCARDED_RANGES.search(range_name) or BUGGY_NAMED_RANGES.search(
range_name):
continue
node_text = name_node.text
if external_range(node_text):
# treat names referring to external workbooks as values
named_range = NamedValue(range_name, node_text)
elif refers_to_range(node_text):
destinations = split_named_range(node_text)
# it can happen that a valid named range references
# a missing worksheet, when Excel didn't properly maintain
# the named range list
destinations = [(workbook[sheet], cells)
for sheet, cells in destinations
if sheet in sheetnames]
if not destinations:
continue
named_range = NamedRange(range_name, destinations)
else:
named_range = NamedValue(range_name, node_text)
location_id = name_node.get("localSheetId")
if location_id is not None:
named_range.scope = workbook.worksheets[int(location_id)]
yield named_range