Thank you very much for explaining everything to me. It makes a lot more sense doing it that way, however I will have to read up on StringIO since I have not used that before. I commented out my functions and replaced it with what you have suggested and there are no errors, however nothing shows up in the uploads field when I view the database. It is empty, I am not sure if it is because I am missing something in regards to downloading the file.
Before I was able to view the excel form being changed in the background and I would open the new excel file and the user inputs would be in the correct cells, but now the files are blank. I am confused if it may have something to do with saving it as a virtual workbook? Since there are no errors, I think it at least indicates that it is opening the excel file and writing to it, I just cannot find where it might be storing the file afterwards since it shows up empty. I also was not aware that I can create functions like so within the models file, that is very convenient. On Tue, Nov 24, 2015 at 12:43 PM, Anthony <[email protected]> wrote: > There is no need to store the data in the session and then redirect to > another action to create the file. Instead, just do it all at once. Also, > if you want to store the file (safely) and make it easily retrievable, then > add an upload field to the database table and store it that way. Finally, > if you want to associated files with their creators, just add a reference > field to the excelform table to reference the auth_user record of the > currently logged in user. This can all be simplified as follows: > > In a model file: > > def create_excel(row): > from openpyxl import load_workbook > from openpyxl.writer.excel import save_virtual_workbook > from cStringIO import StringIO > wb = load_workbook(filename= > '/home/../Documents/web2py/applications/../static/excel.xlsx') > sheet_ranges = wb['Sheet1'] > sheet_ranges['C4'] = row.last_name > sheet_ranges['C6'] = row.first_name > sheet_ranges['C8'] = row.age > sheet_ranges['C10'] = row.location > excel_file = StringIO() > excel_file.write(save_virtual_workbook(wb)) > excel_file.seek(0) > return db.excelform.excel_file.store(excel_file, 'spreadsheet.xlsx') > > db.define_table('excelform', > Field('last_name', 'string', requires=IS_NOT_EMPTY()), > Field('first_name', 'string', requires=IS_NOT_EMPTY()), > Field('age', 'string', requires=IS_NOT_EMPTY()), > Field('location', 'string', requires=IS_NOT_EMPTY()), > Field('excel_file', 'upload', compute=create_excel), > Field('created_by', 'reference auth_user', > default=auth.user_id, readable=False, writable=False)) > > The above adds the excel_file upload field to your model and defines it as > a computed field. When an insert is made, the create_excel function is > called. It creates the workbook, but instead of using wb.save() to create > the file directly, it uses save_virtual_workbook to convert the workbook to > a string. It then writes the string to a StringIO object, which is > ultimately passed to the .store() method of the upload field. The .store() > method creates the file and returns the filename that web2py created for > it, which is ultimately stored in the upload field itself. The second > argument to .store() is the filename you will see when you download the > file (but no the filename used to actually store the file on the > filesystem). > > The created_by field is a reference to auth_user, with the default set to > the ID of the current user (it is not readable or writable, so will not > appear in the form). > > Then, in the controller: > > @auth.requires_login() > def excelform(): > record = db.excelform(request.args(0)) > form = SQLFORM(db.excelform, record, > message_onsuccess='Thanks! The form has been > submitted.', > message_onfailure='Please correct the error(s).'). > process() > return dict(form=form) > > The controller is now quite simple -- it just creates and processes the > form -- the rest of the logic is handled in the model. Note, assuming being > a registered user is required, you should use the @auth.requires_login() > decorator here. > > If you want to show only the files of the current user, you can do: > > grid = SQLFORM.grid(db.excelform.created_by == auth.user_id) > > Anthony > > -- > 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 a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/web2py/B4H1Q6jV9S4/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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.
