when you inadvertently create something too complex in excel

Posted by Parajuli Sushil 4 weeks Ago 2 Comments 7 Min Read

How to Transform Your Complex Excel into a Web Application

When working with a complex Excel workbook that contains multiple worksheets, input fields, and calculated reports, we may find ourselves wanting to use the Excel file itself as a web application. This could be for our own convenience or to control how users interact with the workbook – limiting their input while keeping them from directly modifying the underlying calculations. Essentially, you want to transform your Excel workbook into a web app without rebuilding everything from scratch.

But how do you achieve this, especially when your workbook could be as intricate as a control panel for launching a missile?

There are a few alternatives:

  • Microsoft Graph API – A dedicated service that allows seamless interaction with cloud-hosted Excel files.
  • xlwings – An open-source library that enables direct communication between Python and Excel.

Both of these solutions bridge the gap between Excel-based calculations and a modern web-based interface, enabling you to maintain the integrity of your workbook while improving accessibility and control.

Why Not Rebuild Everything?

Building a software system from the ground up would mean:

  • Rewriting all Excel formulas and logic into a new backend system.
  • Debugging and verifying calculations manually.
  • Losing the flexibility of Excel for future updates.

Instead of reinventing the wheel, xlwings allows you to use Excel as the backend calculator while exposing its power via a web-based API.

Let's discuss about xlwings

xlwings is a Python library that enables seamless interaction with Excel, allowing us to:

  • Read and write data to Excel programmatically.
  • Automate Excel calculations from Python scripts.
  • Expose Excel functions as web APIs for real-time data processing.

Unlike other Python libraries such as pandas or openpyxl, which primarily focus on reading and writing Excel files, xlwings goes a step further. It allows real-time interaction with an open Excel workbook through a COM (Component Object Model) connection. This means that in addition to modifying cell values, xlwings can trigger recalculations of formulas within the workbook—ensuring that all dependencies and complex calculations remain intact, just as they would in a native Excel environment.

How xlwings Can Turn Your Excel Workbook into a Web App

Step 1: Install xlwings

First, install xlwings along with Flask (for creating a web API):

            pip install xlwings flask

Step 2: Expose Excel Functions as API Endpoints

Using xlwings, you can define a Python function that interacts with your Excel file. For example:

            import xlwings as xw
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/calculate', methods=['POST'])
def calculate():
    data = request.json  # Receive input data from frontend
    wb = xw.Book('tax_calculator.xlsx')  # Open your workbook
    sheet = wb.sheets['InputSheet']  # Select the input sheet
    
    # Write input values to Excel
    sheet.range('A1').value = data['income']
    sheet.range('B1').value = data['deductions']
    
    # Read calculated values from Excel
    result = {
        "tax_owed": wb.sheets['ReportSheet'].range('C1').value,
        "effective_rate": wb.sheets['ReportSheet'].range('D1').value
    }
    return jsonify(result)

if __name__ == '__main__':
    app.run(debug=True)

Step 3: Build a Web Frontend

You can create a simple frontend using Django, Flask, or JavaScript frameworks like React. The frontend will collect user inputs, send them to the API, and display the calculated results.

Step 4: Deploy on a Cloud Server

To make your Excel-powered web app accessible online, you can host your Python API using Flask/Django on a VPS.

Advantages of Using xlwings for Web Integration

  1. Leverage existing Excel logic – No need to rewrite formulas or calculations.
  2. Fast development – Connect Excel to web apps in hours, not weeks.
  3. Scalability options – Run calculations locally or on a cloud-hosted Excel instance.
  4. Flexible integrations – Works with Python, Django, Flask, or even serverless Azure Functions.

Example & Usage

Below, we demonstrate how to input a value into the tax calculator Excel workbook. By leveraging Excel’s built-in tools and functions as the processing engine, we retrieve the desired result without replicating Excel’s features in the backend. 

            <!-- index.html -->

<html>
<head>
    <title>Tax Calculator - Update Excel</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tailwindcss/2.2.19/tailwind.min.css">
</head>
<body class="bg-gray-100">
    <div class="container mx-auto mt-10">
        <div class="bg-white p-6 rounded shadow-md">
            <h1 class="text-xl font-bold mb-4">Update Excel File</h1>
            <form method="POST">
                <label class="block text-sm font-medium text-gray-700 mb-2">Enter New Value for Cell B34:</label>
                <input type="text" name="new_value" class="border border-gray-300 rounded p-2 w-full mb-4" required>
                <button type="submit" class="bg-blue-500 text-white rounded py-2 px-4">Submit</button>
            </form>

            {% if result is not none %}
            <div class="mt-6 p-4 bg-green-100 text-green-700 rounded">
                <strong>Value of Cell B29:</strong> {{ result }}
            </div>
            {% endif %}
        </div>
    </div>
</body>
</html>

        
            # app.py

from flask import Flask, render_template, request, jsonify
import xlwings as xw
import os
import shutil
import uuid
import tempfile

app = Flask(__name__)
ORIGINAL_EXCEL_PATH = 'tax_calculator.xlsx'

def process_excel(new_value, temp_file_path):
    try:
        with xw.App(visible=False) as app:
            wb = xw.Book(temp_file_path)
            sheet = wb.sheets['2) Client Info']

            # Set the new value in cell B34
            sheet.range('B34').value = new_value

            # Recalculate the workbook
            wb.app.calculate()

            # Get the value from cell B29
            result = sheet.range('B29').value

            # Save and close the workbook
            wb.save()
            wb.close()
        return result
    finally:
        # Clean up temporary file
        if os.path.exists(temp_file_path):
            os.remove(temp_file_path)

@app.route('/', methods=['GET', 'POST'])
def index():
    result = None
    if request.method == 'POST':
        new_value = request.form.get('new_value')

        # Create a unique temporary copy of the Excel file
        temp_file_path = os.path.join(tempfile.gettempdir(), f"tax_planning_{uuid.uuid4()}.xlsx")
        shutil.copy(ORIGINAL_EXCEL_PATH, temp_file_path)

        # Process the Excel file synchronously
        result = process_excel(new_value, temp_file_path)

    return render_template('index.html', result=result)

if __name__ == '__main__':
    app.run(debug=True)

This is a basic, minimal implementation, but we can expand and innovate as needed to suit our requirements.