Question 249797 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – Writing to excel in VS Code using Python 3.11.4

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:enter image description here

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

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search