Python Xlsx Writer - Write String to new row

Question

I am using the xlsxwriter tool to write some data to a .xlsx from the user input. (I am very new to programming in general so sorry if my code is awful, it would be appreciated if you suggested / corrected any mistakes I have made! sorry!)

Below is current code to do so:

However I am having a few problems writing the data to a new row rather than replace the previous, so the .xlsx ends up being a collection of user data.

import getpass
import os
import xlsxwriter

print('Hello, please answer the follow questions to complete your registration.')

userfirstname = input('Please enter your FIRST name.\n')
print('Thank you, %s' % userfirstname, )

userlastname = input('Please enter your LAST name.\n')
print('Thank you %s' % userfirstname, '%s' % userlastname)

userage = input('Please enter your AGE.\n')
print('Thank you %s' % userfirstname, '%s' % userlastname)

username = input('Please enter your desired USER NAME.\n')
print('Thank you %s' % userfirstname, '%s' % userlastname, 'Your chosen username is %s' %     username)
#Within this section, a database scan must take place to check current used usernames and text  match

userpwd = getpass.getpass('Please enter your desired PASSWORD.\n')
print('Thank you %s' % userfirstname, '%s' % userlastname)
#within this section, a application password authentication must take place to fit password criteria

usermailid = input('Please enter the email address you wish to register with.\n')
print('Your chosen registration email address is: %s' % usermailid)
#Within this section, a database scan must take place to check current registered mail addresses    and text match



#User Database .xlsx
workbook =xlsxwriter.Workbook('UserDatabase.xlsx')
worksheet = workbook.add_worksheet()

worksheet.set_column('A:A',20)
worksheet.set_column('B:B',20)
worksheet.set_column('C:C',20)
worksheet.set_column('D:D',20)
worksheet.set_column('E:E',20)
worksheet.set_column('F:F',20)

worksheet.write('A1', 'First Name')
worksheet.write('B1', 'Last Name')
worksheet.write('C1', 'Age')
worksheet.write('D1', 'User Name')
worksheet.write('E1', 'Password')
worksheet.write('F1', 'Email ID')

worksheet.write_string(  , userfirstname)
worksheet.write_string('', userlastname)
worksheet.write_string('', userage)
worksheet.write_string('', username)
worksheet.write_string('', userpwd)
worksheet.write_string('', usermailid)


workbook.close()

So from here, a lovely .xlsx is created with the column headers and the user data does to the second row which is great but in the worksheet.write_string( section I need to know how to specify how to write the string to a new row or maybe some conditional formatting to say 'if the row already contains text then write to the next free row' etc.

If you know a better tool for this rather than xlsxwriter, feel free to let me know!

Thank you :)

Halation

Relevant: https://mcmap.net/q/698649/-is-it-possible-to-read-data-from-an-excel-sheet-in-python-using-xlsxwriter-if-so-how – Archaeology

Thank you, but I saw this, I don't think it is the same situation as they wanted their code to read a previous created .xlsx where as this is something that would be specified on the creation of the file. – Halation

But the point is that XLSXWriter is write-only so you can't read an existing file or (AFAIK) read from a cell to determine whether it's empty. – Archaeology

Ah I see, so xlsxwriter can only write data to a file if you specify the actual cell ID? I suppose there is a better way to go about this than using this tool then. – Halation

Possibly, that answer links to a page on their website suggesting other tools. – Archaeology

Ah right, thank you for your response, I will take a look at the link on that page and close this question in a few minutes. – Halation

Answer 1

It is possible, and generally more useful to use (row, col) notation in the write_ methods.

Something like this:

row = 0
col = 0

worksheet.write(row, col,     'First Name')
worksheet.write(row, col + 1, 'Last Name')
worksheet.write(row, col + 2, 'Age')
worksheet.write(row, col + 3, 'User Name')
worksheet.write(row, col + 4, 'Password')
worksheet.write(row, col + 5, 'Email ID')

row += 1
worksheet.write_string(row, col,     userfirstname)
worksheet.write_string(row, col + 1, userlastname)
worksheet.write_string(row, col + 2, userage)
worksheet.write_string(row, col + 3, username)
worksheet.write_string(row, col + 4, userpwd)
worksheet.write_string(row, col + 5, usermailid)

See the Working with Cell Notation section of the documentation.

Peers

Answer 2

A better way to do this would be using write_row.

Your code would be reduced to:

row = 0
col = 0

rowHeaders = ['First Name','Last Name','Age','User Name','Password','Email ID']
rowValues = [userfirstname,userlastname,userage,username,userpwd,usermailid ]

worksheet.write_row(row, col,  tuple(rowHeaders))
row += 1
worksheet.write_row(row, col, tuple(rowValues))

Check link for more documentation: write_row

Ruebenrueda