123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687 |
- 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())
|