Appending rows in excel xlswriter
Solution 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
Solution 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()]
Admin
Updated on June 14, 2022Comments
-
Admin almost 2 years
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.