[FIXED] Dynamically Read Excel Spreadsheets with Python, Flask without reload the html page

Issue

The code works if result=random.random(), but would like to dynamically Read Excel Spreadsheets with Python, Flask without reload the html page
Excel file will change at a specified interval and HTML keeping static

.py

@app.route("/rel")
def rel_country():
    z = "test"
    book = load_workbook("rel/" + str(z) + ".xlsx")
    sheet = book.active

    return jsonify(result=sheet) 

HTML:

<head>
    
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
    <script type="text/javascript">
  var $SCRIPT_ROOT = {{ request.script_root|tojson|safe }};


    var intervalID = setInterval(update_values, 1000);

    function update_values() {
        $.getJSON($SCRIPT_ROOT + '/rel',

            function (data) {
                $('#result').text(data.result);
                console.log(data)
            });

    };
    function stopTextColor() {
        clearInterval(intervalID);
    }

    </script>

    <title>Excel To HTML Table</title>
</head>
<body onload="update_values();">
    <h1>Dynamic Update</h1>
    <p>
        <span id="result">?</span>
        <script>
            document.getElementById("result").innerHTML;
        </script>

        <button onclick="stopTextColor();">Stop</button>
</body>

Error:

 raise TypeError(f"Object of type {type(o).__name__} is not JSON serializable")
TypeError: Object of type Worksheet is not JSON serializable

Solution

Use pandas to read the xlsx file. The dataframe that results from that is json serializable.

df = pd.read_excel('tmp.xlsx', index_col=0) 
result = df.to_json(orient="records")

Answered By – Eric Yang

Answer Checked By – Mary Flores (Easybugfix Volunteer)

Leave a Reply

(*) Required, Your email will not be published