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.

Tweezers

Answer 1

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.

Herbertherbicide

Worst error message ever. You saved me hours. – Wilberwilberforce

THIS ANSWER should be accepted ! Cant believe that this was the solution, without any error messages displayed or smth similar. Crazy. Thank you ! – Megargee

Answer 2

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.

Zelaya

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. – Tweezers

I have figured it out - it was using xlwt, as I am working with .xls files. – Tweezers

Good one. I'll look out for that in future. – Zelaya

The D:D is making a smiley with an upside down hat – Tertius

Answer 3

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

Haeres

This works for me thanks... – Sweetmeat

Answer 4

    # monkey path :) -- https://mcmap.net/q/294082/-how-can-i-solve-error-quot-module-39-numpy-39-has-no-attribute-39-float-39-quot-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

Titustityus

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. – Becquerel