How to Build an Online Database APP by Python, PySide6 and Google Sheets (11): View Report

Previously

How to Build an Online Database APP by Python, PySide6 and Google Sheets (10): View Balance

This article is about how to “View Report” in the app:

Call the function in the Main Window

• Clears the existing content in the main window.
• Creates an instance of the ReportTable class, providing google_sheets_client for accessing Google Sheets data.
• Adds the ReportTable widget to the main content area for user interaction.

    def display_reports_table(self):
        self.clear_content()
        reports_table = ReportTable(self.google_sheets_client)
        self.content.layout().addWidget(reports_table)

Create the View Report Class

• __init__: Initializes the widget, sets up the Google Sheets client, and initializes the user interface.

class ReportTable(QWidget):
    def __init__(self, google_sheets_client):
        super().__init__()
        self.google_sheets_client = google_sheets_client
        self.initUI()

• initUI: Sets up the layout, including a dropdown menu for selecting dividend periods and a table for displaying report data. It also includes a button for exporting data to a CSV file.

    def initUI(self):
        layout = QVBoxLayout(self)

        # Create a QHBoxLayout for the label and combo box
        row_layout = QHBoxLayout()

        # Label for Dividend Period ComboBox
        label = QLabel("Dividend Period:")
        label.setFixedSize(150, 30)  # Set the fixed size for the label
        row_layout.addWidget(label, alignment=Qt.AlignVCenter | Qt.AlignRight)  # Align right and center vertically

        # Dropdown for selecting Dividend Period
        self.dividendPeriodComboBox = QComboBox()
        self.dividendPeriodComboBox.setFixedSize(150, 30)  # Set the fixed size for the combo box
        self.dividendPeriodComboBox.currentTextChanged.connect(self.loadData)
        row_layout.addWidget(self.dividendPeriodComboBox, alignment=Qt.AlignVCenter | Qt.AlignLeft)  # Align left and center vertically


        # Button for exporting to CSV
        export_button = QPushButton("Save as Excel")
        export_button.clicked.connect(self.exportToCSV)
        row_layout.addWidget(export_button, alignment=Qt.AlignVCenter | Qt.AlignLeft)  # Align left and center vertically

        layout.addLayout(row_layout)  # Add the row layout to the main layout


        # Create the table widget with scroll feature
        self.table = QTableWidget()
        self.table.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)
        self.desired_columns = ["Dividend Period", "First Name","Last Name", "Balance at End", "Share %", "Total Capital", "Change During the Period", "ID_Account"]
        self.table.setColumnCount(len(self.desired_columns))
        self.table.setHorizontalHeaderLabels(self.desired_columns)

        # Set the resize mode for each column
        for i in range(len(self.desired_columns)):
            self.table.horizontalHeader().setSectionResizeMode(i, QHeaderView.ResizeToContents)

        self.table.horizontalHeader().setStretchLastSection(True)
        self.table.verticalHeader().setDefaultSectionSize(30)
        self.table.verticalHeader().setSectionResizeMode(QHeaderView.Fixed)
        self.table.setEditTriggers(QTableWidget.NoEditTriggers)
        self.table.setSelectionBehavior(QTableWidget.SelectRows)

        layout.addWidget(self.table)

        # Load the periods into the combo box and data into the table
        self.loadDividendPeriods()
        self.loadData(self.dividendPeriodComboBox.currentText())

• loadDividendPeriods: Loads dividend periods from the Google Sheets worksheet and populates the dropdown menu. The current quarter can be excluded if desired.

    def loadDividendPeriods(self):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")
        all_data = worksheet.get_all_values()
        dividend_periods = set(row[all_data[0].index("Dividend Period")] for row in all_data[1:])
        dividend_periods = sorted(list(dividend_periods), reverse=True)

        # Exclude the current quarter if desired
        current_quarter = self.getCurrentQuarter()
        dividend_periods = [period for period in dividend_periods if period != current_quarter]

        self.dividendPeriodComboBox.addItems(dividend_periods)

• loadData: Loads data into the table based on the selected dividend period.

    def loadData(self, selected_period):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        rows = [row for row in all_data[1:] if row[headers.index("Dividend Period")] == selected_period]

        column_indices = {name: index for index, name in enumerate(headers)}
        filtered_rows = [[row[column_indices[col]] for col in self.desired_columns if col in column_indices] for row in rows]

        boldFont = QFont()
        boldFont.setBold(True)

        # Set column header font to bold
        for i in range(len(self.desired_columns)):
            header = self.table.horizontalHeaderItem(i)
            if header:
                header.setFont(boldFont)

        self.table.setRowCount(len(filtered_rows))
        for i, row in enumerate(filtered_rows):
            for j, cell in enumerate(row):
                item = QTableWidgetItem(cell)
                item.setTextAlignment(Qt.AlignCenter)  # Align text to center
                self.table.setItem(i, j, item)

• getCurrentQuarter: Calculates and returns the current quarter of the year, used for filtering purposes.

    def getCurrentQuarter(self):
        # Get the current date
        current_date = datetime.now()

        # Determine the quarter
        month = current_date.month
        quarter = (month - 1) // 3 + 1
        year = current_date.year

        # Format the current quarter as "YYYYQX"
        return f"{year}Q{quarter}"

• exportToCSV: Exports the displayed data to a CSV file. This function prompts the user to select a file location, retrieves the necessary data from Google Sheets, and writes it to the file.

    def exportToCSV(self):
        # Get the selected dividend period
        selected_period = self.dividendPeriodComboBox.currentText()

        # Create a default file name
        file_name = f"Reports_{selected_period}.csv"

        # Ask the user to choose a file location
        file_path, _ = QFileDialog.getSaveFileName(self, "Save CSV File", file_name, "CSV Files (*.csv)")

        if file_path:
            try:
                # Retrieve data directly from Google Sheets for the selected period
                worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")
                all_data = worksheet.get_all_values()

                # Extract headers and rows
                headers = all_data[0]
                rows = all_data[1:]

                # Filter rows based on the selected dividend period
                filtered_rows = [row for row in rows if row[headers.index("Dividend Period")] == selected_period]

                # Write the table data to the CSV file
                with open(file_path, 'w', newline='', encoding='utf-8') as csv_file:
                    csv_writer = csv.writer(csv_file)

                    # Write headers
                    csv_writer.writerow(headers)

                    # Write filtered data rows
                    csv_writer.writerows(filtered_rows)

                QMessageBox.information(self, "Export Successful", "CSV file exported successfully.")
            except Exception as e:
                QMessageBox.critical(self, "Export Error", f"Failed to export CSV file: {e}")

Next Step

How to Build an Online Database APP by Python, PySide6 and Google Sheets (12): View Settings