Excel - Monitor - Dual Rich Panel

   
   
 python  
 monitor  
Python Monitoring Article Series Test

Python - Monitor

Table of Content
Where to Discuss?

Local Group

Preface

Goal: Add common feature such as configuration, terminal interface, and web server

This is a multiparts article.

We are going to apply pattern we have created from Rich Live article series.

Prerequisite

Reference

This article relies heavily on other article series. I strongly suggest you to read this article first before you begin.

14A: Apply Rich Panel: Base Class

Based on article above, we can integrate rich into our monitoring application.

Preview TUI in CLI

Our final output in terminal would be as below figure:

Python: Base Class: Preview TUI in CLI

Python Source Code

The script is availabe. You might consider to have a look.

Required Package

Just add any rich related package.

import asyncio, websockets, json, os, tomli

from watchfiles  import awatch
from rich.layout import Layout
from rich.panel  import Panel
from rich.table  import Table

Python: Apply Rich Panel: Base Class: Import

Constructor

Excel to Web, Abstract Base Class

We need to save the self.live and self.layout.

class Xl2Web:
  def __init__(self, sheet_ident, 
      ref_live, ref_layout, layout_name):
    with open('config.toml', 'rb') as file_obj:
      ...

    # websocket broadcast collection
    self.connections = set()

    # openpyxl: required in descendent class
    self.xlsx = None

    # rich: required in descendent class
    self.live   = ref_live
    self.layout = ref_layout
    self.layout_name = layout_name

Python: Apply Rich Panel: Base Class: Constructor

Instead of print data to terminal, we would use rich panel.

  def _dump_data(self, data):
    self.live.update(
      self.get_layout(data))

Then I put get_layout in separate method, because we also need to initialize layout without any data.

  def get_layout(self, data) -> Layout:
    self.layout[self.layout_name].update(
      self.get_panel(data))
    return self.layout

And finally the get_panel method.

  def get_panel(self, data) -> Panel:
    message = Table.grid(padding=1, expand=True)
    message.add_column(no_wrap=True)

    if data != None:
      message.add_row(
        "Timestamp     : %s" % data["timestamp"])
      message.add_row(
        "File Modified : %s" % data["file"])
      message.add_row(self.generate_table(data))

    panel = Panel(message, title=self.filepath)
    return panel

Python: Apply Rich Panel: Base Class: Rich TUI Related

I put these three methods in base class. We need to implement descendant class. And call them in main program.

14B: Apply Left Panel: Global Review Sheet

Python Source Code

Reading script is not mandatory. But I share the script anyway.

Example Sheet: C Review

This works for either Microsoft Excel or Libreoffice Calc.

You can download the source here:

Excel: XLSX: Example Sheet: C Review

Preview TUI in CLI

Simplified

A single panel result would be similar as preview figure below:

Python: Global Review Sheet: Preview TUI in CLI

Required Package

Include the xl2web_25 base class.

import asyncio, openpyxl, time
import xl2web_25

from openpyxl    import load_workbook
from rich.table  import Table

Python: Global Review Sheet: Import

Skeleton

Consider append three rich related methods.

class Xl2Web_c(xl2web_25.Xl2Web):
  # Excel Reader
  def _pack_data(self, fullname):

  # Legacy, for use without rich TUI
  def _dump_data_text(self, data):

  # Rich TUI related
  def format_table(self, dataTable):
  def to_str(self, label, m):
  def generate_table(self, data) -> Table:

Python: Global Review Sheet: Skeleton

Cell Reader

Pack Data

Due to narrow table width for dual panel, we need to shorten the full file path, into just file name.

  def _pack_data(self, fullname):
    # reopen the worksheet all over again
    wb = load_workbook(self.xlsx, data_only=True)
    ws = wb["Example"]

    ...

    short = fullname.replace(self.filepath, '')

    return {
      "timestamp" : time.ctime(),
      "file"      : short,
      "month_09"  : month_09,
      "month_10"  : month_10
    }

Python: Global Review Sheet: Cell Reader

Legacy

Legacy, for use without rich TUI

Consider dispose this obsolete function, or just rename it.

  def _dump_data_text(self, data):
    print("Timestamp     : %s" % data["timestamp"])
    print("File Modified : %s" % data["file"])
    print("September     : %s" % data["month_09"])
    print("October       : %s" % data["month_10"])
    print()

Python: Global Review Sheet: Legacy

Renderable: Table

Rich TUI Related

This method may vary beetwen different sheet. So this method should be in descendant class.

  def generate_table(self, data) -> Table:
    # Make a new table
    table = Table(
      "Month", "Budget", "Actual", "Gap",
      expand=True)

    self.format_table(table)

    table.add_row(*self.to_str(
      "September", data["month_09"]))

    table.add_row(*self.to_str(
      "October",   data["month_10"]))

    return table

Python: Global Review Sheet: Table

And so is this helper below, we have three columns, that needed to be converted to string.

  def to_str(self, label, m):
    return [label, str(m["budget"]), 
      str(m["actual"]), str(m["gap"])]

Python: Global Review Sheet: Rows String

Renderable: Table Formatting

Pretty Color

We require a few aestethic enhancement. One row header, with three columns.

  def format_table(self, dataTable):
    c = dataTable.columns

    c[1].header_style = "bold blue"
    c[2].header_style = "bold green"
    c[3].header_style = "bold yellow"  

    c[0].style = "bold"
    c[1].style = "blue"
    c[2].style = "green"
    c[3].style = "yellow"

Python: Global Review Sheet:Table Formatting

What is Next 🤔?

We are not done yet.

Since this article become too long, I decide to separate this article into two parts. So we can take a deep breath and amke a cup of hot coffee.

When break over. Consider continue reading [ Excel - Monitor - Dual Rich Panel ].