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 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')
