Tools/tool_lib/create_excel_tables.py

import pandas as pd
import numpy as np
import xlsxwriter
import openpyxl
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 '<a target="_blank" href="{}">{}</a>'.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()