AttributeError: 'Workbook' object has no attribute 'add_format' using openpyxl engine in append mode

Question

I am using Excelwriter with openpyxl engine as I want to open excel file in append mode. I am using append mode so that I would be able to clear previous sheets in workbook while every re run. But I m getting this error while using he syntax as below for adding formats to the excel :-AttributeError: 'Workbook' object has no attribute 'add_format'

How do I make it work with openpyxl engine

def write_dataframes_to_excel_sheet(dataframes, dir, name,writer):
#with pd.ExcelWriter(f'{dir}/{name}.xlsx', engine='xlsxwriter') as writer:
workbook = writer.book
worksheet = workbook.create_sheet(str(id))
writer.sheets[str(id)] = worksheet

COLUMN = 0
row = 0


for df in dataframes:
    #worksheet.write_string(row, COLUMN, df.name)
    row += 1
    df.to_excel(writer, sheet_name=str(id),
                startrow=row, startcol=COLUMN,index=False)
    header_format= workbook.add_format({'bold':True,'fg_color' :'00C0C0C0','border': 1})
    for col_num,value in enumerate(df.columns.values):
        worksheet.write(0,col_num,value,header_format)
        column_len=df[value].astype(str).str.len().max()
        column_len=max(column_len,len(value))+3
        worksheet.set_column(col_num,col_num,column_len)            
    row += df.shape[0] + 3

with pd.ExcelWriter(input_filename, engine='openpyxl',mode='a') as writer:
   write_dataframes_to_excel_sheet(df_array, 'C:/Users/path',input_filename,writer)

Answer 1

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

The add_format() method is an xlsxwriter method so that won't work with openpyxl. You will need to use the equivalent openpyxl method.

Answer 2

You can find all the info here. I was searching for the same thing, so let me give you a snippet. xlsxwriter seems so much easier though.

from openpyxl import Workbook
wb = Workbook(write_only = True)
ws = wb.create_sheet('test')
from openpyxl.cell import WriteOnlyCell
from openpyxl.styles import Font

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        cell = WriteOnlyCell(ws, value="hello world")
        cell.font = Font(bold=True, color='00C0C0C0')
        cell.border = Border(left=Side(border_style='Thin',
                              color='FF000000'),
                    right=Side(border_style='Thin',
                               color='FF000000'),
                    top=Side(border_style='Thin',
                             color='FF000000'),
                    bottom=Side(border_style='Thin',
                                color='FF000000')

You should find here all the border styles you would like to add. Hope this helps!