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

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

Answer 1

[Jeremy Hughes]

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

[Jeremy Hughes]

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]

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.