How do I save each response in an excel file every time I click the submit button?

It currently only saves the recent response and not the previous response as well.

function SubmitButtonPushed(app, event)
               
                data = {firstName, lastName, dob, email, nationality, sex, mobileNumber, type};

                passengerData = cell2table(data, 'VariableNames', {'First Name', 'Last Name', 'Date of Birth', 'Email', 'Nationality', 'Sex', 'Mobile Number', 'Type'});
                disp('New Passenger Data: ') 
                disp(passengerData)
                filename = 'passengerDetails.xlsx';
                if isfile(filename)
                    existingData = readtable(filename);
                    combinedData = [existingData; passengerData];
                else
                    combinedData = passengerData;
                end
                writetable(combinedData,filename, 'WriteMode','append');
                disp('Combined Data: ')
                disp(combinedData)
               
                new_line = randn(1,9);
                sheetName = sprintf('Submission_%d', submissionCount);
                writematrix(new_line, filename, 'Sheet', sheetName, 'WriteMode', 'overwrite');
                submissionCount = submissionCount + 1; 
                
                msgbox('Successfully submitted','Success');
                delete(app);

[dpb]

With the line

writematrix(new_line, filename, 'Sheet', sheetName, 'WriteMode', 'overwrite');

of course you're only going to see the last value; you specifically told it to overwrite whatever else was already there, if anything.

Change to

writematrix(new_line, filename, 'Sheet', sheetName, 'WriteMode', 'append');

It would be more efficient to save the responses in memory and then write all when the cycle is complete; at that point the 'overwrite' option might be the correct choice.

But, if it is a user-driven environment so that time is not of any real concern, then the "one-line-at-a-time" paradigm probably won't be noticeably different in performance than the grouped, single write. I have had issues with calls to one of the writexxxx family in type loops eventually hanging/crashing Excel with a very large number of open/write/close cycles in a tight loop so I would recommend to avoid trying something of that sort.

Answer 1

[Ronit]

Hello Ysabella,

I understand you are trying to add the matrix data every time the submit button is pushed to a new sheet named ‘submission_1’, ‘submission_2’, etc. To achieve this, make sure ‘submissionCount’ is properly managed within the application's scope or persistence if you want it to persist across sessions. As of now, the ‘submissionCount’ is empty and the sheet name is taken as ‘submissionCount_’ for every session. By defining it as a ‘persistent’ variable, the issue can be resolved.

Following is the code that demonstrates the above approach:

persistent submissionCount;
    if isempty(submissionCount)
        submissionCount = 1;
    else
        submissionCount = submissionCount + 1;
    end

Please refer to the documentation of ‘persistent’ for more details: https://www.mathworks.com/help/matlab/ref/persistent.html

You can also update the following part of the code:

if isfile(filename)
    existingData = readtable(filename, 'VariableNamingRule', 'preserve');
    combinedData = [existingData; passengerData];
else
    combinedData = passengerData;
end

writetable(combinedData, filename);

By writing the ‘combinedData’ back to the file without specifying 'WriteMode', 'append', you effectively overwrite the entire sheet with the updated data, which already includes the new and existing entries. This ensures that the sheet always reflects the current state of your data collection.

I hope it helps you query!