def create_named_range(self, name, worksheet=None, value=None, scope=None):
"""Create a new named_range on a worksheet"""
defn = DefinedName(name=name, localSheetId=scope)
if worksheet is not None:
defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title),
value)
else:
defn.value = value
self.defined_names.append(defn)
def test_destinations(self, DefinedName, value, destinations):
defn = DefinedName(name="some")
defn.value = value
assert defn.type == "RANGE"
des = tuple(defn.destinations)
assert des == destinations
def test_add_named_range():
wb = Workbook()
new_sheet = wb.create_sheet()
named_range = DefinedName('test_nr')
named_range.value = "Sheet!A1"
wb.add_named_range(named_range)
named_ranges_list = wb.get_named_ranges()
assert named_range in named_ranges_list
def test_write(self, DefinedName):
defn = DefinedName(name="pi", )
defn.value = 3.14
xml = tostring(defn.to_tree())
expected = """
<definedName name="pi">3.14</definedName>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def test_unpack_print_titles(DefinedName, value, expected):
from openpyxl25.workbook.defined_name import _unpack_print_titles
defn = DefinedName(name="Print_Titles")
defn.value = value
assert _unpack_print_titles(defn) == expected
def test_external_range(self, DefinedName, value, expected):
defn = DefinedName(name="test")
defn.value = value
assert defn.is_external is expected
def test_check_type(self, DefinedName, value, expected):
defn = DefinedName(name="test")
defn.value = value
assert defn.type == expected
def test_unpack_print_area(DefinedName, value, expected):
from openpyxl25.workbook.defined_name import _unpack_print_area
defn = DefinedName(name="Print_Area")
defn.value = value
assert _unpack_print_area(defn) == expected
def write_workbook(workbook):
"""Write the core workbook xml."""
wb = workbook
wb.rels = RelationshipList()
root = WorkbookPackage()
props = WorkbookProperties() # needs a mapping to the workbook for preservation
if wb.code_name is not None:
props.codeName = wb.code_name
if wb.excel_base_date == CALENDAR_MAC_1904:
props.date1904 = True
root.workbookPr = props
# workbook protection
root.workbookProtection = wb.security
# book views
active = get_active_sheet(wb)
wb.views[0].activeTab = active
root.bookViews = wb.views
# worksheets
for idx, sheet in enumerate(wb._sheets, 1):
sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx))
rel = Relationship(type=sheet._rel_type, Target=sheet.path)
wb.rels.append(rel)
if not sheet.sheet_state == 'visible':
if len(wb._sheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.state = sheet.sheet_state
root.sheets.append(sheet_node)
# external references
for link in wb._external_links:
# need to match a counter with a workbook's relations
rId = len(wb.rels) + 1
rel = Relationship(type=link._rel_type, Target=link.path)
wb.rels.append(rel)
ext = ExternalReference(id=rel.id)
root.externalReferences.append(ext)
# Defined names
defined_names = copy(wb.defined_names) # don't add special defns to workbook itself.
# Defined names -> autoFilter
for idx, sheet in enumerate(wb.worksheets):
auto_filter = sheet.auto_filter.ref
if auto_filter:
name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True)
name.value = u"{0}!{1}".format(quote_sheetname(sheet.title),
absolute_coordinate(auto_filter)
)
defined_names.append(name)
# print titles
if sheet.print_titles:
name = DefinedName(name="Print_Titles", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_titles.split(",")])
defined_names.append(name)
# print areas
if sheet.print_area:
name = DefinedName(name="Print_Area", localSheetId=idx)
name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r)
for r in sheet.print_area])
defined_names.append(name)
root.definedNames = defined_names
# pivots
pivot_caches = set()
for pivot in wb._pivots:
if pivot.cache not in pivot_caches:
pivot_caches.add(pivot.cache)
c = PivotCache(cacheId=pivot.cacheId)
root.pivotCaches.append(c)
rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path)
wb.rels.append(rel)
c.id = rel.id
wb._pivots = [] # reset
root.calcPr = wb.calculation
return tostring(root.to_tree())