Where to Discuss?

Local Group

Preface

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

This is a multiparts article.


14C: Apply Right Panel: Daily Progress Sheet

Python Source Code

Most of the time, having ready script is, better than not having one.

Example Sheet: D Progress

This works for either Microsoft Excel or Libreoffice Calc.

You can download the source here:

Excel: XLSX: Example Sheet: D Progress

Preview TUI in CLI

Simplified

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

Python: Daily Progress 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: Daily Progress Sheet: Import

Skeleton

Consider append three rich related methods.

class Xl2Web_d(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: Daily Progress 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,
      "month_11"  : month_11,
      "total"     : total
    }

Python: Daily Progress Sheet: Cell Reader

Legacy

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("November      : %s" % data["month_11"])
    print("Total         : %s" % data["total"])
    print()

Python: Daily Progress 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", "Target", "Actual", "Miss", "Remain",
      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"]))

    table.add_row(*self.to_str(
      "November", data["month_11"]))

    table.add_row(*self.to_str(
      "Total",   data["total"]))

    return table

Python: Daily Progress Sheet: Table

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

  def to_str(self, label, m):
    return [label,
      str(m["target"]), str(m["actual"]),
      str(m["miss"]), str(m["remain"])]

Python: Daily Progress Sheet: Row String

Renderable: Table Formatting

Pretty Color

We require a few aestethic enhancement. One row header, with four 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 red"  
    c[4].header_style = "bold yellow"  

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

Python: Daily Progress Sheet: Formatting


14D: Apply Dual Panel: Main Program

I choose to use class to simply things.

Python Source Code

Good things comes to those blogger who share the script.

Preview TUI in CLI

A dual horizontal panel result would be similar as preview figure below. Save your xlsx spreadsheet, to trigger watch event.

Python: Dual Panel: Preview TUI in CLI

Required Package

We should include these descendant class:

  • xl2web_25c: global review sheet
  • xl2web_25d: daily progress sheet
import asyncio
import xl2web_25c, xl2web_25d

from rich.live   import Live
from rich.panel  import Panel
from rich.layout import Layout

Python: Dual Panel: Brief

Class Skeleton

This only consist three methods.

class DualPanel:
  def make_layout(self):

  def get_layout_empty(self) -> Layout:

  async def main(self):

Python: Dual Panel: Skeleton

Method: Make Layout

This is simply as below

  def make_layout(self):
    # Define the layout.
    self.layout = Layout(name="root")
    self.layout.split_row(
      Layout(name="left"),
      Layout(name="right"),
    )

Python: Dual Panel: Make Layout

Method: Empty Layout

Initalize Panel

The explanation available in other article series.

  def get_layout_empty(self) -> Layout:
    self.layout['left'].update(
      self.watch_left.get_panel(None))
    self.layout['right'].update(
      self.watch_right.get_panel(None))
    return self.layout

Python: Dual Panel: Empty Layout

Async Main Method: Live

Finally, our main course.

  async def main(self):
    self.make_layout()
    self.live = Live()

    self.watch_left = xl2web_25c.Xl2Web_c(
      'sheetGlobal', self.live, self.layout, 'left')

    self.watch_right = xl2web_25d.Xl2Web_d(
      'sheetDaily', self.live, self.layout, 'right')

    with self.live:
      self.live.update(self.get_layout_empty())

      task_left = asyncio.create_task(
        self.watch_left.main())
      task_right = asyncio.create_task(
        self.watch_right.main())

      await(task_left)
      await(task_right)

Python: Dual Panel: Async Main Method: Live

Program Entry Point

As usual, create class instance, then run it in asynchronous mode.

dual = DualPanel()
try:
  asyncio.run(dual.main())
except KeyboardInterrupt:
  print('Goodbye!')

Python: Dual Panel: Brief

Preview TUI in CLI

A dual vertical panel result would be similar as preview figure below:

Python: Dual Panel: Preview TUI in CLI

You can compare the result with the web browser version, provided by file or using AIOHTTP.

Python: Dual Panel: Preview Web Browser

Or better, you can enjoy the TUI result in video. Just save your xlsx spreadsheet, and see what happened in your terminal.


What is Next 🤔?

There is still, web server to go. We will utilize AIOHTTP as web server and Jinja2 as templating engine.

Consider continue reading [ Excel - Monitor - Web Server ].