def test_apply_column_style_no_override_default_style(self):
# testing some edge cases
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.apply_column_style(cols_to_style='a', styler_obj=0)
with self.assertRaises(KeyError):
self.sf.apply_column_style(cols_to_style='non_existing_col',
styler_obj=Styler())
# actual tests
self.apply_column_style(cols_to_style=['a'],
overwrite_default_style=False)
self.assertTrue(
all([
self.sf.at[index, 'a'].style == Styler.combine(
self.default_styler_obj, self.styler_obj_1)
and self.sf.at[index, 'b'].style == self.default_styler_obj
for index in self.sf.index
]))
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions['A'].width, 10)
# range starts from 2 since we don't want to check the header's style
self.assertTrue(
all(
sheet.cell(row=i, column=1)._style == Styler.combine(
self.default_styler_obj,
self.styler_obj_1).to_openpyxl_style()._style
for i in range(2, len(self.sf))))
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 setUpClass(cls):
cls.cli = CommandLineInterface(TEST_JSON_FILE, TEST_FILENAME)
cls.sheet_1_col_a_style = Styler(bg_color=utils.colors.blue, font_color=utils.colors.yellow).to_openpyxl_style()
cls.sheet_1_col_a_cell_2_style = Styler(bold=True, font=utils.fonts.arial, font_size=30,
font_color=utils.colors.green,
border_type=utils.borders.double).to_openpyxl_style()
cls.sheet_1_col_b_cell_4_style = Styler(bold=True, font=utils.fonts.arial, font_size=16).to_openpyxl_style()
def test_default_grid_invalid_args(self):
with self.assertRaises(ValueError):
Styler(border_type=utils.borders.default_grid, bg_color=utils.colors.yellow)
with self.assertRaises(ValueError):
Styler(border_type=utils.borders.default_grid, fill_pattern_type=utils.fill_pattern_types.light_grid)
with self.assertRaises(ValueError):
Styler(border_type=utils.borders.default_grid, bg_color=utils.colors.yellow,
fill_pattern_type=utils.fill_pattern_types.light_grid)
def test_from_openpyxl_style(self):
styler_obj = Styler(bg_color=utils.colors.yellow, bold=True, font=utils.fonts.david, font_size=16,
font_color=utils.colors.blue, number_format=utils.number_formats.date, protection=True,
underline=utils.underline.double, border_type=utils.borders.double,
horizontal_alignment=utils.horizontal_alignments.center,
vertical_alignment=utils.vertical_alignments.bottom, wrap_text=False, shrink_to_fit=True,
fill_pattern_type=utils.fill_pattern_types.gray0625, indent=1)
self.assertEqual(styler_obj, Styler.from_openpyxl_style(styler_obj.to_openpyxl_style(), []))
def setUpClass(cls):
cls.default_styler_obj = Styler(wrap_text=False)
cls.styler_obj_1 = Styler(
bg_color=utils.colors.blue,
bold=True,
font='Impact',
font_color=utils.colors.yellow,
font_size=20.0,
underline=utils.underline.single,
horizontal_alignment=utils.horizontal_alignments.left,
vertical_alignment=utils.vertical_alignments.center,
comment_author='Author 1',
comment_text='styler_obj_1 comment')
cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow,
comment_author='Author 2',
comment_text='styler_obj_2 comment')
cls.openpy_style_obj_1 = cls.styler_obj_1.to_openpyxl_style()._style
cls.openpy_style_obj_2 = cls.styler_obj_2.to_openpyxl_style()._style
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 write_tax_report_xlsx(xlsx_file, csv_file, tax_report_df):
report_template = StyleFrame.read_excel_as_template(xlsx_file,
tax_report_df,
sheet_name='자료',
engine="openpyxl")
report_p2_info = StyleFrame.read_excel(xlsx_file,
sheet_name='작성안내',
read_style=True,
engine="openpyxl")
report_p3_tax_code = StyleFrame.read_excel(xlsx_file,
sheet_name='양도물건종류별 세율코드',
read_style=True,
engine="openpyxl")
report_p4_stock_type = StyleFrame.read_excel(xlsx_file,
sheet_name='양도물건종류별 주식등종류',
read_style=True,
engine="openpyxl")
report_template.apply_column_style(
cols_to_style=['양도일자', '취득일자'],
styler_obj=Styler(number_format=utils.number_formats.date))
report_template.apply_column_style(
cols_to_style=['양도가액', '취득가액', '필요경비'],
styler_obj=Styler(
number_format=utils.number_formats.thousands_comma_sep))
# 양도세 시트 저장
report_filename = os.path.join(REPORT_FOLDER,
f'Treport_{csv_file.split(".")[0]}.xlsx')
# report_filename = f'Treport_{csv_file.split(".")[0]}.xlsx'
report_writer = StyleFrame.ExcelWriter(report_filename)
report_template.to_excel(excel_writer=report_writer, sheet_name='자료')
report_p2_info.to_excel(excel_writer=report_writer, sheet_name='작성안내')
report_p3_tax_code.to_excel(excel_writer=report_writer,
sheet_name='양도물건종류별 세율코드')
report_p4_stock_type.to_excel(excel_writer=report_writer,
sheet_name='양도물건종류별 주식등종류')
# 양도세 신고서 파일 저장
report_writer.save()
# 양도세 신고서 파일명 변환
return report_filename
def test_style_accessor(self):
sf = StyleFrame({'a': list(range(10))})
sf.apply_style_by_indexes(
sf[sf['a'] % 2 == 0],
styler_obj=Styler(bold=True, bg_color=utils.colors.yellow),
complement_style=Styler(bold=False, font=utils.fonts.calibri))
control_sf = StyleFrame({'a': list(range(0, 10, 2))})
test_sf = StyleFrame(
sf.loc[sf['a'].style.font == utils.fonts.arial].reset_index(
drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
control_sf = StyleFrame({'a': list(range(0, 10, 2))})
test_sf = StyleFrame(
sf.loc[sf['a'].style.bg_color == utils.colors.yellow].reset_index(
drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
control_sf = StyleFrame({'a': list(range(0, 10, 2))})
test_sf = StyleFrame(
sf.loc[(sf['a'].style.bg_color == utils.colors.yellow)
& sf['a'].style.font].reset_index(drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
control_sf = StyleFrame({'a': list(range(1, 10, 2))})
test_sf = StyleFrame(
sf.loc[sf['a'].style.font == utils.fonts.calibri].reset_index(
drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
control_sf = StyleFrame({'a': list(range(1, 10, 2))})
test_sf = StyleFrame(
sf.loc[~sf['a'].style.bold].reset_index(drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
control_sf = StyleFrame({'a': list(range(1, 10, 2))})
test_sf = StyleFrame(
sf.loc[~sf['a'].style.bold
& (sf['a'].style.font == utils.fonts.calibri)].reset_index(
drop=True))
assert_frame_equal(control_sf.data_df, test_sf.data_df)
def setUpClass(cls):
cls.yellow_1 = Styler(bg_color='yellow')
cls.yellow_2 = Styler(bg_color='yellow')
cls.blue = Styler(bg_color='blue')
cls.bold = Styler(bold=True)
cls.underline = Styler(underline='single')
cls.yellow_bold_underline = Styler(bg_color='yellow', bold=True, underline='single')
def create_template(array_valid_element, root_last, sheet_name):
template = []
collection = ''
for element in array_valid_element:
if len(element.attrib) == 1 and 'ref' not in element.attrib:
collection = element.attrib.get('name', '')
else:
if element.tag.endswith('element') and 'ref' not in element.attrib:
template.append([
element.get('name', ''),
root_last + '.' + collection + '.', collection, 'ELEMENT'
])
elif 'ref' not in element.attrib:
if element.attrib.get('name') in [
'DATA_ASOF_TSTMP', 'LAST_ASOF_TSTMP'
]:
template.append([
element.get('name', ''), root_last + '/', collection,
'ATTRIBUTE'
])
else:
template.append([
element.get('name',
''), root_last + '.' + collection + '/',
collection, 'ATTRIBUTE'
])
df = pd.DataFrame(template,
columns=['mdrm', 'xml_path', 'collection', 'type'])
excel_writer = StyleFrame.ExcelWriter('XML_Template.xlsx')
sf = StyleFrame(df).\
apply_column_style(['mdrm', 'xml_path', 'collection', 'type'],
styler_obj=Styler(font_size=11, font_color=None,
horizontal_alignment=utils.horizontal_alignments.left,
wrap_text=False, shrink_to_fit=False))
sf.set_column_width_dict({
'mdrm':
define_length_column(df['mdrm']) + 8,
'xml_path':
define_length_column(df['xml_path']) + 10,
'collection':
define_length_column(df['collection']) + 10,
'type':
define_length_column(df['type']) + 5
})
sf.to_excel(excel_writer=excel_writer, sheet_name=sheet_name)
excel_writer.save()
def test_read_excel_with_style_openpyxl_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME,
read_style=True,
use_openpyxl_styles=True)
# making sure content is the same
self.assertTrue(
all(
list(self.sf[col]) == list(sf_from_excel[col])
for col in self.sf.columns))
rows_in_excel = sf_from_excel.data_df.itertuples()
rows_in_self = self.sf.data_df.itertuples()
# making sure styles are the same
self.assertTrue(
all(self_cell.style == Styler.from_openpyxl_style(
excel_cell.style, []) for row_in_excel, row_in_self in zip(
rows_in_excel, rows_in_self) for excel_cell, self_cell in
zip(row_in_excel[1:], row_in_self[1:])))
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 test_read_excel_with_style_header_arg_none(self):
self.sf = StyleFrame({0: ['A1', 'A2', 'A3', 'A4', 'A5']})
self.sf.apply_style_by_indexes(
self.sf[self.sf.data_df[0].isin(('A2', 'A5'))], Styler(bold=True))
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME,
read_style=True,
header=None)
# making sure content is the same
self.assertTrue(
all(
list(self.sf[col]) == list(sf_from_excel[col])[1:]
for col in self.sf.columns))
rows_in_excel = list(sf_from_excel.data_df.itertuples())
rows_in_self = self.sf.data_df.itertuples()
# making sure styles are the same
self.assertTrue(
all(excel_cell.style == self_cell.style for row_in_excel,
row_in_self in zip(rows_in_excel[1:], rows_in_self)
for excel_cell, self_cell in zip(row_in_excel[1:],
row_in_self[1:])))
fix['ulasan_prediksi'] = prediksi
aktual = fix['ulasan_aktual']
aktual.append(ratePakar[i])
fix['ulasan_aktual'] = aktual
outputFile = 'output/compare-output.xlsx'
df = pd.DataFrame(fix)
sf = StyleFrame(df)
for col_name in df.columns:
sf.apply_style_by_indexes(
indexes_to_style=sf[(sf['ulasan_prediksi'] == 'positif')
& (sf['ulasan_aktual'] == 'positif')],
cols_to_style=['ulasan_prediksi', 'ulasan_aktual'],
styler_obj=Styler(bg_color=utils.colors.green))
sf.apply_style_by_indexes(
indexes_to_style=sf[(sf['ulasan_prediksi'] == 'positif')
& (sf['ulasan_aktual'] == 'negatif')],
cols_to_style=['ulasan_prediksi', 'ulasan_aktual'],
styler_obj=Styler(bg_color=utils.colors.blue))
sf.apply_style_by_indexes(
indexes_to_style=sf[(sf['ulasan_prediksi'] == 'negatif')
& (sf['ulasan_aktual'] == 'positif')],
cols_to_style=['ulasan_prediksi', 'ulasan_aktual'],
styler_obj=Styler(bg_color=utils.colors.yellow))
sf.apply_style_by_indexes(
indexes_to_style=sf[(sf['ulasan_prediksi'] == 'negatif')
& (sf['ulasan_aktual'] == 'negatif')],
cols_to_style=['ulasan_prediksi', 'ulasan_aktual'],
styler_obj=Styler(bg_color=utils.colors.red))
master_df = pd.concat(frames, ignore_index=True)
master_df.sort_values(by='Id', inplace=True)
# end of master_df
print(master_df)
print(master_df.columns)
# creating sf dataFrame for color formating
sf = StyleFrame(master_df)
cols_to_style = [cols for cols in sf.columns]
# applying color for student with 1
sf.apply_style_by_indexes(indexes_to_style=sf[(sf['note'] <= 1)],
cols_to_style=cols_to_style,
styler_obj=Styler(bg_color='green',
bold=True,
font_size=10))
# applying color for student with 2
sf.apply_style_by_indexes(indexes_to_style=sf[(sf['note'] > 1)
& (sf['note'] < 3)],
cols_to_style=cols_to_style,
styler_obj=Styler(bg_color='yellow',
bold=True,
font_size=10))
# applying color for student with 3
sf.apply_style_by_indexes(indexes_to_style=sf[(sf['note'] >= 3)],
cols_to_style=cols_to_style,
styler_obj=Styler(bg_color='red',
bold=True,
def test_default_grid_and_bg_color(self):
with self.assertRaises(ValueError):
Styler(border_type=utils.borders.default_grid, bg_color=utils.colors.yellow)
print(sortex_df,"...")
print("Sorted By Date")
#--------------
sortex_df.columns = sortex_df.columns.str.replace('.csv', '')
sorted_df.columns = sorted_df.columns.str.replace('.csv', '')
print("Replaced String")
sortex_df = StyleFrame(sortex_df, Styler(shrink_to_fit=False, wrap_text=False))
sortex_df.set_column_width(sortex_df.columns,12)
writer = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
sorted_df = StyleFrame(sorted_df, Styler(shrink_to_fit=False, wrap_text=False))
sorted_df.set_column_width(sorted_df.columns,12)
print("Stylesheet Created")
sortex_df.to_excel(writer,'Merging By Date',index=False)
sorted_df.to_excel(writer,'Treasury Spread',index=False)
writer.save()
realPath = os.getcwd()
os.chdir(path)
print(sortex_df, "...")
print("Sorted By Date")
#--------------
sortex_df.columns = sortex_df.columns.str.replace('.csv', '')
sorted_df.columns = sorted_df.columns.str.replace('.csv', '')
print("Replaced String")
sortex_df = StyleFrame(sortex_df,
Styler(shrink_to_fit=False, wrap_text=False))
sortex_df.set_column_width(sortex_df.columns, 12)
writer = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
sorted_df = StyleFrame(sorted_df,
Styler(shrink_to_fit=False, wrap_text=False))
sorted_df.set_column_width(sorted_df.columns, 12)
print("Stylesheet Created")
sortex_df.to_excel(writer, 'Merging By Date', index=False)
sorted_df.to_excel(writer, 'Treasury Spread', index=False)
writer.save()
os.chdir(mainpath)
#round the grade weight and weighted grade to 2 decimal places, convert fraction to percent
weight = roundArr(weight, 4)
weightedGradeCalc = roundArr(weightedGradeCalc, 4)
#set up the excel dataframe
df = pd.DataFrame({
'Gradebook Item': names,
'Grade': marks,
'Max Points': maxMark,
'Weight (out of 100%)': weight,
'Weighted Grade (out of 100%)': weightedGradeCalc
})
#define default style
default_style = Styler(font_size=12)
#create a new StyleFrame with excel dataframe using default style
sf[i] = StyleFrame(df, 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)
sf[i].apply_headers_style(styler_obj=header_style)
#apply style for categories. There is a different style for the headings (they are justified to the left)
categoryStyleName = Styler(
bold=True,
font_size=14,
def test_combine(self):
self.assertEqual(Styler.combine(self.yellow_1, self.bold, self.underline), self.yellow_bold_underline)
def test_add(self):
self.assertEqual(self.yellow_1 + self.bold + self.underline, self.yellow_bold_underline)
self.assertEqual(self.yellow_2.bold, False)
self.assertEqual(self.yellow_2 + Styler(), self.yellow_1)
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)