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(): eq_("$ZF$51:$ZF$53", absolute_coordinate("ZF51:ZF$53"))
def test_absolute(): eq_("$ZF$51", absolute_coordinate("ZF51"))
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)