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.
You can download PDF presentation about this article, along with inkscape SVG source.
You can also download UML in inkscape SVG files in link below:
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
You can download the source here:
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.
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.
The requirement in this page, is considered minimal viable feature. This can be implemented clearly in ten articles.
After this long part, we should be ready for requirement change.
Start from the eleventh articles, web begin new journey with additional feature.
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.
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.
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.
The Data Flow Plan
What you thought.
Here is the step.
- Excel file changes triggered by saving the file.
- Excel cells read by a library.
- The data sent into a webserver via push server.
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.
and the conventional
can’t be mixed with each other.
After hours of coding, I finally siwtch to
watchfileslibrary monitor a particular excel file for any changes, in asynchronous mode.
For any changes,
openpyxlread value of some predefined cell address.
websocketsend the data to a specific port, in push server fashioned.
All python code managed using
javacriptreceive the data from a specific port, and put the data in predefined
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.
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.
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.
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.
Do not forget to install python package properly.
In windows, you better set path, and so on…
You might also need to upgrade
Question: How do I keep my battery, finishing this long article series?
Answer: Two Tips that keeps me awake writing this article.
Coffee would help, but giving enough sleep would raise you concentration back.
Sleepy is human. Put Avenged Sevenfold in your ear. Alternatively for senior citizen, you may consider Megadeth.
C: Folder Management
Due to complexity, I also have to refactor folder.
- Documentation: UML, presentation, announcement letters, personal notes
- Base Code: example simple data for blogging, for historical reason
- Enhanced Code: example simple data for blogging (development)
- New Feature: Temporary Folder to learn new library
- Testing: real data for real case (testing in linux)
- 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
You can also tidy the subdirectory.
D: Progress Tracking
Even for personal project, keep your track of your progress, in a nice sheet.
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 ].