Azzera filtri
Azzera filtri

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

24 visualizzazioni (ultimi 30 giorni)
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)
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.
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
function ProducedataButtonPushed(app, event)
app.n = 1001; %inputted the amount of data points manually
for index = 1:app.n
app.Z_(index) ;
x_current = num2str(app.Axes(index));
app.showEditField.Value = x_current;
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
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';
xlRange_Y = 'G2:G202';
xlRange_Z = 'H2:H202';
%% Reading values from the excel sheet
sheet_2 = 'Conclusion';
xlRange_LAC = 'M8';
Repeatbility = readmatrix(app.filename,'Sheet',sheet_2,'Range',xlRange_LAC,'OutputType','char')
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
z < 0.44;
% once z has reached a vlue of less than 0.44 I want the program to stop
% running
yes = msgbox("Data was successfully saved","Success");
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!.
  3 Commenti
dpb il 11 Ott 2023
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 il 11 Ott 2023
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.

Accedi per commentare.

Risposta accettata

dpb il 11 Ott 2023
Modificato: dpb il 11 Ott 2023
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.
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...
  6 Commenti
dpb il 12 Ott 2023
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 il 13 Ott 2023
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.

Accedi per commentare.

Più risposte (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by