writetable does not replace file
조회 수: 97 (최근 30일)
이전 댓글 표시
Hi,
I just observed a somewhat confusing behaviour with the writetable function in matlab. Here is a simple code to explain it
t = T(1:10,:)
writetable(t, '/tmp/test.xlsx')
writetable(t, '/tmp/test1.xlsx')
t = t([2 4 1 3 5],:)
writetable(t, '/tmp/test.xlsx')
writetable(t, '/tmp/test2.xlsx')
now test2.xlsx is a table with 5 rows, as expected.
test.xlsx is a table with 10 rows, wheres the first 5 equal test2.xlsx and the last 5 equal test1.xlsx
I would expect test.xlsx should be the same as test2.xlsx. I had a misunderstanding in a collaboration with colleagues which was close to publish erroneous data.
I there a way to change this?
I'm using matlab on ubuntu 18.04 with libreoffice.
Thanks for any help
Dom
댓글 수: 2
Stephen23
2019년 1월 7일
편집: Stephen23
2019년 1월 7일
"...table.xlsx is a table..."
Your code does not write any file named table.xlsx
"table.xlsx is a table with 10 rows, wheres the first 5 equal test2.xlsx and the last 5 equal test1.xlsx"
As far as I can tell test2.xlsx will have five rows and test2.xlsx will have ten rows. How do you expect ten rows plus five rows to equal ten rows?
"I would expect table.xlsx should be the same as table2.xlsx"
Your code does not write any file named table2.xlsx
Please ensure your question is consistent so that we can understand what you are doing and what you expect to happen.
채택된 답변
Stephen23
2019년 1월 7일
편집: Stephen23
2019년 1월 7일
According to the writetable help, "If filename is the name of an existing spreadsheet file, then writetable writes a table to the specified location, but does not overwrite any values outside that range", and that is exactly what you are observing:
- write ten values to a (new?) spreadsheet.
- write five values to a (now already existing) spreadsheet. According to the documentation this will add those five values to the default location, as in your code and leave all other existing values unchanged.
- thus you still see five of the values from step 1. (the ones not overwritten by step 2.), and five values from step 2..
So far it seems to be behaving as described in the documentation.
While this might be useful in some situations clearly in other cases (like yours) it would be useful to delete any existing content before writing the new content, but there does not currently seem to be an option for this. I suggest that you make an enhancement request (with a link to this thread), as I am sure that others would also find this a useful option.
댓글 수: 3
Philip Borghesani
2019년 1월 7일
(this comment double posted in both answers)
Interesting, I entered a bug report for the documentation about this, I think it should be spelled out more explicity in the help as well as being bolded in the doc.
The enhancment to add an overwite mode exists already in our system. Feel free to contact support and add a bit more push for an enhancment and I will push a bit.
Antoine Skaf
2020년 10월 14일
FYI, you can set the 'WriteMode' option to 'overwritesheet' for WRITETABLE when writing to an Excel file as of R2020a. From the Doc:
'overwritesheet' — Clear the specified sheet and write the input data to the cleared sheet.
- If you do not specify a sheet, then the writing function clears the first sheet and writes the input data to it.
추가 답변 (1개)
Dominik
2019년 1월 7일
편집: Dominik
2019년 1월 7일
댓글 수: 9
Les Beckham
2022년 2월 1일
I agree with Walter's comments. However, I agree with OP and with Ron Fredricks that the message displayed by uiputfile() is misleading/confusing.
Perhaps if the message was "do you want to select this existing file" instead of "do you want to replace this file" it would make more sense, since what uiputfile() does is just select a file and return its name. It never "replaces" any file.
In fact, the name of this function is, in itself, pretty misleading as it doesn't ever "put" any file anywhere.
Obviously this is confusing and should be clarified either in the documentation or by changing the message displayed by the function itself.
My 2 cents.
Ron Fredericks
2022년 2월 2일
Thank you all for looking into my uiputfile with excel's writematrix (or writetable) function. I have taken note of Walter's comments and rewrote my code to avoid this issue. Yet I also agree with Les that the wording (and even the name itsefl) for uiputfile needs a little work to avoid confusion for new users.
My updated code...
investigator = 'dummy value for this test code';
filter = {'*.xlsx'};
[filename,filepath] = uiputfile(filter);
if filename==0
% User aborted or canceled uiputfile, so just return without warning
xWarning = '';
return
end
% Test for requested file to save already exists and is currently open.
if isfile(filename)
[fid, ~] = fopen([filepath filename],'a');
if fid==-1
% Issue warning to user that excel file to be replaced is open,
% and therefore can not be replaced.
xWarning = filename;
return
else
xWarning = '';
fclose(fid);
end
end
lineNum = 1;
infoCol = 'B';
% First use of writematrix includes request to replace file if it
% exists.
writematrix(investigator,[filepath filename],'Sheet',1, ...
'Range',[infoCol num2str(lineNum)],'WriteMode', 'replacefile')
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!