Formatting issue using readmatrix​/readcell/​readtable from an excel sheet in to MATLAB App Designer

I am devleoping an app which reads data from Matlab in to an excel sheet. Once one excel sheet has got enough data points I will ask the app to beign writing in to a new one and then also ask it to read some of the values from the excel sheet back in to MATLAB. The latter task is the porblem, I am very confident that the first two tasks work effecitvely. I will paste the whole code below however the issue is with reading from the excel sheet. I have tried a variety of different commands including: readmatrix/readcell/readtable, with all of them I encounter the same porblem which is when I read the values they are returned as either NaN or #DIV/0!. I have tried formatting the values that I read from the sheet differnelty hence single, double, character and string however that still produces one of the two results above. Here is my full code below with annotations:

function LoadmatlabdataButtonPushed(app, event)
            load('25_Cycle_data.mat','Axis_1_J5','Y_J5','Z_J5');
            app.Axes = Axis_1_J5;
            app.Y_= Y_J5;
            app.Z_ = Z_J5;
            yes = msgbox("Data was successfully saved","Success");
            %Here I am jsut loading exmaple data whereas once this app is
            %fully developed it will read all this data in 1 data point at
            %a time.
end

function ChooseFileLocationButtonPushed(app, event)
            app.savefile = uigetdir('\\fsvr\f\IDT Project Work');
            newfolder = cd(app.savefile);
            % changing the current folder location to the folder which my
            % documents are loacetd in 
end

function ProducedataButtonPushed(app, event)
%%
app.n = 1001; %inputted the amount of data points manually
for index = 1:app.n
    app.Axes(index);
    app.Y_(index);
    pause(0.10);
    app.Z_(index) ;
x_current = num2str(app.Axes(index));
app.showEditField.Value  = x_current;
pause(0.25)
index
u = index/201;
%o = index/80;
%% This section is filename/file number formatting 
if u == floor(u) && index > 200 %only once the data paoints has reached a value of 201 can they be written
 % in to the excel sheet and consequnetly at multiples of 201 as well to
 % cause the app to write it in to a new excel sheet.
if app.InputStartingFileNumberEditField.Value == 0 
        app.p = 0
elseif app.InputStartingFileNumberEditField.Value ~= 0 
        app.p = app.InputStartingFileNumberEditField.Value  
        app.p = (app.InputStartingFileNumberEditField.Value ) - 1
end
app.p = app.p + 1
app.og_filename = app.InputFileNameEditField.Value
source_input = char(app.og_filename)
source_s = string(source_input)
filenames = regexp(source_s,'\.','split')
dot = '.'
source = (filenames(1,1))
excel_s = filenames(1,2)
excel_c = strcat(dot,excel_s)
excel = char(excel_c)
underscore = "_"
q = string(app.p)
increment = strcat(underscore,q)
app.filename = strcat(source,increment,excel)
%% Writing the data to the excel sheet 
sheet = 'Results';
xlRange_Xt = 'D2:D202';
writematrix(app.Axes,app.filename,'Sheet',sheet,'Range',xlRange_Xt);
xlRange_Y = 'G2:G202';
writematrix(app.Y_,app.filename,'Sheet',sheet,'Range',xlRange_Y);
xlRange_Z = 'H2:H202';
writematrix(app.Z_,app.filename,'Sheet',sheet,'Range',xlRange_Z);
%% Reading values from the excel sheet
sheet_2 = 'Conclusion';
xlRange_LAC = 'M8';
Repeatbility = readmatrix(app.filename,'Sheet',sheet_2,'Range',xlRange_LAC,'OutputType','char')
pause(5)
app.LAC_Y = Repeatbility(1,1)
app.LAC_Z = Repeatbility(6,1)
app.Backlash = Repeatbility(22,1)
yd = num2str(app.LAC_Y)
zd = num2str(app.LAC_Z)
backlashd = num2str(app.Backlash)
app.LACYRepeatabilityEditField.Value = yd
app.LACZRepeatabilityEditField.Value = zd
app.BacklashEditField.Value = backlashd
try
z < 0.44;
% once z has reached a vlue of less than 0.44 I want the program to stop
% running 
break
catch
end
end
end
yes = msgbox("Data was successfully saved","Success");
        end
    end

I think the issue is that the MATLAB commands are reading the function in the formual box for that cell rather than the value that is caluated as a consequnece of the function. Hence, I have tried to read the values that I orginally wrote in to the spreadsheet and that worked fine because these values are just values in the formula box rather than a combination of excel functions and cell numbers.

