[Python] Moving from a pool of excel instances to dynamic creation of instances

I have a flask api on a windows server which is used to create/read/write and run macro on excel files. I use xlwings for the interaction with excel.

Atm i have a pool of instances, when a request is made it contains a user_id, which i use to pair an instance with a user. I also have a method to kill an excel instance and create a new one in the pool. If not instance if available i use a backup instance for all other users. This is working pretty well

Now, i would like to create instances dynamically when a user need one. Since i can't create it directly in the thread of the request (excel doesn't support the ineteraction of multiple thread with an excel instance) i thinked about starting an instance in the same way that i kill/start a new instance atm (create a redis data that signal to the main thread that i need a new instance to be created, then store the pid of the instance in redis and get it back in the call's thread).

However this doesn't work, i have the following error when trying to open a workbook:

info:app found f<App [excel] 9232>

ERROR:custom_logger:Error opening workbook: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'La méthode Open de la classe Workbooks a échoué.', 'xlmain11.chm', 0, -2146827284), None)


here is the code when using a pool of instance:

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.append(os.path.dirname(SCRIPT_DIR))

# Initialize Flask app
app = Flask(__name__)

# Configure Flask logging
app.logger.setLevel(logging.DEBUG)

BUCKET_NAME = os.getenv('AWS_BUCKET')

# Initialize COM once during app startup, it's necessary for the communication between xlwings and excel
pythoncom.CoInitialize()


def create_excel_instances(number_of_instances):
xl_apps = []
number_of_instances = int(number_of_instances)
for i in range(1, number_of_instances + 1):
xl_app = xw.App(visible=False)
xl_app.screen_updating = False
xl_app.display_alerts = False
xl_app.enable_events = False
xl_app.ask_to_update_links = False
xl_app.ignore_remote_requests = True
redis_instance.set(f'XL_APP{i}_PID', xl_app.pid)
print(f'xl_app{i} pid: {xl_app.pid}')
xl_apps.append(xl_app)

backup_app = xw.App(visible=False)
backup_app.screen_updating = False
backup_app.display_alerts = False
backup_app.enable_events = False
backup_app.ask_to_update_links = False
backup_app.ignore_remote_requests = True
redis_instance.set('XL_APP_BACK_UP_PID', backup_app.pid)
print(f'backup app pid: {backup_app.pid}')
xl_apps.append(backup_app)
return xl_apps


# Create Excel instances
xl_apps = create_excel_instances(os.getenv('NUMBER_OF_EXCEL_INSTANCES'))

setup_routes(app)

# Start (or restart) scheduler
if scheduler.running:
scheduler.shutdown(wait=False)
scheduler.start()


def create_app():
return app


def restart_excel_instance(instance_key):
try:
custom_logger.info(f"Starting restart of instance {instance_key}")
old_pid = int(redis_instance.get(instance_key))
custom_logger.info(f"Old PID: {old_pid}")

try:
if old_pid in xw.apps:
custom_logger.info(f"Closing via xlwings Excel instance (PID: {old_pid})")
for book in xw.apps[old_pid].books:
try:
book.close()
except Exception as e:
custom_logger.error(f"Error closing workbook: {e}")
try:
xw.apps[old_pid].quit()
except Exception as e:
custom_logger.error(f"Error closing via xlwings: {e}")
except Exception as e:
custom_logger.error(f"Error during xlwings closure: {e}")
# Wait a bit to ensure the application is properly closed
time.sleep(2)

# Force closure via psutil if process still exists
try:
import psutil
process = psutil.Process(old_pid)
if process.is_running():
custom_logger.warning(f"Excel instance still running, attempting forced termination")
process.terminate()
process.wait(timeout=5)
custom_logger.info("Excel instance successfully terminated")
except psutil.NoSuchProcess:
custom_logger.info(f"Process {old_pid} no longer exists")
except Exception as e:
custom_logger.error(f"Error during forced process termination: {e}")

custom_logger.info("Creating new Excel instance")
xl_app = xw.App(visible=False)
xl_app.screen_updating = False
xl_app.display_alerts = False
new_pid = xl_app.pid
redis_instance.set(instance_key, new_pid)
custom_logger.info(f"New instance successfully created (PID: {new_pid})")

return True
except Exception as e:
custom_logger.error(f"Error during instance restart {instance_key}: {e}")
return False


def check_for_restart_requests():
while True:
for instance_key in [f'XL_APP{i}_PID' for i in range(1, int(os.getenv('NUMBER_OF_EXCEL_INSTANCES')) + 1)] + ['XL_APP_BACK_UP_PID']:
restart_flag = redis_instance.get(f'{instance_key}_needs_restart')
if restart_flag and restart_flag.decode('utf-8') == '1':
print(f"Restart required for instance {instance_key}")
restart_excel_instance(instance_key)
redis_instance.delete(f'{instance_key}_needs_restart')
time.sleep(5)


restart_checker = threading.Thread(target=check_for_restart_requests, daemon=True)
restart_checker.start()


if __name__ == "__main__":
from waitress import serve
serve(app, host="0.0.0.0", port=5000, threads=int(os.getenv('MAX_THREADS', 4)))


and here is the one when i try to create dynamic instances:

CRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
sys.path.append(os.path.dirname(SCRIPT_DIR))


# Initialize Flask app
app = Flask(__name__)

# Configure Flask logging
app.logger.setLevel(logging.DEBUG)

BUCKET_NAME = os.getenv('AWS_BUCKET')

# Initialize COM once during app startup, it's necessary for the communication between xlwings and excel
pythoncom.CoInitialize()

def setup_xl_app():
print('open first app')
first_xl_app = xw.App(visible=False)
first_xl_app.screen_updating = False
first_xl_app.display_alerts = False

redis_instance.set('first_xl_instance', first_xl_app.pid)
print('open done')
return [first_xl_app]


xl_apps = setup_xl_app()

setup_routes(app)

# Start (or restart) scheduler
if scheduler.running:
scheduler.shutdown(wait=False)
scheduler.start()

def create_app():
return app


def open_excel_instance(user_id):
custom_logger.info("Creating new Excel instance")
try:
xl_app = xw.App(visible=False)
xl_app.screen_updating = False
xl_app.display_alerts = False

new_pid = xl_app.pid
instance_key = f'xl_instance_user_{user_id}'
redis_instance.set(instance_key, new_pid)

try:
_ = xl_app.calculation
custom_logger.info(f"New instance successfully created (PID: {new_pid})")
return True
except Exception as e:
custom_logger.error(f"Instance créée mais test échoué: {e}")
try:
xl_app.quit()
except:
pass
redis_instance.delete(instance_key)
return False

except Exception as e:
custom_logger.error(f"Error creating Excel instance: {e}")
return False


def check_for_restart_requests():
while True:
user_id = redis_instance.lpop('start_xl_instance')
if user_id:
user_id = user_id.decode('utf-8')
open_excel_instance(user_id)
else:
time.sleep(2)

restart_checker = threading.Thread(target=check_for_restart_requests, daemon=True)
restart_checker.start()


if __name__ == "__main__":
from waitress import serve
serve(app, host="0.0.0.0", port=5000, threads=int(os.getenv('MAX_THREADS', 4)))


I don't see the difference between the 2 way of creating instances, do you have any idea of why i can use the instance in the first way but no in the second way ?

Continue reading...