Tools/tool_lib/create_excel_tables.py

import pandas as pd
import numpy as np
from pandas import DataFrame
from styleframe import StyleFrame
import xlsxwriter

# 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 = []
        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_workbook(self):
         return xlsxwriter.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 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
        currency_format = self.create_workbook().add_format()
        currency_format.set_align('center')
        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=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=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())