I have a form that opens a excel file template, all inputs in the form
correlate to a cell within the template. When the form is submitted, the
new excel file is saved for that user.
I am trying to find the best way to implement the following:
I want to open the existing uploaded file, and edit different cells within
it.
Basically, I want multiple tables for the same form, but I am not sure how
to append the table to the existing uploaded file.
Example:
Excel file consists of data regarding user and 3 days.
Table one is a form for basic user information.
Table 2 is a form for Day 1, user can click submit and then may be
redirected to Day 2 which is table 3, submit and is redirected to table 4
which is Day 3 and the final submit has one excel file with data
correlating to Day 1, Day 2 and Day 3 and the users information.
I eventually intend to store the data using a blob field, if that will be
possible.
Do I need to use sqlform.factory? Migrations?
I am stuck because depending on which day the user is submitting
information for, cal_calc will differ because it will input the information
in different cells, but within the same excel workbook.
I am having trouble finding documentation that helps most with this, I have
looked into pagination, is_in_db documentation, but nothing really
explaining how to reference something within an upload field in this way.
Anything will help, whether its redirecting me to documentation that will
help me achieve this or an example of some sort, if this is even something
possible to do.
Below I have what I am currently trying out (although I think it is
redundant because the same fields are required for each day, so having 3
different tables for the same information seems excessive) , but I am
unable to even test if it works because I am getting the error:
<type 'exceptions.NameError'>(name 'db' is not defined)
I don't understand why, my other model files with tables do not have this
issue.
*Model:*
def cal_calc(row):
from openpyxl import load_workbook
from openpyxl.writer.excel import save_virtual_workbook
from cStringIO import StringIO
import os
wb = load_workbook(filename= os.path.join(request.folder, 'static',
'calcalc.xlsx'))
cells = wb['week1']
cells['A1'] = row.first_name
cells['B1'] = row.last_name
cells['C1'] = row.age
cells['D1'] = row.thedate
cells['A2'] = row.first_meal1
cells['A3'] = row.first_calories1
cells['A4'] = row.second_meal1
cells['A5'] = row.second.calories1
cells['A6'] = row.third_meal1
cells['A7'] = row.third_calories1
cells['B2'] = row.first_meal2
cells['B3'] = row.first_calories2
cells['B4'] = row.second_meal2
cells['B5'] = row.second.calories2
cells['B6'] = row.third_meal2
cells['B7'] = row.third_calories2
cells['C2'] = row.first_meal3
cells['C3'] = row.first_calories3
cells['C4'] = row.second_meal3
cells['C5'] = row.second.calories3
cells['C6'] = row.third_meal3
cells['C7'] = row.third_calories3
excel_file = StringIO()
excel_file.write(save_virtual_workbook(wb))
excel_file.seek(0)
return db.userinfo1.cal_file.store(excel_file, 'calcalc.xlsx')
db.define_table('userinfo1',
Field('first_name', 'string'),
Field('last_name', 'string'),
Field('age', 'string'),
Field('thedate', 'string'),
Field('cal_file', 'upload', compute=cal_calc),
Field('created_by', 'reference auth_user',
default=auth.user_id, readable=False, writable=False))
db.define_table('day1',
Field('first_meal1', 'string'),
Field('first_calories1', 'string'),
Field('second_meal1', 'string'),
Field('second_calories1', 'string'),
Field('third_meal1', 'string'),
Field('third_calories1', 'string'),
Field('cal_file', 'upload', compute=cal_calc),
Field('created_by', 'reference auth_user',
default=auth.user_id, readable=False, writable=False))
db.define_table('day2',
Field('first_meal2', 'string'),
Field('first_calories2', 'string'),
Field('second_meal2', 'string'),
Field('second_calories2', 'string'),
Field('third_meal2', 'string'),
Field('third_calories2', 'string'),
Field('cal_file', 'upload', compute=cal_calc),
Field('created_by', 'reference auth_user',
default=auth.user_id, readable=False, writable=False))
db.define_table('day3',
Field('first_meal3', 'string'),
Field('first_calories3', 'string'),
Field('second_meal3', 'string'),
Field('second_calories3', 'string'),
Field('third_meal3', 'string'),
Field('third_calories3', 'string'),
Field('cal_file', 'upload', compute=cal_calc),
Field('created_by', 'reference auth_user',
default=auth.user_id, readable=False, writable=False))
*Controller: *
@auth.requires_login()
def userinfo1():
record = db.userinfo1(request.args(0))
form = SQLFORM(db.userinfo1, record)
if form.accepts(request.vars, session):
response.flash='Thanks, going to next page.'
redirect(URL(r=request, f='day1',vars=request.vars))
elif form.errors:
response.flash='Please correct errors!'
return dict(form=form)
def day1():
record = db.day1(request.args(0))
form = SQLFORM(db.day1, record)
if form.accepts(request.vars, session):
response.flash='Thanks, going to the next page.'
redirect(URL(r=request, f='day2',vars=request.vars))
elif form.errors:
response.flash='Please correct errors!'
return dict(form=form)
def day2():
record = db.day2(request.args(0))
form = SQLFORM(db.day2, record)
if form.accepts(request.vars, session):
response.flash='Thanks, going to the next page.'
redirect(URL(r=request, f='day3',vars=request.vars))
elif form.errors:
response.flash='Please correct errors!'
return dict(form=form)
def day3():
record = db.day3(request.args(0))
form = SQLFORM(db.day3, record)
if form.accepts(request.vars, session):
response.flash='Thanks! Form submitted.'
redirect(URL(r=request, f='calcalcrecords',vars=request.vars))
elif form.errors:
response.flash='Please correct errors!'
return dict(form=form)
def calcalcrecords():
grid = SQLFORM.grid(db.userinfo1.created_by == auth.user_id,
fields=[db.userinfo1.first_name,
db.userinfo1.last_name, db.userinfo1.cal_file])
return locals()
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
