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")