create_excel_tables.py 3.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
  1. import pandas as pd
  2. import numpy as np
  3. from pandas import DataFrame
  4. from styleframe import StyleFrame
  5. import xlsxwriter
  6. # Values have to be a list of list
  7. # Title_string have to be a list of list of strings
  8. class CreateTable:
  9. def __init__(self, dict_, insert=True, path=None, index=None, axis=0, ordered_by=[], order=[]):
  10. self.insert = insert
  11. self.path = path
  12. self.index = index
  13. self.dict = dict_
  14. self.axis = axis
  15. self.ordered_by = ordered_by
  16. self.order = order
  17. self.column = []
  18. for key, v in self.dict.items():
  19. self.column += [key]
  20. if self.order:
  21. ordered_column = []
  22. for each_key in self.order:
  23. if each_key in self.column:
  24. ordered_column += [each_key]
  25. self.column.remove(each_key)
  26. else:
  27. assert (False, 'Order contains values which are not in the Table')
  28. self.column = ordered_column + self.column
  29. self.dataframes = pd.DataFrame(dict([(k, pd.Series(v)) for k, v in self.dict.items()]), columns=self.column)
  30. def create_writer(self):
  31. return pd.ExcelWriter(self.path, engine='xlsxwriter') #openpyxl xlsxwriter
  32. def create_workbook(self):
  33. return xlsxwriter.Workbook(self.path)
  34. def get_dataframe(self):
  35. return self.dataframes
  36. def append_to_csv(self):
  37. self.dataframes.to_csv(path_or_buf=self.path, index=self.index, mode='a')
  38. def export_to_csv(self):
  39. if self.order:
  40. raise NotImplementedError
  41. if self.ordered_by == []:
  42. self.dataframes.to_csv(path_or_buf=self.path, index=self.index)
  43. else:
  44. self.dataframes = self.dataframes.sort_values(by=self.ordered_by, axis=self.axis)
  45. self.dataframes.to_csv(path_or_buf=self.path, index=self.index)
  46. def export_to_excel(self, writer, additional_dataframes=[]):
  47. if self.ordered_by == []:
  48. pass
  49. else:
  50. self.dataframes = self.dataframes.sort_values(by=self.ordered_by, axis=self.axis)
  51. #Format
  52. currency_format = self.create_workbook().add_format()
  53. currency_format.set_align('center')
  54. currency_format.set_align('center')
  55. # First Tab
  56. self.dataframes.to_excel(excel_writer=writer, sheet_name='Karte ' + str(1), index=False)
  57. for column in self.dataframes:
  58. column_width = max(self.dataframes[column].astype(str).map(len).max(), len(column)+5)
  59. col_idx = self.dataframes.columns.get_loc(column)
  60. writer.sheets['Karte ' + str(1)].set_column(col_idx, col_idx, column_width, cell_format=currency_format)
  61. # All other Tabs
  62. for count, each_dataframe in enumerate(additional_dataframes):
  63. each_dataframe.to_excel(writer, sheet_name='Karte ' + str(count + 2),index=False)
  64. for column in each_dataframe:
  65. column_width = max(each_dataframe[column].astype(str).map(len).max(), len(column)+5)
  66. col_idx = each_dataframe.columns.get_loc(column)
  67. writer.sheets['Karte ' + str(count + 2)].set_column(col_idx, col_idx, column_width, cell_format=currency_format)
  68. writer.save()
  69. def append_df_to_xlsx(self, df): #TODO extract even more
  70. df_excel = pd.read_excel(self.path)
  71. self.dataframes = pd.concat([df_excel, df], ignore_index=True)
  72. self.export_to_excel(self.create_writer())