Write to specific Excel cell from MATLAB

0
votes

I want to avoid writing over existing cells when writing data to an Excel spreadsheet through MATLAB.

I need to save a 1X2 array of experiment results into Excel using MATLAB. Each time I run the script, MATLAB overwrites the results to cell A1 in Excel. I need to stop this from happening, and have MATLAB find the next free row in Excel and write to it.

My approach is to first read and store the spreadsheet as an array. Calculate the size of array, figure out the next free cell and write to it to avoid overwrites. However I can't seem to find how to write to a specific cell from MATLAB.

Any suggestions on either how to write to a specific cell or avoid overwrites altogether using simple code?

Sample Code:

num = xlsread('var_data.xlsx');
row_no = size(num,1);
xlswrite('var_data.xlsx',var_dat,'**ideally row_no**')
2
excelmatlab
Requirements aren't clear enough, could you explain with a sample? - Divakar
@Divakar I need to save a 1X2 array of experiment results into Excel using MATLAB. Each time I run the script, MATLAB overwrites the results to cell A1 in Excel. I need to stop this from happening, and have MATLAB find the next free row in Excel and write to it. Does that make sense? - user2550888
So ultimately you are doing vertical concatenation in the excel sheet and it becomes Nx2 array in the sheet? BY free you mean there must be 1x2 cells free? 1X2 array would need a row of two cells in the excel sheet right? - Divakar
Exactly, but each time I run my scripts in MATLAB, I need to clear all data so I can't do the concatenation in MATLAB hence I'm exporting to Excel. By free I mean the next empty 1X2 cells below the current row so that the concatenation won't replace any existing data. - user2550888
Okay, so to my knowledge appending isn't possible when writing to excel sheet with MATLAB, but if you are okay with CSV files, that might be possible. - Divakar

2 Answers

0
votes

This may be helpful.

fname='Book1.xlsx';
sname='Sheet1';
startingColumn='A';  %change if you want a different column
newData=[10 11];     %this is for test only

[~,~,Data]=xlsread(fname,sname);  %read in the old data, text and all
nextRow=size(Data,1)+1;           %get the row number of the end
range=sprintf('%s%d',startingColumn,nextRow);  %this tells excel where to stick it
xlswrite(fname,newData,sname,range)  % write the new data after the old data

You can do this without excel, if you want. Use the matlab save and load command, and concatenate the data. A code fragment would look like

load oldDataFile  %mat file containging variable oldData
oldData=[oldData;newData]; %update the data record
save oldDataFile oldData   %save the concatenated record
0
votes

Code

%%// Filenames
temp_csvfile = 'temp1.txt';
final_excelfile = 'final_data.xls';

for k = 1:4 %// No. of steps

    %// Generate data to be written to CSV/Excel files (random data for now)
    a1 = num2cell(20.*rand(1,2));

    %%// Temporarily store data into a CSV file
    fid = fopen(temp_csvfile,'a');
    fprintf(fid, '%f,%f\n',a1{:} );
    fclose(fid);

    %%// Verify the CSV file contents
    type(temp_csvfile)

end

%%// Final stage of storing into excel file
fid = fopen(temp_csvfile,'r');
all_data = textscan(fid, '%f,%f');
fclose(fid);
xlswrite(final_excelfile,num2cell(cell2mat(all_data)))

Output (growing rows)

12.982309,14.634448

12.982309,14.634448
12.954919,9.018474

12.982309,14.634448
12.954919,9.018474
10.940178,5.926416

12.982309,14.634448
12.954919,9.018474
10.940178,5.926416
14.893856,3.779100