def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4',
'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'})
book_views = SubElement(root, 'bookViews')
SubElement(book_views, 'workbookView', {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
'showHorizontalScroll': '1', 'showSheetTabs': '1',
'showVerticalScroll': '1', 'tabRatio': '600',
'visibility': 'visible'})
# worksheets
sheets = SubElement(root, 'sheets')
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title,
'sheetId': '%d' % (i + 1), 'r:id': 'rId%d' % (i + 1)})
if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
sheet_node.set('state', sheet.sheet_state)
# Defined names
defined_names = SubElement(root, 'definedNames')
# named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, 'definedName',
{'name': named_range.name})
if named_range.scope:
name.set('localSheetId', '%s' % workbook.get_index(named_range.scope))
if isinstance(named_range, NamedRange):
# as there can be many cells in one range, generate the list of ranges
dest_cells = []
for worksheet, range_name in named_range.destinations:
dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"),
absolute_coordinate(range_name)))
# finally write the cells list
name.text = ','.join(dest_cells)
else:
assert isinstance(named_range, NamedRangeContainingValue)
name.text = named_range.value
# autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter
if not auto_filter:
continue
name = SubElement(defined_names, 'definedName',
dict(name='_xlnm._FilterDatabase',
localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
'fullCalcOnLoad': '1'})
return get_document_content(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4',
'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'})
book_views = SubElement(root, 'bookViews')
SubElement(book_views, 'workbookView', {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
'showHorizontalScroll': '1', 'showSheetTabs': '1',
'showVerticalScroll': '1', 'tabRatio': '600',
'visibility': 'visible'})
# worksheets
sheets = SubElement(root, 'sheets')
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title,
'sheetId': '%d' % (i + 1), 'r:id': 'rId%d' % (i + 1)})
if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
sheet_node.set('state', sheet.sheet_state)
# Defined names
defined_names = SubElement(root, 'definedNames')
# named ranges
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, 'definedName',
{'name': named_range.name})
if named_range.scope:
name.set('localSheetId', '%s' % workbook.get_index(named_range.scope))
if isinstance(named_range, NamedRange):
# as there can be many cells in one range, generate the list of ranges
dest_cells = []
for worksheet, range_name in named_range.destinations:
dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"),
absolute_coordinate(range_name)))
# finally write the cells list
name.text = ','.join(dest_cells)
else:
assert isinstance(named_range, NamedRangeContainingValue)
name.text = named_range.value
# autoFilter
for i, sheet in enumerate(workbook.worksheets):
#continue
auto_filter = sheet.auto_filter
if not auto_filter:
continue
name = SubElement(defined_names, 'definedName',
dict(name='_xlnm._FilterDatabase',
localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
'fullCalcOnLoad': '1'})
return get_document_content(root)
def value(self):
dest_cells = []
for ws, xlrange in self.destinations:
dest_cells.append(
"'%s'!%s" %
(ws.title.replace("'", "''"), absolute_coordinate(xlrange)))
return ",".join(dest_cells)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(root, '{%s}fileVersion' % SHEET_MAIN_NS,
{'appName': 'xl', 'lastEdited': '4', 'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS,
{'defaultThemeVersion': '124226', 'codeName': 'ThisWorkbook'})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(book_views, '{%s}workbookView' % SHEET_MAIN_NS,
{'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
'showHorizontalScroll': '1', 'showSheetTabs': '1',
'showVerticalScroll': '1', 'tabRatio': '600',
'visibility': 'visible'})
# worksheets
sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS,
{'name': sheet.title, 'sheetId': '%d' % i,
'{%s}id' % REL_NS: 'rId%d' % i })
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError("The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter+1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS, {"{%s}id" % REL_NS:"rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i), hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace("'", "''"),
absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS,
{'calcId': '124519', 'calcMode': 'auto', 'fullCalcOnLoad': '1'})
return tostring(root)
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('workbook', {'xmlns': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
'xml:space': 'preserve', 'xmlns:r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'})
SubElement(root, 'fileVersion', {'appName': 'xl', 'lastEdited': '4',
'lowestEdited': '4', 'rupBuild': '4505'})
SubElement(root, 'workbookPr', {'defaultThemeVersion': '124226',
'codeName': 'ThisWorkbook'})
book_views = SubElement(root, 'bookViews')
SubElement(book_views, 'workbookView', {'activeTab': '%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1', 'firstSheet': '0', 'minimized': '0',
'showHorizontalScroll': '1', 'showSheetTabs': '1',
'showVerticalScroll': '1', 'tabRatio': '600',
'visibility': 'visible'})
# worksheets
sheets = SubElement(root, 'sheets')
for i, sheet in enumerate(workbook.worksheets):
sheet_node = SubElement(sheets, 'sheet', {'name': sheet.title,
'sheetId': '%d' % (i + 1), 'r:id': 'rId%d' % (i + 1)})
if not sheet.sheet_state == sheet.SHEETSTATE_VISIBLE:
sheet_node.set('state', sheet.sheet_state)
# named ranges
defined_names = SubElement(root, 'definedNames')
for named_range in workbook.get_named_ranges():
name = SubElement(defined_names, 'definedName',
{'name': named_range.name})
# as there can be many cells in one range, generate the list of ranges
dest_cells = []
cell_ids = []
for worksheet, range_name in named_range.destinations:
cell_ids.append(workbook.get_index(worksheet))
dest_cells.append("'%s'!%s" % (worksheet.title.replace("'", "''"),
absolute_coordinate(range_name)))
# for local ranges, we must check all the cells belong to the same sheet
base_id = cell_ids[0]
if named_range.local_only and all([x == base_id for x in cell_ids]):
name.set('localSheetId', '%s' % base_id)
# finally write the cells list
name.text = ','.join(dest_cells)
SubElement(root, 'calcPr', {'calcId': '124519', 'calcMode': 'auto',
'fullCalcOnLoad': '1'})
return get_document_content(root)
def value(self):
dest_cells = []
for ws, xlrange in self.destinations:
dest_cells.append("'%s'!%s" % (ws.title.replace("'", "''"),
absolute_coordinate(xlrange)))
return ",".join(dest_cells)
def test_absolute_multiple():
assert '$ZF$51:$ZF$53' == absolute_coordinate('ZF51:ZF$53')
def test_absolute():
assert '$ZF$51' == absolute_coordinate('ZF51')
def __export_to_xlsx_test_cases(template, ws, features, row_index):
scenario_status_locator = {}
style_attrs = ("borders", "font", "fill", "alignment", "number_format")
fields = {
"feature": 1,
"scenario": 2,
"steps": 3,
"empty1": 4,
"empty2": 5,
"automatic": 6,
"status": 7,
"duration": 8,
"tester": 9
}
row_index = __copy_template_table__(template, ws, row_index, "Test Cases")
model_row_index = row_index
# Fill values
for feature in features:
# Get info
values = dict()
for field in fields.keys():
values[field] = 0
values['feature'] = feature['name']
# Get first automatic cases
scenarios_auto = []
scenarios_manual = []
for scenario in feature['scenarios']:
if ("MANUAL" in scenario['tags']):
scenarios_manual.append(scenario)
else:
scenarios_auto.append(scenario)
scenarios = scenarios_auto + scenarios_manual
for scenario in scenarios:
values['scenario'] = scenario['name']
values['status'] = scenario['status'].capitalize()
values['duration'] = scenario['duration']
if ("MANUAL" in scenario['tags']):
values['automatic'] = "Manual"
values['tester'] = ''
else:
values['automatic'] = "Automatic"
values['tester'] = "Automatic"
values['steps'] = ''
for step in scenario['steps']:
fail_string = ""
if step['status'] == "failed":
error_msg, error_lines = gather_errors(scenario)
fail_string = " (FAILED!)\n"
if error_msg:
fail_string += "-" + error_msg + "\n"
for line in error_lines:
fail_string = fail_string + "-" + line + "\n"
values['steps'] = values[
'steps'] + step['step_type'].capitalize() + ' ' \
+ step['name'] + fail_string + '\n'
if 'table' in step:
table_length = 0
for key in step['table']:
values['steps'] = values['steps'] + '|' + key
table_length = len(step['table'][key])
values['steps'] += '|\n'
for i in range(table_length):
for key in step['table']:
values['steps'] += '|' + step['table'][key][i]
values['steps'] += '|\n'
# Fill info
for field in fields.keys():
model_cell = ws.cell(row=model_row_index - 1,
column=fields[field] - 1)
cell = ws.cell(row=row_index - 1, column=fields[field] - 1)
if field == 'status':
scenario_status_locator[values['scenario']] = \
Cell.absolute_coordinate(cell.get_coordinate())
for attr in style_attrs:
setattr(cell.style, attr, getattr(model_cell.style, attr))
cell.value = values[field]
# Advance row
row_index += 1
# Return next free row
return row_index, scenario_status_locator
def test_absolute_multiple():
eq_('$ZF$51:$ZF$53', absolute_coordinate('ZF51:ZF$53'))
def test_absolute():
eq_('$ZF$51', absolute_coordinate('ZF51'))
def test_absolute_multiple():
assert '$ZF$51:$ZF$53' == absolute_coordinate('ZF51:ZF$53')
def test_absolute():
assert '$ZF$51' == absolute_coordinate('ZF51')
def test_absolute_multiple():
eq_("$ZF$51:$ZF$53", absolute_coordinate("ZF51:ZF$53"))
def test_absolute():
eq_("$ZF$51", absolute_coordinate("ZF51"))
def test_absolute():
eq_('$ZF$51', absolute_coordinate('ZF51'))
def test_absolute_multiple():
eq_('$ZF$51:$ZF$53', absolute_coordinate('ZF51:ZF$53'))
def write_workbook(workbook):
"""Write the core workbook xml."""
root = Element('{%s}workbook' % SHEET_MAIN_NS)
SubElement(
root, '{%s}fileVersion' % SHEET_MAIN_NS, {
'appName': 'xl',
'lastEdited': '4',
'lowestEdited': '4',
'rupBuild': '4505'
})
SubElement(root, '{%s}workbookPr' % SHEET_MAIN_NS, {
'defaultThemeVersion': '124226',
'codeName': workbook.code_name
})
# book views
book_views = SubElement(root, '{%s}bookViews' % SHEET_MAIN_NS)
SubElement(
book_views, '{%s}workbookView' % SHEET_MAIN_NS, {
'activeTab':
'%d' % workbook.get_index(workbook.get_active_sheet()),
'autoFilterDateGrouping': '1',
'firstSheet': '0',
'minimized': '0',
'showHorizontalScroll': '1',
'showSheetTabs': '1',
'showVerticalScroll': '1',
'tabRatio': '600',
'visibility': 'visible'
})
# worksheets
sheets = SubElement(root, '{%s}sheets' % SHEET_MAIN_NS)
for i, sheet in enumerate(workbook.worksheets, 1):
sheet_node = SubElement(
sheets, '{%s}sheet' % SHEET_MAIN_NS, {
'name': sheet.title,
'sheetId': '%d' % i,
'{%s}id' % REL_NS: 'rId%d' % i
})
if not sheet.sheet_state == Worksheet.SHEETSTATE_VISIBLE:
if len(workbook.worksheets) == 1:
raise ValueError(
"The only worksheet of a workbook cannot be hidden")
sheet_node.set('state', sheet.sheet_state)
# external references
if getattr(workbook, '_external_links', []):
external_references = SubElement(
root, '{%s}externalReferences' % SHEET_MAIN_NS)
# need to match a counter with a workbook's relations
counter = len(workbook.worksheets) + 3 # strings, styles, theme
if workbook.vba_archive:
counter += 1
for idx, _ in enumerate(workbook._external_links, counter + 1):
ext = Element("{%s}externalReference" % SHEET_MAIN_NS,
{"{%s}id" % REL_NS: "rId%d" % idx})
external_references.append(ext)
# Defined names
defined_names = SubElement(root, '{%s}definedNames' % SHEET_MAIN_NS)
_write_defined_names(workbook, defined_names)
# Defined names -> autoFilter
for i, sheet in enumerate(workbook.worksheets):
auto_filter = sheet.auto_filter.ref
if not auto_filter:
continue
name = SubElement(
defined_names, '{%s}definedName' % SHEET_MAIN_NS,
dict(name='_xlnm._FilterDatabase', localSheetId=str(i),
hidden='1'))
name.text = "'%s'!%s" % (sheet.title.replace(
"'", "''"), absolute_coordinate(auto_filter))
SubElement(root, '{%s}calcPr' % SHEET_MAIN_NS, {
'calcId': '124519',
'calcMode': 'auto',
'fullCalcOnLoad': '1'
})
return tostring(root)
