'Worksheet' object has no attribute 'set_column'

Question

I am trying to auto adjust the length of every header in my dataframe while converting it to an excel file like the following:

from pandas import ExcelWriter


    for k,v in final_1.items():
        v.to_excel(writer, sheet_name=k, index=False)

        for column in v:
            column_length = max(v[column].astype(str).map(len).max(), len(column))
            col_idx = v.columns.get_loc(column)
            writer.sheets[k].set_column(col_idx, col_idx, column_length+1)

where final_1 is a dictionary of dataframes.

This is working fine on the local server but upon deployment to AWS it shows the following error:

'Worksheet' object has no attribute 'set_column'

complete Traceback:

Traceback (most recent call last):
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/django/views/generic/base.py", line 71, in view
    return self.dispatch(request, *args, **kwargs)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/var/app/venv/staging-LQM1lest/lib/python3.8/site-packages/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/var/app/current/inventory/RFIDReport.py", line 204, in get
    writer.sheets[k].set_column(col_idx, col_idx, column_length+1)

Exception Type: AttributeError at /rfid-dumpdownload/
Exception Value: 'Worksheet' object has no attribute 'set_column'

I am unable to understand why this is happening as both server has same version of python and pandas

Without having any panda knowledge, can you check on the server what the sheets[k] object is supposed to be, (dir/type/etc.) because it seems that it's either a None (although that would be a different error) or some other object that does not hold the set_column attribute. Normally I'd advice to put in breakpoints but I'm not quite sure that's even possible on AWS. — JustLudo, Sep 19 '22 at 12:19

jmcnamara · Accepted Answer · 2022-09-20T07:38:24.877

1

Pandas can use either openpyxl or xlsxwriter as "engines" for creating xlsx files with to_excel().

The set_column() method is a xlsxwriter method but the error message about the missing method/attribute indicates that pandas isn't using it, probably because it isn't installed, and is probably using openpyxl instead.

In order to avoid the you should specify explicitly the xlsx engine that want when creating the writer object:

writer = pd.ExcelWriter('filename.xlsx', engine='xlsxwriter')

You will probably also need to install xlsxwriter. That is probably the difference between the AWS server and the test server.

edited Sep 20 '22 at 07:38

answered Sep 19 '22 at 23:18

jmcnamara

38,196
6
90
108

How did you find out that Pandas is using openpyxl? – Rahul Sharma Sep 20 '22 at 06:16
I thought I saw a reference to openpyxl in the trace but now that I check that isn't the case. In which case it was just an informed guess based on seeing similar issues. – jmcnamara Sep 20 '22 at 07:35

'Worksheet' object has no attribute 'set_column'

1 Answers1