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')
eq_(named_range, found_named_range)
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_add_local_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = NamedRange('test_nr', [(new_sheet, 'A1')])
named_range.local_only = True
wb.add_named_range(named_range)
dest_filename = osp.join(TMPDIR, 'local_named_range_book.xlsx')
wb.save(dest_filename)
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 read_named_ranges(xml_source, workbook):
"""Read named ranges, excluding poorly defined ranges."""
named_ranges = []
root = fromstring(xml_source)
names_root = root.find(
QName('http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'definedNames').text)
if names_root is not None:
for name_node in names_root.getchildren():
range_name = name_node.get('name')
if name_node.get("hidden", '0') == '1':
continue
valid = True
for discarded_range in DISCARDED_RANGES:
if discarded_range in range_name:
valid = False
for bad_range in BUGGY_NAMED_RANGES:
if bad_range in name_node.text:
valid = False
if valid:
if refers_to_range(name_node.text):
destinations = split_named_range(name_node.text)
new_destinations = []
for worksheet, cells_range in destinations:
# it can happen that a valid named range references
# a missing worksheet, when Excel didn't properly maintain
# the named range list
#
# we just ignore them here
worksheet = workbook.get_sheet_by_name(worksheet)
if worksheet:
new_destinations.append((worksheet, cells_range))
named_range = NamedRange(range_name, new_destinations)
else:
named_range = NamedRangeContainingValue(
range_name, name_node.text)
location_id = name_node.get("localSheetId")
if location_id:
named_range.scope = workbook.worksheets[int(location_id)]
named_ranges.append(named_range)
return named_ranges
def read_named_ranges(xml_source, workbook):
"""Read named ranges, excluding poorly defined ranges."""
root = fromstring(xml_source)
names_root = root.find('{%s}definedNames' % SHEET_MAIN_NS)
existing_sheets = workbook.get_sheet_names()
if names_root is not None:
for name_node in names_root:
range_name = name_node.get('name')
node_text = name_node.text or ''
if bool(name_node.get("hidden", False)):
continue
if DISCARDED_RANGES.search(
range_name) or BUGGY_NAMED_RANGES.search(range_name):
continue
if refers_to_range(node_text):
destinations = split_named_range(node_text)
new_destinations = []
for worksheet_name, cells_range in destinations:
# it can happen that a valid named range references
# a missing worksheet, when Excel didn't properly maintain
# the named range list
#
# we just ignore them here
if worksheet_name in existing_sheets:
worksheet = workbook[worksheet_name]
new_destinations.append((worksheet, cells_range))
if not new_destinations:
continue
named_range = NamedRange(range_name, new_destinations)
else:
named_range = NamedRangeContainingValue(range_name, node_text)
location_id = name_node.get("localSheetId")
if location_id:
named_range.scope = workbook.worksheets[int(location_id)]
yield named_range
def create_named_range(self, name, worksheet, range, scope=None):
"""Create a new named_range on a worksheet"""
if not isinstance(worksheet, self._worksheet_class):
raise TypeError("Worksheet is not of the right type")
named_range = NamedRange(name, [(worksheet, range)], scope)
self.add_named_range(named_range)
def create_named_range(self, name, worksheet, range):
"""Create a new named_range on a worksheet"""
assert isinstance(worksheet, Worksheet)
named_range = NamedRange(name, [(worksheet, range)])
self.add_named_range(named_range)