def test_add(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) data = OrderedDict({"Row 5": [10, 11, 12]}) s1 = s.row + data assert s1.row["Row 5"] == [10, 11, 12] assert_not_in("Row 5", s.row)
def test_formatter_by_named_row2(self): """Test a list of string as index""" s = Sheet(self.data, "test") s.name_rows_by_column(0) s.row.format(["Row 1", "Row 2"], str) assert s.row["Row 1"] == ["1", "2", "3"] assert s.row["Row 2"] == ["4", "5", "6"]
def collate_wb_to_sheet(wb=()): headers = ['row_names'] + wb[0].colnames sheet_to_replace_wb = Sheet(colnames=headers) unique_records = UniqueDict() for sheet in wb: for i, name in enumerate(sheet.rownames): unique_records[name] = sheet.row_at(i) for rec in sorted(unique_records.keys()): sheet_to_replace_wb.row += [rec] + unique_records[rec] sheet_to_replace_wb.name_rows_by_column(0) return sheet_to_replace_wb
def test_top_left_with_colnames_and_rownames(self): data = [ ["", "col 1", "col 2", "col 3", "col 4", "col 5", "col 6"], ["row 1", 1, 2, 3, 4, 5, 6], ["row 2", 11, 2, 3, 4, 5, 6], ["row 3", 21, 2, 3, 4, 5, 6], ["row 4", 31, 2, 3, 4, 5, 6], ["row 5", 41, 2, 3, 4, 5, 6], ["row 6", 51, 2, 3, 4, 5, 6], ] s = Sheet(data) s.name_columns_by_row(0) s.name_rows_by_column(0) top_sheet = s.top_left() expected = [["", "col 1", "col 2", "col 3", "col 4", "col 5"], ["row 1", 1, 2, 3, 4, 5], ["row 2", 11, 2, 3, 4, 5], ["row 3", 21, 2, 3, 4, 5], ["row 4", 31, 2, 3, 4, 5], ["row 5", 41, 2, 3, 4, 5]] eq_(top_sheet.array, expected)
def test_delete_indexed_row3(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) del s.row["Row 0", "Row 1"] assert s.number_of_rows() == 2 s.row["Row 1"] # already deleted
def test_delete_indexed_row2(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.delete_named_row_at(2) assert s.number_of_rows() == 3 s.row["Row 2"] # already deleted
def test_delete_named_row(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) del s.row["Row 2"] assert s.number_of_rows() == 3 s.row["Row 2"] # already deleted
def test_add_wrong_type(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s = s.row + "string type" # bang
def test_extend_rows_using_wrong_data_type(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.extend_rows([1, 2])
def test_formatter_by_named_row_2(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.row.format("Row 1", str) assert s.row["Row 1"] == ["1", "2", "3"]
def test_dot_notation(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) eq_(s.row.Row_3, [7, 8, 9])
def test_iadd(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) data = OrderedDict({"Row 5": [10, 11, 12]}) s.row += data assert s.row["Row 5"] == [10, 11, 12]
def test_formatter_by_named_row(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.row.format("Row 1", str) eq_(s.row["Row 1"], ["1", "2", "3"])
def test_set_named_row(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) s.row["Row 2"] = [11, 11, 11] assert s.row["Row 2"] == [11, 11, 11]
def test_row_series_to_dict(self): s = Sheet(self.data, "test") s.name_rows_by_column(0) content = s.to_dict(True) keys = ["Row 0", "Row 1", "Row 2", "Row 3"] assert keys == list(content.keys())
def main(): directory = join(dirname(dirname(abspath(__file__))), 'raw_files') reports = { 'agent_time_card': open_wb(get_book(file_name=join(directory, 'Agent_Time_Card.xlsx'))), 'feature_trace': open_wb(get_book(file_name=join(directory, 'Agent_Realtime_Feature_Trace.xlsx'))) } schedule = create_schedule( get_sheet(file_name=join(r'M:/Help Desk/', 'Schedules for OPS.xlsx'), name_columns_by_row=0, name_rows_by_column=0) ) # Bind read function to respective reports check_time_card = MethodType(read_time_card, reports['agent_time_card']) check_feature_card = MethodType(read_feature_card, reports['feature_trace']) output = Sheet(colnames=['', '% BW', '% Avail', 'Absences', 'Late']) summary = [0, 0, 0] try: for sheet_name, emp_data in in_schedule(schedule): try: tc_data = check_time_card(sheet_name, emp_data) except KeyError: pass else: dnd_time = check_feature_card(sheet_name) output.row += [ '{row_name}'.format( row_name=emp_data.ext ), '{val:.1%}'.format( val=safe_div(dnd_time, tc_data['Duration']) ), '{val:.1%}'.format( val=(1 - safe_div(dnd_time, tc_data['Duration'])) ), VALID_DAYS_IN_MONTH - tc_data['Log Events'], tc_data['Late'] ] summary[0] += dnd_time summary[1] += tc_data['Duration'] summary[2] += safe_div(dnd_time, tc_data['Duration']) else: output.name_rows_by_column(0) print(output) print( '{val:.1%}'.format( val=safe_div(summary[0], summary[1]) ) ) print( '{val:.1%}'.format( val=safe_div(summary[2], 30) ) ) output.save_as(filename=join(dirname(dirname(abspath(__file__))), 'output', 'outfile.xlsx')) except Exception: print(traceback.format_exc()) print('completed life cycle')