Creating realtime dashboards in libreoffice calc

The libreoffice suite is capable of great many things. The libreoffice SDK lets programmers extend the features of the suite and do even more with it.

I really haven’t looked up the VBscript documentation for similar functionality. But I’m pretty sure almost all of the information covered here is transferable to MS Excel.

Mouse tracking application

In this article we’ll see how to setup a simple flask web application to serve data, which in this case are mouse pointer coordinates from a web page, and configure the spreadsheet to update the graph in real-time (well kinda..)

The libreoffice SDK’s star module should provide all the features you’ll need for this project.

Writing the server

So the flask app will do nothing more than record the user’s mouse movements on a canvas and serve it to the spreadsheet. Once you start the server, you can access the app on any device connected to the same network.

Webpage

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Canvas Mouse Position with Server Tracking</title>
    <style>
        body, html {
            margin: 0;
            padding: 0;
            height: 100%;
        }

        canvas {
            display: block;
            background-color: #f0f0f0;
        }
    </style>
</head>
<body>
    <canvas id="myCanvas"></canvas>

    <script>
        const canvas = document.getElementById('myCanvas');
        const ctx = canvas.getContext('2d');

        // Resize the canvas to fill the screen
        function resizeCanvas() {
            canvas.width = window.innerWidth;
            canvas.height = window.innerHeight;
        }

        resizeCanvas();

        let isMouseDown = false;
        const innerCircleSize = 40;
        const outerCircleSize = innerCircleSize * 1.3;

        function drawCursor(x, y) {
            ctx.clearRect(0, 0, canvas.width, canvas.height);

            ctx.beginPath();
            ctx.arc(x, y, outerCircleSize, 0, Math.PI * 2);
            ctx.fillStyle = "rgba(255, 0, 0, 0.3)";
            ctx.fill();

            ctx.beginPath();
            ctx.arc(x, y, innerCircleSize, 0, Math.PI * 2);
            ctx.fillStyle = "red";
            ctx.fill();
        }

        // Send mouse position to the server using fetch (AJAX)
        function sendMousePosition(x, y) {
            fetch('/track-mouse', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({ x, y })
            })
            .then(response => response.json())
            .then(data => {
                console.log('Server response:', data);
            })
            .catch(error => {
                console.error('Error sending mouse position:', error);
            });
        }

        // Mouse move event (desktop)
        canvas.addEventListener('mousemove', (e) => {
            const mouseX = e.offsetX;
            const mouseY = e.offsetY;
            
            if (isMouseDown) {
                console.log(`Dragging: X = ${mouseX}, Y = ${mouseY}`);
            }

            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY); // Send position to the server
        });

        // Mouse down event (desktop)
        canvas.addEventListener('mousedown', (e) => {
            isMouseDown = true;
            const mouseX = e.offsetX;
            const mouseY = e.offsetY;
            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY);
        });

        // Mouse up event (desktop)
        canvas.addEventListener('mouseup', (e) => {
            isMouseDown = false;
            const mouseX = e.offsetX;
            const mouseY = e.offsetY;
            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY);
        });

        // Prevent the default behavior for touch events (like page swipe) on mobile
        canvas.addEventListener('touchstart', (e) => {
            e.preventDefault();  // Prevent page reload on touch
            isMouseDown = true;
            const touch = e.touches[0];
            const mouseX = touch.clientX;
            const mouseY = touch.clientY;
            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY);
        });

        canvas.addEventListener('touchmove', (e) => {
            e.preventDefault();  // Prevent default touch behavior (like scrolling)
            const touch = e.touches[0];
            const mouseX = touch.clientX;
            const mouseY = touch.clientY;

            if (isMouseDown) {
                console.log(`Dragging: X = ${mouseX}, Y = ${mouseY}`);
            }

            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY);
        });

        canvas.addEventListener('touchend', (e) => {
            e.preventDefault();
            isMouseDown = false;
            const touch = e.changedTouches[0];
            const mouseX = touch.clientX;
            const mouseY = touch.clientY;
            drawCursor(mouseX, mouseY);
            sendMousePosition(mouseX, mouseY);
        });

        window.addEventListener('resize', resizeCanvas);
    </script>
</body>
</html>

Flask server program

server.py

from flask import Flask, jsonify, send_from_directory, request
import os
import json

app = Flask(__name__)

# Global variable to store the last mouse position
mouse_position = {'x': None, 'y': None}

@app.route("/")
def serve_index():
    return send_from_directory(os.getcwd(), 'index.html')

# Route to handle AJAX (POST) requests for mouse position (same as before)
@app.route('/track-mouse', methods=['POST'])
def track_mouse():
    global mouse_position
    data = request.get_json()
    mouse_position['x'] = data.get('x')
    mouse_position['y'] = data.get('y')
    print(f"Received mouse position: X = {mouse_position['x']}, Y = {mouse_position['y']}")
    
    # Respond with a JSON object
    return jsonify({'status': 'success', 'message': 'Mouse position received'})

# Route to retrieve the last mouse position (GET request)
@app.route('/get-mouse-position', methods=['GET'])
def get_mouse_position():
    return jsonify(mouse_position)

# Start the Flask web server
if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080, debug=True)

Building the dashboard

Our setup is a basic spreadsheet to display a real-time line graph. And if possible, add a few fancy buttons to start and stop the listening process.

You may download the .ods file here📥.

The Graph