Here is a screenshot of the page where I want to read the calculated values from:

The values in blue are the ones I want to read out from the excel sheet. However, I thought with that cell having had some formatting done to it I'd make the column M equal to those values as well to try and read that instead. Additionally, I tried using the VALUETOTEXT function for the column in N and then reading those values. Neither of those methods worked and it still returned either NaN or #DIV/0!.

[Kevin Holly]

Have you tried using the Import Tool? Is it possible to share an example file?

[dpb]

Simplify down to the basics w/o all the overhead of the app gui stuff first and get that to work...readtable, readmatrix and friends don't return formulas but the spreadsheet has to be set to recompute when new data are written. I've got several apps that update cell formulas and haven't seen such issues on returning results; are you sure you're converting the table values to numeric before writing them; that would explain reading back as nonnumeric if the data for the cells the formula references aren't numeric.

But, as @Kevin Holly says, we'd need a minimal functional example to be able to diagnose what's happening because w/o the spreadsheet content it's just not possible to know.

[Joseph]

Hi @dpb and @Kevin Holly,

Thank you both for your input it has been really helpful.

@dpb I think the issue is what you suggested that when I write the data to the spreadsheet it isn't recomputing and hence when I try to read the value there's no value ot read. I am certain of this becasuse I wrote the data to my spreadsheet (in one section of code), opened and closed it, then using readmatrix (in another section of code) I succesfully read the data in to matlab.

Please could you advise me on how to set my spreadsheet so that is automatically updates when I add data to it? I am also going to have a look online.

Thanks for your help.

Answer 1

[dpb]

There's an issue with Excel and writetable and friends -- I took an existing workbook that had four columns of numbers and added a fifth that was the sum of the four, then read that into MATLAB, added +1 to the first column and rewrote only the first four columns to the workbook. On opening the spreadsheet, the formula was updated, but reading it always returned the updated values in the first four columns but always the original sum in the fifth even though the formula was updated when opened the spreadsheet.

The problem is that those values are not recomputed until the spreadsheet is opened, and if that spreadsheet is not saved afterwards, then it reverts to the previous formula value in the column with the formula.

I had not uncovered this behavior in my app because it uses COM and saves and closes the file with ActiveX and that forces the update so the above symptom doesn't appear.

With the MATLAB high-level routines, that doesn't seem to be happening; I think it is worthy of a bug report.

OTOMH, the only way I can think of to ensure the sheet is updated would be to use activex something like

excel=actxserver('Excel.Application');                          % open actx connection
fname=fullfile(wd,'yourfile.xlsx');                             % MUST use fully-qualified filename
Workbook = excel.Workbooks.Open(fname);                         % open it
excel.ActiveWorkbook.Save;                                      % save it
excel.ActiveWorkbook.Close(0);                                  % and close it, no user prompt
excel.Quit, delete(excel), clear excel                          % terminate, clean up

after you've done the other updates.

My experience has been you can mix the two ways to access an Excel spreadsheet as long as you don't try to create an extremely tight loop with the MATLAB highlevel routines -- like iterating over each cell in a big workbook. That becomes very slow and can in larger cases, actually hang the system forcing one to terminate the Excel process externally. Needless to say, that's unacceptable for a user app, so is to be avoided; if you must work on a file in such a manner (best to avoid in lieu of matrix operations inside MATLAB and then update the entire sheet), then ActiveX is the only way out.

ADDENDUM

I did just verify that even with the "Automatic" update option checked for the workbook, the write to the fields with the MATLAB highlevel routines apparently doesn't trigger the recalculation then before the file is closed; only on reopening does it appear that it is recognized the "changed" flag is set and the worksheet recalculated. I also did verify the above ActiveX sequence does cause recalculation and so a subsequent read of the file will produce the updated formula values just as if one had manually opened and saved the file.

I think this behavior worthy of a bug report...

[dpb]

ADDENDUM SECOND:

The one way to do it with only high level MATLAB functions is to always rewrite the cell containing the formula -- to do this need to use writecell with the 'UseExcel',1 optional parameter every time; when the cell itself is written, then it is updated as well. Apparently internally they're using only the affected range of the write.

[dpb]

I did go ahead and submit a "bug/feature" determination request...

[Joseph]

Hi @dpb,

Firstly thank you for all your help on this issue. Both methods/answers you gave have worked perfcetly well in MATLAB. I have been able to check this with a varity of excel sheets with different names saved in different places, so, I am very confident that both solutions provided do work reall well.

