Writing in an existing Excel file

82 views (last 30 days)
I am making a GUI with the AppDesigner in Matlab. The app needs to be able to run multiple times, each time the results need to be published in the same Excel file. Except, when running the app again, the data in the Excel is lost and only new data added to the file, even when manually saving the Excel file. These are my callbacks:
Does anyone know why it is not overwriting the existing Excel file?
% Callbacks that handle component events
methods (Access = private)
% Value changed function: SamplenumberEditField
function SamplenumberEditFieldValueChanged(app, event)
value = app.SamplenumberEditField.Value;
end
% Button pushed function: StartButton
function StartButtonPushed(app, event)
ind = app.SamplenumberEditField.Value;
filename = 'Format.xlsx';
table_starttimes = cell(100,1);
table_starttimes{ind} = datestr(now, 'HH:MM:SS');
xlswrite('Format.xlsx',table_starttimes,'Sheet1','F2')
end
% Button pushed function: SnapshotButton
function SnapshotButtonPushed(app, event)
im = imread('image.tif');
ind = app.SamplenumberEditField.Value;
filename = 'Format.xlsx';
table_sample = cell(100,1);
table_name = cell(100,1);
table_amount = cell(100,1);
table_conc = cell(100,1);
table_endtimes = cell(100,1);
table_volume = cell(100,1);
table_inside = cell(100,1);
table_outside = cell(100,1);
table_sample{ind} = ind;
table_name{ind} = 'name';
table_volume{ind} = 2;
table_inside{ind} = 2;
table_outside{ind} = 2;
%% Save endtime and concentration to excel file
amount=f_calculate()
binder_concentration=amount/2
table_amount{ind} = amount;
table_conc{ind} = binder_concentration;
table_endtimes{ind} = datestr(now,'HH:MM:SS');
xlswrite('Format.xlsx',table_name,'Sheet1','A2')
xlswrite('Format.xlsx',table_sample,'Sheet1','B2')
xlswrite('Format.xlsx',table_volume,'Sheet1','C2')
xlswrite('Format.xlsx',table_outside,'Sheet1','D2')
xlswrite('Format.xlsx',table_inside,'Sheet1','E2')
xlswrite('Format.xlsx',table_endtimes,'Sheet1','G2')
xlswrite('Format.xlsx',table_amount,'Sheet1','H2')
xlswrite('Format.xlsx',table_conc,'Sheet1','I2')
winopen('Format.xlsx')
end
end
  2 Comments
dpb
dpb on 28 Jul 2021
"when running the app again, the data in the Excel is lost and only new data added to the file, even when manually saving the Excel file. These are my callbacks:..."
"Does anyone know why it is not overwriting the existing Excel file?"
Those two statements are contradictory.
Please clarify which is the desired result -- and what, specifically is wrong are you seeing that isn't as desired?
It surely looks as though the above would insert all new data into the same file at the same locations each time it is run.
I would, however, strongly recommend to restructure your code to put the data into a column array and write the array once instead of calling xlswrite multiple times--this is a real bottleneck and unnecessary.
Also, use writematrix instead of the deprecated xlswrite
Giulia Pötgens
Giulia Pötgens on 29 Jul 2021
Thank you very much! I'm sorry for not clarifying my question well enough. I want all the data acquired when pressing buttons in the GUI to be printed in the same Excel file, but now every time a button is pressed the previously acquired data is lost and only the new data is visible.
I think it's indeed a good idea to save all data in a column array and then print it all at once in an Excel file, except I don't understand how to save variables in a callback, so I can use them in another callback. I want to make a button that allows you to put all data at once in an Excel file, but I need the variables from a previous button, but I can't seem to make it work. I've tried the guidata function, but I don't really understand how it works. Anyone tips?
Also I don't understand the writematrix function, how does it work?
function SnapshotButtonPushed(app, event)
im = imread('image.tif');
ind = app.SamplenumberEditField.Value
amount = f_calculate();
%% Save endtime and concentration
table_amount{ind} = amount;
table_conc{ind} = concentration;
table_endtimes{ind} = datestr(now,'HH:MM:SS');
guidata(hobject,table_amount);
function ButtonPushed2(app, event)
ind = app.SamplenumberEditField.Value;
table_amount=guidata(hobject)
xlswrite(filename,table_name,'Sheet1','A2')
xlswrite('Format.xlsx',table_sample,'Sheet1','B2')
xlswrite('Format.xlsx',table_volume,'Sheet1','C2')
xlswrite('Format.xlsx',table_outside,'Sheet1','D2')
xlswrite('Format.xlsx',table_inside,'Sheet1','E2')
xlswrite('Format.xlsx',table_endtimes,'Sheet1','G2')
xlswrite('Format.xlsx',table_amount,'Sheet1','H2')
xlswrite('Format.xlsx',table_conc,'Sheet1','I2')
winopen('Format.xlsx')

Sign in to comment.

Accepted Answer

Ananya Tewari
Ananya Tewari on 10 Aug 2021
Hi,
It is recommended to use writematrix instead of xlswrite. By default The writematrix function overwrites any existing file. So you need to set the WriteMode property as append to actually add data to a existing file and not overwriting it. Here is the example code for the same. Refer to this link for more information.
M1 = magic(5)
M2 = [5 10 15 20 25; 30 35 40 45 50]
writematrix(M1,'M.xls')
writematrix(M2,'M.xls','WriteMode','append')

More Answers (0)

Products


Release

R2020a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!