def formatexcel(df1,temp):
df1.iloc[1:,-1:]=temp.iloc[1:,-1:]
sf = StyleFrame(df1)
for col_name in df1.columns[:-2]:
#### Apply Red colour to invalid cells
sf.apply_style_by_indexes(
sf[temp[col_name]==1],cols_to_style=col_name,
styler_obj=Styler(bg_color='red',font_size=8,
number_format=utils.number_formats.general_float,
wrap_text=False))
#### Apply Green colour to valid cells
sf.apply_style_by_indexes(
sf[temp[col_name]==0],cols_to_style=col_name,
styler_obj=Styler(bg_color="#228B22",font_size=8,
number_format=utils.number_formats.general_float,
wrap_text=False))
#### Apply Yellow colour to Operator cells
sf.apply_style_by_indexes(
sf[temp[col_name]==0.0505],cols_to_style=col_name,
styler_obj=Styler(bg_color="yellow",font_size=8,
wrap_text=False))
header_style = Styler(bg_color="#c4c4ff",text_rotation=90,
font_size=8,wrap_text=False)
sf.apply_headers_style(styler_obj=header_style)
sf.set_column_width(columns=sf.columns, width=10)
sf.to_excel("Output "+str(int(time.time()))+".xlsx").save()
return True
def change_coloring(df, path):
sf = StyleFrame(df)
sf.apply_column_style(cols_to_style=sf.columns,
styler_obj=Styler(font_size=11,
font=utils.fonts.calibri))
sf.apply_headers_style(
styler_obj=Styler(bold=True, font_size=12, font=utils.fonts.calibri))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Freezing'] > 6],
cols_to_style=['Pre Freezing'],
styler_obj=Styler(bg_color=utils.colors.blue,
font_size=11,
font_color=utils.colors.white))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Duration'] >= 1],
cols_to_style=['Duration'],
styler_obj=Styler(bg_color=utils.colors.yellow,
font_size=11))
ew = StyleFrame.ExcelWriter(path)
sf.to_excel(ew)
ew.save()
def change_coloring(df, path):
sf = StyleFrame(df)
sf.apply_column_style(cols_to_style=sf.columns, styler_obj=Styler(font_size=11, font=utils.fonts.calibri))
sf.apply_headers_style(styler_obj=Styler(bold=True, font_size=12, font=utils.fonts.calibri))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Hidden'] > 6], cols_to_style=['Pre Hidden'], styler_obj=Styler(bg_color=utils.colors.blue, font_size=11, font_color=utils.colors.white))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Center'] > 6], cols_to_style=['Pre Center'],
styler_obj=Styler(bg_color=utils.colors.blue, font_size=11,
font_color=utils.colors.white))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Approach'] > 6], cols_to_style=['Pre Approach'],
styler_obj=Styler(bg_color=utils.colors.blue, font_size=11,
font_color=utils.colors.white))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Hidden'] > 6], cols_to_style=['Hidden Start Time'],
styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Center'] > 6], cols_to_style=['Center Start Time'],
styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['Pre Approach'] > 6], cols_to_style=['Approach Start Time'],
styler_obj=Styler(bg_color=utils.colors.yellow, font_size=11))
ew = StyleFrame.ExcelWriter(path)
sf.to_excel(ew)
ew.save()
def main():
response_sheet_1 = "data/sheet_1_resp.xlsx"
key_sheet_1 = "data/sheet_1_key.xlsx"
response_sheet_2 = "data/sheet_2_resp.xlsx"
key_sheet_2 = "data/sheet_2_key.xlsx"
response_data_1, key_data_1 = readData(response_sheet_1, key_sheet_1)
response_data_2, key_data_2 = readData(response_sheet_2, key_sheet_2)
response_data_1 = response_data_1.sort_values('OMR No.',
ascending=True,
na_position='first')
response_data_1 = response_data_1.drop('Timestamp', axis=1)
response_data_2 = response_data_2.sort_values('OMR No.',
ascending=True,
na_position='first')
response_data_2 = response_data_2.drop(
['Timestamp', 'NAME OF THE STUDENT'], axis=1)
number_colums_sheet_1 = len(response_data_1)
number_colums_sheet_2 = len(response_data_2)
# print(type(key_data_1))
# print(type(key_data_2))
# #TEST COL Sheet 2
sheet2_data = response_data_2.iloc[0]
unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2 = StudentNullAnalysis_sheet2(
sheet2_data)
QuestionsAnalysis_sheet2(sheet2_data, key_data_2, unanswered_phy_sheet2,
unanswered_chem_sheet2, unanswered_math_sheet2,
total_unanswered_sheet2)
#TEST Col
rowData = response_data_1.iloc[1]
unanswered_phy, unanswered_chem, unanswered_math, total_unanswered = StudentNullAnalysis_sheet1(
rowData)
studentName, physics_total, chem_total, math_total, total = QuestionsAnalysis_sheet1(
rowData, key_data_1, unanswered_phy, unanswered_chem, unanswered_math,
total_unanswered)
# print(studentName)
result_sheet_1 = []
result_sheet_2 = []
for i in range(number_colums_sheet_1):
rowData = response_data_1.iloc[i]
unanswered_phy, unanswered_chem, unanswered_math, total_unanswered = StudentNullAnalysis_sheet1(
rowData)
studentName, physics_total, chem_total, math_total, total = QuestionsAnalysis_sheet1(
rowData, key_data_1, unanswered_phy, unanswered_chem,
unanswered_math, total_unanswered)
result_sheet_1.append([
studentName, physics_total, chem_total, math_total, total,
unanswered_phy, unanswered_chem, unanswered_math, total_unanswered
])
resultdf = pd.DataFrame(result_sheet_1,
columns=[
'Student Name', 'Physics Total',
'Chemistry Total', 'Mathematics Total',
'Total', 'Unanswered Physics',
'Unanswered Chemistry', 'Unanswered Math',
'Total Unanswered Questions'
])
for i in range(number_colums_sheet_2):
sheet2_data = response_data_2.iloc[i]
unanswered_phy_sheet2, unanswered_chem_sheet2, unanswered_math_sheet2, total_unanswered_sheet2 = StudentNullAnalysis_sheet2(
sheet2_data)
studentName_2, physics_total_2, chem_total_2, math_total_2, total_2 = QuestionsAnalysis_sheet2(
sheet2_data, key_data_2, unanswered_phy_sheet2,
unanswered_chem_sheet2, unanswered_math_sheet2,
total_unanswered_sheet2)
result_sheet_2.append([
studentName_2, physics_total_2, chem_total_2, math_total_2,
total_2, unanswered_phy_sheet2, unanswered_chem_sheet2,
unanswered_math_sheet2, total_unanswered_sheet2
])
resultdf_2 = pd.DataFrame(result_sheet_2,
columns=[
'Student Name', 'Physics Total',
'Chemistry Total', 'Mathematics Total',
'Total', 'Unanswered Physics',
'Unanswered Chemistry',
'Unanswered Math',
'Total Unanswered Questions'
])
print(resultdf)
print(resultdf_2)
resultdf.to_csv(index=False)
resultdf.to_excel("Result_sheet1.xlsx",
sheet_name='Result_sheet1',
engine='openpyxl',
index=False)
resultdf_2.to_csv(index=False)
resultdf_2.to_excel("Result_sheet2.xlsx",
sheet_name='Result_sheet2',
engine='openpyxl',
index=False)
excel_writer = StyleFrame.ExcelWriter('Result_sheet1.xlsx')
sf = StyleFrame(resultdf)
styler = Styler(font_size=15, vertical_alignment='distributed')
sf.apply_headers_style(styler, style_index_header=True)
sf.set_column_width('Student Name', 40)
sf.set_column_width(
['Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total'], 20)
sf.set_column_width([
'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math',
'Total Unanswered Questions'
], 30)
sf.to_excel(excel_writer=excel_writer, columns_and_rows_to_freeze='B2')
excel_writer.save()
excel_writer = StyleFrame.ExcelWriter('Result_sheet2.xlsx')
sf = StyleFrame(resultdf_2)
styler = Styler(font_size=15, vertical_alignment='distributed')
sf.apply_headers_style(styler, style_index_header=True)
sf.set_column_width('Student Name', 40)
sf.set_column_width(
['Physics Total', 'Chemistry Total', 'Mathematics Total', 'Total'], 20)
sf.set_column_width([
'Unanswered Physics', 'Unanswered Chemistry', 'Unanswered Math',
'Total Unanswered Questions'
], 30)
sf.to_excel(excel_writer=excel_writer)
excel_writer.save()
def reports(request):
if request.method == POST_METHOD:
print(request.POST)
query_dict_str = str(request.POST)
if "Vendor" in query_dict_str:
query_dict_str = query_dict_str.replace("Vendor", "VendorName")
query_dict_str = query_dict_str.replace('<QueryDict:',
'').replace('>', '')
query_dict_str = query_dict_str.replace('\\n', '')
filter_dict = eval(query_dict_str)
# Reading master file
start_time = time.time()
EmployeeMaster = read_main_file()
t1 = time.time() - start_time
print("Emp Read")
print(t1)
# Reading Frequency value
start_time = time.time()
frequency = return_frequency(filter_dict)
print(time.time() - start_time)
# Preparing Date list
start_time = time.time()
date_value_list = return_date_list(frequency, filter_dict)
print(time.time() - start_time)
# Reading Report Type
start_time = time.time()
report_type = return_report_type(filter_dict)
print(time.time() - start_time)
# Returning filtered dataframe based on dimensions
start_time = time.time()
filered_df = filtered_dataframe(EmployeeMaster, filter_dict)
print(time.time() - start_time)
# Returning final table
final_dataframe = return_final_table(filered_df, date_value_list,
report_type, frequency)
# Dataframe post processing
final_dataframe["VendorName"] = final_dataframe[
"VendorName"].str.upper().str.title()
final_dataframe.style.set_properties(subset=["VendorName"],
**{'text-align': 'left'})
collist = final_dataframe.columns.tolist()
if report_type != "Attrition rate":
# if frequency == "MTD":
final_dataframe[collist[1:]] = final_dataframe[collist[1:]].astype(
int)
if frequency == "MTD" or frequency == "Quarterly YTD" or frequency == "Annually":
if report_type != "Net Addition":
df_dict = {}
from datetime import datetime
for col in collist[1:]:
date_object = datetime.strptime(col, '%Y-%m-%d')
df_dict[col] = date_object.strftime('%b %y')
for col in final_dataframe.columns:
for name in df_dict:
if name == col:
final_dataframe = final_dataframe.rename(
columns={col: df_dict[name]})
final_dataframe = final_dataframe.append(
final_dataframe.sum(numeric_only=True), ignore_index=True)
final_dataframe['VendorName'] = final_dataframe[
'VendorName'].fillna('Total')
else:
is_NaN = final_dataframe.isnull()
row_has_NaN = is_NaN.any(axis=1)
final_dataframe = final_dataframe[row_has_NaN]
final_dataframe['VendorName'] = final_dataframe[
'VendorName'].replace(np.nan, "Net Addition")
final_dataframe.rename(columns={'VendorName': "Report Type"},
inplace=True)
df_dict = {}
from datetime import datetime
for col in collist[1:]:
date_object = datetime.strptime(col, '%Y-%m-%d')
df_dict[col] = date_object.strftime('%b %y')
for col in final_dataframe.columns:
for name in df_dict:
if name == col:
final_dataframe = final_dataframe.rename(
columns={col: df_dict[name]})
end_time = time.time()
print("------------------------")
print("Time Taken by the engine:")
print(end_time - start_time)
print("------------------------")
# print(final_dataframe[:4])
default_style = Styler(font=utils.fonts.calibri,
font_size=11,
fill_pattern_type=None,
border_type=None)
sf = StyleFrame(final_dataframe, styler_obj=default_style)
sf.apply_column_style(
cols_to_style=list(final_dataframe.columns)[:1],
styler_obj=Styler(
horizontal_alignment=utils.horizontal_alignments.left,
font=utils.fonts.calibri,
font_size=11,
border_type=None,
fill_pattern_type=None,
bg_color='#808080'),
style_header=True)
header_style = Styler(
bold=True,
bg_color='#808080',
horizontal_alignment=utils.horizontal_alignments.left,
font_color=utils.colors.white,
font=utils.fonts.calibri,
font_size=11,
border_type=None)
sf.apply_headers_style(styler_obj=header_style)
sf.set_column_width(columns=list(final_dataframe.columns)[:1],
width=45)
df_file = sf.to_excel('static/df_to_excel/final_output.xlsx',
index=False).save()
return render(request, TABLE_HTML, {
'data_frame': final_dataframe,
'df_file': df_file
})
field_list = [
EMPLOYEES, VENDORS, STATES, LOCATIONS, GENDERS, TEAMS, FUNCTIONS,
REPORT_TYPES, FREQUENCIES, DIMENSIONS, CITIES, SUB_TEAMS, REGIONS,
CTC_SLABS, EXIT_TYPES, AGES, EMP_TYPES, TENURES, ENTITIES
]
active_fields = fetch_active_fields2(field_list)
return render(request, REPORT_HTML, active_fields)
"Course": courseNames,
"Grade (out of 100%)": finalGrades
})
#define default style
default_style = Styler(font_size=12)
#create a new StyleFrame with excel dataframe using default style
overviewSf = StyleFrame(overviewDf, styler_obj=default_style)
#apply header style
header_style = Styler(bg_color=utils.colors.black,
font_color=utils.colors.white,
bold=True,
font_size=18,
shrink_to_fit=False)
overviewSf.apply_headers_style(styler_obj=header_style)
#apply style for GPA cell
finalGradeStyle = Styler(bg_color=utils.colors.grey,
bold=True,
font_size=16,
horizontal_alignment=utils.horizontal_alignments.left)
overviewSf.apply_style_by_indexes(
indexes_to_style=overviewSf[overviewSf['Course'] == 'GPA'],
overwrite_default_style=False,
styler_obj=finalGradeStyle)
#write GPA and course grades to sheet
overviewSf.to_excel(writer,
sheet_name="Overview",
best_fit=("Course", "Grade (out of 100%)"))