Where to Discuss?

Local Group


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:

Excel: File Monitoring: XLSX

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):

Python: Reading Excel Cells: Brief

We can write class diagram as below:

| Xl2WebExample |
| - filepath    |
| - filename    |
| - site        |
| - port        |
| + __init()__  |
| - pack_data() |
| - dump_data() |
| - handler()   |
| + main()      |

Python: Class Diagram

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

Python: Reading Excel Cells: Pack Data

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"])

Python: Reading Excel Cells: Dump Data

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])
          await websocket.send(

Python: Reading Excel Cells: Handler

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.py
Timestamp     : Wed Jan  4 10:28:51 2023
File Modified : /home/epsi/awatch/test-a.xlsx
Data 1: 1700
Data 2: 1500
Data 3: 2023

Receiver in CLI

Then we should also check if the receiver get the dictionary as well.

❯ python -m websockets ws://localhost:8765
Connected 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}

Python: Reading Excel Cells: Output

How does it Works?

So far, the sequence diagram can be drawn in figure below:

Python: Sequence Diagram: OpenPyXL

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.

  <title>I have a job for you</title>
  <link rel="stylesheet" href="bulma.min.css">
  <script src="15-script.js"></script>

Site: Reading Excel Cells: 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 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 class="column is-one-third">
        <button class="button is-dark"
          >Key 3</button>
        <a class="button is-info" id="value-a3"
          >Value 3</a>

Site: Reading Excel Cells: HTML Columns

Finally we should manage the websocket via javascript.

  "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;

Site: Reading Excel Cells: On Message

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.

Site: Reading Excel Cells: Display: Init

If we trigger file modification by saving the file, some element will show new value, as shown below.

Site: Reading Excel Cells: Display: Fill

The page above is just an example. We can present our data in anyway we like. For example using this 3D animation cube below.

Site: Reading Excel Cells: Display: Cubes

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.

Python: Sequence Diagram: Websocket

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 ].