AttributeError: 'Worksheet' object has no attribute 'set_column'

Question

I am getting an error that seems... wrong. Because of course worksheet object has set_column() as a function, it's in the docs. I've probably done something dumb like drop a parenthesis.

Here's the error:

Traceback (most recent call last):
  File "scrubaddresses.py", line 137, in <module>
    run()
  File "scrubaddresses.py", line 118, in run
    format_col_width(worksheet)
  File "scrubaddresses.py", line 24, in auto_format_cell_width
    ws.set_column('B:C', 20)
AttributeError: 'Worksheet' object has no attribute 'set_column'

Here's my ridiculous import. Config is some constants, controller has some helper functions.

from smartystreets_python_sdk import StaticCredentials, exceptions, Batch, ClientBuilder
from smartystreets_python_sdk.us_street import Lookup as StreetLookup
from pathlib import Path
import pandas as pd
import numpy as np
import config
from controller import getExcel, clean

The func in question:

def format_col_width(ws):
    ws.set_column('B:C', 20)
    ws.set_column('D', 1)
    ws.set_column('E', 20)

Where the ws being passed comes from:

            df1 = df.replace(np.nan, '', regex=True)
            print(df1)

            df1.to_excel(writer, sheet, index = False, engine='xlsxwriter')
            worksheet = writer.sheets[sheet]
            format_col_width(worksheet)

Did I forget to import something? Xlsxwriter is installed.

score 11 · Answer 1 · answered Sep 08 '22 at 01:02

11

The reason it gives: AttributeError: 'Worksheet' object has no attribute 'write'

This is because you have not installed xlsxwriter on your PC.

you can use:

pip install xlsxwriter

and it will work isa.

answered Sep 08 '22 at 01:02

Abdelrahman Khallaf

379
3
10

score 5 · Answer 2 · answered Aug 19 '20 at 22:11

5

There is an error in the single column ranges. They should be D:D instead of D since the method needs a start and end column even if they are the same.

With that modification the code should work:

import pandas as pd

def format_col_width(ws):
    ws.set_column('B:C', 20)
    ws.set_column('D:D', 1)
    ws.set_column('E:E', 20)

df = pd.DataFrame({'Data1': [10, 20, 30, 20, 15, 30, 45]})

writer = pd.ExcelWriter('pandas_test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')

workbook  = writer.book
worksheet = writer.sheets['Sheet1']

format_col_width(worksheet)

writer.save()

Output:

Try the above code and see if it works. If it doesn't then XlsxWriter may not be installed and Pandas is defaulting to OpenPyXL.

answered Aug 19 '20 at 22:11

jmcnamara

38,196
6
90
108

I changed it, but unfortunately that's not the problem - the error is on the first line, ` File "scrubaddresses.py", line 24, in auto_format_cell_width ws.set_column('B:C', 20) ` I did run pip install xlsxwriter, and nothing changed. Maybe i should reboot and try again, lol. Weirdly enough, I ran your code as test.py and it threw no errors. – Laura Aug 19 '20 at 23:09
I have figured it out - it was using xlwt, as I am working with .xls files. – Laura Aug 27 '20 at 22:24
Good one. I'll look out for that in future. – jmcnamara Aug 27 '20 at 22:56

score 4 · Answer 3 · answered Jan 08 '21 at 15:34

4

I had the same problem, the following worked for me:

def format_col_width(ws):
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 20
    ws.column_dimensions['D'].width = 1
    ws.column_dimensions['E'].width = 20

answered Jan 08 '21 at 15:34

Cristobal Sarome

178
11

1

This works for me thanks... – Ajay Kumar Nov 09 '21 at 06:55

score 0 · Answer 4 · answered Apr 21 '23 at 18:52

    # monkey path :) -- https://stackoverflow.com/questions/74844262/how-can-i-solve-error-module-numpy-has-no-attribute-float-in-python
    # as of 2023/04/21
    # AttributeError: module 'numpy' has no attribute 'float'
    np.float = float  
    writer = pd.ExcelWriter('test.xlsx') 
    df.to_excel(writer, sheet_name='Sheet1', index=False, na_rep='NaN')
    for column in df:
        column_length = max(df[column].astype(str).map(len).max(), len(column))
        col_idx = df.columns.get_loc(column)
        #writer.sheets['Sheet1'].set_column(col_idx, col_idx, column_length)  <-- set_column deprecated
        writer.sheets['Sheet1'].column_dimensions[chr(65+col_idx)].width = column_length + 5   # add some extra space {5 here} to have a better look

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Apr 25 '23 at 09:10

AttributeError: 'Worksheet' object has no attribute 'set_column'

4 Answers4