Updating large dataframe with around 6500 rows into xlsm file using Xlwings in python

Question

I am trying to update excel file with extension .xlsm with ws[cellid].options(header=False, index=False).value = df.It doesn't contains empty columns or Nan values or any equations.But the same code is working when I try to update few rows.When I try to execute I am getting this error

  self._oleobj_.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147024882), None)

I have tried with different versions of Xlwings and also some addin installations.Only problem with large data frames

Answer 1

For big DataFrames, you can chunk the data like this:

ws[cellid].options(header=False, index=False, chunksize=10_000).value = df

Check out the documentation for options.