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 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 create_baking_excel(self):
data_frame = self.database_controller.to_data_frame()
data_collection = DataCollection()
data_collection.create(self.is_calibration, data_frame, self.fbg_names, self.main_queue)
column_ordering = [DATE_TIME_HEADER, DELTA_TIME_HEADER, TEMPERATURE_HEADER]
add_times(data_frame, data_collection)
add_wavelength_power_columns(column_ordering, data_frame)
add_baking_duplicate_columns(data_frame, data_collection)
column_ordering.extend([DELTA_TIME_HEADER1, DELTA_TEMPERATURE_HEADER1])
self.add_delta_wavelengths(data_frame, data_collection, column_ordering)
column_ordering.extend([DELTA_TIME_HEADER2, DELTA_TEMPERATURE_HEADER2])
self.add_delta_powers(data_frame, data_collection, column_ordering)
column_ordering.extend([DELTA_TEMPERATURE_HEADER3, MEAN_DELTA_WAVELENGTH_HEADER, MEAN_DELTA_POWER_HEADER])
data_frame[MEAN_DELTA_WAVELENGTH_HEADER] = data_collection.mean_delta_wavelengths_pm
data_frame[MEAN_DELTA_POWER_HEADER] = data_collection.mean_delta_powers
data_frame = data_frame[column_ordering]
baking_coefficients = self._create_baking_coefficients(data_frame)
trend_line_indexes = self._create_trend_lines(data_frame, baking_coefficients)
sensitivity_indexes = self._create_sensitivity_lines(data_frame, trend_line_indexes)
style_frame = self.create_style_frame(data_frame)
style_frame.apply_column_style(cols_to_style=MEAN_DELTA_WAVELENGTH_HEADER,
styler_obj=Styler(font_color=utils.colors.red))
style_frame.apply_column_style(cols_to_style=[DATE_TIME_HEADER],
styler_obj=Styler(number_format=utils.number_formats.date_time_with_seconds))
delta_temperature_headers = [col for col in data_frame.columns.values if DELTA_TEMPERATURE_HEADER in col]
style_frame.apply_column_style(cols_to_style=delta_temperature_headers,
styler_obj=Styler(font_color=utils.colors.red))
parameters = BakingGraphParameters(len(data_frame.index), trend_line_indexes, sensitivity_indexes)
self.show_excel([style_frame], ["Baking Data"], parameters, self._graph_bake_results,
baking_coefficients=baking_coefficients)
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().name
for i in range(2, len(self.sf))))
def test_from_openpyxl_style(self):
styler_obj = Styler(bg_color=utils.colors.yellow, bold=True, font=utils.fonts.david, font_size=16,
font_color=utils.colors.blue, number_format=utils.number_formats.date, protection=True,
underline=utils.underline.double, border_type=utils.borders.double,
horizontal_alignment=utils.horizontal_alignments.center,
vertical_alignment=utils.vertical_alignments.bottom, wrap_text=False, shrink_to_fit=True,
fill_pattern_type=utils.fill_pattern_types.gray0625, indent=1)
self.assertEqual(styler_obj, Styler.from_openpyxl_style(styler_obj.to_openpyxl_style(), []))
def setUpClass(cls):
cls.default_styler_obj = Styler(wrap_text=False)
cls.styler_obj_1 = Styler(bg_color=utils.colors.blue, bold=True, font='Impact', font_color=utils.colors.yellow,
font_size=20, 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 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 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 setUpClass(cls):
cls.cli = CommandLineInterface(TEST_JSON_FILE, TEST_FILENAME)
cls.sheet_1_col_a_style = Styler(
bg_color=utils.colors.blue,
font_color=utils.colors.yellow).create_style()
cls.sheet_1_col_a_cell_2_style = Styler(
bold=True,
font=utils.fonts.arial,
font_size=30,
font_color=utils.colors.green,
border_type=utils.borders.double).create_style()
cls.sheet_1_col_b_cell_4_style = Styler(bold=True,
font=utils.fonts.arial,
font_size=16).create_style()
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 _load_sheet(self, sheet):
sheet_name = sheet['sheet_name']
default_cell_style = sheet.get('default_styles', {}).get('cells')
data = defaultdict(list)
for col in sheet['columns']:
col_name = col['col_name']
col_width = col.get('width')
if col_width:
self.col_names_to_width[sheet_name][col_name] = col_width
for cell in col['cells']:
provided_style = cell.get('style') or col.get(
'style') or default_cell_style or {}
unrecognized_styler_kwargs = set(
provided_style.keys()) - styler_kwargs
if unrecognized_styler_kwargs:
raise TypeError(
'Styler dict {} contains unexpected argument: {}.\n'
'Expected arguments: {}'.format(
provided_style, unrecognized_styler_kwargs,
styler_kwargs))
else:
data[col_name].append(
Container(cell['value'],
Styler(**(provided_style)).create_style()))
sf = StyleFrame(pd.DataFrame(data=data))
self._apply_headers_style(sf, sheet)
self._apply_cols_and_rows_dimensions(sf, sheet)
sf.to_excel(excel_writer=self.excel_writer,
sheet_name=sheet_name,
**sheet.get('extra_features', {}))
setattr(self, '{}_sf'.format(sheet_name), sf)
def apply_highlights_pol_rows(self, sf, df):
# create masks based on row polarities
pol_sets = df.PossiblePols.apply(lambda x: set(x.keys()))
pp_mask = pol_sets.apply(lambda x: True if 'Pp' in x else False)
pn_mask = pol_sets.apply(lambda x: True if 'Pn' in x else False)
nc_mask = pol_sets.apply(lambda x: True if 'Nc' in x else False)
np_mask = pol_sets.apply(lambda x: True if 'Np' in x else False)
# list all cases and its colour code
list_cases = []
list_cases.append(
tuple([sf[(pp_mask & pn_mask & nc_mask & np_mask)], 'a80000']))
list_cases.append(
tuple([sf[(pp_mask & pn_mask & (nc_mask ^ np_mask))], 'ff0000']))
list_cases.append(
tuple([sf[(pp_mask & pn_mask & ~(nc_mask | np_mask))], 'ff6400']))
list_cases.append(
tuple([sf[((pp_mask ^ pn_mask) & nc_mask & np_mask)], 'ff9200']))
list_cases.append(
tuple([sf[(pp_mask & ~pn_mask & (nc_mask ^ np_mask))], 'ffc800']))
list_cases.append(
tuple([sf[(~pp_mask & pn_mask & (nc_mask ^ np_mask))], 'e3b200']))
list_cases.append(
tuple([sf[~pp_mask & ~pn_mask & nc_mask & np_mask], 'fffe00']))
list_cases.append(tuple([sf[sf.PolScore == 0], 'b3ff00']))
# apply
for case in list_cases:
sf.apply_style_by_indexes(indexes_to_style=case[0],
styler_obj=Styler(bg_color=case[1]))
def get_prices(ticker, writer, start_date, end_date):
df = yf.download(ticker, start=start_date[:10], end=end_date[:10])
i = 0
for val in df['Close']:
df.at[df.index[i], 'Close'] = int(val * 100) / 100
i += 1
df.insert(loc=0,
column='Date',
value=[datetime.strftime(d, "%Y-%m-%d") for d in df.index])
del df['Open']
del df['High']
del df['Adj Close']
del df['Low']
del df['Volume']
sf = StyleFrame(df)
sf.apply_column_style(
cols_to_style='Date',
styler_obj=Styler(number_format=utils.number_formats.date))
col_list = list(df.columns)
sf.to_excel(excel_writer=writer,
sheet_name=ticker + ' Price',
best_fit=col_list)
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 get_dividends(ticker, writer, quarters_back):
stock = yf.Ticker(ticker)
div_df = stock.actions
if (len(div_df) == 0 or
(div_df.loc[div_df.index[0], 'Dividends'] == 0 and len(div_df) == 1)):
return
del div_df['Stock Splits']
quarters_back_calc = len(div_df.index) - quarters_back
kept_div_df = div_df.drop(div_df.index[list(range(quarters_back_calc))])
kept_div_df.insert(
loc=0,
column='Ex-Dates',
value=[datetime.strftime(d, "%Y-%m-%d") for d in kept_div_df.index])
sf_div = StyleFrame(kept_div_df)
sf_div.apply_column_style(
cols_to_style='Ex-Dates',
styler_obj=Styler(number_format=utils.number_formats.date))
col_list_div = list(kept_div_df.columns)
sf_div.to_excel(excel_writer=writer,
sheet_name=ticker + ' Dividends',
best_fit=col_list_div)
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 setUpClass(cls):
cls.yellow_1 = Styler(bg_color='yellow')
cls.yellow_2 = Styler(bg_color='yellow')
cls.blue = Styler(bg_color='blue')
cls.bold = Styler(bold=True)
cls.underline = Styler(underline='single')
cls.yellow_bold_underline = Styler(bg_color='yellow', bold=True, underline='single')
def create_calibration_excel(self):
data_frame = self.database_controller.to_data_frame()
data_collection = DataCollection()
data_collection.create(self.is_calibration, data_frame, self.fbg_names, self.main_queue)
real_point_data_frame = data_frame[data_frame[REAL_POINT_HEADER] == "True"]
cycles = list(set(real_point_data_frame[CYCLE_HEADER]))
cycles.sort()
del real_point_data_frame[REAL_POINT_HEADER]
del real_point_data_frame[DATE_TIME_HEADER]
full_column_ordering = [DATE_TIME_HEADER, DELTA_TIME_HEADER, TEMPERATURE_HEADER]
add_times(data_frame, data_collection)
add_wavelength_power_columns(full_column_ordering, data_frame)
full_column_ordering.extend([CYCLE_HEADER, REAL_POINT_HEADER])
data_frame = data_frame[full_column_ordering]
container = CalibrationExcelContainer(real_point_data_frame, cycles)
calibration_data_frame = container.get_data_frame()
calibration_style_frame = self.create_style_frame(calibration_data_frame)
full_style_frame = self.create_style_frame(data_frame)
delta_temperature_headers = [col for col in data_frame.columns.values if DELTA_TEMPERATURE_HEADER in col]
full_style_frame.apply_column_style(cols_to_style=delta_temperature_headers,
styler_obj=Styler(font_color=utils.colors.red))
full_style_frame.apply_column_style(cols_to_style=[DATE_TIME_HEADER],
styler_obj=
Styler(number_format=utils.number_formats.date_time_with_seconds))
first_column = "Temperature (K) Cycle {}".format(container.cycles[0])
temperatures = calibration_data_frame[first_column].values
parameters = CalibrationGraphParameters(len(calibration_data_frame.index), temperatures,
container.cycles, container.mean_wavelength_indexes,
container.deviation_wavelength_indexes, container.mean_power_indexes,
container.deviation_power_indexes,
container.sensitivity_wavelength_indexes,
container.sensitivity_power_indexes,
container.master_temperature_column, container.mean_temperature_column)
coefficients = [container.wavelength_mean_coefficients, container.power_mean_coefficients]
self.show_excel([calibration_style_frame, full_style_frame], ["Cal", "Full Cal"],
parameters, self._graph_calibration_results, coefficients)
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 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 apply_highlights_mut_rows(self, sf, df):
# create masks based on row mutations
colour_base = 127
for index, row in df.iterrows():
muts_dict = row.PossibleMuts
rgb = tuple([
colour_base+muts_dict['Sil']*(255-colour_base) if 'Sil' in muts_dict else colour_base,
colour_base+muts_dict['Mis']*(255-colour_base)*5 if 'Mis' in muts_dict else colour_base,
colour_base+muts_dict['Non']*(255-colour_base)*5 if 'Non' in muts_dict else colour_base
])
sf.apply_style_by_indexes(
indexes_to_style=[index],
styler_obj=Styler(bg_color=self.rgb2hex(*rgb))
)
def combine_csvs(self, delete_batches: bool = False, to_excel: bool = True):
"""Combines batch csv output files into the final csv file and optionally an excel file
Args:
delete_batches (bool): Whether the batch csv files should be deleted after stitching them to the combined
csv file. Defaults to False (not deleting the batch csv files)
to_excel (bool): Whether the csv file should also be saved as an excel file. Defaults to True (creating the
Excel file)
"""
logger = sip.MapsXmlParser.create_logger(self.log_file_path)
items = os.listdir(self.csv_base_path)
stitched_csvs = []
for name in items:
if name.endswith('_stitched.csv'):
stitched_csvs.append(name)
stitched_csvs.sort()
annotation_tiles = {}
for csv in stitched_csvs:
current_tiles = sip.MapsXmlParser.load_annotations_from_csv(self.base_header, self.csv_base_path / csv)
for key in current_tiles:
annotation_tiles[key] = current_tiles[key]
csv_output_path = self.csv_base_path / (self.project_name + '_fused' + '.csv')
sip.MapsXmlParser.save_annotation_tiles_to_csv(annotation_tiles, self.base_header, csv_output_path)
# Delete all the batch files if the option is set for it
if delete_batches:
for name in items:
os.remove(self.csv_base_path / name)
if to_excel:
logger.info('Saving the annotations csv as an excel file')
data_frame = pd.read_csv(csv_output_path)
excel_output_path = self.csv_base_path / (self.project_name + '_fused' + '.xlsx')
# Create a list of headers whose column should be expanded to fit the content
fitting_headers = list(data_frame.columns.values)
non_fitting_headers = ['img_path', 'surrounding_tile_names', 'surrounding_tile_exists']
for header in non_fitting_headers:
if header in fitting_headers:
fitting_headers.remove(header)
no_wrap_text_style = Styler(wrap_text=False, shrink_to_fit=False)
excel_writer = StyleFrame.ExcelWriter(excel_output_path)
styled_df = StyleFrame(data_frame, styler_obj=no_wrap_text_style)
styled_df.to_excel(excel_writer, 'MapsAnnotations', index=False, columns_and_rows_to_freeze='A1',
best_fit=fitting_headers)
excel_writer.save()
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
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 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
def _load_sheet(self, sheet):
sheet_name = sheet['sheet_name']
default_cell_style = sheet.get('default_styles', {}).get('cells')
data = defaultdict(list)
for col in sheet['columns']:
col_name = col['col_name']
col_width = col.get('width')
if col_width:
self.col_names_to_width[sheet_name][col_name] = col_width
for cell in col['cells']:
data[col_name].append(
Container(
cell['value'],
Styler(
**(cell.get('style') or col.get('style')
or default_cell_style or {})).create_style()))
sf = StyleFrame(pd.DataFrame(data=data))
self._apply_headers_style(sf, sheet)
self._apply_cols_and_rows_dimensions(sf, sheet)
sf.to_excel(excel_writer=self.excel_writer,
sheet_name=sheet_name,
**sheet.get('extra_features', {}))
setattr(self, '{}_sf'.format(sheet_name), sf)
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()
def test_add(self):
self.assertEqual(self.yellow_1 + self.bold + self.underline, self.yellow_bold_underline)
self.assertEqual(self.yellow_2.bold, False)
self.assertEqual(self.yellow_2 + Styler(), self.yellow_1)
def test_combine(self):
self.assertEqual(Styler.combine(self.yellow_1, self.bold, self.underline), self.yellow_bold_underline)
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
