Here is the table output attached as screenshot On Tue, Nov 24, 2015 at 3:19 PM, Anthony <[email protected]> wrote:
> 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 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.
