How to write new data to the existing excel file?

Question

Alex Zai 2021-8-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1442304-how-to-write-new-data-to-the-existing-excel-file

评论： Simon Chan 2021-8-29

Respected All,

Hello everyone! I wanna add new data to the existing excel file from the last row of it. How can I do that please? I have tried and the error appeared "The number of table variables in an assignment must match.". And I opened that "Results.xlsx" file which is too small grid cells (attached photo).

Please someone suggest and help me. Thanks all.

Here example code:

Dtime = datetime('now','TimeZone','local','Format','d-MMM-y HH:mm:ss');
Name = {'Alex'};
DateAndTime = Dtime;
ConfidenceScore = 95;
if isfile('Results.xlsx')
    T1 = readtable('Results.xlsx');
    [rd, cd] = size(T1);
    T = table(Name, DateAndTime, ConfidenceScore);
    T1(rd+1, 1:cd-1) = T;
    writetable(T1,'Results.xlsx');
else
    T = table(Name, DateAndTime, ConfidenceScore);
    writetable(T,'Results.xlsx');
end

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Simon Chan 2021-8-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/1442304-how-to-write-new-data-to-the-existing-excel-file#answer_775944

在 MATLAB Online 中打开

You may replace the entire loop by using 'Append' as follows:

writetable(T,'Results.xlsx','UseExcel', true, 'WriteMode','Append')

4 个评论
显示 2更早的评论隐藏 2更早的评论

Simon Chan 2021-8-28

在 MATLAB Online 中打开

You may refer to the following code to modify yours, there is no need for the if-else loop unless I misunderstand your issue.

for k = 1:10
    Dtime = datetime('now','TimeZone','local','Format','dd-MMM-yyyy HH:mm:ss');        
    Name = {'Alex'};                                                                    % Name
    DateAndTime = Dtime;                                                                % Date and time                      
    ConfidenceScore = 95;                                                               % Confidence Score
    pause(1);                                               % Just add more time to show time difference
    T = table(Name, DateAndTime, ConfidenceScore);          % Create the table
    writetable(T,'Results.xlsx','UseExcel', true, 'WriteMode','Append')                 % Write to excel
end

Then, check the result using funtion readtable as follows:

readtable('Results.xlsx')

Resut like that:

ans =
  10×3 table
      Name          DateAndTime         ConfidenceScore
    ________    ____________________    _______________
    {'Alex'}    28-Aug-2021 20:59:29          95       
    {'Alex'}    28-Aug-2021 20:59:30          95       
    {'Alex'}    28-Aug-2021 20:59:32          95       
    {'Alex'}    28-Aug-2021 20:59:33          95       
    {'Alex'}    28-Aug-2021 20:59:34          95       
    {'Alex'}    28-Aug-2021 20:59:36          95       
    {'Alex'}    28-Aug-2021 20:59:38          95       
    {'Alex'}    28-Aug-2021 20:59:39          95       
    {'Alex'}    28-Aug-2021 20:59:40          95       
    {'Alex'}    28-Aug-2021 20:59:42          95            

Alex Zai 2021-8-28

在 MATLAB Online 中打开

Thanks for your answer. I understood your answer. But I want to do that...

Name = {'Alex'}; %may be other name in every time. DateAndTime = Dtime; %may be another time. ConfidenceScore = 95; %also another score (different)

This 3 value are updating when i run code (means may be different).

So I want to add these updated (news) value into existing table after the last row. Here example code I was a little change. Hope you will understand and you can help me.

I already run this example code and appeared error -

An error occurred when concatenating the table variable 'DateAndTime' using VERTCAT.

Caused by:

Error using datetime/vertcat (line 1394)

Unable to concatenate a datetime array that has a time zone with one that does not have a time

zone.

Dtime = datetime('now','TimeZone','local','Format','d-MMM-y HH:mm:ss Z');
Name = {'Alex'};
DateAndTime = Dtime;
ConfidenceScore = 80;
if isfile('Results.xlsx')
    T1 = readtable('Results.xlsx');
    Tnew = table(Name, DateAndTime, ConfidenceScore);
    T = [T1; Tnew];
    writetable(T, 'Results.xlsx','WriteMode','Append');
else
    T = table(Name, DateAndTime, ConfidenceScore);
    writetable(T,'Results.xlsx', 'WriteMode','Append');
end                             

Simon Chan 2021-8-29

在 MATLAB Online 中打开

If you still want to use the if-else-end, there is no need to use 'Append' for writetable.

The issue of having an error is simply due to the datetime format read back from the excel file which is not the same as the datetime format of the variable Dtime.

So you may try to convert the datetime format once again to make sure they are consistenct before writing to excel. (Added one line after readtable as shown below)

Dtime = datetime('now','TimeZone','local','Format','d-MMM-y HH:mm:ss Z');
Name = {'Alex'};
DateAndTime = Dtime;
ConfidenceScore = 80;
if isfile('Results.xlsx')
    T1 = readtable('Results.xlsx');
    T1.DateAndTime = datetime(T1.DateAndTime,'TimeZone','local','Format','d-MMM-y HH:mm:ss Z');
    Tnew = table(Name, DateAndTime, ConfidenceScore);
    T = [T1; Tnew];
    writetable(T, 'Results.xlsx');
else
    T = table(Name, DateAndTime, ConfidenceScore);
    writetable(T,'Results.xlsx');
end     