Writing to excel in VS Code using Python 3.11.4

Question

So I have some code that I was able to execute in Anaconda (for testing) and now I am trying to implement at work where we use VS Code. The script runs almost entirely with the exception of the last part where I am trying to write the output to excel. Here is the code:

# Exporting as XLSX file 
writer = pd.ExcelWriter(path+"\\output-{}.xlsx".format(todaysdate))

table.to_excel(writer, startcol=2,startrow=1, index=False, sheet_name = 'Retention')
f.to_excel(writer, sheet_name='Comparison Data', index=False)
Current.to_excel(writer, sheet_name='2022 Payments', index=False)
Previous.to_excel(writer, sheet_name='2021 Payments', index =False)

print(writer)


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

# formatting table on first tab

# auto fit first column by index
worksheet.set_column(2, 8, 22)

# Percentage Format
per_format = workbook.add_format({'num_format': '0%','align':'center'})
worksheet.set_column('F:I',20,per_format)

# Add accounting Format to numbers
acc_format = workbook.add_format({'num_format':'$#,##0','align':'center'})
worksheet.set_column('G:H',20,acc_format)

# Add number format
num_format = workbook.add_format({'num_format':'#,##0','align':'center'})
worksheet.set_column('D:E',20,num_format)

writer.save()

Error I receive is as follows:

Before anyone notices I have installed and imported openpyxl and pandas and I know writer has these attributes (like .set_column()) but I have zero idea why it will not recognize.

Sample Data

df1 = pd.DataFrame({'2022_num_payments': [2, 4, 8, 0],
                   '2022_num_orders': [2, 1, 3, 1]},
                  index=['Mr. Black', 'Mr. Blonde', 'Mr. Pink', 'Mr. White'])

df2 = pd.DataFrame({'2021_num_payments': [11, 5, 9, 10],
                   '2021_num_orders': [4, 2, 3, 5]},
                  index=['Mr. Black', 'Mr. Blonde', 'Mr. Pink', 'Mr. White'])

# creating Retention %
table['Retention Rate'] = table['2022_num_orders'] / table['2021_num_orders']
table['2022 Dollar Retention'] = table['2022_num_payments'] / table['2021_num_payments']
print(table.head())

Answer 1

To build on @matleg's example, the issue you're facing has to do with the writer you're using.

Attempting to run @matleg's example on my end in an environment without xlsxwriter installed raised the error you encountered: AttributeError: 'Worksheet' object has no attribute 'set_column'.
And printing the attributes of worksheet returns, among other things:

[..., 'selected_cell', 'set_printer_settings', 'sheet_format', 'sheet_properties', 'sheet_state', 'sheet_view', 'show_gridlines', 'show_summary_below', 'show_summary_right', 'tables']

However, after installing the xlsxwriter package, printing the attributes of worksheet returns a lot more, including set_column:

[..., 'row_sizes', 'row_spans', 'rstring', 'screen_gridlines', 'select', 'selected', 'selections', 'set_background', 'set_cols', 'set_column', 'set_column_pixels', 'set_comments_author', ...]

To fix your issue:

1. Install xlsxwriter.
2. (Optional but recommended) Explicitly set the writer engine.