Flask + xlwings: Building Powerful Automation for Excel Calculation Tasks

In day-to-day work, Excel files often need dynamic data updates and complex calculations. If you've struggled with handling Excel data manually, why not let Python take care of it all? This tutorial will guide you on how to use Python's Flask and xlwings libraries to create an API. By sending a simple POST request, you can automate Excel data input and real-time calculations, taking Excel processing to a whole new level.

Project Highlights

Automated Excel Processing: No manual work in Excel—API handles data entry, calculation, and result retrieval automatically.
Easy Integration: Create an API service through Flask that can integrate with any client program.
Streamlined Workflow: Perfect for scenarios like financial reports and data analysis where frequent data updates are needed.

Let's dive into how to make this project happen!

Environment Setup

Before starting, make sure you've installed the following libraries:

pip install flask xlwings

Flask: A lightweight web framework for quickly building APIs.
xlwings: Allows Python to control Excel file operations.

Code Explanation: Building the Excel Processing API

Here is the complete Python code, with a step-by-step explanation to help you understand how it works.

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

app = Flask(__name__)

def process_excel(file_path, input_value1, input_value2):
    # Check if the file exists
    if not os.path.exists(file_path):
        print(f"Error: '{file_path}' does not exist.")
        return None
    
    # Start Excel application (invisible mode)
    app = xw.App(visible=False)
    try:
        # Open the Excel file
        wb = app.books.open(file_path)
        sheet = wb.sheets.active
        
        # Enter data
        sheet.range('I10').value = input_value1
        sheet.range('I11').value = input_value2
        
        # Retrieve calculation result
        result = sheet.range('I13').value
        
        print(f"Input value 1: {input_value1}")
        print(f"Input value 2: {input_value2}")
        print(f"Calculation result: {result}")
        
        return result
    except Exception as e:
        print(f"Error: {str(e)}")
        return None
    finally:
        # Close the Excel file and application
        wb.close()
        app.quit()

@app.route('/process_excel', methods=['POST'])
def excel_service():
    data = request.json
    file_path = data.get('file_path')
    input_value1 = data.get('input_value1')
    input_value2 = data.get('input_value2')
    
    # Validate parameters
    if not all([file_path, input_value1, input_value2]):
        return build_failed_rsp(400, "Missing required parameters"), 200, {"Content-Type": "application/json"}
    
    # Process the Excel file
    result = process_excel(file_path, input_value1, input_value2)
    
    if result is not None:
        return build_success_rsp(result), 200, {"Content-Type": "application/json"}
    else:
        return build_failed_rsp(500, "Processing failed"), 200, {"Content-Type": "application/json"}

def build_success_rsp(data):
    rsp = {
        "message": "Success",
        "code": 0,
        "data": data
    }
    return json.dumps(rsp, ensure_ascii=False)

def build_failed_rsp(code, msg):
    rsp = {
        "message": msg,
        "code": code
    }
    return json.dumps(rsp, ensure_ascii=False)

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=50073)

Testing the API Service

You can use Postman or other tools to test this API. Just send the following POST request:

POST http://127.0.0.1:50073/process_excel
Content-Type: application/json

{
    "file_path": "/Users/zhanghuan/Desktop/20200606093420.xlsx",
    "input_value1": 100.6,
    "input_value2": 100.7
}

The API will read the data from the Excel file and return the calculation result.

Example Response

On success, the response format is:

{
    "message": "Success",
    "code": 0,
    "data": 201.3
}

If the file path is incorrect or the processing fails, the response will be:

{
    "message": "Processing failed",
    "code": 500
}

FAQ

1. Can this code run without Excel software?
No, xlwings relies on a local installation of Excel, so please ensure you run it on Windows or Mac with Excel installed.

2. Can this API endpoint handle batch processing of Excel files?
Currently, it processes one file at a time. You could handle multiple files by looping through requests or by adding batch processing logic in the code.

3. How to debug if processing fails?
Check if the file path is correct, the Excel file is undamaged, or if Excel supports the required formulas.

Conclusion

With Python, Flask, and the xlwings library, you can effortlessly build an automated Excel processing API service. Whether you need regularly updated data sheets or complex financial report calculations, this approach can greatly simplify your workflow. We hope this tutorial supports your Python projects and enhances productivity at work!

Ready to say goodbye to repetitive Excel tasks? Try out this automated Excel API now!