def save_adjust_xlsx(df, file='test.xlsx', width=60):
'''save and re-adjust excel format'''
df = df.reset_index(drop='index').fillna('')
StyleFrame.A_FACTOR = 5
StyleFrame.P_FACTOR = 1.2
sf = StyleFrame(df,
Styler(wrap_text=False, shrink_to_fit=True, font_size=12))
if ('add_index' in df.columns.tolist()):
sf.apply_style_by_indexes(
indexes_to_style=sf[sf['add_index'] == 'new'],
styler_obj=Styler(bg_color='yellow'),
overwrite_default_style=False)
sf.apply_column_style(cols_to_style=['当事人', '诉讼代理人', '地址'],
width=width,
styler_obj=Styler(wrap_text=False,
shrink_to_fit=True))
else:
sf.set_column_width_dict(
col_width_dict={('当事人', '诉讼代理人', '地址'): width})
if len(df):
sf.to_excel(file,
best_fit=sf.data_df.columns.difference(
['当事人', '诉讼代理人', '地址']).tolist()).save()
else:
sf.to_excel(file).save()
print_log('>>> 保存文件 => 文件名 \'%s\' => 数据保存成功...' % (file))
return df
def _format_table(self, df):
sort_criteria = self.SETTINGS['sort_data']['incomes_table']
excel_format = df[sort_criteria].apply(
lambda value: f"{value.date().strftime(r'%d/%m/%Y')}"
if f"{type(value)}" == "<class 'pandas._libs.tslibs.timestamps.Timestamp'>"
else value)
df[sort_criteria] = excel_format
sf = StyleFrame(df)
sf.apply_style_by_indexes(indexes_to_style=sf[sf['TITULO'] != ''],styler_obj=Styler(font_size=10))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['TURMA'] == 'TURMA'],styler_obj=Styler(bold=False,
bg_color=utils.colors.grey,
border_type=utils.borders.thin,
font_size=10,
wrap_text=False,
shrink_to_fit=False))
sf.apply_style_by_indexes(indexes_to_style=sf[sf['SACADO'] == 'TOTAL'],styler_obj=Styler(font_size=10, bold=True))
sf.apply_headers_style(styler_obj=Styler(bold=False,
bg_color=utils.colors.grey,
border_type=utils.borders.thin,
font_size=10,
wrap_text=False,
shrink_to_fit=False))
col_width = {
'TURMA': 32,
'TITULO': 8,
'VENCIMENTO': 12,
'SACADO': 34,
'VAL_COMISSAO': 12,
'VAL_RECEBIDO':13.2,
'VAL_PARCELA': 12,
'VAL_PRODUCAO': 12,
'VAL_MULTA': 12,
'VAL_DESCONTO': 12,
'VAL_ADESAO': 12,
'DATA_CREDITO':12,
}
sf.set_column_width_dict(col_width)
return sf
def as_xlsx_file(self):
df = pd.DataFrame(
data=self.days,
columns=['number', 'start_hour', 'end_hour', 'amount'])
df.loc[len(df)] = [''] * 4 # Empty row.
total_working_days = self.total_working_days()
total_working_days_row = ['סה"כ ימים', total_working_days, '', '']
df.loc[len(df)] = total_working_days_row
total_working_hours = self.total_working_hours()
total_working_hours_row = ['סה"כ שעות', total_working_hours, '', '']
df.loc[len(df)] = total_working_hours_row
total_driving_km_row = ['נסיעות', self.driving_in_km, 'ק"מ', '']
df.loc[len(df)] = total_driving_km_row
user_info = [self.user.first_name, self.user.last_name, '', '']
df.loc[len(df)] = user_info
df.columns = ['יום', 'שעת התחלה', 'שעת סיום', 'מספר גנים']
sf = StyleFrame(df)
sf.set_column_width_dict({
('שעת התחלה', 'שעת סיום'): 16,
('יום', 'מספר גנים'): 13
})
sf.apply_style_by_indexes(indexes_to_style=sf.index[-4:-1],
styler_obj=Styler(bold=True),
cols_to_style='יום')
output = BytesIO()
ew = StyleFrame.ExcelWriter(output)
sf.to_excel(
ew,
right_to_left=True,
row_to_add_filters=0,
).save()
return output
class StyleFrameTest(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter("test.xlsx")
cls.style_kwargs = dict(
bg_color=utils.colors.blue,
bold=True,
font="Impact",
font_color=utils.colors.yellow,
font_size=20,
underline=utils.underline.single,
)
cls.styler_obj = Styler(**cls.style_kwargs)
cls.openpy_style_obj = cls.styler_obj.create_style()
def setUp(self):
self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]})
self.apply_column_style = partial(self.sf.apply_column_style, **self.style_kwargs)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes, **self.style_kwargs)
self.apply_headers_style = partial(self.sf.apply_headers_style, **self.style_kwargs)
def export_and_get_default_sheet(self):
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,
)
return self.ew.book.get_sheet_by_name("Sheet1")
def test_init_styler_obj(self):
self.sf = StyleFrame({"a": [1, 2, 3], "b": [1, 2, 3]}, styler_obj=self.styler_obj)
self.assertTrue(all(self.sf.ix[index, "a"].style == self.openpy_style_obj 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
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))
)
)
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)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_apply_column_style(self):
self.apply_column_style(cols_to_style=["a"])
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
and self.sf.ix[index, "b"].style != self.openpy_style_obj
for index in self.sf.index
]
)
)
sheet = self.export_and_get_default_sheet()
# 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 for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col(self):
self.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"])
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2
)
)
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.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
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_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_apply_column_style_styler_obj(self):
self.sf.apply_column_style(cols_to_style=["a"], styler_obj=self.styler_obj)
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
and self.sf.ix[index, "b"].style != self.openpy_style_obj
for index in self.sf.index
]
)
)
sheet = self.export_and_get_default_sheet()
# 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 for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], cols_to_style=["a"], styler_obj=self.styler_obj)
self.assertTrue(
all(
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2
)
)
def test_apply_style_by_indexes_all_cols_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf["a"] == 2], styler_obj=self.styler_obj)
self.assertTrue(
all(
[
self.sf.ix[index, "a"].style == self.openpy_style_obj
for index in self.sf.index
if self.sf.ix[index, "a"] == 2
]
)
)
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
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_headers_style_styler_obj(self):
self.sf.apply_headers_style(styler_obj=self.styler_obj)
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_set_column_width(self):
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):
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):
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):
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):
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"]
class StyleFrameTest(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter(TEST_FILENAME)
cls.styler_obj_1 = Styler(
bg_color=utils.colors.blue,
bold=True,
font='Impact',
font_color=utils.colors.yellow,
font_size=20,
underline=utils.underline.single,
horizontal_alignment=utils.horizontal_alignments.left,
vertical_alignment=utils.vertical_alignments.center)
cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow)
cls.openpy_style_obj_1 = cls.styler_obj_1.create_style()
cls.openpy_style_obj_2 = cls.styler_obj_2.create_style()
def setUp(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]})
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.book.get_sheet_by_name('Sheet1')
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.loc[index, 'a'].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__get_item__(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__getattr__(self):
self.assertEqual(self.sf.fillna, self.sf.data_df.fillna)
with self.assertRaises(AttributeError):
self.sf.non_exisiting_method()
def test_apply_column_style(self):
# testing some edge cases
with self.assertRaises(TypeError):
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.loc[index, 'a'].style == self.openpy_style_obj_1
and self.sf.loc[index, 'b'].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_style_by_indexes_single_col(self):
with self.assertRaises(TypeError):
self.sf.apply_style_by_indexes(indexes_to_style=0, styler_obj=0)
self.apply_style_by_indexes(self.sf[self.sf['a'] == 2],
cols_to_style=['a'])
self.assertTrue(
all(self.sf.loc[index, 'a'].style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.loc[index, 'a'] == 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.loc[index, 'a'].style == self.openpy_style_obj_1
for index in self.sf.index if self.sf.loc[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_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 ==
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 ==
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, 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):
self.sf.set_column_width(columns='a', width='a')
with self.assertRaises(ValueError):
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):
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):
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):
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.apply_headers_style()
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_style(self):
self.apply_headers_style()
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.value == self_cell.value 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, row_in_self)))
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 == styles[index %
len(styles)].create_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 % len(styles)]
for i in self.sf.index))
def group_by_color(
filepath,
row_height,
column_width,
include_sources,
output_name,
):
from StyleFrame import StyleFrame, Styler, utils
import numpy as np
import pandas as pd
if not filepath.endswith(".xlsx"):
filepath += ".xlsx"
sf = StyleFrame.read_excel(filepath, read_style=True)
color_groupings = {}
for c_index, c in enumerate(sf.columns):
if "Cues" in c.value:
for value in sf[c]:
if type(value.value) is not str and np.isnan(value.value):
continue
if value.style.bg_color + "_value" not in color_groupings:
color_groupings[value.style.bg_color + "_value"] = []
if include_sources and (
value.style.bg_color + "_source" not in color_groupings
):
color_groupings[value.style.bg_color + "_source"] = []
color_groupings[value.style.bg_color + "_value"].append(
value.value
)
if include_sources:
color_groupings[value.style.bg_color + "_source"].append(
sf.iloc[0,c_index - 1].value
)
# colors of our stuff
for k,length in zip(color_groupings.keys(), map(len, color_groupings.values())):
print( "color", k + ":", length, "entries")
# pad data so it is all the same size
maxlen = max(map(len, color_groupings.values()))
for k,v in color_groupings.items():
color_groupings[k] = v + ['']*(maxlen - len(v))
print("old length:", len(v), "|| new length:", len(color_groupings[k]))
# add padded data to df, print 'er out
new_df = pd.DataFrame(color_groupings)
new_df
defaults = {'font': utils.fonts.calibri, 'font_size': 9}
new_sf = StyleFrame(new_df, styler_obj=Styler(**defaults))
for c in new_sf.columns:
# color to use
color = c.value.strip("_source").strip("_value")
# apply style to ALL values (incl headers)
new_sf.apply_column_style(
cols_to_style=[c.value],
styler_obj=Styler(bold=True, font_size=10, bg_color=color,
font_color=utils.colors.white),
style_header=True,
)
# revert style for non-headers
new_sf.apply_column_style(
cols_to_style=[c.value],
styler_obj=Styler(**defaults),
style_header=False
)
# row height
all_rows = new_sf.row_indexes
new_sf.set_row_height_dict(
row_height_dict={
all_rows[0]: 24,
all_rows[1:]: row_height
}
)
# col width
all_cols = tuple(map(lambda x: x.value, new_sf.columns))
new_sf.set_column_width_dict(
col_width_dict={
all_cols: column_width
}
)
# save to excel file
if not output_name.endswith(".xlsx"):
output_name += ".xlsx"
new_sf.to_excel(output_name).save()
return 0
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, underline=utils.underline.single,
horizontal_alignment=utils.horizontal_alignments.left,
vertical_alignment=utils.vertical_alignments.center,
comment_text='styler_obj_1 comment')
cls.styler_obj_2 = Styler(bg_color=utils.colors.yellow, 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_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_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)
class StyleFrameTest(unittest.TestCase):
@classmethod
def setUpClass(cls):
cls.ew = StyleFrame.ExcelWriter('test.xlsx')
cls.style_kwargs = dict(bg_color=utils.colors.blue,
bold=True,
font='Impact',
font_color=utils.colors.yellow,
font_size=20,
underline=utils.underline.single)
cls.styler_obj = Styler(**cls.style_kwargs)
cls.openpy_style_obj = cls.styler_obj.create_style()
def setUp(self):
self.sf = StyleFrame({'a': [1, 2, 3], 'b': [1, 2, 3]})
self.apply_column_style = partial(self.sf.apply_column_style,
**self.style_kwargs)
self.apply_style_by_indexes = partial(self.sf.apply_style_by_indexes,
**self.style_kwargs)
self.apply_headers_style = partial(self.sf.apply_headers_style,
**self.style_kwargs)
def export_and_get_default_sheet(self):
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)
return self.ew.book.get_sheet_by_name('Sheet1')
def test_init_styler_obj(self):
self.sf = StyleFrame({
'a': [1, 2, 3],
'b': [1, 2, 3]
},
styler_obj=self.styler_obj)
self.assertTrue(
all(self.sf.ix[index, 'a'].style == self.openpy_style_obj
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
for i in range(2, len(self.sf))
for j in range(1, len(self.sf.columns))))
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)
def test_len(self):
self.assertEqual(len(self.sf), len(self.sf.data_df))
self.assertEqual(len(self.sf), 3)
def test_apply_column_style(self):
self.apply_column_style(cols_to_style=['a'])
self.assertTrue(
all([
self.sf.ix[index, 'a'].style == self.openpy_style_obj
and self.sf.ix[index, 'b'].style != self.openpy_style_obj
for index in self.sf.index
]))
sheet = self.export_and_get_default_sheet()
# 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
for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col(self):
self.apply_style_by_indexes(self.sf[self.sf['a'] == 2],
cols_to_style=['a'])
self.assertTrue(
all([
self.sf.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index if self.sf.ix[index, 'a'] == 2
]))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2))
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.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index if self.sf.ix[index, 'a'] == 2
]))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
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_headers_style(self):
self.apply_headers_style()
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(
sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_apply_column_style_styler_obj(self):
self.sf.apply_column_style(cols_to_style=['a'],
styler_obj=self.styler_obj)
self.assertTrue(
all([
self.sf.ix[index, 'a'].style == self.openpy_style_obj
and self.sf.ix[index, 'b'].style != self.openpy_style_obj
for index in self.sf.index
]))
sheet = self.export_and_get_default_sheet()
# 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
for i in range(2, len(self.sf))))
def test_apply_style_by_indexes_single_col_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf['a'] == 2],
cols_to_style=['a'],
styler_obj=self.styler_obj)
self.assertTrue(
all(self.sf.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index if self.sf.ix[index, 'a'] == 2))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=1).style == self.openpy_style_obj
for i in range(1, len(self.sf))
if sheet.cell(row=i, column=1).value == 2))
def test_apply_style_by_indexes_all_cols_styler_obj(self):
self.sf.apply_style_by_indexes(self.sf[self.sf['a'] == 2],
styler_obj=self.styler_obj)
self.assertTrue(
all([
self.sf.ix[index, 'a'].style == self.openpy_style_obj
for index in self.sf.index if self.sf.ix[index, 'a'] == 2
]))
sheet = self.export_and_get_default_sheet()
self.assertTrue(
all(
sheet.cell(row=i, column=j).style == self.openpy_style_obj
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_headers_style_styler_obj(self):
self.sf.apply_headers_style(styler_obj=self.styler_obj)
self.assertEqual(self.sf.columns[0].style, self.openpy_style_obj)
sheet = self.export_and_get_default_sheet()
self.assertEqual(
sheet.cell(row=1, column=1).style, self.openpy_style_obj)
def test_set_column_width(self):
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):
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):
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):
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):
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 get_sheet_table(self):
"""
需要处理的时将excel的内容读出来和修改内容并保存
:return:
"""
# 读取一个excel的文本文件（当前默认时读一个文件的一个sheet页）
ex = pd.read_excel(unicode(self.filepath, "utf8"))
# 用pd格式化
df = pd.DataFrame(ex)
# 迭代器遍历sheet页里的内容
for row in df.itertuples(name="RowData"):
# 实例化一个数据模型对象
ds = DataStructer()
# 用读到的excel行数据来填充这个对象
self.data_mapping(row, ds)
# 通过这个对象的属性值，来发起一次request请求，在请求的过程把结果及校验的数据处理完后，
self.send_req(ds)
print ds.__dict__
# 接口发起后的结果写入到excel对应行的对应列中
# 执行修改操作
df.update(
pd.Series(ds.result, name="test_result", index=[row.Index]))
df.update(pd.Series(ds.notes, name="test_notes",
index=[row.Index]))
# 执行数据更新后的保存操作：这里有个问题就是源文件覆盖保存，会没有特定的样式，需要再升级一下
# df.to_excel(unicode(self.filepath, "utf8"))
# 创建StyleFrame对象，该对象包装我们的DataFrame并分配默认样式。
defaults = {'font': utils.fonts.aharoni, 'font_size': 12}
sf = StyleFrame(df, styler_obj=Styler(**defaults))
"""
# Style the headers of the table
header_style = Styler(bold=True, font_size=14)
sf.apply_headers_style(styler_obj=header_style)
# Change the columns width and the rows height
sf.set_column_width(columns=sf.columns, width=20)
sf.set_row_height(rows=sf.row_indexes, height=90)
"""
sf.set_column_width_dict(
col_width_dict={
('cid', 'type', 'method', 'result'):
7,
('project', 'module', 'function', 'desc', 'protocol', 'assertion'):
13,
('url', ):
20,
('header', 'cookie', 'entity', 'assertion', 'notes'):
30
})
row_num = sf.row_indexes
sf.set_row_height_dict(row_height_dict={
row_num[0]: 28,
row_num[1:]: 90
})
sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.grey))
sf.to_excel(unicode(self.filepath, "utf8")).save()
print 30 * "*"
"Japanese Name", "English Name", "URL"
])
# 因為 Series 沒有橫列的標籤, 所以加進去的時候一定要 ignore_index=True
df = df.append(s, ignore_index=True)
page = page + 1
print("WebScrayping Completed.")
sf = StyleFrame(df) # 轉成StyleFrame格式
sf.set_column_width_dict(
col_width_dict={ # 設定列寬
("Picture"):
25.5,
("Stars", "Number of Reviews", "Average Cost", "Japanese Name", "English Name"):
20,
("URL"):
65.5
})
# 設定行高
all_rows = sf.row_indexes
sf.set_row_height_dict(row_height_dict={all_rows[1:]: 120})
# 將資料存入Excel檔
sf.to_excel(ExcelDataLocation,
sheet_name='Sheet1',
right_to_left=False,
columns_and_rows_to_freeze='A1',
row_to_add_filters=0).save()
