How to save data to existing excel file

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.

Answer 1

[dpb]

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!".

[Happy PhD]

I did in the end use the writecell as you suggested and it works well.

Yes, I realise that it is not optimal. Unfortunately the measurement method used need to save the data per image analysed. In the end I will probably solve it with having Labview control everything and "store" the data for me. But with code for manual calculations in MATLAB this is the solution I am stuck with.

The only issue I have is that it opens an additional excel file "book1" when I create a new sheet within existing excel file.

% creates a new sheet if it does not exist
try
sheets = sheetnames(excelfilename);
sheetValid = any(strcmp(sheets,sheetName));

% if sheet does not exist create a new one
if sheetValid == 0
e = actxserver('Excel.Application');
Add(e.Workbooks);
e.Visible = 1;
eSheets = e.ActiveWorkbook.Sheets;
eSheet1 = Item(eSheets,1);
eNewSheet = Add(eSheets,sheetName,eSheet1);
Quit(e)
delete(e)
end % end if

Any idea why?

[dpb]

% 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.

Answer 2

[Cris LaPierre]

Try using readtable and writetable.

[Happy PhD]

I get the following error:

tDataHead =
1×3 table
Var1     inData_header    worstData_header
______    _____________    ________________
"date"     1×12 string        1×6 string   
Error using writetable (line 244)
Incorrect argument order. writetable expects a table as its first
argument and a filename as its second argument.
Error in fkn_saveLaserSafetyData (line 88)
writetable(excelfilename, tDataHead, sheetName, cellReference);

My code:

if isfile(excelfilename)
     % File exists.
     [~, ~, raw] = readtable(excelfilename,sheetName);
     lastRow = size(raw, 1);
else
     % File does not exist.
     lastRow = 1
     cellReference = sprintf('A%d', lastRow);
     tDataHead =  table("date", inData_header,worstData_header)
     writetable(excelfilename, tDataHead, sheetName, cellReference);
end

nextRow = lastRow + 1;
cellReference = sprintf('A%d', nextRow);
tData =  table(timeStr, inData',worstData');
writetable(excelfilename, tData, sheetName, cellReference);

I don't understand what the issue is.

The "var1" in the table is what i want to use the strings in the "tDataHead" since its a long line of data and the data should be below that row. "inData_header" and "worstData_header" are cell of strings.

The timeStr is just a string of today date and 'inData' and 'worstData' are both array of double values.

[Cris LaPierre]

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);

Answer 3

[dpb]

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.

[Happy PhD]

I manage to get the data after another in a sheet I choose, but the first row with variables names (created in my if-else) seem to be disappearing as I add the row with tData. I did check, it goes into my if-sats if its new and create a row with [ date dist lens_f X and Y ]. My current code is:

if isfile(excelfilename)
     % File exists.
else
     % File does not exist.
     tDataHead =  {"date" "dist" "lens_f" "X" "Y" };
     writecell(tDataHead,excelfilename, 'Sheet',sheetName,'WriteMode','append')
end

tData =  {timeStr, inData',worstData'};
writecell(tData,excelfilename,'Sheet',sheetName,'WriteMode','append')

I checked the code and it's when i pick another sheetname it doesn't create the header. Any idea how to create a new header if its a new sheet or/and new file?

It also creates an empty "Sheet1" when I add data into a sheet named "1".

[dpb]

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.

Answer 4

[Seth Furman]

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"

[Happy PhD]

Ok, thank you!