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

Previously

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

This article is about how to “View Balance” in the App:

Call the function in the Main Window

• Clears the current content in the main window.
• Creates an instance of BalancesTable, passing google_sheets_client as a parameter.
• Adds the BalancesTable widget to the main content area for user interaction.

    def display_balances_form(self):
        self.clear_content()
        balances_table = BalancesTable(self.google_sheets_client)
        self.content.layout().addWidget(balances_table)

Create View Balance Class

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

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

• initUI: Sets up the layout, which includes a QTableWidget for displaying balance data. The table is configured with specific columns, resizing policies, and selection behaviors.

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

        # 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 Start", "New Account Balance", "ID_Account","ID_Balance"]
        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)

        # Load the data into the table
        self.loadData()

        layout.addWidget(self.table)

• loadData: Loads balance data from the Google Sheets worksheet named "05_Balances". It filters the data for the current quarter, sorts it, and populates the table.

    def loadData(self):
        current_quarter = self.get_current_quarter()

        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        dividend_period_index = headers.index("Dividend Period")
        id_balance_index = headers.index("ID_Balance")

        # Filter the data for the current quarter
        filtered_data = [row for row in all_data[1:] if row[dividend_period_index] == current_quarter]

        # Sort the filtered data in reverse order based on "ID_Balance"
        sorted_data = sorted(filtered_data, key=lambda x: x[id_balance_index], reverse=True)

        # Map column names to indices
        column_indices = {name: index for index, name in enumerate(headers)}

        # Prepare rows to be displayed in the table
        display_rows = []
        for row in sorted_data:
            display_row = [row[column_indices[col]] for col in self.desired_columns if col in column_indices]
            display_rows.append(display_row)

        self.table.setRowCount(len(display_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)

        # Align content to center and set rows
        for i, row in enumerate(display_rows):
            for j, cell in enumerate(row):
                item = QTableWidgetItem(cell)
                item.setTextAlignment(Qt.AlignCenter)  # Align text to center
                self.table.setItem(i, j, item)

• get_current_quarter: Calculates and returns the current quarter of the year. This method is used to filter the balance data relevant to the current period.

    def get_current_quarter(self):
        month = datetime.now().month
        quarter = ((month - 1) // 3) + 1
        year = datetime.now().year
        return f"{year}Q{quarter}"

Next Step

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