class PlotBarWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", x_label=None, y_label=None, parent=None, already_sorted=False):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self._x_label = x_label
self._y_label = y_label
self._already_sorted = already_sorted
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.ax.bar(self._labels, self._data)
if self._x_label != None:
self.ax.set_xlabel(self._x_label)
if self._y_label != None:
self.ax.set_ylabel(self._y_label)
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
field_name = self._field_name.text()
if file_name != "":
title = self.ax.title.get_text()
last_row = len(self._data)+2
labels = sorted(self._labels)
if self._already_sorted:
sorted_data = {label:[self._data[i]] for i, label in enumerate(self._labels)}
else:
data = {label:[self._data[i]] for i, label in enumerate(self._labels)}
sorted_data = {label:data[label] for label in labels}
df = pd.DataFrame(data=sorted_data)
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name=field_name, index=False)
workbook = writer.book
worksheet = writer.sheets[field_name]
chart = workbook.add_chart({"type": 'column'})
chart.set_title({"name": title})
if self._x_label != None:
chart.set_x_axis({'name': self._x_label})
if self._y_label != None:
chart.set_y_axis({'name': self._y_label})
chart.add_series({"categories": "={fn}!$A$1:${lc}$1".format(lc=self._last_column, fn=field_name),
"values": "={fn}!$A$2:${lc}$2".format(lc=self._last_column, fn=field_name),
"fill": {'color': '#0000CC'}})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()
class PlotPieWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", random_colors=False, has_explode=True, parent=None):
super().__init__(parent)
self._main_widget = QWidget(self)
self._layout = QVBoxLayout(self._main_widget)
self._title = title
self._data = data
self._df_data = {k:v for (k,v) in zip(labels, data)}
self.setWindowTitle(title)
# Pie chart, where the slices will be ordered and plotted counter-clockwise:
self._labels = labels
self._sizes = data
self._legend_labels = [label+" - "+"{:.1f}".format(value)+"%" for label, value in zip(self._labels, self._sizes)]
self._last_column = excelColumnFromNumber(len(self._labels))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
explode = [0 for value in data]
explode[1] = 0.1
explode = tuple(explode)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self._canvas = FigureCanvas(self._figure)
colors = ['yellowgreen', 'lightskyblue']
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
if random_colors:
if has_explode:
self.ax.pie(self._sizes, explode=explode, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
if has_explode:
self.ax.pie(self._sizes, explode=explode, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
else:
self.ax.pie(self._sizes, colors=colors, autopct = "%1.1f%%", shadow=True, startangle=int(90))
self.ax.legend(labels=self._legend_labels, loc="best")
# Equal aspect ratio ensures that pie is drawn as a circle.
self.ax.axis("equal")
self.ax.set_title(title)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
if file_name != "":
title = self.ax.title.get_text()
df = pd.DataFrame(data=[self._df_data])
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Pie_Chart', index=False)
workbook = writer.book
worksheet = writer.sheets["Pie_Chart"]
chart = workbook.add_chart({"type": 'pie'})
chart.set_title({"name": title})
chart.add_series({"categories": "=Pie_Chart!$A$1:${lc}$1".format(lc = self._last_column), "values": "=Pie_Chart!$A$2:${lc}$2".format(lc = self._last_column)})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()
class PlotHBarWindow(QMainWindow):
def __init__(self, title, labels=[], data=[], table_name="", field_name="", parent=None):
super().__init__(parent)
self._title = title
self._labels = labels
self._data = data
self._last_column = excelColumnFromNumber(len(self._data))
self._plot_location = excelColumnFromNumber(len(self._data)+2)
self.setWindowTitle(self._title)
self._figure = plt.figure(figsize=(5, 4), dpi=100, facecolor=(1,1,1), edgecolor=(0,0,0))
self.ax = self._figure.add_subplot()
self.ax.set_title(self._title)
self._canvas = FigureCanvas(self._figure)
self._navigation_toolbar = NavigationToolbar(self._canvas, None)
self.addToolBar(self._navigation_toolbar)
self._bottom_toolbar = QToolBar(self)
self._bottom_toolbar.setMovable(False)
self._bottom_toolbar.setFloatable(False)
self._bottom_toolbar.setStyleSheet("QToolBar {border-bottom: None; border-top: 1px solid #BBBBBB;}")
self._table_name_label = QLabel(" Table:")
self._field_name_label = QLabel(" Field:")
self._table_name = FormEntry(self)
self._table_name.setMaximumHeight(20)
self._field_name = FormEntry(self)
self._field_name.setMaximumHeight(20)
self._table_name.setReadOnly(True)
self._field_name.setReadOnly(True)
self._table_name.setText(table_name)
self._field_name.setText(field_name)
self._bottom_toolbar.addWidget(self._table_name_label)
self._bottom_toolbar.addWidget(self._table_name)
self._bottom_toolbar.addWidget(self._field_name_label)
self._bottom_toolbar.addWidget(self._field_name)
self._export_chart_button = QPushButton("Export")
self._export_chart_button.setIcon(QIcon(QPixmap("export.png")))
self._export_chart_button.clicked.connect(self.exportChart)
self._bottom_toolbar.addWidget(HorizontalFiller(self))
self._bottom_toolbar.addWidget(self._export_chart_button)
self.addToolBar(Qt.BottomToolBarArea, self._bottom_toolbar)
y_pos = np.arange(len(data))
self.ax.barh(y_pos, data, align="center", color='lightskyblue', alpha=0.5)
self.ax.set_xlabel(labels[len(labels)-1])
#self.ax.set_ylabel(labels[1])
rects = self.ax.patches
low_rect = rects[0]
high_rect = rects[len(rects)-1]
width = low_rect.get_width()
self.ax.text(low_rect.get_x()+width, low_rect.get_y()+1, "Lowest: $"+str(data[0]))
width = high_rect.get_width()
self.ax.text(high_rect.get_x(), high_rect.get_y()+1, "Highest: $"+str(data[len(data)-1]))
self.setCentralWidget(self._canvas)
def exportChartFileDialog(self):
file_dialog = QFileDialog()
file_dialog.setNameFilters(["*. xlsx"])
file_name, ext = file_dialog.getSaveFileName(self, 'Export File', "", "Excel (*.xlsx)")
if file_name and ext == "Excel (*.xlsx)":
return file_name
return ""
def exportChart(self):
file_name = self.exportChartFileDialog()
field_name = self._field_name.text()
if file_name != "":
title = self.ax.title.get_text()
last_row = len(self._data)+2
col_1 = ["" for data in self._data]
col_2 = col_1.copy()
col_1[0] = self._data[0]
col_2[0] = self._data[len(self._data)-1]
data = {
self._labels[0]: self._data,
self._labels[1]: col_1,
self._labels[2]: col_2
}
df = pd.DataFrame(data=data)
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name=field_name, index=False)
workbook = writer.book
worksheet = writer.sheets[field_name]
chart = workbook.add_chart({"type": 'bar'})
chart.set_title({"name": title})
chart.set_x_axis({'name': self._labels[0]})
chart.add_series({"values": "={fn}!$A$2:$A${lr}".format(lr=last_row, fn=field_name), 'fill': {'color': '#0000CC'}, 'border': {'color': '#0000CC'}})
worksheet.insert_chart(self._plot_location+"2", chart)
writer.save()
writer.close()