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())

M69k65y · Accepted Answer · 2023-09-21 10:42:55Z

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:

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

matleg · Accepted Answer · 2023-09-21 10:07:06Z

You are right that worksheet has an attribute called set_column and that this attribute is a function. It is a bounded method as indicated by the debugger. I tried to reproduce your problem with this simplified example and was not able to, could you please try it and see if it helps target the bug ? (I know it is not a real answer, I'll delete it if it brings nothing).

import pandas as pd

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"],
)

writer = pd.ExcelWriter("pandas_simple.xlsx", engine="xlsxwriter")
df1.to_excel(writer, sheet_name="toto")

workbook = writer.book
worksheet = writer.sheets["toto"]
print(dir(worksheet))  # here we see set_column
print(worksheet.set_column)  # here there is no bug

Collectives™ on Stack Overflow

Writing to excel in VS Code using Python 3.11.4

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related