Appending rows in excel xlswriter

Question

I have created an xls file in which I write some user inputs into the cells. So far so good, the program works; it writes the first line. But when I run again the program instead of appending the rows it writes on top of the first one. I'm trying to understand how to make it append a new row into the excel sheet save it and close it etc

import xlsxwriter

workbook = xlsxwriter.Workbook("test.xlsx",)
worksheet = workbook.add_worksheet()

row = 0
col = 0

worksheet.write(row, col,     'odhgos')
worksheet.write(row, col + 1, 'e/p')
worksheet.write(row, col + 2, 'dromologio')
worksheet.write(row, col + 3, 'ora')


row += 1
worksheet.write_string(row, col,     odigosou)
worksheet.write_string(row, col + 1, dromou)
worksheet.write_string(row, col + 2, dromologio)
worksheet.write_string(row, col + 3, ora)

workbook.close()

With this code I created I'm able to write in the file but how do I make it to append a row in the existing sheet. All tutorials I watched, all instructions I researched, just don't work; I'm doing something wrong obviously but I'm not able to spot it.

Ohm

You can't append to existing file with xlsxwriter. What you can do, is read the file, write it to a new one, and then append on top of that. You could use openpyxl which can do this natively, or read the data with something like xlrd. – Pyramidal

poo..... i feel really stupid now thank you very much mate 4 days of work back to 0 again :D dont you love coding ? again thank you very much :) – Ohm

If you're running the script above, you're probably getting this Error: NameError: name 'odigosou' . If you quote the 3rd parameter in all your worksheet.write_string()'s your script should produce two rows. – Industrialism

Answer 1

Question: ... how do I make it to append a row in the existing sheet

Solution using openpyxl, for instance:

from openpyxl import load_workbook

new_row_data = [
    ['odhgos', 'e/p', 'dromologio', 'ora'],
    ['odigosou', 'dromou', 'dromologio', 'ora']]

wb = load_workbook("test/test.xlsx")
# Select First Worksheet
ws = wb.worksheets[0]

# Append 2 new Rows - Columns A - D
for row_data in new_row_data:
    # Append Row Values
    ws.append(row_data)

wb.save("test/test.xlsx")

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2

Lemkul

workes just fine, also under python 2.7. Cant understand why this response is not marked as solving – Landri

But it appends last rows even I delete rows manually, it does not accept and still adding previous last row like it has'nt been deleted – Showboat

@Lemkul : have you verified if it works well if the Excel file is formulated ? – Clarsach

@MustafaUçar You must delete the entire row, not only the contents of the row. – Chevet

Answer 2

Another solution which avoids FileNotFound errors by creating the file if it doesn't exist:

from openpyxl import Workbook
from openpyxl import load_workbook

filename = "myfile.xlsx"
new_row = ['1', '2', '3']

# Confirm file exists. 
# If not, create it, add headers, then append new data
try:
    wb = load_workbook(filename)
    ws = wb.worksheets[0]  # select first worksheet
except FileNotFoundError:
    headers_row = ['Header 1', 'Header 2', 'Header 3']
    wb = Workbook()
    ws = wb.active
    ws.append(headers_row)

ws.append(new_row)
wb.save(filename)

# Note: if you're adding values from a list, you could instead use:
# new_row = ""
# new_row += [val for val in list]
# Similarly, for adding values from a dict:
# new_row = ""
# new_row = += [val for val in mydict['mykey'].values()]

Howland