xlsxwriter: How to insert a new row

Question

Using xlsxwriter, how do I insert a new row to an Excel worksheet? For instance, there is an existing data table at the cell range A1:G10 of the Excel worksheet, and I want to insert a row (A:A) to give it some space for the title of the report.

I looked through the documentation here http://xlsxwriter.readthedocs.io/worksheet.html, but couldn't find such method.

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()
worksheet.insert_row(1)  # This method doesn't exist

Answer 1

December 2021, this is still not a possibility. You can get around this by doing some planning, and then writing your dataframe starting on different row. Building on the example from the xlsxwriter documentation:

df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
writer = pd.ExcelWriter('my_excel_spreadsheet.xlsx', engine='xlsxwriter')
with writer as writer:
    df.to_excel(writer, sheet_name='Sheet1', startrow = 4) # <<< notice the startrow here

And then, you can write to the earlier rows as mentioned in other comments:

workbook = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.write(row, 0, 'Some Text') # <<< Then you can write to a different row

Not quite the insert() method we want, but better than nothing.

I have found that the planning involved in this process is not really ever something I can get around, even if I didn't have this problem. When I reach the stage where I am taking my data to excel, I have to do a little 'by hand' work in order to make the excel sheet pretty enough for human consumption, which is the whole point of moving things to excel. So, I don't look at the need to pre-plan my start rows as too much out of my way.

Answer 2

Unfortunately this is not something xlsxwriter can do.

openpyxl is a good alternative to xlsxwriter, and if you are starting a new project do not use xlsxwriter.

Currently openpyxl can not insert rows, but here is an extension class for openpyxl that can.

openpyxl also allows reading of excel documents, which xlsxwriter does not.