I'm new to Python so I hope this sounds right. How could I use Python to write to an Excel file from user input? I want my script to ask users "Name:" "Job Title:" "Building Number:" "Date:" etc. and from that raw input, fill in the corresponding columns one after the other in an Excel spreadsheet. I don't want future use of the script to overwrite previous data in the sheet either. I'd like each time to create a new line in the spreadsheet and then fill in the correct entries in each row. I hope that makes sense. Thank you so much in advance for your help.
5
-
xlsxwriter.readthedocs.org You can create spreadsheed files using this module. Basicly you can use raw_input to collect data from user.mnrl– mnrl2015-08-27 21:52:52 +00:00Commented Aug 27, 2015 at 21:52
-
2Another route is to write to a csv file which Excel can read. That way you can use the csv library shipped with Python - note however that this is purely a data format, you won't be able to do pretty colours/borders/ etc. with csvTim Wakeham– Tim Wakeham2015-08-27 21:55:40 +00:00Commented Aug 27, 2015 at 21:55
-
@Tim Wakeham love your idea about the csv file. Any more details on how to do this? It doesn't have to be pretty just legible with the required fields and able to append future inputs.kittenparade– kittenparade2015-08-27 22:13:42 +00:00Commented Aug 27, 2015 at 22:13
-
@pbnjenni it's very easy. The docs are docs.python.org/2/library/csv.html?highlight=csv#module-csv the examples are illustrative.Tim Wakeham– Tim Wakeham2015-08-27 22:16:39 +00:00Commented Aug 27, 2015 at 22:16
-
@Tim Wakeham going to try and figure out how to get it to do specifically what I'd likekittenparade– kittenparade2015-08-27 22:24:31 +00:00Commented Aug 27, 2015 at 22:24
Add a comment
|
3 Answers
You could use openpyxl to write to the workbook. Here's some basic usage, and should help avoid overwriting:
import openpyxl
wb = openpyxl.load_workbook('C:/test.xlsx')
ws = wb.active
i = 0
cell_val = ''
# Finds which row is blank first
while cell_val != '':
cell_val = ws['A' + i].value
i += 1
# Modify Sheet, Starting With Row i
wb.save('C:/test.xlsx')
Hope This Helps.
Edited, getting input and time:
For getting information from the user, use
x = input('Prompt: ')
However, if you want the actual current, I suggest using the time module:
>>> from time import strftime
>>> date = strftime('%m-%d-%y')
>>> time = strftime('%I:%M%p')
>>> print(date)
08-28-15
>>> print(time)
01:57AM
2 Comments
kittenparade
How do I ask for user input for specific fields with this? i.e. date, time
SuperNova
@pbnjenni I updated my answer, hope this works for you.
I will also add that XlsxWriter is also an excellent library for writing to Excel, however, unlike OpenPyXl, it is only a writer and does not read Excel files.
An example found from their documentation is as follows:
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses01.xlsx')
worksheet = workbook.add_worksheet()
# Some data we want to write to the worksheet.
expenses = (
['Rent', 1000],
['Gas', 100],
['Food', 300],
['Gym', 50],
)
# Start from the first cell. Rows and columns are zero indexed.
row = 0
col = 0
# Iterate over the data and write it out row by row.
for item, cost in (expenses):
worksheet.write(row, col, item)
worksheet.write(row, col + 1, cost)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')
workbook.close()
Comments
You may want to use the pandas module. It makes reading, writing, and manipulating Excel files very easy:
Pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.
1 Comment
Alex Huszagh
I wouldn't recommend using Pandas due to the limitations of their own Excel writer. Although the Excel writer is quite handy, unless the user is explicitly working with data already using Pandas, there will be significant overhead to load all the data into Pandas, then to export the data to XlsxWriter or OpenPyXl, and suffer from very limited possibilities with none of the extensibility of both modules. For example, OpenPyXl and XlsxWriter enable conditional formating, custom column formats, etc., which could potentially be of interest to the OP.
