xlsxwriter error: AttributeError: 'Workbook' object has no attribute 'add_format'

Question

I'm doing some simple conditional formatting using xlsxwriter but I am getting this error when I run the code below.

AttributeError: 'Workbook' object has no attribute 'add_format'

I have updated xlsxwriter and looked at a lot of questions on SO and documentation but nothing has worked yet.

This is my code:

workbook = load_workbook(input_excel_filename)
writer = pd.ExcelWriter(input_excel_filename, engine="xlsxwriter")
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

trends_sheet = writer.sheets["Trends"]
slight_increase = writer.book.add_format({"bg_color":"#d3e6d5"})
trends_sheet.conditional_format("E:E", {"type":"cell", "criteria":"==", "value":"Slight Increase", "format":slight_increase})

Focal

What's the type of writer.book? – Bathroom

Answer 1

Check if xlsxwriter package is installed or not....even I faced the same issue..resolved it after installing the package...same answer goes for any attribute error issue related to workbook/writer if your code is correct

Esparto

This solved my problem! Thanks for the big help – Aenea

This is a good answer. What I found is upgrading the package worked. pip install xlsxwriter --upgrade – Canaan

Answer 2

Cause and solution

1. Makesure variable is usable
   • such as mine
     • first: workbook = writer.book
     • then: header_format = workbook.add_format(
2. Makesure already set pandas's engine (here using xlsxwriter)
   • when init ExcelWriter, set your engine
     • writer = pd.ExcelWriter(outputFile, engine='xlsxwriter’, options={'strings_to_urls': False} )
3. Makesure already installed related lib (xlsxwriter)
   • pip install xlsxwriter
     • or mine: pipenv install xlsxwriter

Full code for refer

import pandas as pd

writer = pd.ExcelWriter(
    output_final_total_file,
    engine='xlsxwriter',
    options={'strings_to_urls': False}
)

...
df = pd.read_csv(outputExcelFile, sep=pandas_sep)
...
df.to_excel(outputExcelFile.replace('.csv', '.xlsx'), index=False)
...
df.to_excel(writer, sheet_name=SheetNamePay, startrow=1, header=False, index=False)
...

workbook  = writer.book

header_format = workbook.add_format( # !!! here workable, no error
    {
        'bold': True,
        'text_wrap': True,
        # 'valign': 'top',
        'valign': 'center',
        # 'fg_color': '#D7E4BC',
        'bg_color': '#edbd93',
        'border': 1
    }
)

Balanced

#76331844 . Please could someone help me with this – Accumbent

Answer 3

Part of the problem was I needed to set writer.book explicitly. So add the line writer.book = workbook after defining writer. Also adding engine="openpyxl" to the ExcelWriter got rid of a subsequent error. Altogether this seems to work:

workbook = load_workbook(input_excel_filename)
writer = pd.ExcelWriter(input_excel_filename, engine="openpyxl")
writer.book = workbook
writer.sheets = dict((ws.title, ws) for ws in wb.worksheets)
data.to_excel(writer, sheet_name="Data", index=False)
writer.save()
writer.close()

I couldn't get it to work with conditional formatting but setting formatting in the Excel spreadsheet directly actually seems to work, because even if the data is rewritten the formatting stays intact.

Focal

You did not get it work because you set engine to "openpyxl". having xlsxwriter package installed, either set the engine to "xlsxwriter " or just dont set that engine parameter. The default engine is "xlsxwriter " already. – Macias

Answer 4

In my case, I solved it with specifying the type of the engine:

with pd.ExcelWriter('file.xlsx', engine='xlsxwriter') as writer:

Cytology