Where to Discuss?

Local Group

Preface

Goal: Monitor excel file changes and show the result in browser using websocket.

Good day! Happy new year everyone. New year means new challenge. It is a good time to combine different technology.

This is the first, of four parts article.

Presentation

You can download PDF presentation about this article, along with inkscape SVG source.

Presentation: Thumbs

The template can be obtained here:

Diagram

You can also download UML in inkscape SVG files in link below:

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:

Quick Fix

And also Dirty

This article is intended for any reader, who have limited time constraint. You can use this script as temporary solution. For permanent solution, you should consider make an application instead. Do not forget to review later.


A: Requirement

Managing Requirement: Defining The Blue Rocks

This long part should give us overview, on how hard it is to define requirement, when we don’t know exactly what we need.

First Part

The requirement in this page, is considered minimal viable feature. This can be implemented clearly in ten articles.

Second Part

After this long part, we should be ready for requirement change.

Start from the eleventh articles, web begin new journey with additional feature.

The Challenge

What your manager thought.

My office need to modernize their job. Instead of show our daily report on whiteboard, we need to show the report on TV screen instead. We can put the in frontoffice, so we can show off to our customer, on how advance we are.

Site: Multiple Connection Demo: Display: Duo

It is pretty easy, actually. Hardware is getting cheaper. We can have browser, in raspi, mini-pc, or even in the TV itself. Then we can manually, craft a HTML that show us, our daily result.

Sure we can go further with realtime data. In order to do that we need to make an automation. Since I do not want to change any workflow, of any employee here, I’m going to use their own very tools.

It is the excel spreadsheet, the most productive tools. Excel is not just easy to use, but also easy to utilize. There is already a bunch of tools, that can read this spreadsheet document.

Excel: Multiple Connection Demo: XLSX

User Perspective

What your friend thought.

All the end user have to do is, to save their excel file, each time they want to update the large TV screen.

The script will send the data, to web page in a browser, in that large TV screen. The script take care all the technical stuff.

Python: Asynchronous Request Task: Output

The Data Flow Plan

What you thought.

Here is the step.

  1. Excel file changes triggered by saving the file.
  2. Excel cells read by a library.
  3. The data sent into a webserver via push server.

Site: Asynchronous Request Task: Websocket Sum

I know nodejs library that can do all those. But since I already use a lot of python to do spreadsheet task, I constraint my project to be python script only. And no other stack involved.

I can help my friend in their cubicle, to makes them looks cool, by showing their performance in your office. Bu I simply do not want to, install huge node_module stuff, in my friend notebook, as a trade off.

The Real Logic Flow

What really happened: Various Library.

I thought this task will be a one day coding, and be happy. So easy in paper, but later I face other issue. The asynchronous websocket, and the conventional watchdog event, can’t be mixed with each other. After hours of coding, I finally siwtch to watchfiles.

  1. watchfiles library monitor a particular excel file for any changes, in asynchronous mode.

  2. For any changes, openpyxl read value of some predefined cell address.

  3. websocket send the data to a specific port, in push server fashioned.

  4. All python code managed using aiosync library.

  5. javacript receive the data from a specific port, and put the data in predefined html element.

  6. Do not forget to turn on the TV Screen,

I also realize, that I do not need web server at all. Just web browser and Voila.

Python: Asynchronous Request Task: Brief

We can have multiple websocket in one HTML page. This means, that we can render multiple panel in HTML, with different data source, such as different spreadsheet, from different PC. This means, one TV screen can serve multiple employee report, in its own panel, in one HTML page.

How does it Works?

I finally made it.

This would be easier if we can draft, the sequence diagram in text below.

  +-------------+           +------------+          +-------------+
  |    Excel    |           |  Script    |          | Web Browser |
  +-------------+           +------------+          +-------------+
         |                        |                         |
         |                        |                         || page open
         |                        |                         |<----------
         |                        |                         || check con
         |                        |                         |<----------
         |                        |                      |--||
         |                        |                      |--> 5s timeout  
         |                        |      open websocket     ||
         |                        |<------------------------||
         ||   read cells value    ||                        ||
         ||----------------------->|                        ||
         |                        ||       JSON data        ||
         |                        ||----------------------->||
         |                        |                         || update
         |                        |                         |---------->
         |                        |                         |
         |---|                    |                         |
   wait  <---|                    |                         |
         |                        |                         |
   save  |                        |                         |
 -------->|   read cells value    |                         |
         ||----------------------->                         |
         |                        ||       JSON data        |
         |                        ||----------------------->||
         |                        |                         || update
         |                        |                         |---------->
         |                        |                         |

Since text has many limitation, the draft diagram above might not be accurate.

We can rewrite the sequence diagram as below.

Python: Sequence Diagram: Websocket

One day coding, another day for bug fixing and report to management, and one more day for blogging and make presentation. That’s it, the cycle of simple project.

All in less than 90 lines of python code, relatively short to be examined.


B: Preparation

System

PIP

This is a long article series, and we require a bunch of python package.

❯ pip install asyncio watchfiles openpyxl websockets json os tomli jinja2 aiohttp aiohttp_jinja2

Python also works in windows.

Python: PIP Install

Do not forget to install python package properly. In windows, you better set path, and so on… You might also need to upgrade pip using pip itself.

Python: PIP

Personal Stamina

Question: How do I keep my battery, finishing this long article series?

Answer: Two Tips that keeps me awake writing this article.

  1. Coffee would help, but giving enough sleep would raise you concentration back.

  2. Sleepy is human. Put Avenged Sevenfold in your ear. Alternatively for senior citizen, you may consider Megadeth.


C: Folder Management

Internal Changes

Due to complexity, I also have to refactor folder.

  1. Documentation: UML, presentation, announcement letters, personal notes
  2. Base Code: example simple data for blogging, for historical reason
  3. Enhanced Code: example simple data for blogging (development)
  4. New Feature: Temporary Folder to learn new library
  5. Testing: real data for real case (testing in linux)
  6. Windows: real data for real case (production in windows)

Note that the windows folder is usually empty. But whenever I make changes form production back to testing, I put them over there.

The test folder should contain only the latest working script. We should not confuse end user, with tutorial.

❯ exa -TL1
.
├── code-00-new-feat
├── code-01-base
├── code-02-enh
├── code-05-test
├── code-06-win
└── documentation

Internal: Folder Refactoring

You can also tidy the subdirectory.

Internal: Nemo Folders


D: Progress Tracking

Even for personal project, keep your track of your progress, in a nice sheet.

Internal: Progress Track


What is Next 🤔?

Let’s get it on.

We shall begin with basic stuff. Out main idea with simple example.

Consider continue reading [ Excel - Monitor - Watchfiles ].