Preface
Goal: Monitor excel file changes and show the result in browser using websocket.
This is a multiparts article.
6: Reading Excel Cells
Official Documentation
Spreadsheet File
Excel as Data Source
Again, have a look at this spreadsheet below:
We have this three Cells:
C2: 1700 (data 1)C3: 1500 (data 2)C4: 2023 (data 3)
We need to read these value using openpyxl.
Python Source Code
Class Skeleton
Not called asynchronously
More methods to come. Two miscellanous helper, related with data.
class Xl2WebExample:
def __init__(self, filepath, filename, site, port):
def pack_data(self, fullname):
def dump_data(self, data):
async def handler(self, websocket, path):
async def main(self):
We can write class diagram as below:
+---------------+
| Xl2WebExample |
+---------------+
| - filepath |
| - filename |
| - site |
| - port |
+---------------+
| + __init()__ |
| - pack_data() |
| - dump_data() |
| - handler() |
| + main() |
+---------------+
Class: Miscellanous Helper
We cannot put the worksheet as global variable. We have to reopen the excel spreadsheet all over again, to get the latest changes.
def pack_data(self, fullname):
# reopen the worksheet all over again
wb = load_workbook(fullname)
ws = wb["Example"]
return {
"time": time.ctime(),
"file": fullname,
"val1": ws['C2'].value,
"val2": ws['C3'].value,
"val3": ws['C4'].value
}
Of course, you can set parameter for loading the workbook.
But beware of the read_only properties,
as it can block saving in windows.
wb = load_workbook(self.xlsx,
data_only=True, read_only=True)We also need to dump the data, for debugging purpose.
def dump_data(self, data):
print("Timestamp : %s" % data["time"])
print("File Modified : %s" % data["file"])
print("Data 1: %s" % data["val1"])
print("Data 2: %s" % data["val2"])
print("Data 3: %s" % data["val3"])
print()
Dumping the data is optional If everything is working properly, you can skip or ignore it.
Class: Websocket Handler
To send structure data, such as dictionary,
we can utilize json.dumps.
async def handler(self, websocket, path):
async for changes in awatch(self.filepath):
xlsx = self.filepath + '/' + self.filename
for change in changes:
if change[1] == xlsx:
event_data = self.pack_data(change[1])
self.dump_data(event_data)
await websocket.send(
json.dumps(event_data))
Output in CLI
Trigger the file watch by saving the excel file. In order to do this right, you should run both sender and receiver,
Sender in CLI
First we need to check if the sender, have the proper data.
❯ python 15-openpyxl.pyTimestamp : Wed Jan 4 10:28:51 2023
File Modified : /home/epsi/awatch/test-a.xlsx
Data 1: 1700
Data 2: 1500
Data 3: 2023Receiver in CLI
Then we should also check if the receiver get the dictionary as well.
❯ python -m websockets ws://localhost:8765Connected to ws://localhost:8765.
< {"time": "Wed Jan 4 10:28:51 2023", "file": "/home/epsi/awatch/test-a.xlsx", "val1": 1700, "val2": 1500, "val3": 2023}
How does it Works?
So far, the sequence diagram can be drawn in figure below:
but hey, we’got another beast. It is the web browser as client.
Web Source Code
Client Side
I separate the javascript from the html file. And also add bulma CSS framework, o we have nice looking good dashboard.
The HTML head, is also simple.
<head>
<title>I have a job for you</title>
<link rel="stylesheet" href="bulma.min.css">
<script src="15-script.js"></script>
</head>
For simplicty, I use Bulma button. Note that this won’t be a proper case for complex data. However, this is just an example.
<div class="box columns has-text-centered">
<div class="column is-one-third">
<button class="button is-dark"
>Key 1</button>
<a class="button is-info" id="value-a1"
>Value 1</a>
</div>
<div class="column is-one-third">
<button class="button is-dark"
>Key 2</button>
<a class="button is-info" id="value-a2"
>Value 2</a>
</div>
<div class="column is-one-third">
<button class="button is-dark"
>Key 3</button>
<a class="button is-info" id="value-a3"
>Value 3</a>
</div>
</div>
Finally we should manage the websocket via javascript.
document.addEventListener(
"DOMContentLoaded", function(event) {
const websocket = new WebSocket("ws://localhost:8765");
const value_a1 = document.getElementById("value-a1");
const value_a2 = document.getElementById("value-a2");
const value_a3 = document.getElementById("value-a3");
websocket.onmessage = function(event) {
const data = JSON.parse(event.data);
value_a1.innerHTML = data.val1;
value_a2.innerHTML = data.val2;
value_a3.innerHTML = data.val3;
}
});
Browser Result
There is no need for any webserver. We can just right click the html file, and open it in firefox.
The initial looks of our page is as below.
If we trigger file modification by saving the file, some element will show new value, as shown below.
The page above is just an example. We can present our data in anyway we like. For example using this 3D animation cube below.
The source of this 3D animation can be achieved from codepen. This is one of the coolest CSS only animation that I have seen.
I did not make the 3D animation code. I just grab it from that codepen above. Just as an example.
How does it Works?
Consider change the sequence diagram, so that we can involve our websocket JSON data, to be displayed in a web browser.
What is Next 🤔?
We are done with sending excel data. But how about receiving from web page client?
Consider continue reading [ Excel - Monitor - Async Request ].