Why are there a few bytes added every time I call writetable?

1 次查看（过去 30 天）
Martin Caron
Martin Caron 2021-8-25
评论： Martin Caron 2021-8-25
Ran in:
I have some tests that require updating some fields of an excel file, but every time I save it, it adds a few bytes of data. Opening the file with microsoft excel doesn't show any additional data and saving it (without doing anything else) purges all the extra bytes added. This makes me think that maybe writetable adds some metadata.
My main issue there, is that after a bunch of cycles the file gets corrupted and the code crashes when attempting to re-load the .xlsx file. Running this on my computer results in about 6 bytes added, on another one it is about 19, while in this description code, it adds about 3 bytes.
filename = "test.xlsx";
% Create a table with dummy data
ta = array2table(rand(3,3));
writetable(ta,filename,'WriteVariableNames',false);
%% Do many xlsread/writetable cycles
N = 100;
fileLength(N) = 0;
figure();
al = animatedline('linestyle','-','marker','.','color','k');
xlabel('Save cycle')
ylabel('File size [bytes]')
for i = 1:N
% read file (to a cell array)
[~, ~, data] = xlsread(filename, 1, '');
data(cellfun(@(x) ~isempty(x) && isnumeric(x) && isnan(x),data)) = {''};
% Convert to table and writetable
ta = data;
writetable(table(ta),filename,'WriteVariableNames',false);
pause(0.01);
% Check the file size (I was using .NET's fileinfo, but had to switch
% to matlab's dir for this code example to run in the browser.
% temp = System.IO.FileInfo(filename);
% fileLength(i) = temp.Length;
temp = dir(filename);
fileLength(i) = temp.bytes;
addpoints(al, i, fileLength(i));
end
%% Polyfit to get the increase in file size
x = 1:i-1;
y = fileLength(1:i-1);
p = polyfit(x,y,1);
fprintf("File increases by %.1f bytes on average\n",p(1));
File increases by 3.1 bytes on average

请先登录，再回答此问题。

采纳的回答

Jeremy Hughes
Jeremy Hughes 2021-8-25
编辑：Jeremy Hughes 2021-8-25
Ran in:
writetable write "into" a sheet by default. And there's some meta-data being added, but I'm not sure if it's MATLAB, or the excel format. I used the "replacefile" option, and that made the graph flat.... but that might not be what you want to use in general. I don't know if you intend to write in-place, or not.
If your real work requires the file to be written in place to different sheets, this might not resolve the issue.
I'll look into it some more. (Edited for code brevity)
filename = "test.xlsx";
% Create a table with dummy data
ta = array2table(rand(3,3));
writetable(ta,filename,'WriteVariableNames',false);
%% Do many xlsread/writetable cycles
N = 100;
fileLength(N) = 0;
figure();
al = animatedline('linestyle','-','marker','.','color','k');
xlabel('Save cycle')
ylabel('File size [bytes]')
for i = 1:N
% read file (to a cell array)
data = readtable(filename);
% Convert to table and writetable
writetable(data,filename,'WriteVariableNames',false,"WriteMode","replacefile");
temp = dir(filename);
fileLength(i) = temp.bytes;
addpoints(al, i, fileLength(i));
end
  2 个评论
Jeremy Hughes
Jeremy Hughes 2021-8-25
After looking at what's going on, it seems that there's an internal number style with a "border" being added each time. (it's the same one each time)
I'll report this behavior.
If you're on windows, you can bypass this by adding "UseExcel",true. The file size doesn't remain constant, but it doesn't grow monotonically.
Martin Caron
Martin Caron 2021-8-25
Thanks! I tried using the "replacefile" option, but it seems to be unavailable in my version of matlab. In the mean time, I will switch to the "UseExcel". Even though it adds coupling to an external software, we tend to have it installed on every computer where I work.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Introduction to Installation and Licensing 的更多信息

标签

产品


版本

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by