Convert DataFrame to excel by preserving existing sheets and increase the column size of excel

Question

I am trying to convert the DataFrame to excel without overwriting the existing sheet. The solution is using pd.ExcelWriter with openpyxl engine which supports append mode. Now, I have to increase the column size of the excel, I use pd.ExcelWriter with XlsxWriter engine but it overwrites the remaining sheets.

Openpyxl as an engine:

with pd.ExcelWriter("test.xlsx", engine="openpyxl", mode="a") as writer:
  df.to_excel(writer, sheet_name="name", startrow=num, startcol=num)

XlsxWriter as an engine:

workbook   = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(0, 0, 20)

Can someone please suggest to me a solution where I can do both things:

1. Keep the existing sheets
2. Increase the column width

Answer 1

you can use your ExcelWriter to adjust the column width. Example below. Note that you can only add a new tab and the data with this, not update text within an existing tab. But, it will NOT delete any contents, like in case of xlsxwriter.

from openpyxl.utils.cell import get_column_letter

startRow = 12  #Change as per your req
startCol = 3   #Change as per your req
with pd.ExcelWriter("test.xlsx", engine="openpyxl", mode="a") as writer:  #Your code
    df.to_excel(writer, sheet_name="name", startrow=startRow, startcol=startCol)  #Your code... mostly

    worksheet = writer.sheets['name'] #Get worksheet name    
    for i, col in enumerate(df.columns):  #For each column in df, set width to 60
        worksheet.column_dimensions[get_column_letter(startCol+i+1)].width = 60