As you can see, the graph takes data from cells C4:C14. This will be the most important component of the program. The idea is that since the graph updates itself with whatever values are given in the corresponding cells, any code/formula to modify these cells should automatically be reflected in the graph.

The Code

There are 3 major code snippets / macros we’ll be using to control the program:

•fetchMousePos - Fetch data from the server program and populate the cells
•updateHistory – Cleanup any entries more than 10 (C4:C14)
•triggerMacro – Start / Stop the listening process

fetchMousePos

First we need to fetch the data from the server, which can be achieved with this function.

Function GetMousePositionFromServer() As String
    On Error GoTo ErrorHandler

    ' Create FunctionAccess object to call spreadsheet functions
    Dim functionAccess As Object
    functionAccess = CreateUnoService("com.sun.star.sheet.FunctionAccess")

    ' URL of the Flask server that returns mouse position (JSON response)
    Dim url As String
    url = "http://192.168.1.35:8080/get-mouse-position" ' Replace with your Flask server IP
    
    ' Call WEBSERVICE function to get the response from the server
    Dim response As String
    response = functionAccess.callFunction("WEBSERVICE", Array(url))
    
    ' Return the response (JSON data) from the server
    GetMousePositionFromServer = response
    Exit Function

ErrorHandler:
    GetMousePositionFromServer = "Error " & Err
End Function

Next, we populate the data fetched in the step above into the “Mouse Position Tracked” cells.

                                   Sub ParseAndInsertMousePosition() ' Call GetMousePositionFromServer to get the JSON response Dim jsonResponse As String jsonResponse = GetMousePositionFromServer() ' Check if the response is valid If InStr(jsonResponse, "Error") = 0 Then ' Parse the JSON response to get the mouse position Dim x As Double Dim y As Double  ' Use basic string manipulation to extract X and Y values (simple example) Dim xPos As Integer Dim yPos As Integer  xPos = InStr(jsonResponse, """x"":") + 5 yPos = InStr(jsonResponse, """y"":") + 5  If xPos > 0 And yPos > 0 Then ' Extract values after "x" and "y" x = Val(Mid(jsonResponse, xPos, InStr(xPos, jsonResponse, ",") - xPos)) y = Val(Mid(jsonResponse, yPos, InStr(yPos, jsonResponse, "}") - yPos))  ' Insert the mouse position into the spreadsheet (cells A1 and B1) ThisComponent.Sheets(0).getCellByPosition(0, 1).Value = x ' A1 ThisComponent.Sheets(0).getCellByPosition(1, 1).Value = y ' B1 Else MsgBox "Invalid response format." End If Else MsgBox "Error in response: " & jsonResponse End If #080; font-weight: bold">End Sub

Upon the code updating those fields, it should automatically update the “Converted value” cell as well, which is nothing but the average of the x and y coordinates; which in turn pushes the value to the history queue, ultimately updating the graph.

updateHistory

Here’s the fun part. This is where we push the existing data to the cells below such that any incoming data always remains at the top of list. We’ll also need to take care of any entries exceeding the limit of the preset number of cells.

Essentially, we implement a queue push.

REM  *****  BASIC  *****

Sub updateHistory
    Dim oSheet As Object
    Dim c4Value As Variant
    Dim c2Value As Variant
    Dim i As Integer
    
    ' Get the current active sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet
    
    ' Get the value of C4 and C2
    c4Value = oSheet.getCellRangeByName("C4").Value
    c2Value = oSheet.getCellRangeByName("C2").Value
    
    ' Push values from C4 to C5:C14 (next 10 cells)
    For i = 10 To 1 Step -1
        oSheet.getCellByPosition(2, 3 + i).Value = oSheet.getCellByPosition(2, 2 + i).Value
    Next i
    
    ' Update C4 with the value of C2
    oSheet.getCellRangeByName("C4").Value = c2Value
End Sub

Start/Stop listening

triggerMacro

This is more like an add-on feature we can implement to make the dashboard – well, feel like a dashboard.

These “buttons” are actually just images to which macros are attached – turning them into trigger points without any having to deal with any fancy event listeners or such.

The triggerMacro macro is implemented as follows

REM  *****  BASIC  *****

' Declare the timerOn variable globally (outside of any subroutine)
Dim timerOn As Boolean

Sub StartTimer
	timerOn = True
	 oSheet = ThisComponent.CurrentController.ActiveSheet
	oSheet.getCellByPosition(1, 2).String = "Running"
	Call TriggerTimer
End Sub

Sub TriggerTimer
    ' Ensure that fetchMousePos.Macro1 exists and is correctly named
    Call fetchMousePos.ParseAndInsertMousePosition ' Call Macro1 from fetchMousePos module
    Call updateHistory.updateHistory 
    ' Wait for 2 seconds (2000 milliseconds)
    Wait(1000)
    
    ' Check if the timer is still on, then repeat the process
    If timerOn Then
        Call StartTimer  ' Recursively call StartTimer to repeat the process
    End If
End Sub

Sub StopTimer
    ' Stop the timer by setting the flag to False
    timerOn = False
    oSheet = ThisComponent.CurrentController.ActiveSheet
    oSheet.getCellByPosition(1, 2).String = "Not running"
End Sub

Bringing everything together..

Yes !! Time to start your python server, launch your app in the browser and hit the play button on your dashboard.

Click around on the webpage to see how the graph behaves.

Note: In case you get the following error, its probably because flask is serving on a different address

Correct the address in ‘fetchMousePos’ function with the address displayed in the flask app terminal and you should be good to go.

Contents