Error using xlwings package with streamlit

Question

I have a streamlit app that allow user to read excel file and try to split each sheet into a different workbook for this I am using the xlwings package when I try to debug the code it crash and display the error below:

error :com_error:(-2147221008,'Colnitialize has not been called.',None,None)

What does this error mean? And how to fix it?

Code:

import streamlit as st 
import pandas as pd
import numpy as np

from pathlib import Path
import xlwings as xw  

sheet5_path = "C:/Users/gm/Documents"

st.markdown("""

<style>
.header_title {
        font-size:30px !important;
        color:black;
        text-align :center
    }
.big-font {
        font-size:50px !important;
        color:Red
</style>
""", unsafe_allow_html=True)

st.title("Excel Split & Send")
try:
    data = st.sidebar.file_uploader("Upload Dataset",type=["csv","xlsx","xls"])
    
except Exception as e:
    st.write("error is: {}".format(e))
    
if data is not None:
    sheet1,sheet2,sheet3,sheet4,sheet5 = None,None,None,None,None
    with pd.ExcelFile(data,) as reader:
        sheet1 = pd.read_excel(reader,sheet_name="sheet1")
        sheet2 = pd.read_excel(reader,sheet_name="sheet2")
        sheet3 = pd.read_excel(reader,sheet_name="sheet3")
        sheet4 = pd.read_excel(reader,sheet_name="sheet4")
        sheet5 = pd.read_excel(reader,sheet_name="sheet5")

        st.write(sheet1.finished[0])
        st.write(sheet1.unfinished[0])
        st.markdown('<p class="header_title">sheet1|{}</p>'.format(str(sheet1.shape[0])), unsafe_allow_html=True)
        sheet1 = pd.read_excel(reader,sheet_name="sheet1",usecols=[0,1,2,3,4])
        st.dataframe(sheet1)
    
        st.write(sheet2.finished[0])
        st.write(sheet2.unfinished[0])
        st.markdown('<p class="header_title">sheet2|{}</p>'.format(str(sheet2.shape[0])), unsafe_allow_html=True)
        sheet2 = pd.read_excel(reader,sheet_name="sheet2",usecols=[0,1,2,3,4])   
        st.dataframe(sheet2)

        st.write(sheet3.finished[0])
        st.write(sheet3.unfinished[0])  
        st.markdown('<p class="header_title">sheet3|{}</p>'.format(str(sheet3.shape[0])), unsafe_allow_html=True)
        sheet3 = pd.read_excel(reader,sheet_name="sheet3",usecols=[0,1,2,3,4])
        st.dataframe(sheet3)
        
        st.write(sheet4.finished[0])
        st.write(sheet4.unfinished[0])
        st.markdown('<p class="header_title">sheet4|{} </p>'.format(str(sheet4.shape[0])), unsafe_allow_html=True)
        sheet4 = pd.read_excel(reader,sheet_name="sheet4",usecols=[0,1,2,3,4])
        st.dataframe(sheet4)

        st.write(sheet5.finished[0])
        st.write(sheet5.unfinished[0])
        st.markdown('<p class="header_title">sheet5|{} </p>'.format(str(sheet5.shape[0])), unsafe_allow_html=True)
        sheet5 = pd.read_excel(reader,sheet_name="sheet5",usecols=[0,1,2,3,4])  
        st.dataframe(sheet5)
    
    if st.sidebar.button("Split & Send"):
        #create  xlwings object
        app = xw.App(visible = False)

        # create workbook
        wb = xw.Book(data)
        # iterate over the sheets of the created workbook
    # if sheet name = total  delete it (do not copy it)
        for  sheet in wb.sheets:
            if "total" in sheet.name:
                sheet.delete()
            
            # create new work book copy the first sheet  
            else:
                wb_new = app.books.add()
                sheet.copy(after = wb_new.sheets[0])
                wb_new.sheets[0].delete()
                
                #delete the columns G:H 
                wb_new.sheets[0].range('G:H').delete()
                
                # save the new excel files  as name of each sheet  
                if "sheet5"  in sheet.name:
                    wb_new.save(f"{sheet5_path}/{sheet.name}.xlsx")
                else:
                    wb_new.save(f"{sheet.name}.xlsx")
                # close the create workbook
                wb_new.close()

        
else:
    st.markdown('<p class="big-font">You Need to Upload a FILE !!</p>', unsafe_allow_html=True)

Once ib click the button so the line next the if statement `app= xw.App(visible =False)` — Ghgh Lhlh, Apr 05 '22 at 04:58

score 0 · Answer 1 · answered Apr 05 '22 at 07:26

0

Define xw.App() after defining the existing data.

if st.sidebar.button("Split & Send"):  
    wb = xw.Book(data)

    # Use context manager to prevent zombie processes.
    with xw.App(visible=False) as app:
        wb_new = app.books.add()
        # ...
        wb_new.close()
        
    wb.close()

answered Apr 05 '22 at 07:26

ferdy

4,396
2
4
16

I tried your suggestion as defining `xw.App()` after defining the existing data. The system crash and display the below error: `Attribute Error: 'UploadedFile' object has no attribute 'lower' lib/site-packages/xlwings /main.py line 817 fullname =fullname.lower()` – Ghgh Lhlh Apr 05 '22 at 09:19
Review your code on how to upload file properly at https://docs.streamlit.io/library/api-reference/widgets/st.file_uploader – ferdy Apr 05 '22 at 09:41
Its the same function that the streamlit document show it. I think the problem is in the xlwings package as it say that in the error. But i do now know where and what to fix. – Ghgh Lhlh Apr 05 '22 at 09:47

score 0 · Answer 2 · answered Apr 06 '22 at 10:03

0

I solved this question by importing pythoncom package and call the pythoncom.CoInitialize() each time the button is pressed

answered Apr 06 '22 at 10:03

Ghgh Lhlh

155
1
3
14

Error using xlwings package with streamlit

Code:

2 Answers2