import pandas as pd import numpy as np # from pandas import DataFrame # from styleframe import StyleFrame import xlsxwriter import openpyxl # import Jinja2 from openpyxl.worksheet.datavalidation import DataValidation # Values have to be a list of list # Title_string have to be a list of list of strings class CreateTable: def __init__(self, dict_, insert=True, path=None, index=None, axis=0, ordered_by=[], order=[]): self.insert = insert self.path = path self.index = index self.dict = dict_ self.axis = axis self.ordered_by = ordered_by self.order = order self.column = [] self.currency_format = [] for key, v in self.dict.items(): self.column += [key] if self.order: ordered_column = [] for each_key in self.order: if each_key in self.column: ordered_column += [each_key] self.column.remove(each_key) else: assert (False, 'Order contains values which are not in the Table') self.column = ordered_column + self.column self.dataframes = pd.DataFrame(dict([(k, pd.Series(v)) for k, v in self.dict.items()]), columns=self.column) def create_writer(self): return pd.ExcelWriter(self.path, engine='xlsxwriter') # openpyxl xlsxwriter def create_openpyxl_writer(self): return pd.ExcelWriter(self.path, engine='openpyxl') # openpyxl xlsxwriter def load_openpyxl_workbook(self, additional_path=''): if additional_path: return openpyxl.load_workbook("dropdown.xlsx") return openpyxl.load_workbook("dropdown.xlsx") def create_workbook(self): return xlsxwriter.Workbook(self.path) def create_openpyxl_workbook(self): return openpyxl.Workbook(self.path) def get_dataframe(self): return self.dataframes def append_to_csv(self): self.dataframes.to_csv(path_or_buf=self.path, index=self.index, mode='a') def add_style_(self, style_dict): return self.dataframes.style.format(style_dict) def set_style_properties(self, style_dict): return self.dataframes.style.set_properties(**style_dict) def create_format(self): self.currency_format = self.create_workbook().add_format() def export_to_csv(self): if self.order: raise NotImplementedError if self.ordered_by == []: self.dataframes.to_csv(path_or_buf=self.path, index=self.index) else: self.dataframes = self.dataframes.sort_values(by=self.ordered_by, axis=self.axis) self.dataframes.to_csv(path_or_buf=self.path, index=self.index) def export_to_excel(self, writer, additional_dataframes=[]): if self.ordered_by == []: pass else: self.dataframes = self.dataframes.sort_values(by=self.ordered_by, axis=self.axis) # Format if self.currency_format: self.currency_format.set_align('center') else: self.create_format() self.currency_format.set_align('center') # First Tab self.dataframes.to_excel(excel_writer=writer, sheet_name='Karte ' + str(1), index=False) for column in self.dataframes: column_width = max(self.dataframes[column].astype(str).map(len).max(), len(column) + 5) col_idx = self.dataframes.columns.get_loc(column) writer.sheets['Karte ' + str(1)].set_column(col_idx, col_idx, column_width, cell_format=self.currency_format) # All other Tabs for count, each_dataframe in enumerate(additional_dataframes): each_dataframe.to_excel(writer, sheet_name='Karte ' + str(count + 2), index=False) for column in each_dataframe: column_width = max(each_dataframe[column].astype(str).map(len).max(), len(column) + 5) col_idx = each_dataframe.columns.get_loc(column) writer.sheets['Karte ' + str(count + 2)].set_column(col_idx, col_idx, column_width, cell_format=self.currency_format) writer.save() def append_df_to_xlsx(self, df): # TODO extract even more df_excel = pd.read_excel(self.path) self.dataframes = pd.concat([df_excel, df], ignore_index=True) self.export_to_excel(self.create_writer()) def create_custom_excel_economy_calendar(self, writer): if self.ordered_by == []: pass else: self.dataframes = self.dataframes.sort_values(by=self.ordered_by, axis=self.axis) # Format self.currency_format = self.create_workbook().add_format() self.currency_format.set_align('center') self.currency_format.set_align('center') self.dataframes.to_excel(excel_writer=writer, sheet_name='Karte ' + str(1), index=False) for column in self.dataframes: column_width = max(self.dataframes[column].astype(str).map(len).max(), len(column) + 5) col_idx = self.dataframes.columns.get_loc(column) writer.sheets['Karte ' + str(1)].set_column(col_idx, col_idx, column_width, cell_format=self.currency_format) writer.save() def add_another_page_to_excel(self, additional_dataframes, writer): # Format currency_format = self.create_workbook().add_format() currency_format.set_align('center') currency_format.set_align('center') for count, each_dataframe in enumerate(additional_dataframes): each_dataframe.to_excel(writer, sheet_name='Karte ' + str(count + 2), index=False) for column in each_dataframe: column_width = max(each_dataframe[column].astype(str).map(len).max(), len(column) + 5) col_idx = each_dataframe.columns.get_loc(column) writer.sheets['Karte ' + str(count + 2)].set_column(col_idx, col_idx, column_width, cell_format=currency_format) return writer def create_hyperlink_from_string(self, name: str): self.dataframes.style.format({name: make_clickable}) def make_clickable(val): # target _blank to open new window return '{}'.format(val, val) class XlsxWriterClass: def __init__(self, path, another_sheet=''): ''' :param path: Absolute Path :param list_of_sheets: in this version only two sheets can be created. A little bit sloppy.. ''' self.path = path self.wb = xlsxwriter.Workbook(self.path) self.ws = self.wb.add_worksheet('Karte 1') self.last_column = 0 self.alphabet = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ', ] if another_sheet: self.another_sheet = self.wb.add_worksheet('Karte2') self.last_column_sheet2 = 1 def create_format_for_data(self, added_style): self.style_ = self.wb.add_format(added_style) def del_current_format_for_data(self): self.style_ = False def set_column_prop(self, col, width): self.ws.set_column(first_col=col, last_col=col, width=width) def set_row(self, count_rows, height): for row in range(1, count_rows): self.ws.set_row(row, height=height) def write_data_in_free_column_with_bold_header(self, data, column_range=1, format_=False, data_with_own_format={}): ''' :param data: dict format [key: list_of_values] or list of lists :param column_range: only for list of lists :param data_with_own_format: tuple of data and format : (data, format) :return: ''' assert column_range + self.last_column <= len(self.alphabet), 'Alphabet len is to small add new Columns [self.alphabet]' if data_with_own_format: special_data = data_with_own_format[0] special_data_format = data_with_own_format[1] if isinstance(data, dict): for col, key in enumerate(data.keys()): offset=0 # Header self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1), key, self.wb.add_format({'bold': True})) if data_with_own_format: for row in range(1, 1 + len(special_data[key])): self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), special_data[key][row - 1], self.wb.add_format(special_data_format)) offset += 1 for row in range(1, 1 + len(data[key])): if format_: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row+offset), data[key][row - 1], self.style_) else: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row+offset), data[key][row - 1]) self.last_column += len(data.keys()) else: for col in range(0, column_range): offset=0 # Header self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1), data[col][0], {'bold': True}) if data_with_own_format: for row in range(1, 1 + len(special_data[col])): self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), special_data[col][row], self.wb.add_format(special_data_format)) offset += 1 for row in range(1, 1 + len(data[col])): if format_: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row+offset), data[col][row], self.style_) else: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row+offset), data[col][row]) self.last_column += len(data) def write_dict_of_dicts_in_free_column_with_color_for_each_cell(self, data, column_range=1): ''' :param data: dict format [key: {Color: [...], Value : [...]}] :param column_range: len of used columns for dict only in assertions :return: ''' assert column_range + self.last_column <= len(self.alphabet), 'Alphabet len is to small add new Columns [self.alphabet]' for col, key in enumerate(data.keys()): # Header self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1), key, self.wb.add_format({'bold': True})) for row in range(1, 1 + len(data[key])): self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), data[key][row - 1]['Value'], self.wb.add_format({'font_color': data[key][row - 1]['Color']})) self.last_column += len(data.keys()) def write_data_in_free_column_with_custom_style_and_dropdown(self, data, column_range=1, format_=False, source=[1]): ''' :param data: dict format [key: list_of_values] or list of lists :param column_range: only for list of lists :param source : list of dropdown values :return: ''' assert column_range + self.last_column <= len(self.alphabet), 'Alphabet len is to small add new Columns [self.alphabet]' if isinstance(data, dict): for col, key in enumerate(data.keys()): # Header self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1), key, self.wb.add_format({'bold': True})) for row in range(1, 1 + len(data[key])): if format_: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), data[key][row - 1], self.style_) self.ws.conditional_format('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'type': 'cell', 'criteria': '>=', 'value': 10, 'format': self.wb.add_format( { 'bg_color': '#90ee90', 'font_color': 'black'})}) self.ws.data_validation('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'validate': 'list', 'source': source }) else: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), data[key][row - 1]) self.ws.conditional_format('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'type': 'cell', 'criteria': '>=', 'value': 10, 'format': self.wb.add_format( { 'bg_color': '#90ee90', 'font_color': 'black'})}) self.ws.data_validation('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'validate': 'list', 'source': source }) self.last_column += len(data.keys()) else: for col in range(0, column_range): # Header self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1), data[col][0], {'bold': True}) for row in range(1, 1 + len(data[col])): if format_: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), data[col][row], self.style_) self.ws.conditional_format('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'type': 'cell', 'criteria': '>=', 'value': 10, 'format': self.wb.add_format( { 'bg_color': '#90ee90', 'font_color': 'black'})}) self.ws.data_validation('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'validate': 'list', 'source': source }) else: self.ws.write('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), data[col][row]) self.ws.conditional_format('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'type': 'cell', 'criteria': '>=', 'value': 10, 'format': self.wb.add_format( { 'bg_color': '#90ee90', 'font_color': 'black'})}) self.ws.data_validation('{}{}'.format(self.alphabet[col + self.last_column], 1 + row), {'validate': 'list', 'source': source }) self.last_column += len(data) def write_data_in_free_column_on_another_sheet(self, data, column_range=1, format_=False): ''' TODO This function does not work properly, generalization is recommended :param data: dict format or list of lists :param column_range: :return: ''' assert column_range <= len(self.alphabet), 'Alphabet len is to small add new Columns [self.alphabet]' if isinstance(data, dict): pass else: for col in range(0, column_range): # Generates 99 "ip" address in the Column A; for row in range(self.last_column_sheet2, self.last_column_sheet2 + len(data[col])): if format_: self.another_sheet.write('{}{}'.format(self.alphabet[col], row), data[col][row - 1], self.style_) else: self.another_sheet.write('{}{}'.format(self.alphabet[col], row), data[col][row - 1]) self.last_column_sheet2 = len(data) def save_and_close_excel(self): self.wb.close()