Save data to Excel without overlapping

A, a 3x2 variables that store 3 points detected.

B, variable to store angle of that 3 points.

A and B is in a for loop, since I detect many frames (around 400 frames).

How can I store A and B for all frames in two different excel files.

Excel filename for A: Coordinates.xlsx, B: Angle.xlsx

Answer 1

[Sindar]

Check out writematrix: https://www.mathworks.com/help/matlab/ref/writematrix.html#mw_f36f6f84-e6bd-4749-8957-a88b07036116

Something like this should work:

writematrix(A,'Coordinates.xlsx','WriteMode','append')
writematrix(B,'Angle.xlsx','WriteMode','append')

[Teo Chou Wen]

Thanks for your reply.

But the data will overwrite, and only save the last group of data but not data for all frame

[Sindar]

Did you try it, that's the point of the append option: "Append an array of data below existing data in a spreadsheet." https://www.mathworks.com/help/matlab/ref/writematrix.html#mw_f36f6f84-e6bd-4749-8957-a88b07036116

[Teo Chou Wen]

Yes, I used the append function. Below show the output

Error using writematrix (line 156)

Unable to determine range. Range must be of the form 'A1' (cell), 'A:B' (column-select), '1:5' (row-select), 'A1:B5'

(rectangle-select), or a valid named range in the sheet.

But when I add the range, below show

Error using writematrix (line 156)

'Range' is not supported with 'WriteMode' 'append'.

[Sindar]

What Matlab version are you using and can you include your code? This minimal example works fine for me:

for ind=1:10
    A=rand(3,2);
    B=rand(3,1);
    writematrix(A,'Coordinates.xlsx','WriteMode','append')
    writematrix(B,'Angle.xlsx','WriteMode','append')
end

[Teo Chou Wen]

I just updated to R2020a. Below is the complete code for my proposed system. Thanks for your help.

video=VideoReader('BikeFit1Ori.mp4'); %read video %'BikeFit1.mp4' can be change as users need.
nFrame=video.NumFrames; %number of frame in the video

for img=1:nFrame
    frames=read(video,img); %read every frames in video
    imshow(frames); %show the frames of vidoe
 
    myVideo=VideoWriter('AfterDetected'); %play all frames with result
    myVideo.FrameRate=10; %play all frames with result
    open(myVideo) %play all frames with result    
    
    %detect red areas
    diff_im=imsubtract(frames(:,:,1), rgb2gray(frames)); %extracting the Red color from grayscale image
    diff_im=im2bw(diff_im,0.18); %converting grayscale image into binary image
    diff_im=bwareafilt(diff_im,[1000 2200]); %retaining only those objects with areas between 1000 and 2200
    diff_im=bwareafilt(diff_im,3); %retaining only the 3 objects with the largest areas
    bw=bwlabel(diff_im,8); %create the label matrix using 8-connected objects
    stats=regionprops(bw, 'BoundingBox', 'Centroid'); %draw rectangular boxes around the red object detected & label image
    
    AllCentroids=vertcat(stats.Centroid); %save Centroid detected into array
    writematrix(AllCentroids,'Coordinates.xlsx','WriteMode','append')

    hold on
    for object=1:length(stats)
        %mark the red areas
        bb=stats(object).BoundingBox;
        bc=stats(object).Centroid;
        rectangle('Position',bb,'EdgeColor','b','LineWidth',2)
        plot(bc(1),bc(2),'-m')
        a=text(bc(1)+15,bc(2),strcat('(X: ',num2str(round(bc(1))),'  Y: ',num2str(round(bc(2))),')'));
        set(a,'FontName','Arial','FontWeight','bold','FontSize',12,'Color','white');
        
        %draw line
        line1=plot([AllCentroids(1,1),AllCentroids(end,1)],[AllCentroids(1,end),AllCentroids(end,end)],...
            'Color','k','LineWidth',2);
        line2=plot([AllCentroids(end,1),AllCentroids(2,1)],[AllCentroids(end,end),AllCentroids(2,end)],...
            'Color','k','LineWidth',2);
        
        %calculate and plot angle
        theta1=atan((AllCentroids(1,end)-AllCentroids(end,end))/(AllCentroids(1,1)-AllCentroids(end,1)));
        theta2=atan((AllCentroids(end,end)-AllCentroids(2,end))/(AllCentroids(end,1)-AllCentroids(2,1)));
        theta=(abs((theta1-theta2)*(180/pi)));
        writematrix(theta,'Angle.xlsx','WriteMode','append')
        
        %set the position of Angle display
        p=10.0;
        q=15.0;
        t=text(theta,theta,strcat('Angle: ',[sprintf('%1.2f',theta),'{\circ}']),'FontName', 'Arial',...
            'FontWeight', 'bold', 'FontSize', 12, 'Color', 'white');
        set(t,'Position',[p q 0]);
    end
    hold off
    
    pause(0.01) %play all frames with result
    frames2=getframe(gcf); %play all frames with result
    writeVideo(myVideo,frames2); %play all frames with result
    close(myVideo) %play all frames with result
end

[Sindar]

Ah, the issue seems to be with using append mode to print scalars. I might actually submit a bug report about this. Here's my observations:

• "appending" a scalar to a file that doesn't exist creates the file and fills A1
• "appending" a scalar to a file with A1 and another A[n] cell filled will fill A[n+1]
• "appending" a scalar to a file with only A1 filled throws the error

So, with that in mind, here's a method to get around it: replace

writematrix(theta,'Angle.xlsx','WriteMode','append')

with

if img==1 && object==2
    writematrix(theta,'Angle.xlsx','Range','A2')
else
    writematrix(theta,'Angle.xlsx','WriteMode','append')
end

or, if you aren't sure the file will be empty at the start:

try
    writematrix(theta,'Angle.xlsx','WriteMode','append')
catch
    writematrix(theta,'Angle.xlsx','Range','A2')
end

[Teo Chou Wen]

The first method does not work.

Error show is may not have write permission or the file may be open. *I didn't open any excel file.

The second able to work, but it will duplicate a lot of data (as I have 370 frames, so the number of data for angle should be 370, but it save more than 370, which is around 1000). I will continue try with this. If you have any idea to solve the duplicate data, may share with me too.

Thank you for your reply. Really glad for your help.

[Sindar]

whoops, typo in the first solution, corrected to be object==2

[Sindar]

Looking back at it, I realize that you probably want three thetas to a row. In that case, try this:

thetas=nan(1,length(stats));
for object=1:length(stats)
        ...
        theta2=atan((AllCentroids(end,end)-AllCentroids(2,end))/(AllCentroids(end,1)-AllCentroids(2,1)));
        theta=(abs((theta1-theta2)*(180/pi)));
        
        
        %set the position of Angle display
        p=10.0;
        q=15.0;
        t=text(theta,theta,strcat('Angle: ',[sprintf('%1.2f',theta),'{\circ}']),'FontName', 'Arial',...
            'FontWeight', 'bold', 'FontSize', 12, 'Color', 'white');
        set(t,'Position',[p q 0]);
        thetas(object) = theta;
end
writematrix(thetas,'Angle.xlsx','WriteMode','append')

[Teo Chou Wen]

Thanks. The code work well as I need.