Diary of a Trainee Electronics Engineer: April 2018
Using MATLAB to read an .xls file for graphical user interface
For my Final Year Project at university I need to use MATLAB to process data in the graphical user interface (GUI), so in last month’s post I looked at what MATLAB can be used for and getting to grips with some of the basics.
The image above shows basic calculations completed in MATLAB and then creating a variable, this was discussed in Diary of a Trainee Electronics Engineer: March 2018.
As I mentioned in a previous post, for my university final year project I will be looking at Energy Monitoring and Usage Management in Machine Tools. The aim of this project is to look at the energy consumed by a CNC machine via the various different processes. The tool used for this measurement is the Multicube950, using this the data can be downloaded in an .xls file.
The meter outputs many different readings such as:
- Phase Volts
- Line Volts
- Amps Demand
- Volts Demand
- Peak Amps
- Peak Volts
- KW Demand
- Power Factor …. Etc.
Not all the data which is outputted is relevant. For the purpose of this post we will look at comparing the KW against time to determine the amount of energy consumed at each stage. Since I would only be looking at these two columns of data (column B and L) I decided to temporarily delete column A and everything between and including C to K so I had the two columns I was looking at side by side.
When opening a file in MATLAB the first thing you need to do is make sure the file you want to open in MATLAB is saved in the MATLAB directory on your computer. For example, mine is – Chelsea > This PC > Documents > MATLAB.
I had to do some online reading to figure out what I needed to include in the script to output a graph from the data in the .xlsx file. To get started I needed to specify the name of the file which I wanted MATLAB to look at, to do this I used:
FileName = ‘Machine_Example.xlsx’
I then wrote a line which would instruct MATLAb to read the contents of the file which I stored as FileName.
A = xlsread(FileName);
Next, I needed to specify which columns in the file I wanted to look at and specify which axis I wanted each on, since removing the columns which I didn’t need for this my columns were the first and second ( A and B in excel). Since column A is time and B is the KW reading I wanted to plot time on the x-axis and the readings on the y-axis.
X = a(:, 1);
Y = a(:, 2);
If we look in the workspace section of MATLAB we can see that the file has been logged, a is set to a 6515x124 .xls(x) file and then X and Y are both 6515x1 which is correct as above I specified to include all the rows and one column for each.
Above we can see that we have set which columns in the file we want to use as the X and Y values. In this line we say:
The X axis will = read specified file = ‘:’ include all the rows, and column 1 (time);
The Y axis will = read specified file = ‘:’ include all the rows, and column 2 (KW);
Since both X and Y have been specified we can use the ‘plot’ function to output a graph of this data.
After looking through the data I could see that the y-axis looked correct as well as the plot of the graph itself, however, the numbers along the x-axis didn’t make much sense to me. I figured this must be because of how the time was written in column 1. As the time was written as 12:03:45 it seems to have confused the formatting of the x-axis – I decided to recreate the graph from the same data to see what happened there.
In the excel graph plot I can see along the x-axis here it has plotted the time as I expected it would have in MATLAB. I’m not quite sure why MATLAB has outputted the graph like this, I think it requires some more reading to get to the bottom of it.