class Data_preparation(): def __init__(self): self.log = Logger() self.log.message_debug("Create class Data preparation") def tmp(self): Cars = { 'Brand': ['Honda Civic', 'Toyota Corolla', 'Ford Focus', 'Audi A4'], 'Price': [22000, 25000, 27000, 35000], 'Format': [ { "style": { 'Brand': "highlight" }, "merge_cell": { 'Brand': [2, 0] } }, 0, 0, { "style": { 'Price': "highlight" } }, ] } return Cars
class Style_pandas(): def __init__(self): self.log = Logger() self.log.message_debug("Create class Style_pandas") def pars_style_from_dataframe(self, str_format): try: list_format = str_format.split(';') for format in list_format: dict_format = ast.literal_eval(format) if type(dict_format) == dict: for key in dict_format.keys(): style_from_column = dict_format[key].items() for col, style in style_from_column: yield key, col, style except Exception as e: self.log.message_error(e) return 0 def highlight_max(self, s): ''' highlight the maximum in a Series yellow. ''' # is_max = s == s.max() return 'background-color: yellow' def color_negative_red(self, val): """ Takes a scalar and returns a string with the css property `'color: red'` for negative strings, black otherwise. """ color = 'red' return 'merge-cells: B2:B4' def fill_style(self, DataFrame): DataFrame.style. \ applymap(self.color_negative_red)
class Read_data(): def __init__(self): self.log = Logger() self.log.message_debug("Create class Read_data") def read_file_excel(self, path="../template/ПО V2.xlsx", sheet='ПрайсПО'): try: # self.DataFrame_Sheet = pd.read_excel(work_path_project, # sheet_name=self.workSheets_TC_TU, # skiprows=1, # usecols=self.work_cols) DataFrame = pd.read_excel(path, sheet_name=sheet, engine='openpyxl') self.log.message_debug("Read file path: {}".format(path)) return DataFrame except Exception as e: self.log.message_error(e) return 0
def __init__(self): self.log = Logger() self.log.message_debug("Create class Style_pandas")
def __init__(self): self.log = Logger() self.log.message_debug("Create class Data preparation")
def __init__(self): self.log = Logger()
class Sheets(): def __init__(self): self.log = Logger() # зачитка листа excel # -------------------------------------------------------------------------------------------------------------- def color_negative_red(self, val): """ Takes a scalar and returns a string with the css property `'color: red'` for negative strings, black otherwise. """ color = 'red' return 'merge-cells: B2:B4' def highlight_max(self, s): ''' highlight the maximum in a Series yellow. ''' # is_max = s == s.max() return 'background-color: yellow' def create_df(self): self.df = pd.DataFrame( [('bird', 'Falconiformes', 389.0), ('bird', 'Psittaciformes', 24.0), ('mammal', 'Carnivora', 80.2), ('mammal', 'Primates', np.nan), ('mammal', 'Carnivora', 58)], index=['falcon', 'parrot', 'lion', 'monkey', 'leopard'], columns=('class', 'order', 'max_speed')) # np.random.seed(24) # self.df = pd.DataFrame({'A': np.linspace(1, 1.2, 10)}) # self.df = pd.concat([self.df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))], # axis=1) # self.df.iloc[0, 2] = np.nan def write_to_excel(self): with pd.ExcelWriter('styled.xlsx') as writer: # ws = writer.active writer.merge_cells('B2:B8') self.df.style. \ applymap(self.color_negative_red). \ to_excel(writer, sheet_name='Sheet1') def read_sheet_by_excel(self): work_path_project = "../template/ПО.xlsx" workSheets_TC_TU = 'ПрайсПО' try: # self.DataFrame_Sheet = pd.read_excel(work_path_project, # sheet_name=self.workSheets_TC_TU, # skiprows=1, # usecols=self.work_cols) self.DataFrame_Sheet = pd.read_excel(work_path_project, sheet_name=workSheets_TC_TU, engine='openpyxl') print(1) except Exception as e: self.log.message_error(e) return 0 def DataFarme_to_Json(self): self.DataFrame_Sheet.to_json('tmp.json', orient='values', date_format="iso") def DataFarme_to_Excel(self): self.DataFrame_Sheet.to_excel('tmp.xlsx', engine='openpyxl')
def __init__(self): self.log = Logger() self.log.message_debug("Create class Write_data") self.sp = Style_pandas() self.fm = Formating() self.ido = 1
class Excel(): def __init__(self): self.log = Logger() self.log.message_debug("Create class Write_data") self.sp = Style_pandas() self.fm = Formating() self.ido = 1 # self.dp=Data_preparation() def CustomParser(self, data): import ast res = ast.literal_eval(data) print(res) # import json # n = json.dumps(data) # j1 = json.loads(n) return res def read_xlsx_load_workbook(self, path, file_name, sheet_name='Лист1'): wb = load_workbook(filename=os.path.join(path, file_name), read_only=True) ws = wb[sheet_name] DataFrame = pd.DataFrame() for row in ws.iter_rows(): DataFrame = DataFrame.append(pd.Series( [cell.value for cell in row]), ignore_index=True) DataFrame = pd.DataFrame(DataFrame.values[1:], columns=DataFrame.iloc[0]) wb.close() return DataFrame def read_xlsx_pandas(self, path, file_name, sheet_name='Лист1'): try: DataFrame = pd.read_excel(os.path.join(path, file_name), sheet_name=sheet_name, engine='openpyxl', data_only=False) # DataFrame=DataFrame.replace('\'','',regex=True) # DataFrame.Sum.str.upper() # i=DataFrame.Sum.str.replace('_', '',regex=True) # DataFrame=DataFrame.dropna(how='all') except Exception as e: self.log.message_error(e) return 0 return DataFrame def create_marks(self, descript, list_mark): for row in descript.iter_rows(max_row=22): for cell in row: mark = str(cell.value).partition("~")[2].partition("~")[0] if mark in list_mark: mark_in_excel = { 'startcol': cell.column, 'startrow': cell.row } yield mark, mark_in_excel.values() def create_xlsx(self, path='', original_file='template.xlsx', copy_file='document.xlsx'): shutil.copyfile(os.path.join(path, original_file), os.path.join(path, copy_file)) main_path = os.path.join(path, copy_file) primary = load_workbook(main_path) primary.add_named_style(self.fm.template_format()) primary.add_named_style(self.fm.template_format1()) primary.save(main_path) primary.close() def add_sheet_to_xlsx(self, path='', file_name='', new_title=''): main_path = os.path.join(path, file_name) primary = load_workbook(main_path) ws = primary.get_sheet_by_name('ПрайсПО') primary.copy_worksheet(ws) self.ido += 1 wss = primary.worksheets[self.ido] wss.title = new_title primary.save(main_path) return primary def write_to_excel(self, mv, DataFrame, path='', file_name='document.xlsx', list_columns=None, sheet=''): if sheet == '': list_sheet = dict(map(lambda ws: (ws.title, ws), mv.worksheets)) else: list_sheet = [sheet] for sheet in list_sheet: get_descript_sheet = mv[sheet] tmp_startrow = 0 for mark, (startcol, startrow) in self.create_marks(get_descript_sheet, list_columns): self.log.message_debug("Finde mark: {}".format(mark)) if startrow != tmp_startrow and len(DataFrame.index) > 1: tmp_startrow = startrow get_descript_sheet.insert_rows(startrow + 1, len(DataFrame)) for index, row in enumerate(DataFrame.loc[:, [mark]].iterrows()): if type( get_descript_sheet.cell( row=startrow + index, column=startcol)).__name__ != 'MergedCell': cell = get_descript_sheet.cell(row=startrow, column=startcol) new_cell = get_descript_sheet.cell( row=startrow, column=startcol).offset(row=index, column=0) from openpyxl.formula.translate import Translator from openpyxl.formula import Tokenizer tok = Tokenizer(str(row[1][mark])) if tok.formula.startswith('='): new_cell.value = Translator( row[1][mark], origin=cell.coordinate).translate_formula( row_delta=startrow - 2) else: new_cell.value = row[1][mark] if cell.has_style: new_cell.font = copy(cell.font) new_cell.border = copy(cell.border) new_cell.fill = copy(cell.fill) new_cell.number_format = copy(cell.number_format) new_cell.protection = copy(cell.protection) new_cell.alignment = copy(cell.alignment) for key, col, style in self.sp.pars_style_from_dataframe( DataFrame.iloc[index]['Format']): if col == mark: if key == 'style': get_descript_sheet.cell( row=startrow + index, column=startcol).style = style if key == 'merge_cell': get_descript_sheet.merge_cells( start_row=startrow + index, start_column=startcol, end_row=startrow + index + style[1], end_column=startcol + style[0]) if key == 'hide': get_descript_sheet.row_dimensions[ startrow + index].hidden = True new_cell.value = style mv.save(os.path.join(path, file_name)) # mv.close() self.log.message_debug("Write file: {}".format(path))
def __init__(self): self.log = Logger() self.log.message_debug("Create class Read_data")