def write2xl(self, ew, title_list, data_list, sheet_name):
logging.debug('method: write2xl')
logging.debug(title_list)
df = pd.DataFrame(columns=title_list, data=data_list)
defaults = {'font': utils.fonts.aharoni, 'font_size': 14}
list_sf = StyleFrame(df, styler_obj=Styler(**defaults))
# Style the headers of the table
header_style = Styler(bold=True, font_size=18)
list_sf.apply_headers_style(styler_obj=header_style)
# Change the columns width and the rows height
list_sf.set_column_width(columns=list_sf.columns, width=30)
list_sf.set_row_height(rows=list_sf.row_indexes, height=25)
# list_sf.apply_column_style(cols_to_style=title_list)
list_sf.to_excel(
excel_writer=ew,
sheet_name=sheet_name,
# Add filters in row 0 to each column.
row_to_add_filters=0,
# Freeze the columns before column 'A' (=None) and rows above '2' (=1),
# columns_and_rows_to_freeze='A2'
).save()
def style_df(df):
# Create StyleFrame object that wrap our DataFrame and assign default style.
defaults = {'font': utils.fonts.aharoni, 'font_size': 11}
sf = StyleFrame(df, styler_obj=Styler(**defaults))
# Style the headers of the table
header_style = Styler(bold=True, font_size=11)
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=25)
return sf
def create_style_frame(self, data_frame: pd.DataFrame):
defaults = {'font_size': 14}
style_frame = StyleFrame(data_frame, styler_obj=Styler(**defaults, shrink_to_fit=False, wrap_text=False))
header_style = Styler(bold=True, font_size=18)
style_frame.set_column_width(columns=style_frame.columns, width=35)
style_frame.apply_headers_style(styler_obj=header_style)
for fbg_name, hex_color in zip(self.fbg_names, HEX_COLORS):
style_frame.apply_column_style(cols_to_style=[col for col in data_frame.columns.values if fbg_name in col],
styler_obj=Styler(bg_color=hex_color))
temperature_headers = [col for col in data_frame.columns.values if "Temperature" in col]
style_frame.apply_column_style(cols_to_style=temperature_headers,
styler_obj=Styler(font_color=utils.colors.red))
return style_frame
def _handle_export_button_click(self):
utils.create_missing()
path = QtWidgets.QFileDialog.getSaveFileName(
self, "Сохранить",
os.path.join(
utils.get_exports_path(),
utils.get_file_name_for_export(self.company_id, self.period)),
"Файл Excel (*.xlsx)")
self.export_button.setEnabled(False)
if not path:
return
elif os.path.exists(path[0]):
os.remove(path[0])
headers = parsing.table_columns
with StyleFrame.ExcelWriter(path[0]) as writer:
export_data = [[float(cell) for cell in row[1:]]
for row in self.data]
export_string_codes = [int(row[0]) for row in self.data]
df = DataFrame(data=export_data,
columns=headers,
index=export_string_codes)
df.index.name = "Код строки"
sf = StyleFrame(df)
sf.apply_headers_style(Styler(bold=True, font_size=14))
sf.to_excel(writer,
sheet_name='Полная таблица',
index=True,
best_fit=headers)
writer.save()
self.export_button.setEnabled(True)
result = QtWidgets.QMessageBox.question(
self,
"Успех!",
"Данные успешно экспортированы!\nОткрыть файл?",
buttons=QtWidgets.QMessageBox.Yes | QtWidgets.QMessageBox.No)
if result == QtWidgets.QMessageBox.Yes:
command = 'start' if Runtime.is_windows else 'xdg-open'
subprocess.Popen([command, os.path.normpath(path[0])], shell=True)
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 style_df_all(df):
# Create StyleFrame object that wrap our DataFrame and assign default style.
defaults = {'font': utils.fonts.aharoni, 'font_size': 10}
sf = StyleFrame(df, styler_obj=Styler(**defaults))
# Style the headers of the table
header_style = Styler(bold=True, font_size=11)
sf.apply_headers_style(styler_obj=header_style)
# Change the columns width and the rows height
sf.set_column_width(columns=sf.columns, width=12)
sf.set_column_width(
columns=['transDate', 'description', 'remark', 'amountMoney'],
width=20)
sf.set_column_width(columns=["shoppingsheetId", "billId", "relationId"],
width=30)
sf.set_row_height(rows=sf.row_indexes, height=25)
# Set the background color to red where the test marked as 'failed'
valid_style = Styler(bg_color=utils.colors.red,
font_color=utils.colors.white,
**defaults)
invalid_style = Styler(bg_color=utils.colors.green,
font_color=utils.colors.black,
**defaults)
# sf.apply_style_by_indexes(indexes_to_style=sf[sf['是否有效流水'] == 0],
# cols_to_style='amountMoney',
# styler_obj=valid_style)
sf.apply_style_by_indexes(indexes_to_style=sf[sf['是否有效流水'] == 1],
cols_to_style='amountMoney',
styler_obj=invalid_style)
# 单元格左对齐
col_style = Styler(horizontal_alignment=utils.horizontal_alignments.left,
font_size=10)
# sf.set_column_width(columns=["remark"], width=80)
# sf.apply_column_style(cols_to_style=["remark", 'description', 'nameOnOppositeCard'],
# styler_obj=col_style)
return sf
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"]
if valid_issue == False:
print ('Issue not assigned to anyone!')
continue
if 'status' in issue.raw['fields'] and not (issue.raw['fields']['status'] is None):
if 'statusCategory' in issue.raw['fields']['status'] and not (issue.raw['fields']['status']['statusCategory'] is None):
if 'name' in issue.raw['fields']['status']['statusCategory'] and not (issue.raw['fields']['status']['statusCategory']['name'] is None):
print ('Current status=' + issue.raw['fields']['status']['statusCategory']['name'])
state = issue.raw['fields']['status']['statusCategory']['name']
if state != 'Done':
new_dic[assignee][2] += 1
if (create_date > start_date and create_date < end_date):
print ('This issue created in this time')
new_dic[assignee][0] += 1
if (update_date > start_date) and (update_date < end_date) and (state == 'Done'):
print ('This issue completed in this time')
new_dic[assignee][1] += 1
df = pandas.DataFrame(new_dic)
df.index = ['Tasks Created', 'Tasks Completed', 'Tasks pending']
print(df.T)
#export output to excel sheet
writer = StyleFrame.ExcelWriter("taskreport.xlsx")
sf=StyleFrame(df.T)
sf.apply_column_style(cols_to_style=df.T.columns, styler_obj=Styler(bg_color=utils.colors.white, bold=True, font=utils.fonts.arial,font_size=8),style_header=True)
sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.blue, bold=True, font_size=8, font_color=utils.colors.white,number_format=utils.number_formats.general, protection=False))
sf.to_excel(writer, sheet_name='Sheet1', index=True)
writer.save()
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 * "*"
import pandas as pd
from StyleFrame import StyleFrame, Styler
# df1 = pd.read_excel('D:/DSR/BIDW_Daily_load_status_04_14_2020_SO.xlsx',index_col=None)
df2 = pd.read_excel('D:/DSR/DRR.xlsx', index_col=False, header=None)
# df3 = pd.read_excel('D:/DSR/Daily_load_status _4_14_2020_INTLAR.xlsx',index_col=None)
df4 = pd.read_excel('D:/DSR/IURR.xlsx', index_col=False, header=None)
# df5 = pd.read_excel('D:/DSR/Daily_Status_Report_04_14_2020_AR.xlsx',index_col=None)
final = pd.concat([df2, df4])
# final.to_excel('D:/DSR/final_dsr.xlsx',index=False)
sf = StyleFrame(final)
sf.apply_style_by_indexes(sf[sf['Status'] == 'Running on track'],
cols_to_style='Status',
styler_obj=Styler(bg_color='green'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'Delayed'],
cols_to_style='Status',
styler_obj=Styler(bg_color='red'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'Completed'],
cols_to_style='Status',
styler_obj=Styler(bg_color='green'))
sf.apply_style_by_indexes(sf[sf['Status'] == 'On Hold'],
cols_to_style='Status',
styler_obj=Styler(bg_color='purple'))
sf.to_excel('D:/DSR/final_dsr.xlsx').save()
sf.apply_headers_style()
