How to save data to existing excel file

74 ビュー (過去 30 日間)
Happy PhD
Happy PhD 2022 年 9 月 7 日
編集済み: dpb 2022 年 9 月 15 日
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.

採用された回答

dpb
dpb 2022 年 9 月 7 日
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 件のコメント
Happy PhD
Happy PhD 2022 年 9 月 15 日
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
dpb 2022 年 9 月 15 日
編集済み: dpb 2022 年 9 月 15 日
% 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.

サインインしてコメントする。

その他の回答 (3 件)

Cris LaPierre
Cris LaPierre 2022 年 9 月 7 日
Try using readtable and writetable.
  2 件のコメント
Happy PhD
Happy PhD 2022 年 9 月 7 日
編集済み: Happy PhD 2022 年 9 月 7 日
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
Cris LaPierre 2022 年 9 月 7 日
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);

サインインしてコメントする。


dpb
dpb 2022 年 9 月 7 日
編集済み: dpb 2022 年 9 月 7 日
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 件のコメント
Happy PhD
Happy PhD 2022 年 9 月 7 日
編集済み: Happy PhD 2022 年 9 月 7 日
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
dpb 2022 年 9 月 7 日
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.

サインインしてコメントする。


Seth Furman
Seth Furman 2022 年 9 月 13 日
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"
  1 件のコメント
Happy PhD
Happy PhD 2022 年 9 月 15 日
Ok, thank you!

サインインしてコメントする。

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

製品


リリース

R2021b

Community Treasure Hunt

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

Start Hunting!

Translated by