However, when I try and translate it in to the app I am developing neither solution seems to work. I even included two separate buttons one for writng in to excel and another for reading from it, to imitate the run section button I have been using in normal MATLAB:

        % Button pushed function: writeexceldataButton
        function writeexceldataButtonPushed(app, event)
%% Writing the data to the excel sheet 
sheet = 'Results';
xlRange_Xt = 'D2:D202';
writematrix(app.Axes,app.filename,'Sheet',sheet,'Range',xlRange_Xt,'UseExcel',true);
xlRange_Y = 'G2:G202';
writematrix(app.Y_,app.filename,'Sheet',sheet,'Range',xlRange_Y,'UseExcel',true);
xlRange_Z = 'H2:H202';
writematrix(app.Z_,app.filename,'Sheet',sheet,'Range',xlRange_Z,'UseExcel',true);
yes = msgbox("Data was successfully saved","Success");
%% Reading values from the excel sheet
        end

        % Button pushed function: readexceldataButton
        function readexceldataButtonPushed(app, event)
%% Reading values from the excel sheet
sheet_2 = 'Conclusion';
xlRange_LAC = 'M8';
Repeatbility = readmatrix(app.filename,'Sheet',sheet_2,'Range',xlRange_LAC,'UseExcel',true)
pause(1)
app.LAC_Y = Repeatbility(1,1)
pause(1)
app.LAC_Z = Repeatbility(6,1)
pause(1)
app.Backlash = Repeatbility(17,1)
pause(1)
app.YEditField.Value = app.LAC_Y;
app.LACZRepeatabilityEditField.Value = app.LAC_Z;
app.BacklashEditField.Value = aapp.Backlash;
yes = msgbox("Data was successfully saved","Success");
        end
        end

I have been checking the variables in the command window and all of them are still returned as NaN. I have checked every other aspect of my app to be certain that it is not an issue with that and that doesn't seem to be the case.

Can you think of any reason as to why in App Designer I can't make these fucntions work like they do in normal MATLAB?

I am happy to share any infomration that will assist you in this.

Thanks a lot

Joe

[dpb]

So the workbook is supposed to update something on the 'Conclusion' worksheet when the 'Results' worksheet is modified, I gather?

What I don't see in your code above is anything that does what we outlined above that the only way the formulas in other cells were updated when the cell(s) upon which they were dependent was when either the file was reopened and saved or the cell formula(s) themselves were rewritten. All you've shown is what we had illustrated has an issue that only written cells are refreshed by the high level MATLAB functions.

I think your writeexceldataButtonPushed function needs that functionality included in it.

I presume that if you manually update and save the file and then read it, then you get expected results, correct? That you get NaN or something else nonnumeric would indicate the cell content at the time its being read isn't numeric -- that could also be the case if there were an error in the cell formulas in the cell(s) being read.

For anybody here to debug the details of the specific app beyond the symptoms we've discussed here that are generic, would have to have the spreadsheet and enough code to be able to run a small example app that would reproduce the problem.

[dpb]

ADDENDUM: I have received a response from TMW on the problem report I submitted -- in it, they claim their tests work if 'UseExcel',1 is used with writematrix(). I've yet to have had time to test that out and I surely think I had given that a try earlier and it didn't solve the problem...but I didn't save all the testing so will have to redo that to prove one way or t'other.

The official response is that with default of false for 'UseExcel', then it is expected behavior that only written cells will update--that makes a certain amount of sense for systems without Excel installed for which it can't start a session even it wanted to, but it probably still doesn't meet what most users expectations would be off the cuff. The tech support guy will put in comments to the documentation team to make the behavior be more explicitly explained in the doc that would help (those who bother to read the doc, anyway).

I'm still not fully convinced, but will be a while before I can run some more tests given other commitments at the moment...

[dpb]

ADDENDUM:

I added a second sheet to my test case workbook that references the cell in the first sheet that contains the formula and followed the MW suggested solution of using 'UseExcel', 1 with writematrix and updated only one of the numeric cells on which the first sheet formula is dependent. The result then returned for both Sheet1 and Sheet2 was the expected result with the formula value returned showing it had been updated. Hence, it appears that if do create the instance of Excel when writing the updated date, the result will be available immediately upon the next read.

Hence, it would appear that if that result isn't happening, there's something amiss in either the code/data that is being written to Excel or there's a problem inside the workbook itself causing the returned value to not be what you expect. As noted above, to diagnose that would require having access to the failing workbook to test.