def test_read_excel_template_with_use_df_boundaries(self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2'],
'b': ['col_b_row_1', 'col_b_row_2']
},
styler_obj=self.styler_obj_1)
template_sf.to_excel(TEST_FILENAME).save()
df = pd.DataFrame(data={
'A': [1],
}, columns=['A'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=True,
read_comments=True)
self.assertListEqual([col.value for col in sf_from_template.columns],
['A'])
self.assertEqual(len(df), len(sf_from_template))
expected_cell_style = template_sf['a'][0].style
actual_cell_style = sf_from_template['A'][0].style
self.assertEqual(
actual_cell_style, expected_cell_style,
'Different styles in template cell with style {template_style}'
'\nand actual cell with style {actual_cell_style}'.format(
template_style=expected_cell_style,
actual_cell_style=actual_cell_style))
self.assertEqual(sf_from_template['A'][0].value, 1)
def get_standard_matrix():
filepath = self.story_logic_sheet
sf = StyleFrame.read_excel(filepath,
read_style=True,
use_openpyxl_styles=False)
def only_cells_with_red_text(cell):
if cell.style.bg_color in {utils.colors.red, 'FFFF0000'}:
return 120
sf_2 = StyleFrame(sf.applymap(only_cells_with_red_text))
#print(qualifying_dict)
sf_2.to_excel().save()
df = pd.read_excel(curdir + '/output.xlsx')
df = df.iloc[:self.row_lim, 1]
#print(df)
standard_matrix = df.values
return standard_matrix
def test_init_dataframe(self):
self.assertIsInstance(
StyleFrame(pd.DataFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
})), StyleFrame)
self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame)
def setUp(self):
self.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
self.sf = StyleFrame(
{
'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']
}, self.default_styler_obj)
self.apply_column_style = partial(self.sf.apply_column_style,
styler_obj=self.styler_obj_1,
width=10)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes,
styler_obj=self.styler_obj_1,
height=10)
self.apply_headers_style = partial(self.sf.apply_headers_style,
styler_obj=self.styler_obj_1)
def test_read_excel_no_style(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME)
# making sure content is the same
self.assertTrue(
all(
list(self.sf[col]) == list(sf_from_excel[col])
for col in self.sf.columns))
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_read_excel_columns_width(self):
self.sf.set_column_width(columns='a', width=25)
self.sf.set_column_width(columns='b', width=15)
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True)
# Assert the number of rows with height is the length of our data plus 1 for headers row
self.assertEqual(len(sf_from_excel._columns_width),
len(self.sf.columns))
self.assertEqual(sf_from_excel._columns_width['a'], 25)
self.assertEqual(sf_from_excel._columns_width['b'], 15)
def test_read_excel_template_boundaries_with_less_rows_and_columns_than_df(
self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']
},
styler_obj=self.styler_obj_1)
template_sf.index[0].style = self.styler_obj_2
template_sf.to_excel(TEST_FILENAME, index=True).save()
df = pd.DataFrame(data={
'A': [1, 2, 3, 4],
'B': [3, 2, 1, 4],
'C': [-1, -2, -3, -4],
},
columns=['A', 'B', 'C'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=False,
index_col=0,
read_comments=True)
for template_rows, sf_rows in zip(
template_sf.data_df.itertuples(),
sf_from_template.data_df.itertuples()):
for template_cell, actual_cell in zip(template_rows, sf_rows):
self.assertEqual(
template_cell.style, actual_cell.style,
'Different styles in template cell {template_cell} with style {template_style}'
'\nand actual cell {actual_cell} with style {actual_cell_style}'
.format(template_cell=template_cell,
template_style=template_cell.style,
actual_cell=actual_cell,
actual_cell_style=actual_cell.style))
# Assert values are equals to df and not to the original values from template
assert_frame_equal(sf_from_template.data_df,
df,
check_index_type=False,
check_dtype=False,
check_column_type=False)
def test_init_styler_obj(self):
self.sf = StyleFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
},
styler_obj=self.styler_obj_1)
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_1 for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def test_read_excel_rows_height(self):
self.sf.set_row_height(rows=1, height=25)
self.sf.set_row_height(rows=2, height=15)
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True)
# Assert the number of rows with height is the length of our data plus 1 for headers row
self.assertEqual(len(sf_from_excel._rows_height), len(self.sf) + 1)
self.assertEqual(sf_from_excel._rows_height[1], 25)
self.assertEqual(sf_from_excel._rows_height[2], 15)
self.assertEqual(sf_from_excel._rows_height[3], None)
self.assertEqual(sf_from_excel._rows_height[4], None)
def test_read_excel_template_boundaries_with_more_rows_and_columns_than_df(
self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2'],
'b': ['col_b_row_1', 'col_b_row_2']
},
styler_obj=self.styler_obj_1)
template_sf.to_excel(TEST_FILENAME).save()
df = pd.DataFrame(data={
'A': [1],
}, columns=['A'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=False,
read_comments=True)
# Since template is larger than the df and use_df_boundaries is false, 'b' column shouldn't change
# and be left from the original template
self.assertListEqual([col.value for col in sf_from_template.columns],
['A', 'b'])
self.assertEqual(
template_sf['a'][0].style, sf_from_template['A'][0].style,
'Different styles in template cell with style {template_style}'
'\nand actual cell with style {actual_cell_style}'.format(
template_style=template_sf['a'][0].style,
actual_cell_style=sf_from_template['A'][0].style))
self.assertEqual(sf_from_template['A'][0].value, 1)
# Assert extra column equals
self.assertListEqual(list(sf_from_template['b']),
list(template_sf['b']))
# Assert extra row exists and equals
self.assertListEqual(list(sf_from_template.iloc[1]),
list(template_sf.iloc[1]))
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:])))
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_styler_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=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(excel_cell.style == self_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 extract_PDF(read_path, write_path):
files = [f for f in os.listdir(read_path) if isfile(join(read_path, f))]
importlib.reload(pfs)
for file in files:
# Process PDFs and read them into dataframe
results = pfs.process_PDF(read_path + file)
# Save processed results to local drive so they can be easily retrieved without
# having to re-perform processing (as this takes a long time)
results.to_pickle(write_path + file.replace('pdf', 'pkl'))
results = pd.read_pickle(write_path + file.replace('pdf', 'pkl'))
# Reformat values and delete unnecessary attributes
results['value'] = results['value'].apply(lambda x: float(
x.replace('-', '0').replace(')', '').replace('(', '-')))
results = results[['label', 'statement', 'year', 'unit', 'value']]
results['year'] = results['year'].astype("int64")
results.drop_duplicates(['label', 'year'], keep='last', inplace=True)
# Unstack dataframe
results = results.groupby(['label', 'year', 'unit', 'statement'],
sort=False)['value'].sum().unstack('year')
# Split dataframe into balance sheet and income statement
balance_sheet = results.query("statement == 'Balance sheet'")
income_statement = results.query("statement == 'Income statement'")
for i in (balance_sheet, income_statement):
i.reset_index(inplace=True)
i.drop(['statement'], axis=1, inplace=True)
# Export to Excel
writer = StyleFrame.ExcelWriter(write_path +
file.replace('pdf', 'xlsx'))
balance_sheet.to_excel(excel_writer=writer, sheet_name='Balance sheet')
income_statement.to_excel(excel_writer=writer,
sheet_name='Income statement')
writer.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 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()
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()
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 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()
# print("Inside Loop")
# print(x , i)
#Process
xf = bonds[i].sub(bonds[x])
xf = xf[~xf.index.duplicated()]
xf = xf.fillna(value="na")
newBonds.append(xf)
parent_newBonds.append(newBonds)
count += 1
#print(parent_newBonds)
resulx, sortex_df = extractingTreasuryYeilds(nbonds)
writer = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
writerRenamed = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
if key in shortlisted_countries:
shortlist_sortex_df = shortlistedYeild(resulx)
writerShorlisted = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
writerShortlistedRenamed = StyleFrame.ExcelWriter(
'{0}.xlsx'.format(key))
mod_date_sortex_df, price_resulx, date_sortex_df = quarterlyInsightsYeild(
resulx, resulx, 0)
imputed_yeild_df = ImputedYeild(mod_date_sortex_df)
writerImputed = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
writerQuarterly = StyleFrame.ExcelWriter('{0}.xlsx'.format(key))
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)
# Write to Excel file
SCTID_Columns = excel_file.filter(regex=f"SCTID.*").columns
all_list = SCTID_Columns.to_list(
) + Semantic_Tag_Columns + FSN_Columns.to_list()
excel_file = excel_file.drop(columns=all_list)
col_list = excel_file.columns.tolist()
excel_writer = pd.ExcelWriter(f"Output/{file_name}")
# sf = StyleFrame(excel_file)
# sf.apply_headers_style(Styler(bold=False, horizontal_alignment='left'))
excel_file.to_excel(excel_writer=excel_writer,
index=False,
sheet_name="Variables")
file_df.pop("Variables")
for file_name in file_df:
sf = StyleFrame(file_df[file_name])
col_list = file_df[file_name].columns.tolist()
# sf.apply_headers_style(Styler(bold=False, horizontal_alignment='left'))
file_df[file_name].to_excel(excel_writer=excel_writer,
index=False,
sheet_name=file_name)
excel_writer.save()
# CSV + YAML
elif mode == 2:
_, _, files = next(walk("Files"))
excel_dfs = []
for file_name in files:
_, file_extension = os.path.splitext(f"Files/{file_name}")
if file_extension != ".xlsx":
break
excel_df = edit_excel(f"Files/{file_name}")
def func01():
sheet_range = [21, 22]
df_origin = pd.DataFrame()
for num in sheet_range:
excel_sheet_name = re.sub('00', str(num), 'day00')
df_origin = df_origin.append(pd.read_excel("data/vocabulary.xlsx", sheet_name=excel_sheet_name, header=None),
ignore_index=True)
shuffled_df = df_origin.iloc[np.random.permutation(df_origin.index)].reset_index(drop=True)
shuffled_df_len = len(shuffled_df.index)
df_origin = pd.DataFrame(index=[1, 2, 3, 4])
df_quiz = pd.DataFrame(index=[1, 2, 3, 4])
for num in range(0, shuffled_df_len, 2):
df_origin.loc[int(num/2), 1] = shuffled_df.loc[num, 0]
df_origin.loc[int(num/2), 2] = shuffled_df.loc[num, 1]
df_origin.loc[int(num/2), 3] = shuffled_df.loc[num+1, 0]
df_origin.loc[int(num/2), 4] = shuffled_df.loc[num+1, 1]
seed = random.choice(['VOCA', 'MEANING'])
if seed == 'VOCA':
df_quiz.loc[int(num/2), 1] = shuffled_df.loc[num, 0]
df_quiz.loc[int(num/2), 2] = ''
elif seed == 'MEANING':
df_quiz.loc[int(num/2), 1] = ''
df_quiz.loc[int(num/2), 2] = shuffled_df.loc[num, 1]
seed = random.choice(['VOCA', 'MEANING'])
if seed == 'VOCA':
df_quiz.loc[int(num/2), 3] = shuffled_df.loc[num+1, 0]
df_quiz.loc[int(num/2), 4] = ''
elif seed == 'MEANING':
df_quiz.loc[int(num/2), 3] = ''
df_quiz.loc[int(num/2), 4] = shuffled_df.loc[num+1, 1]
excel_writer = StyleFrame.ExcelWriter('data/vocabulary_quiz.xlsx')
sf_origin = StyleFrame(df_origin)
sf_quiz = StyleFrame(df_quiz)
# sf.set_row_height(rows=[n for n in range(1, math.ceil(shuffled_df_len/2)+1)], height=)
sf_origin.set_column_width(columns=['A', 'C'], width=16.0)
sf_origin.set_column_width(columns=['B', 'D'], width=40.0)
sf_quiz.set_column_width(columns=['A', 'C'], width=16.0)
sf_quiz.set_column_width(columns=['B', 'D'], width=40.0)
sf_origin.to_excel(excel_writer, header=False, index=False, sheet_name='origin')
sf_quiz.to_excel(excel_writer, header=False, index=False, sheet_name='quiz')
excel_writer.save()
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
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,
bg_color=utils.colors.grey,
horizontal_alignment=utils.horizontal_alignments.left)
class StyleFrameTest(unittest.TestCase):
@classmethod
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 setUp(self):
self.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
self.sf = StyleFrame(
{
'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']
}, self.default_styler_obj)
self.apply_column_style = partial(self.sf.apply_column_style,
styler_obj=self.styler_obj_1,
width=10)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes,
styler_obj=self.styler_obj_1,
height=10)
self.apply_headers_style = partial(self.sf.apply_headers_style,
styler_obj=self.styler_obj_1)
@classmethod
def tearDownClass(cls):
try:
os.remove(TEST_FILENAME)
except OSError as ex:
print(ex)
def export_and_get_default_sheet(self, save=False):
self.sf.to_excel(excel_writer=self.ew,
right_to_left=True,
columns_to_hide=self.sf.columns[0],
row_to_add_filters=0,
columns_and_rows_to_freeze='A2',
allow_protection=True)
if save:
self.ew.save()
return self.ew.sheets['Sheet1']
def get_cf_rules(self, sheet):
conditional_formatting = sheet.conditional_formatting
try:
return conditional_formatting.cf_rules
except AttributeError:
return conditional_formatting
def test_init_styler_obj(self):
self.sf = StyleFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
},
styler_obj=self.styler_obj_1)
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_1 for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def test_init_dataframe(self):
self.assertIsInstance(
StyleFrame(pd.DataFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
})), StyleFrame)
self.assertIsInstance(StyleFrame(pd.DataFrame()), StyleFrame)
def test_init_styleframe(self):
self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})),
StyleFrame)
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_str(self):
self.assertEqual(str(self.sf), str(self.sf.data_df))
def test__getitem__(self):
self.assertEqual(self.sf['a'].tolist(), self.sf.data_df['a'].tolist())
self.assertTrue(self.sf.data_df[['a',
'b']].equals(self.sf[['a',
'b']].data_df))
def test__setitem__(self):
self.sf['a'] = range(3)
self.sf['b'] = range(3, 6)
self.sf['c'] = 5
self.sf['d'] = self.sf['a'] + self.sf['b']
self.sf['e'] = self.sf['a'] + 5
self.assertTrue(
all(self.sf.applymap(lambda x: isinstance(x, Container)).all()))
def test__getattr__(self):
self.assertEqual(self.sf.fillna, self.sf.data_df.fillna)
self.assertTrue(self.sf['a'].equals(self.sf.a))
with self.assertRaises(AttributeError):
self.sf.non_exisiting_method()
def test_apply_column_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'])
self.assertTrue(
all([
self.sf.at[index, 'a'].style.to_openpyxl_style()._style
== self.openpy_style_obj_1
and self.sf.at[index, 'b'].style.to_openpyxl_style()._style !=
self.openpy_style_obj_1 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 == self.openpy_style_obj_1
for i in range(2, len(self.sf))))
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 test_apply_style_by_indexes_single_col(self):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0)
self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_2'],
cols_to_style=['a'])
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_1 for index in self.sf.index
if self.sf.at[index, 'a'] == 'col_a_row_2'))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1)._style == self.openpy_style_obj_1
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2))
self.assertEqual(sheet.row_dimensions[3].height, 10)
def test_apply_style_by_indexes_all_cols(self):
self.apply_style_by_indexes(self.sf[self.sf['a'] == 2])
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_1 for index in self.sf.index
if self.sf.at[index, 'a'] == 2))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1
for i in range(1, len(self.sf))
for j in range(1, len(self.sf.columns))
if sheet.cell(row=i, column=1).value == 2))
def test_apply_style_by_indexes_complement_style(self):
self.apply_style_by_indexes(self.sf[self.sf['a'] == 'col_a_row_1'],
complement_style=self.styler_obj_2)
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_1 for index in self.sf.index
if self.sf.at[index, 'a'] == 'col_a_row_1'))
self.assertTrue(
all(self.sf.at[index, 'a'].style.to_openpyxl_style()._style ==
self.openpy_style_obj_2 for index in self.sf.index
if self.sf.at[index, 'a'] != 'col_a_row_1'))
def test_apply_style_by_indexes_with_single_index(self):
self.apply_style_by_indexes(self.sf.index[0])
self.assertTrue(
all(self.sf.iloc[0, self.sf.columns.get_loc(col)].style.
to_openpyxl_style()._style == self.openpy_style_obj_1
for col in self.sf.columns))
sheet = self.export_and_get_default_sheet()
# row=2 since sheet start from row 1 and the headers are row 1
self.assertTrue(
all(
sheet.cell(row=2, column=col)._style == self.openpy_style_obj_1
for col in range(1, len(self.sf.columns))))
def test_apply_style_by_indexes_all_cols_with_multiple_indexes(self):
self.apply_style_by_indexes([1, 2])
self.assertTrue(
all(self.sf.iloc[index, self.sf.columns.get_loc(col)].style.
to_openpyxl_style()._style == self.openpy_style_obj_1
for index in [1, 2] for col in self.sf.columns))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j)._style == self.openpy_style_obj_1
for i in [3, 4] # sheet start from row 1 and headers are row 1
for j in range(1, len(self.sf.columns))))
def test_apply_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style.to_openpyxl_style()._style,
self.openpy_style_obj_1)
sheet = self.export_and_get_default_sheet()
self.assertEqual(
sheet.cell(row=1, column=1)._style, self.openpy_style_obj_1)
def test_set_column_width(self):
# testing some edge cases
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_column_width(columns='a', width='a')
with self.assertRaises(ValueError):
# noinspection PyTypeChecker
self.sf.set_column_width(columns='a', width=-1)
# actual tests
self.sf.set_column_width(columns=['a'], width=20)
self.assertEqual(self.sf._columns_width['a'], 20)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.column_dimensions['A'].width, 20)
def test_set_column_width_dict(self):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_column_width_dict(None)
width_dict = {'a': 20, 'b': 30}
self.sf.set_column_width_dict(width_dict)
self.assertEqual(self.sf._columns_width, width_dict)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(sheet.column_dimensions[col.upper()].width == width_dict[col]
for col in width_dict))
def test_set_row_height(self):
# testing some edge cases
with self.assertRaises(TypeError):
self.sf.set_row_height(rows=[1], height='a')
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=[1], height=-1)
with self.assertRaises(ValueError):
self.sf.set_row_height(rows=['a'], height=-1)
# actual tests
self.sf.set_row_height(rows=[1], height=20)
self.assertEqual(self.sf._rows_height[1], 20)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.row_dimensions[1].height, 20)
def test_set_row_height_dict(self):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.set_row_height_dict(None)
height_dict = {1: 20, 2: 30}
self.sf.set_row_height_dict(height_dict)
self.assertEqual(self.sf._rows_height, height_dict)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(sheet.row_dimensions[row].height == height_dict[row]
for row in height_dict))
def test_rename(self):
with self.assertRaises(TypeError):
# noinspection PyTypeChecker
self.sf.rename(columns=None)
original_columns_name = list(self.sf.columns)
names_dict = {'a': 'A', 'b': 'B'}
# testing rename with inplace = True
self.sf.rename(columns=names_dict, inplace=True)
self.assertTrue(
all(new_col_name in self.sf.columns
for new_col_name in names_dict.values()))
new_columns_name = list(self.sf.columns)
# check that the columns order did not change after renaming
self.assertTrue(
all(
original_columns_name.index(old_col_name) ==
new_columns_name.index(new_col_name)
for old_col_name, new_col_name in names_dict.items()))
# using the old name should raise a KeyError
with self.assertRaises(KeyError):
# noinspection PyStatementEffect
self.sf['a']
# testing rename with inplace = False
names_dict = {v: k for k, v in names_dict.items()}
new_sf = self.sf.rename(columns=names_dict, inplace=False)
self.assertTrue(
all(new_col_name in new_sf.columns
for new_col_name in names_dict.values()))
# using the old name should raise a KeyError
with self.assertRaises(KeyError):
# noinspection PyStatementEffect
new_sf['A']
def test_read_excel_no_style(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME)
# making sure content is the same
self.assertTrue(
all(
list(self.sf[col]) == list(sf_from_excel[col])
for col in self.sf.columns))
def test_read_excel_with_string_sheet_name(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME,
read_style=True,
sheet_name='Sheet1',
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 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 test_read_excel_with_style_openpyxl_objects_and_save(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:])))
sf_from_excel.to_excel(TEST_FILENAME).save()
def test_read_excel_with_style_styler_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=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(excel_cell.style == self_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 test_read_excel_with_style_comments_openpyxl_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME,
read_style=True,
read_comments=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 test_read_excel_with_style_comments_styler_objects(self):
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME,
read_style=True,
read_comments=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(excel_cell.style == self_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 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:])))
def test_read_excel_rows_height(self):
self.sf.set_row_height(rows=1, height=25)
self.sf.set_row_height(rows=2, height=15)
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True)
# Assert the number of rows with height is the length of our data plus 1 for headers row
self.assertEqual(len(sf_from_excel._rows_height), len(self.sf) + 1)
self.assertEqual(sf_from_excel._rows_height[1], 25)
self.assertEqual(sf_from_excel._rows_height[2], 15)
self.assertEqual(sf_from_excel._rows_height[3], None)
self.assertEqual(sf_from_excel._rows_height[4], None)
def test_read_excel_columns_width(self):
self.sf.set_column_width(columns='a', width=25)
self.sf.set_column_width(columns='b', width=15)
self.export_and_get_default_sheet(save=True)
sf_from_excel = StyleFrame.read_excel(TEST_FILENAME, read_style=True)
# Assert the number of rows with height is the length of our data plus 1 for headers row
self.assertEqual(len(sf_from_excel._columns_width),
len(self.sf.columns))
self.assertEqual(sf_from_excel._columns_width['a'], 25)
self.assertEqual(sf_from_excel._columns_width['b'], 15)
def test_read_excel_template_equal_boundaries(self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']
},
styler_obj=self.styler_obj_1)
template_sf.index[0].style = self.styler_obj_2
template_sf.to_excel(TEST_FILENAME, index=True).save()
df = pd.DataFrame(data={
'A': [1, 2, 3],
'B': [3, 2, 1]
},
columns=['A', 'B'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=False,
index_col=0,
read_comments=True)
for template_rows, sf_rows in zip(
template_sf.data_df.itertuples(),
sf_from_template.data_df.itertuples()):
for template_cell, actual_cell in zip(template_rows, sf_rows):
self.assertEqual(
template_cell.style, actual_cell.style,
'Different styles in template cell {template_cell} with style {template_style}'
'\nand actual cell {actual_cell} with style {actual_cell_style}'
.format(template_cell=template_cell,
template_style=template_cell.style,
actual_cell=actual_cell,
actual_cell_style=actual_cell.style))
# Assert values are equals to df and not to the original values from template
assert_frame_equal(sf_from_template.data_df,
df,
check_index_type=False,
check_dtype=False,
check_column_type=False)
def test_read_excel_template_boundaries_with_more_rows_and_columns_than_df(
self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2'],
'b': ['col_b_row_1', 'col_b_row_2']
},
styler_obj=self.styler_obj_1)
template_sf.to_excel(TEST_FILENAME).save()
df = pd.DataFrame(data={
'A': [1],
}, columns=['A'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=False,
read_comments=True)
# Since template is larger than the df and use_df_boundaries is false, 'b' column shouldn't change
# and be left from the original template
self.assertListEqual([col.value for col in sf_from_template.columns],
['A', 'b'])
self.assertEqual(
template_sf['a'][0].style, sf_from_template['A'][0].style,
'Different styles in template cell with style {template_style}'
'\nand actual cell with style {actual_cell_style}'.format(
template_style=template_sf['a'][0].style,
actual_cell_style=sf_from_template['A'][0].style))
self.assertEqual(sf_from_template['A'][0].value, 1)
# Assert extra column equals
self.assertListEqual(list(sf_from_template['b']),
list(template_sf['b']))
# Assert extra row exists and equals
self.assertListEqual(list(sf_from_template.iloc[1]),
list(template_sf.iloc[1]))
def test_read_excel_template_boundaries_with_less_rows_and_columns_than_df(
self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2', 'col_a_row_3'],
'b': ['col_b_row_1', 'col_b_row_2', 'col_b_row_3']
},
styler_obj=self.styler_obj_1)
template_sf.index[0].style = self.styler_obj_2
template_sf.to_excel(TEST_FILENAME, index=True).save()
df = pd.DataFrame(data={
'A': [1, 2, 3, 4],
'B': [3, 2, 1, 4],
'C': [-1, -2, -3, -4],
},
columns=['A', 'B', 'C'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=False,
index_col=0,
read_comments=True)
for template_rows, sf_rows in zip(
template_sf.data_df.itertuples(),
sf_from_template.data_df.itertuples()):
for template_cell, actual_cell in zip(template_rows, sf_rows):
self.assertEqual(
template_cell.style, actual_cell.style,
'Different styles in template cell {template_cell} with style {template_style}'
'\nand actual cell {actual_cell} with style {actual_cell_style}'
.format(template_cell=template_cell,
template_style=template_cell.style,
actual_cell=actual_cell,
actual_cell_style=actual_cell.style))
# Assert values are equals to df and not to the original values from template
assert_frame_equal(sf_from_template.data_df,
df,
check_index_type=False,
check_dtype=False,
check_column_type=False)
def test_read_excel_template_with_use_df_boundaries(self):
template_sf = StyleFrame(obj={
'a': ['col_a_row_1', 'col_a_row_2'],
'b': ['col_b_row_1', 'col_b_row_2']
},
styler_obj=self.styler_obj_1)
template_sf.to_excel(TEST_FILENAME).save()
df = pd.DataFrame(data={
'A': [1],
}, columns=['A'])
sf_from_template = StyleFrame.read_excel_as_template(
path=TEST_FILENAME,
df=df,
use_df_boundaries=True,
read_comments=True)
self.assertListEqual([col.value for col in sf_from_template.columns],
['A'])
self.assertEqual(len(df), len(sf_from_template))
expected_cell_style = template_sf['a'][0].style
actual_cell_style = sf_from_template['A'][0].style
self.assertEqual(
actual_cell_style, expected_cell_style,
'Different styles in template cell with style {template_style}'
'\nand actual cell with style {actual_cell_style}'.format(
template_style=expected_cell_style,
actual_cell_style=actual_cell_style))
self.assertEqual(sf_from_template['A'][0].value, 1)
def test_row_indexes(self):
self.assertEqual(self.sf.row_indexes, (1, 2, 3, 4))
def test_style_alternate_rows(self):
styles = [self.styler_obj_1, self.styler_obj_2]
openpy_styles = [self.openpy_style_obj_1, self.openpy_style_obj_2]
self.sf.style_alternate_rows(styles)
self.assertTrue(
all(self.sf.iloc[index.value, 0].style.to_openpyxl_style() ==
styles[index.value % len(styles)].to_openpyxl_style()
for index in self.sf.index))
sheet = self.export_and_get_default_sheet()
# sheet start from row 1 and headers are row 1, so need to add 2 when iterating
self.assertTrue(
all(
sheet.cell(row=i.value +
2, column=1)._style == openpy_styles[i.value %
len(styles)]
for i in self.sf.index))
def test_add_color_scale_conditional_formatting_start_end(self):
self.sf.add_color_scale_conditional_formatting(
start_type=utils.conditional_formatting_types.percentile,
start_value=0,
start_color=utils.colors.red,
end_type=utils.conditional_formatting_types.percentile,
end_value=100,
end_color=utils.colors.green)
sheet = self.export_and_get_default_sheet(save=True)
cf_rules = self.get_cf_rules(sheet=sheet)
rules_dict = cf_rules['A1:B4']
self.assertEqual(rules_dict[0].type, 'colorScale')
self.assertEqual(rules_dict[0].colorScale.color[0].rgb,
utils.colors.red)
self.assertEqual(rules_dict[0].colorScale.color[1].rgb,
utils.colors.green)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].type,
utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].type,
utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 100.0)
def test_add_color_scale_conditional_formatting_start_mid_end(self):
self.sf.add_color_scale_conditional_formatting(
start_type=utils.conditional_formatting_types.percentile,
start_value=0,
start_color=utils.colors.red,
mid_type=utils.conditional_formatting_types.percentile,
mid_value=50,
mid_color=utils.colors.yellow,
end_type=utils.conditional_formatting_types.percentile,
end_value=100,
end_color=utils.colors.green)
sheet = self.export_and_get_default_sheet(save=True)
cf_rules = self.get_cf_rules(sheet=sheet)
rules_dict = cf_rules['A1:B4']
self.assertEqual(rules_dict[0].type, 'colorScale')
self.assertEqual(rules_dict[0].colorScale.color[0].rgb,
utils.colors.red)
self.assertEqual(rules_dict[0].colorScale.color[1].rgb,
utils.colors.yellow)
self.assertEqual(rules_dict[0].colorScale.color[2].rgb,
utils.colors.green)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].type,
utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[0].val, 0.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].type,
utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[1].val, 50.0)
self.assertEqual(rules_dict[0].colorScale.cfvo[2].type,
utils.conditional_formatting_types.percentile)
self.assertEqual(rules_dict[0].colorScale.cfvo[2].val, 100.0)
def test_columns_setter(self):
self.sf.columns = ['c', 'd']
self.assertTrue(
all(isinstance(col, Container) for col in self.sf.columns))
self.assertEqual([col.value for col in self.sf.columns], ['c', 'd'])
df1 = pd.read_excel("file2.xlsx")
print(df1)
print(df1.columns)
frames = [df0, df1]
# when in production, replace frames for create_list... function
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,
def test_init_styleframe(self):
self.assertIsInstance(StyleFrame(StyleFrame({'a': [1, 2, 3]})),
StyleFrame)
with self.assertRaises(TypeError):
StyleFrame({}, styler_obj=1)
komentar = fix['komentar']
komentar.append(data['komentar'])
fix['komentar'] = komentar
prediksi = fix['ulasan_prediksi']
prediksi.append(data['hasil'].lower())
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'],
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()