How to read and make calculations with excel data through matlab app designer

Hello, i'm a newbbie in matlab!

I have an assignment where I have to use data from an excel sheet, make some calculations and show the result inside the application. These calculations, include finding the minimum, maximum, median and average value from a column of values inside the Excel file. I would also like to perform standard deviation and find the range of the values. I have created an 'open file' button that opens a 'uigetfile' box, letting the user choose the .xlsx file and then saves the path of that file in the text of a label. Then a different button named 'Minimum' should find the minimum value through the column with the values inside the specific .xlsx file. My problem is that, as much as I have researched, I cannot get Matlab to properly read the file, let alone finding the minimum, maximum, median etc. In the code below, I'm trying to test it by printing the excel file data in a label and a text area. (By the way, I'm using Matlab version R2017a and an old 2010 excel version).

Here is my excel file data:

3.7

0.1

3.9

3

4.4

3.1

7.9

5.8

6.2

4.5

4.3

7.1

3.4

6.1

3.8

3.7

3.3

3

3.3

5.2

5.5

7.4

4.8

5

3.7

3.5

3.2

4

5.3

6.9

8

2.8

7

4.3

2.2

4.5

2.6

4

5.3

7.1

4.2

3.1

3.8

3.3

3.3

5.3

5.5

3.5

2.8

7.4

1.5

Here is the code for the 'open file' button:

%         [baseName, folder] = uigetfile({'*.xlsx'},'Choose File...');
          fullFileName = fullfile(folder, baseName);
          app.pathLabel.Text = fullFileName;
          app.pathchecker.Text = int2str(fullFileName);

And here is the code i have so far for the 'Minimum' button:

    %       filename = (app.pathLabel.Text);
            col1cell1 = xlsread(filename, 'A1');
            app.text_output.Value = col1cell1;
            app.label_output.Text = col1cell1;

The latter one gives an error saying: "Error using xlsread (line 260) Worksheet 'A1' not found.", although clearly an 'A1' cell does exist in my excel file.

Can anyone provide some help ? Thank you for your time !

[Francheska Payongayong]

Hi, what were the components of app.pathLabel and app.pathchecker? And what were they used for? Thank you!

[Vas Mar]

Hi. "app.pathLabel" saves the path of the excel (.xlsx) file that the user chose in the openfile dialog. Then the label is displayed in the GUI, pretty much just showing the User which file is being used.

The "app.pathchecker" is another label that I used to overcome a problem I had. I wanted to perform a check, right before each of my app's 'calculation' functions is executed, that a file has been selected, and if not, an error message should be displayed.

By 'a file being selected', I mean that the 'app.pathLabel' has a value saved in it, which has to be the path for a '.xlsx' document. So I tried to create a simple "if" statement, where if the 'app.pathLabel' had a value (meaning a path) stored in it, then the code would be executed. If however the stored value in the 'app.pathLabel' was empty, the code would only display an error.

However, the code:

fullFileName = fullfile(folder, baseName);

which is used to save the actual document's path as a value, in the 'app.pathLabel', would save a '\' character when the User cancels the openfile dialog instead of choosing a document. So normally, I would create an 'if' statement to filter out this action and display an error, as such:

if app.pathLabel.Text ~= '\'
  % code where an actual document has been selected and the code continues normally
end
if app.pathLabel.Text == '\'
  % code does not continue and an error msg is displayed
end

The problem is that for whatever reason (that I did not manage to find after some good time smashing my head on my desk), the first comparisson:

if app.pathLabel.Text ~= '\'

would just not compile, giving me an error for the '~=' symbols specifically. So I had to find some other way to do the check.

That is why I created the 'app.pathchecker' label, which basically converts the selected document's path from value to string, by using:

app.pathchecker.Text = int2str(fullFileName);

Now, I tested and found out that '\' value, gives precisely '0 92 0' when converted to a string. So I created a 'string compare' method which is used in before each of my app's function, instead of the 'if' statement I said before. Here it is:

s1 = '0  92   0';
s2 = app.pathchecker.Text;
comparison = strcmp(s1,s2);
if comparison == 0
    %(path exists, code executes normally)

end
if comparison == 1
     %(path does NOT exist, code does not execute, error                      
     msg is displayed)
end

and that's it.

[Vas Mar]

I analyzed my coding pattern as much as I could so you could understand it as easily as possible. I hope this helped!

Answer 1

[Ameer Hamza]

The second input to xlsread() is the sheet number or the range in a complete format such as 'A1:A5'. Change the line like this

col1cell1 = xlsread(filename, 1);

[Vas Mar]

Hi, thanks for the answer. I have a problem understanding how xlsread() works. I have a question about it now: By what you said, can I 'read' a full column by specifying the first and the last cell of e.g. the 'A' column (by writing 'col1cell1 = xlsread(filename, A1:A51);' ???

I just tried to replace 'col1cell1 = xlsread(filename, 'A1');' with 'col1cell1 = xlsread(filename, A1:A51);' and I got this error: "Undefined function or variable 'A1'.".

I also tried: "col1cell1 = xlsread(filename, 1:A51);" and i got "Undefined function or variable 'A51'.",

whereas, 'col1cell1 = xlsread(filename, 1:51);' gave "Error using xlsread (line 149) Sheet argument must be a character vector or an integer."...

Can you provide any more help ??? Thanks again !

[Ameer Hamza]

you need to specify the range as char array inside apostrophe ( ' ). For example

col1cell1 = xlsread(filename, 1, 'A1:A51');

will read cell A1:A51 from sheet 1.

[Vas Mar]

This made a lot of sense to me but it gives this error: "Error using matlab.ui.control.TextArea/set.Value (line 87) 'Value' must be a character vector or a vector cell array of character vectors, such as 'Speedometer' or {'line1'; 'line2'; 'line3'}'."

I also tried: col1cell1 = char2str(xlsread(filename, 1, 'A1:A51')); But it gives another error: "Undefined function 'char2str' for input arguments of type 'double'."

[Ameer Hamza]

This is not an error with the reading of xls file. This error is happening because col1cell1 is a matrix. You can display one element from the matrix, not the entire matrix.

app.text_output.Value = num2str(col1cell1(1));
app.label_output.Text = num2str(col1cell1(1));

[Vas Mar]

You are right. It finally gives a result, thank you ! But the result is only the first cell of the excel file (which is simply '3.7'). What do i need to look into to make the rest appear as a list(each cell between the previous one) ?

Writing this: "app.label_output.Text = num2str(col1cell1(1),col1cell1(2));" returns "4" which I'm not sure where it came from (P.S.: It's the number of the cell A38 in my excel file, but I don't know if that is related).

Moreover, how can I perform a calculation between all the cells of the A column ? Like for example, in order to find the average (which in excel i can find by typing "=AVERAGE(A1:A51)" ?

I know I've asked too much already and I hope I'm not putting you in trouble... Thanks for each reply though, they are quite helpful.

[Ameer Hamza]

text edit field can only display one value at a time. If you want to display several values you will need some other App designer component such as a table or a list.

Yes, you can do a mathematical calculation on the data you read from excel file. The data is in col1cell1 variable. To calculate average, MATLAB has a builtin function call mean(). So

value = mean(col1cell1);
app.text_output.Value = num2str(value);

will display the mean value in the edit box. Similarly, you can search for several other built-in functions from MATLAB documentation. https://www.mathworks.com/help/matlab/index.html

[Vas Mar]

Thank you so much for everything. These seem to be working. I will now work with all your info and might post again later.

[Ameer Hamza]

You are welcome.

[Vas Mar]

I can confirm, for everyone else who might want to know, that this works like a charm for more mathematical calculations as: minimum, maximum, average(mean), range, variance, standard deviation(std), sum and median.

Process goes same way:

value = min(col1cell1); value = max(col1cell1); value = mean(col1cell1); value = range(col1cell1); value = var(col1cell1); value = std(col1cell1); value = sum(col1cell1); value = median(col1cell1);