How to save an excel workbook from within python

Question

I'm new to python I made this code which makes you input data that is Automatticly inputted into excel. The thing is when I run the program twice it overwrites the previous data so I need to know how to save it. this is the code I wrote

import xlsxwriter

workbook = xlsxwriter.Workbook('Book1.xlsx')
worksheet = workbook.add_worksheet()

name = input('your full name: ')
age = int(input('your age: '))
emaill = input('your email: ')


idontknow = (
    ['Name', name],
    ['Age', age],
    ['Email', emaill],
)
row = 0
col = 0
for item, cost in idontknow:
    worksheet.write(row, col, item)
    worksheet.write(row, col + 1, cost)
    row += 1
workbook.close()

any suggestion will help

well you're starting from 0,0 every time, so you're writing into the same cells. You need to see where the data already in the sheet ends, and set row/col to values large enough not to overwrite the old data. — Esther, Jun 13 '22 at 19:16
actually, I just looked it up: you can't read or modify excel files using xlsxwriter, only write them. If you need to read/modify, use a different library. — Esther, Jun 13 '22 at 19:20
Does this answer your question? [xlsxwriter: is there a way to open an existing worksheet in my workbook?](https://stackoverflow.com/questions/18002133/xlsxwriter-is-there-a-way-to-open-an-existing-worksheet-in-my-workbook) — Esther, Jun 13 '22 at 19:21

score 0 · Answer 1 · answered Jun 13 '22 at 20:03

0

I would recommend using "csv" - https://docs.python.org/3/library/csv.html excel can read and save to csv format, and I find it easier to work with.

answered Jun 13 '22 at 20:03

Mathias Graabeck

39
7

score 0 · Accepted Answer · answered Jun 13 '22 at 20:21

I would recommend using the pandas library to perform the data manipulation. A code that performs the task you indicate would be the following:

import pandas as pd

#read the file and make a DataFrame
data = pd.read_excel('Book1.xlsx')
df = pd.DataFrame(data)

#get the data
name = input('your full name: ')
age = int(input('your age: '))
email = input('your email: ')

#get the last edited row
row = len(df)

#write the data
df.loc[row, 'name'] = name
df.loc[row, 'age'] = age
df.loc[row, 'email'] = email

#save the file
df.to_excel('Book1.xlsx',index=False)

Additionally, this code allows you to have headers for each column in your file, which is convenient in most situations.

score 0 · Answer 3 · answered Jun 14 '22 at 07:06

openpyxl provide more functionality in parsing excel files, with similar syntax as xlsxwriter. (worksheet.max_row for example is accessible in openpyxl but not in xlsxwriter)

import openpyxl

workbook = openpyxl.load_workbook('Book1.xlsx')
worksheet = workbook.create_sheet(0)

name = input('your full name: ')
age = int(input('your age: '))
emaill = input('your email: ')


idontknow = (
    ['Name', name],
    ['Age', age],
    ['Email', emaill],
)

row = worksheet.max_row + 1
for item, cost in idontknow:
    worksheet.cell(row=row, column=0, value=item)
    worksheet.cell(row=row, column=1, value=cost)
    row += 1
workbook.save('Book1.xlsx')

How to save an excel workbook from within python

3 Answers3