Win32Com python create global instance and use it in flask function

Question

I am currently trying to run the following piece of code but I keep getting the error that

pywintypes.com_error: (-2147417842, 'The application called an interface that was marshalled for a different thread.', None, None)

Do i need to execute the flask function through a separate thread. Is there no other way of just creating a global excel instance and using it to open and modify separate workbooks?

Any help regarding the same would be highly appreciated.

from flask import Flask, request, jsonify
import json   
import pythoncom
import win32com.client
from win32com.client import Dispatch

xl = None
xl = win32com.client.Dispatch("Excel.Application")

app = Flask(__name__)


@app.route("/check", methods=['POST'])
def check():

    pythoncom.CoInitialize()
    if request.method == 'POST':
        data = request.get_json()
        fname = data['fname']
        phName = data['PH_Name']
        liName = data['LI_NAME']
        ppm = data['PPM']
        policyTerm = data['Policy_Term']
        sumAssured = data['Sum_Assured']


        wb = None

        if xl : 
            wb = xl.Workbooks.Open(fname) 


            inp_sheet = wb.Sheets["Inputs"]

            #read data from sheet
            data = inp_sheet.Range('D8').Value


            wb.Close(False)
            xl.Quit()
            return (data)


if __name__ == '__main__':
    app.run(threaded=True, port=5001, debug=True)

Cant you use any (pure) Python library to read the Excel file (like openpyxl or any other) ? — Maurice Meyer, Mar 05 '20 at 11:34
The idea is to change the values and then read the output generated through an excel formula. Openpyxl doesnt allow that. — Prateek, Mar 05 '20 at 11:42

score 0 · Answer 1 · edited May 06 '21 at 20:02

You should try to create win32com variable after multi-threading.

Try this:

from flask import Flask, request, jsonify
import json   
import pythoncom
import win32com.client
from win32com.client import Dispatch

xl = None

app = Flask(__name__)

@app.route("/check", methods=['POST'])
def check():

    pythoncom.CoInitialize()
    xl = win32com.client.Dispatch("Excel.Application")
    if request.method == 'POST':
        data = request.get_json()
        fname = data['fname']
        phName = data['PH_Name']
        liName = data['LI_NAME']
        ppm = data['PPM']
        policyTerm = data['Policy_Term']
        sumAssured = data['Sum_Assured']


        wb = None

        if xl : 
            wb = xl.Workbooks.Open(fname) 


            inp_sheet = wb.Sheets["Inputs"]

            #read data from sheet
            data = inp_sheet.Range('D8').Value


            wb.Close(False)
            xl.Quit()
            return (data)


if __name__ == '__main__':
    app.run(threaded=True, port=5001, debug=True)

Win32Com python create global instance and use it in flask function

1 Answers1