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