Preface
Goal: Housekeeping previous code
This is a multiparts article.
Consider tidy, all the codes, with base class. This way, we can separate generic logic flow and custom data setting.
In order to do this, we require to fresh sheet, with more complexity. Completely abandoning previous example.
Data Flow
Focus on Data
A script is just a tool. So we can do more about our job.
With class contain only data setting, we can easily focus on data flow for each transition sequence.
XLSX Files
This works for either Microsoft Excel or Libreoffice Calc.
This script does not depend on either MS Excel or LO Calc.
The openpyxl only know the .xlsx in filesystem.
Both userbase should feel comfortable using this openpyxl library.
You can download the source here:
9: Python Class Refactoring
Inheritance
We are going to make something like below figure:
Base Class: Skeleton
Parent: Focus on Flow Logic
In order to make base class, we can just copy paste the code. Test it in the same python file, and move to its own file later.
class Xl2WebBase:
def __init__(self, filepath, filename, site, port):
# websocket related
async def __send_data(self, fullname):
async def __monitor_localfile(self):
async def __monitor_webclient(self, websocket):
async def __monitor_localfileconn(self, websocket):
# websocket handler
async def __handler(self, websocket, path):
async def main(self):*__
This way, the base class contain only the logic flow.
Base Class: Init
Additional connections property,
initialized as empty set.
class Xl2WebExample:
def __init__(self, filepath, filename, site, port):
# save initial parameter
self.filepath = filepath
self.filename = filename
self.site = site
self.port = port
# websocket broadcast collection
self.connections = set()
# self.xlsx required in descendent class
self.xlsx = None
Base Class: Detail
Since we apply both broadcast and send.
There will so much changes.
Here, watchfiles result triggering broadcast.
async def __monitor_localfile(self):
async for changes in awatch(self.filepath):
self.xlsx = os.path.join(self.filepath, self.filename)
for change in changes:
if change[1] == self.xlsx:
print(change[0])
event_data = self._pack_data(self.xlsx)
self._dump_data(event_data)
websockets.broadcast(
self.connections, json.dumps(event_data))
And any message from client,
will trigger send.
async def __monitor_webclient(self, websocket):
while True:
try:
message = await websocket.recv()
except websockets.exceptions.ConnectionClosed:
break
self.xlsx = os.path.join(self.filepath, self.filename)
event_data = self._pack_data(self.xlsx)
self._dump_data(event_data)
await websocket.send(
json.dumps(event_data))
The message itself is completely discarded in this use case.
Base Class: Handler
Magic
And here we are, polling the connection, and gather into set collection.
async def __monitor_localfileconn(self, websocket):
self.connections.add(websocket)
try:
await websocket.wait_closed()
finally:
self.connections.remove(websocket)
The handler is almost the same, except the websocket will passed as parameter argument.
async def __handler(self, websocket, path):
task_localfile = asyncio.create_task(
self.__monitor_localfileconn(websocket))
task_webclient = asyncio.create_task(
self.__monitor_webclient(websocket))
# run these two coroutines concurrently
await(task_localfile)
await(task_webclient)
And finally, on websocket return, What it mean is, while websocket running forever, waiting to be closed.
On websocket return, we ask the watchfiles to run. For every changes in watchfiles, we broadcast.
async def main(self):
# Start the server
async with await websockets.serve(
self.__handler, self.site, self.port):
await self.__monitor_localfile()
If you find this async and await confusing.
Sometimes we better think of it as magic.
Example Sheet: Global Review
We are going to start with the simple one. Consider have a look at the address of our brand new worksheet.
We have this six Cells:
E3:E5G3:G5
Data Mapping Flow
We have to map, each cell to python, then map python to javascript, and finally javascript to HTML element ID.
For example.
- Excel: Cell
E3, - Python:
data.month_09.budget, - Javascript:
event.datathenstruct.budget, - HTML element ID:
c-09-budget.
Descendant Class: Skeleton
Child: Focus on Data Handling
No need to write all code here, since it is exactly the same as the previous article.
# Excel to Web, Example Class
class Xl2Web_c(xl2web.Xl2WebBase):
def _pack_data(self, fullname):
def _dump_data(self, data):
Instead of private method, we define the method as protected modifier.
Descendant Class: Data
Now we can, map the cell data as below:
class Xl2Web_c(xl2web.Xl2WebBase):
# miscellanous helper
def _pack_data(self, fullname):
# reopen the worksheet all over again
wb = load_workbook(self.xlsx, data_only=True)
ws = wb["Example"]
month_09 = {
"budget": ws['E3'].value,
"actual": ws['E4'].value,
"gap" : ws['E5'].value#### Class: Init
Additional `connections` property,
initialized as empty set.#### Base Class: Detail
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-python" data-lang="python"><span style="color:#66d9ef">class</span> <span style="color:#a6e22e">Xl2WebExample</span>:
<span style="color:#66d9ef">def</span> __init__(self, filepath, filename, site, port):
<span style="color:#75715e"># save initial parameter</span>
self<span style="color:#f92672">.</span>filepath <span style="color:#f92672">=</span> filepath
self<span style="color:#f92672">.</span>filename <span style="color:#f92672">=</span> filename
self<span style="color:#f92672">.</span>site <span style="color:#f92672">=</span> site
self<span style="color:#f92672">.</span>port <span style="color:#f92672">=</span> port
<span style="color:#75715e"># websocket broadcast collection</span>
self<span style="color:#f92672">.</span>connections <span style="color:#f92672">=</span> set()</code></pre></div>
![Python: Broadcast: Init][13b-py-init]#### Base Class: Detail
}
month_10 = {
"budget": ws['G3'].value,
"actual": ws['G4'].value,
"gap" : ws['G5'].value
}
return {
"timestamp" : time.ctime(),
"file" : fullname,
"month_09" : month_09,
"month_10" : month_10
}[50-handler]: https://epsi.bitbucket.io/assets/posts/monitor/2023/01/websocket/50-handler.png
[50-local-file]: https://epsi.bitbucket.io/assets/posts/monitor/2023/01/websocket/50-local-file.png
[50-local-file-conn]: https://epsi.bitbucket.io/assets/posts/monitor/2023/01/websocket/50-local-file-conn.png
[50-web-client]: https://epsi.bitbucket.io/assets/posts/monitor/2023/01/websocket/50-web-client.png
And dump later on.
def _dump_data(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()
Output in CLI
Trigger the file watch by saving the excel file. In order to do this right, you should run both script and websocket.
❯ python 21c-show.pyTimestamp : Wed Jan 11 09:18:12 2023
File Modified : None
September : {'budget': 1230, 'actual': 890, 'gap': 340}
October : {'budget': 1320, 'actual': 1410, 'gap': -90}
Example Sheet: Daily Progress
The same applied with any other sheet. Such as more complex data below:
Descendant Class
Child: Focus on Data Handling
The code is very similar to previous one.
class Xl2Web_d(xl2web.Xl2WebBase):
def _pack_data(self, fullname):
def _dump_data(self, data):
class Xl2Web_d(xl2web.Xl2WebBase):
# miscellanous helper
def _pack_data(self, fullname):
# reopen the worksheet all over again
wb = load_workbook(self.xlsx, data_only=True)
ws = wb["Example"]
month_09 = {
"target": ws['D3'].value,
"actual": ws['E3'].value,
"miss" : ws['F3'].value,
"remain": ws['G3'].value
}
month_10 = {
"target": ws['D4'].value,
"actual": ws['E4'].value,
"miss" : ws['F4'].value,
"remain": ws['G4'].value
}
month_11 = {
"target": ws['D5'].value,
"actual": ws['E5'].value,
"miss" : ws['F5'].value,
"remain": ws['G5'].value
}
total = {
"target": ws['D6'].value,
"actual": ws['E6'].value,
"miss" : ws['F6'].value,
"remain": ws['G6'].value
}
return {
"timestamp" : time.ctime(),
"file" : fullname,
"month_09" : month_09,
"month_10" : month_10,
"month_11" : month_11,
"total" : total
}#### Base Class: Detail
def _dump_data(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 21d-show.pyTimestamp : Wed Jan 11 09:19:21 2023
File Modified : None
September : {'target': 4801, 'actual': 3803, 'miss': 403, 'remain': 595}
October : {'target': 1300, 'actual': 1200, 'miss': 10, 'remain': 90}
November : {'target': 1105, 'actual': 1005, 'miss': 55, 'remain': 45}
Total : {'target': 7206, 'actual': 6008, 'miss': 468, 'remain': 730}
What is Next 🤔?
It works on my computer
After we are done with refactoring using python inheritance. We can modernize our javascript.
Consider continue reading [ Excel - Monitor - Javascript Class ].