Works perfectly for me (the only difference is I don't have your template so am creating a workbook from scratch). How are you "viewing the database" and downloading the files?
Anthony On Tuesday, November 24, 2015 at 1:47:38 PM UTC-5, Aeta Gothno wrote: > > 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.
