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

8 ビュー (過去 30 日間)
Martin Caron
Martin Caron 2021 年 8 月 25 日
コメント済み: Martin Caron 2021 年 8 月 25 日
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 日
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 ExchangeSpreadsheets についてさらに検索

製品


リリース

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by