How to save data to existing excel file

33 views (last 30 days)
Happy PhD
Happy PhD on 7 Sep 2022
Edited: dpb on 15 Sep 2022
I am trying to save data in excel but I am having trouble with adding header to first row and then data beneath existing rows.
This is an example layout of how I want to save data. Its just some values I made to give an general idea.
Parts of my current code from an fucntion (remvoed most of the data columns because its quite a lot of variables I want to save):
% example of data to save
inData_header = ["dist" "lens_f"];
inData = [100 ;100];
worstData_header = ["alphaX" "alphaY"];
worstData = [1.5; 1.5];
timeStr = datestr(now, 'yymmdd_HHMM');
sheetName = 'sheet687';
% saves data in excel-file.
% --------------------------------
filename = 'testResult.xls';
if isfile(filename)
% File exists.
[~,~, raw] = xlsread(filename,sheetName);
lastRow = size(raw, 1);
else
% File does not exist.
lastRow = 1
cellReference = sprintf('A%d', lastRow);
tDataHead = {"date", inData_header,worstData_header}
xlswrite(filename, tDataHead,sheetName, cellReference);
end
nextRow = lastRow + 1;
cellReference = sprintf('A%d', nextRow);
tData = {timeStr, inData,worstData}
xlswrite(filename, tData,sheetName, cellReference);
I am not sure what "cellReference" does? Maybe thats the issue?
How do I create a new sheet1 if I would like to save the data in a new sheet?
Is xlswrite still valid? It seems like its not recommended.

Accepted Answer

dpb
dpb on 7 Sep 2022
OK, had a few more minutes -- as @Cris LaPierre (and I had also) suggests, it would be a somewhat simpler job to build the table in memory and the write it -- more effective, too, than writing into an Excel file record-by-record--that means opening and closing the file every time and the associated overhead(*) that entails.
BUT, the problem in your code that causes it to fail is in the construction of the tData vector -- when you write
timeStr = datestr(now, 'yymmdd_HHMM');
inData=[100;100];worstData=[1.5;1.5];
tData = {timeStr, inData',worstData'};
you end up with
(tData)
tData = 1×3 cell array
{'220907_1952'} {[100 100]} {[1.5000 1.5000]}
which is a 1x3 cell array, not the five values you're expecting and to match up with your aforementioned headers (which, btw, the code to write those into a new, freshly created Excel workbook worked just as expected and left just the one sheet with the new sheetname).
What you need there instead would be
tData=[{timeStr},num2cell([inData.',worstData.'])];
writecell(tData,excelfilename, 'Sheet',sheetname,'WriteMode','append')
This does, in fact, produce the expected result of adding the new record at the end of the existing table but as noted above is far less efficient and more trouble to build, besides, than the table.
(*) In the past I have tried updating Excel spreadsheets on a cell-by-cell basis and can affirm that as the number of cells grows and the for..end loop increases, the time required goes up exponentially and will, at some point actually hang/crash the ActiveX COM engine and MATLAB. This is NOT the way to design a code to update a spreadsheet--"there be dragons!".
  2 Comments
dpb
dpb on 15 Sep 2022
Edited: dpb on 15 Sep 2022
% creates a new sheet if it does not exist
try
sheets = sheetnames(excelfilename);
% if sheet does not exist create a new one
if ~any(strcmp(sheets,sheetName)) % any returns logical, testing explicit value is superfluous
e = actxserver('Excel.Application');
Add(e.Workbooks);
...
Don't have your Add function, but I'd guess that's what it's doing when Add(e.Workbooks); is executed.
All of this is unneeded, anyway, though...
writecell (and friends) will automagically create a new sheet in the workbook if the sheet named in the argument list doesn't exist and a new workbook of the given filename if the workbook itself doesn't exist.

Sign in to comment.

More Answers (3)

Cris LaPierre
Cris LaPierre on 7 Sep 2022
Try using readtable and writetable.
  2 Comments
Cris LaPierre
Cris LaPierre on 7 Sep 2022
There will likely need to be more changes to your code to be able to use these functions, the first being your data must be in a table. Here's an example.
% example of data to save
dist = 100;
lens_f = 100;
alphaX = 1.5;
alphaY = 1.5;
date = datetime('now','Format','yyMMdd_HHmm')
date = datetime
220907_1431
sheetName = 'sheet687';
% saves data in excel-file.
% --------------------------------
filename = 'testResult.xls';
if isfile(filename)
% File exists.
tData = readtable(filename,'Sheet',sheetName);
lastRow = height(raw)+1;
else
% File does not exist.
lastRow = 1;
tData = table();
end
tData = [tData; table(date,dist,lens_f,alphaX,alphaY)]
tData = 1×5 table
date dist lens_f alphaX alphaY ___________ ____ ______ ______ ______ 220907_1431 100 100 1.5 1.5
writetable(tData,filename,'Sheet',sheetName);

Sign in to comment.


dpb
dpb on 7 Sep 2022
Edited: dpb on 7 Sep 2022
You didn't define cellReference before trying to use it in the else clause if the file didn't exist. Must not have executed that path or it would have thrown an error on undefined variable.
If the workbook exists, then wouldn't have gone that way and gotten to the xlswrite call ok.
However, your trouble is in the way you defined the output data for xlswrite -- the doc includes the following note on the input matrix content -- "If A is a cell array containing something other than a scalar numeric or text, then xlswrite silently leaves the corresponding cell in the spreadsheet empty."
in
{timeStr, inData,worstData}
you've defined a cell which contains a string and two arrays so the above caveat will hold and nothing will have been written but you won't have gotten any indication of failure (the "silently" part above means no warning or error is generated). To use xlswrite you would need to do something like
{timeStr, num2cell([inData,worstData])}
to follow the required syntax for the input data.
To write a new sheet, replace the Worksheet name with a new name; xlswrite will create the new sheet automagically.
And, yes, xlsread/write have been (rightfully) deprecated with the introduction of the new writeXXX routines -- use those instead. For your case, writecell would be most appropriate.
  2 Comments
dpb
dpb on 7 Sep 2022
It would probably be simpler to convert your data into a table -- then the column variable names become the header -- and if you would build the table in memory and write when done the full table would be simpler.
I've never tried the 'append' option with spreadsheets; it would rely on the internal 'UsedRange' property of the sheet to have been updated in the previous write -- and how reliable that is I don't know.
We can't run your code to test to see if there's a missing logic error or not in the way it's being used that might explain the symptoms.
As for the last, Excel must have at least one sheet in a workbook; when you create a new workbook that's what the sheet will be named. There's not a builtin highlevel function in MATLAB that renames sheets so when you refer to another sheet name instead, 'Sheet 1' is still there and will be and remain empty. "That's just how Excel works." and has nothing to do with MATLAB, per se.
MATLAB is it's own program; the interface to Excel is a convenience provided by TMW and has what facilities they've chosen to implement so far -- it's not their job to build a full Excel interface to every possible interaction.

Sign in to comment.


Seth Furman
Seth Furman on 13 Sep 2022
I should mention that datestr is discouraged. Prefer datetime where possible.
For example,
dt = datetime("now","Format","yyMMdd_HHmm")
dt = datetime
220913_1305
string(dt)
ans = "220913_1305"

Products


Release

R2021b

Community Treasure Hunt

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

Start Hunting!