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

By using openpyxl you can insert iew rows and columns

import openpyxl

file = "xyz.xlsx"
#loading XL sheet bassed on file name provided by user
book = openpyxl.load_workbook(file)
#opening sheet whose index no is 0
sheet = book.worksheets[0]

#insert_rows(idx, amount=1) Insert row or rows before row==idx, amount will be no of 
#rows you want to add and it's optional
sheet.insert_rows(13)

Hope this helps

Answer 3

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.

Answer 4

You can try this

import xlsxwriter

wb = Workbook("name.xlsx")
ws = wb.add_worksheet("sheetname")

# Write a blank cell
ws.write_blank(0, 0, None, cell_format)
ws.write_blank('A2', None, cell_format)

Here is the official documentation: Xlsxwriter worksheet.write_blank() method

Another alternative is to merge a few blank columns

ws.merge_range('A1:D1', "")

Otherwise you'll need to run a loop to write each blank cell

# Replace 1 for the row number you need
for c in range(0,10):
   ws.write_blank(1, c, None, cell_format)

Inserting a row is equivalent to adding +1 to your row count. Technically there is no need for a "blank row" method and I'm pretty sure that's why it isn't there.

Answer 5

you should usewrite

read this: set_column(first_col, last_col, width, cell_format, options)

for example:

import xlsxwriter
workbook =xlsxwriter.Workbook('xD.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(row, col,     'First Name')
workbook.close()

Answer 6

I am very much unhappy with the answers. The library xlxsWriter tends to perform most of the operations easily. To add a row in the existing worksheet , you can

    wb.write_row(rowNumber,columnNumber,listToAdd)