Alternative of xlwings without using CoInitialize

Question

I am trying to write a code that will be able to replace cells in an existing excel sheet with a dataframe values. The lines work, but the problem is: that the codes require pythoncom library, a windows base library. So when I tried to upload it to streamlit cloud, which is based on Linux, an error arose. the code is like this:

pythoncom.CoInitialize()
with xw.App() as app:
    wb=xw.Book(path)
    wb.sheets(sheet_name).range(kolom_cluster+str(header+1)).options( index=False,chunksize=30_000).value=df["cluster"]
    wb.save(path)     
    wb.close()

Therefore, I am wondering if there is an alternative to doing the same (writing df values to an existing excel file) without the need for pythoncom?

Thank you very much for your kind attention and solution.

This is my first question in Stackoverflow. Have been using it for quite a while. I have tried to search for the same solution over StackOverflow quite some time. Thus, I am hoping, if you guys could help me solve the problem.

Thank you very much.

PS. I have tried script like this, using openpyxl

writer = pd.ExcelWriter(path, engine='openpyxl',
    mode='a',    # append data instead of overwriting all the book by default
    if_sheet_exists='overlay'   # write the data over the old one instead of raising a ValueError
    )

df["cluster"].to_excel(
    writer, 
    sheet_name=sheet_name,
    startrow=header+1,     # upper left corner where to start writing data
    startcol=ord(kolom_cluster),     # note that it starts from 0, not 1 as in Excel
    index=False,    # don't write index
    header=False    # don't write headers
)

writer.save()
writer.close()

But it returns some error:

Vitalizzare · Answer 1 · 2022-09-08T12:49:49.207

I think we could use pandas.ExcelWriter in this case with openpyxl as an engine, for example. I hope the code below is self-explaining:

writer = pd.ExcelWriter(
    "path to the file of interest", 
    engine='openpyxl',
    mode='a',    # append data instead of overwriting all the book by default
    if_sheet_exists='overlay'   # write the data over the old one instead of raising a ValueError
)

df.to_excel(
    writer, 
    sheet_name="worksheet of interest"
    startrow=0,     # upper left corner where to start writing data
    startcol=0,     # note that it starts from 0, not 1 as in Excel
    index=False,    # don't write index
    header=False    # don't write headers
)

writer.save()

Update

Here's a short test to see if it works:

import pandas as pd
from pathlib import Path
from openpyxl import Workbook

df = pd.DataFrame(1, [1,2,3], [*'abc'])

f = Path('test_openpyxl.xlsx')
if not f.exists():
    wb = Workbook()
    wb.worksheets[0].title = "Data"
    wb.save(f)
    wb.close()

with pd.ExcelWriter(
    f, 
    mode='a',    
    if_sheet_exists='overlay'  
) as writer:
    assert writer.engine == 'openpyxl'
    for n, (i, j) in enumerate(zip([0,0,3,3],[0,3,0,3]), 1):
        (n*df).to_excel(
            writer, 
            sheet_name="Data",
            startrow=i,   
            startcol=j,   
            index=False,  
            header=False  
        )

My environment:

python 3.9.7
pandas 1.4.3
openpyxl 3.0.10
excel version 2108 (Office 365)

Thank you very much for your reply. I have run your program and I found that the excel file is corrupted after I run the script. This kind of error does not happen when I use xlwings — bedy kharisma, Sep 08 '22 at 09:20
@bedykharisma Unfortunately, I couldn't catch any error in my env. An excel file opened smoothly and its content was what I expected to see. Could you please show your environment? — Vitalizzare, Sep 08 '22 at 12:50

Alternative of xlwings without using CoInitialize

1 Answers1